Execution of some queries deeply depend on variables where is not always the best to reuse same execution plan for all executions. For those queries I want to avoid using literals and inefficient execution plans. Also, I want to use SQL plan baselines as I have automatic capturing enabled.
Question is, can I make Adaptive Cursor Sharing to work with SQL Plan Baselines without changing query?
Activate bind awareness for every execution to avoid inefficient execution plans?
I want to avoid even one inefficient execution or wait for ACS kick in automatically, because this one lousy execution could be potentially big problem.
For demo case I’m using 1000000 rows table with skewed data:
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 select owner, count(*) from big_table group by owner; OWNER COUNT(*) ---------- ---------- MDSYS 1 PUBLIC 499999 SYS 499999 ORDSYS 1 create index IDX_OWNER on BIG_TABLE(owner); begin dbms_stats.gather_table_stats(ownname=>'MSUTIC',tabname=>'BIG_TABLE',cascade=>TRUE, estimate_percent=>100, method_opt=>'for columns size 4 owner'); end; /
This is my test query.
SQL> var own varchar2(10);
SQL> exec :own := 'SYS';
select owner, sum(object_id)
from big_table
where owner = :own
group by owner;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));
SQL_ID 5cdba9s9mkag7, child number 0
-------------------------------------
select owner, sum(object_id) from big_table where owner = :own group by
owner
Plan hash value: 2943376087
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3552 (100)| |
| 1 | SORT GROUP BY NOSORT| | 499K| 9277K| 3552 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | BIG_TABLE | 499K| 9277K| 3552 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"=:OWN)
For a first execution bind sensitivity is enabled because I have gathered statistics with histogram.
select sql_id
, is_bind_aware
, is_bind_sensitive
, is_shareable
, plan_hash_value
from v$sql
where sql_id = '5cdba9s9mkag7';
SQL_ID I I I PLAN_HASH_VALUE
------------- - - - ---------------
5cdba9s9mkag7 N Y Y 2943376087
To enable bind awareness I want to insert BIND_AWARE hint without changing query.
I will use SQL Patch for this:
SQL> begin
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => 'select owner, sum(object_id)
from big_table
where owner = :own
group by owner',
hint_text => 'BIND_AWARE',
name => 'bind_aware_patch');
end;
/ 2 3 4 5 6 7 8 9 10
PL/SQL procedure successfully completed.
Now let’s check execution and bind awareness for the query.
SQL> var own varchar2(10);
SQL> exec :own := 'SYS';
select owner, sum(object_id)
from big_table
where owner = :own
group by owner;
SQL_ID 5cdba9s9mkag7, child number 0
-------------------------------------
select owner, sum(object_id) from big_table where owner = :own group by
owner
Plan hash value: 2943376087
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3552 (100)| |
| 1 | SORT GROUP BY NOSORT| | 499K| 9277K| 3552 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | BIG_TABLE | 499K| 9277K| 3552 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"=:OWN)
Note
-----
- SQL patch "bind_aware_patch" used for this statement
select sql_id
, is_bind_aware
, is_bind_sensitive
, is_shareable
, plan_hash_value
from v$sql
where sql_id = '5cdba9s9mkag7';
SQL_ID I I I PLAN_HASH_VALUE
------------- - - - ---------------
5cdba9s9mkag7 Y Y Y 2943376087
We have note that SQL patch is used and we have bind awareness enabled. For every query execution, during hard parse, Oracle will peak variable and calculate efficient execution plan accordingly. At least, I would expect this.
Let’s try with another variable - will Oracle alter execution plan.
SQL> var own varchar2(10);
SQL> exec :own := 'MDSYS';
select owner, sum(object_id)
from big_table
where owner = :own
group by owner;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));
SQL_ID 5cdba9s9mkag7, child number 1
-------------------------------------
select owner, sum(object_id) from big_table where owner = :own group by
owner
Plan hash value: 1772680857
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY NOSORT | | 1 | 19 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 19 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OWNER | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"=:OWN)
Note
-----
- SQL patch "bind_aware_patch" used for this statement
select sql_id
, is_bind_aware
, is_bind_sensitive
, is_shareable
, plan_hash_value
from v$sql
where sql_id = '5cdba9s9mkag7';
SQL_ID I I I PLAN_HASH_VALUE
------------- - - - ---------------
5cdba9s9mkag7 Y Y Y 2943376087
5cdba9s9mkag7 Y Y Y 1772680857
Notice how Oracle changed execution plan and now we have two plans for specified sql text.
Capture SQL plans from cursor cache to create baseline.
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '5cdba9s9mkag7');
END;
/
We have two ACCEPTED plans saved for this query which Oracle will consider during execution, and SQL patch forcing bind awareness.
set lines 200 col sql_handle for a25 col plan_name for a35 select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_handle='SQL_f02626d2f3cad6cc'; SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------------- ----------------------------------- --- --- --- SQL_f02626d2f3cad6cc SQL_PLAN_g09j6ubtwppqc69a8f699 YES YES NO SQL_f02626d2f3cad6cc SQL_PLAN_g09j6ubtwppqcaf705ad7 YES YES NO
Now we will perform test to check will Oracle alter execution plan on variable value.
SQL> var own varchar2(10);
SQL> exec :own := 'SYS';
select owner, sum(object_id)
from big_table
where owner = :own
group by owner;
OWNER SUM(OBJECT_ID)
-------------------------------- --------------
SYS 7.5387E+10
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));
SQL_ID 5cdba9s9mkag7, child number 0
-------------------------------------
select owner, sum(object_id) from big_table where owner = :own group by
owner
Plan hash value: 2943376087
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3552 (100)| |
| 1 | SORT GROUP BY NOSORT| | 499K| 9277K| 3552 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | BIG_TABLE | 499K| 9277K| 3552 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"=:OWN)
Note
-----
- SQL patch "bind_aware_patch" used for this statement
- SQL plan baseline SQL_PLAN_g09j6ubtwppqcaf705ad7 used for this statement
Oracle used SQL patch and SQL plan baseline.
What if I change variable value.
SQL> var own varchar2(10);
SQL> exec :own := 'MDSYS';
select owner, sum(object_id)
from big_table
where owner = :own
group by owner;
OWNER SUM(OBJECT_ID)
-------------------------------- --------------
MDSYS 182924
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'TYPICAL'));
SQL_ID 5cdba9s9mkag7, child number 1
-------------------------------------
select owner, sum(object_id) from big_table where owner = :own group by
owner
Plan hash value: 1772680857
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT GROUP BY NOSORT | | 1 | 19 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1 | 19 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_OWNER | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"=:OWN)
Note
-----
- SQL patch "bind_aware_patch" used for this statement
- SQL plan baseline SQL_PLAN_g09j6ubtwppqc69a8f699 used for this statement
Oracle immediately changed execution plan and used different SQL plan baseline.
At the end I have original query with bind variables, I have SQL plan baselines captured, and I’m using powerful ACS feature to have efficient plans for different variables.
Thank you for this share ,
ReplyDeleteFoued