This will be the extension of the previous
post, incase if you haven’t gone through that, please read that
before to proceed here.
In continuing our discussion with what can cause
plans to differ- we are going to see how setting cursor_sharing
= FORCE can cause difference in plans.
Cursor_Sharing
= FORCE:
By setting the cursor_sharing
= FORCE, we are asking Oracle to replace the literal values with system
generated bind variables. The intent is to reduce the number of cursors
generated in the shared pool.
Let’s take the previous
example, and replace the bind variable with literal value and see
what happens when cursor_sharing set to force and we use the explain plan
command.
demo@ORA12C>
alter session set cursor_sharing = force;
Session
altered.
demo@ORA12C>
select max( object_id ) from t where x = 1;
MAX(OBJECT_ID)
--------------
16
demo@ORA12C>
select * from table( dbms_xplan.display_cursor(format=>'allstats last') );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID ffkynd1uq7zq7, child number 0
-------------------------------------
select max(
object_id ) from t where x = :"SYS_B_0"
Plan hash
value: 1339972470
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | | 1 |
| 1 |
SORT AGGREGATE
| | 1 |
1 | 1 |
| 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 13 | 1 |
|* 3 |
INDEX RANGE SCAN
| T_IDX | 1 | 13 |
1 |
---------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
3 -
access("X"=:SYS_B_0)
20 rows
selected.
demo@ORA12C>
explain plan for
2
select max( object_id ) from t where x = 1;
Explained.
demo@ORA12C>
select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
Plan hash
value: 1339972470
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 8 | 2
(0)|
| 1 |
SORT AGGREGATE |
| 1 | 8 | |
| 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| T |
13 | 104 | 2
(0)|
|* 3 |
INDEX RANGE SCAN
| T_IDX | 13 | |
1 (0)|
-----------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
3 -
access("X"=1)
15 rows
selected.
demo@ORA12C>
The plan is same in both the places, but if you
look at the predicate information under both the plans, you will notice that
explain plan command did not do the literal replacement to system generated
bind variables.
So why no literal replacement for explain plan
command?
The cursor generated by explain plan command are
not sharable by design, since the cursor is not being shared there is no point
in doing literal replacement that would allow the cursor to be shared.
Therefore explain plan command doesn’t replace the literals.
To show the explain plan command cursor’s are not
shared. Here is a quick demo for it.
demo@ORA12C>
col sql_text format a30
demo@ORA12C>
select sql_id,sql_text,child_number,executions
2 from
v$sql
3
where lower(sql_text) like 'explain plan
for%select%max(%object_id%)%from%t%where%x%=%1%';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS
-------------
------------------------------ ------------ ----------
d45wh8my5c1td
explain plan for select max( o
0 1
bject_id ) from t where x = 1
demo@ORA12C>
explain plan for
2
select max( object_id ) from t where x = 1;
Explained.
demo@ORA12C>
explain plan for
2
select max( object_id ) from t where x = 1;
Explained.
demo@ORA12C>
explain plan for
2
select max( object_id ) from t where x = 1;
Explained.
demo@ORA12C>
select sql_id,sql_text,child_number,executions
2 from
v$sql
3
where lower(sql_text) like 'explain plan
for%select%max(%object_id%)%from%t%where%x%=%1%';
SQL_ID SQL_TEXT CHILD_NUMBER EXECUTIONS
-------------
------------------------------ ------------ ----------
d45wh8my5c1td
explain plan for select max( o 0 1
bject_id ) from t where x = 1
d45wh8my5c1td
explain plan for select max( o 1 1
bject_id ) from t where x = 1
d45wh8my5c1td
explain plan for select max( o 2 1
bject_id ) from t where x = 1
d45wh8my5c1td
explain plan for select max( o 3 1
bject_id ) from t where x = 1
demo@ORA12C>
For explain plan version of the statement, no
literal replacement occurred and each execution created a new child cursor
(0,1,2,3..) demonstrating that no cursor sharing occurs with the explain plan
command.
So what is the big deal if we ended up with few
extra cursors?
The big deal is if you want to use the plan
stability features like SQL
Plan baselines – then we won’t see the effect of these feature with
explain plan when cursor_sharing is set to force.
·
Assuming we created
a sql plan baseline for a statement with system generated bind variable
:SYS_B_0
· But then check
which plan will be used with an explain plan command – no literal replacement
occur, therefore no corresponding baseline will be found for the statement
demo@ORA12C>
create table t as select * from all_objects;
Table created.
demo@ORA12C>
set serveroutput off
demo@ORA12C>
alter session set cursor_sharing=force;
Session
altered.
demo@ORA12C>
select object_name from t where object_id = 42;
OBJECT_NAME
--------------------
I_ICOL1
demo@ORA12C>
select * from table( dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 6236y5tvdy73z, child number 0
-------------------------------------
select
object_name from t where object_id = :"SYS_B_0"
Plan hash
value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
312 (100)| |
|* 1 |
TABLE ACCESS FULL| T | 1 |
40 | 312 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:SYS_B_0)
18 rows
selected.
demo@ORA12C>
variable n number
demo@ORA12C>
begin
2
:n := dbms_spm.load_plans_from_cursor_cache(
3 sql_id
=>'6236y5tvdy73z',
4
plan_hash_value=>'1601196873');
5 end;
6 /
PL/SQL
procedure successfully completed.
demo@ORA12C>
print n
N
----------
1
demo@ORA12C>
select t2.*
2 from
dba_sql_plan_baselines t1,
3
table( dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name)
) t2;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle:
SQL_2691051741ce81e0
SQL text:
select object_name from t where object_id = :"SYS_B_0"
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name:
SQL_PLAN_2d4852x0wx0g094ecae5c
Plan id: 2498539100
Enabled:
YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From
dictionary
--------------------------------------------------------------------------------
Plan hash
value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| | 312 (100)| |
|* 1 |
TABLE ACCESS FULL| T | 1 |
40 | 312 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:SYS_B_0)
25 rows
selected.
So far we have created a table and baselined the
plan for the sqlid “6236y5tvdy73z”
Next build an index on the object_id column, then
re-run the same baselined sql and verify the plan to see if baselined plan got
used.
demo@ORA12C>
create unique index t_idx on t(object_id);
Index created.
demo@ORA12C>
select object_name from t where object_id = 42;
OBJECT_NAME
--------------------
I_ICOL1
demo@ORA12C>
select * from table( dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 6236y5tvdy73z, child number 0
-------------------------------------
select
object_name from t where object_id = :"SYS_B_0"
Plan hash
value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
312 (100)| |
|* 1 |
TABLE ACCESS FULL| T | 1 |
40 | 312 (15)| 00:00:01 |
--------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=:SYS_B_0)
Note
-----
- SQL plan baseline
SQL_PLAN_2d4852x0wx0g094ecae5c used for this statement
The above highlighted “notes” section confirms we
used the accepted plans from the plan baselines.
But when you do an explain plan for the same
statement, plans were different.
demo@ORA12C>
explain plan
2 for
3
select object_name
4 from
t
5 where
object_id = 42;
Explained.
demo@ORA12C>
select * from table( dbms_xplan.display );
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash
value: 2929955852
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 40 | 2
(0)| 00:00:01 |
| 1 |
TABLE ACCESS BY INDEX ROWID| T
| 1 | 40 |
2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN
| T_IDX | 1 | |
1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=42)
14 rows
selected.
When we run an explain plan for the same
statement (same everything including the literals) will trigger a hard parse,
because the cursor generated by an explain plan command are not sharable, since
the cursor’s isn’t shared there is no point in doing the literal replacement
that would allow the cursors to be shared. Therefore the explain plan command
does not replace literals, since no literal replacement no plan baseline for
that statement and hence the explain plan command shows the index access plan.
No comments:
Post a Comment