In order to demonstrate the problem and the fix we need to have something to play around with.
SQL> create table skew(c1 number(6), c2 char(20));
Table created.
SQL> insert into skew select 1,1 from dual connect by level <= 10000;
10000 rows created.
SQL> update skew set c1 = 2 where rownum <= 10;
10 rows updated.
SQL> create index skew_idx on skew(c1);
Index created.
SQL> exec dbms_stats.gather_table_stats(null,'skew', -
> method_opt => 'for all columns size 2')
PL/SQL procedure successfully completed.
SQL> select c1, count(*) from skew group by c1;
C1 COUNT(*)
---------- ----------
1 9990
2 10
We now have an indexed table with skewed data in it with current object statistics in place including a histogram on the skewed column. Lets execute a query using a bind variable on the skewed column and see what the query optimizer expects and what execution plan it considers optimal.
SQL> var x number
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
9990
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 568322376
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| SKEW | 9990 |
-------------------------------------------
The example above shows that the query optimizer predicted the cardinality correctly and choosed the optimal execution plan based upon this information. It could do so because there is a histogram available to describe the data skew in the table. Now see what happens if we bind the value 2 and execute the query again:
SQL> exec :x := 2;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
10
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 568322376
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| SKEW | 9990 |
-------------------------------------------
Because the statement is not hard parsed again the same execution plan is used based on the prediction of 9990 rows. Because the query only returns 10 rows this execution plan is no longer optimal for the given value for the bind variable. If this query gets executed many times with this value of the bind variable we do have a performance problem for as long as this execution plan remains in the library cache. If this is indeed the case it might be beneficial to flush this cursor out of the shared pool. Starting with 10.2.0.4.0 this can be done using the PURGE procedure in the DBMS_SHARED_POOL package as demonstrated below:
SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
SQL> select address, hash_value from v$sqlarea
2 where sql_text = 'select count(c2) from skew where c1 = :x';
ADDRESS HASH_VALUE
-------- ----------
27308318 2934790721
SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c')
PL/SQL procedure successfully completed.
Because the DBMS_SHARED_POOL package is not installed at database creation time, it has to be installed manually as shown above. The PURGE procedure needs the ADDRESS and HASH_VALUE of the cursor being flushed and the flag ‘C’ to indicate that we are flushing a cursor. This knowledge comes out of the dbmspool.sql script. The ADDRESS and HASH_VALUE can be retrieved from V$SQLAREA as shown in the example. A successful execution of the PURGE procedure indicates that the parent cursor is gone among with its children. A next execution of the query will force a hard parse and the creation of a new execution plan as we can see below:
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
10
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 3493361220
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 10 |
| 3 | INDEX RANGE SCAN | SKEW_IDX | 10 |
---------------------------------------------------------
This time the query optimizer predicted the correct number of rows for the given value of the bind variable and selected the optimal execution plan for the given situation. The difficulty is of course to detect these situations before we can correct them. An indication could be a difference in the predicted number of rows and the actual number of rows in an execution plan, but therefore we need to set the STATISTICS_LEVEL parameter to ALL or add the GATHER_PLAN_STATISTICS hint to all possible affected statements which might be difficult to do. Once a possible affected statement has been found we can see the used bind value in the execution plan by using the PEEKED_BINDS options in the format specifier in the call to DBMS_XPLAN.
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
9990
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 3493361220
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 10 |
| 3 | INDEX RANGE SCAN | SKEW_IDX | 10 |
---------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 2
In this final example we bounded the value 1 again and executed the query which retrieved 9990 rows whilst the execution plan shows a prediction of only 10 rows. By using PEEKED_BINDS we receive extra information from DBMS_XPLAN telling us that this particular execution plan is based on the value 2 of the first bind variable in the statement which is named ‘:x’ and is a number data type.
Conclusion: By using the PURGE procedure in the DBMS_SHARED_POOL package we can flush a cursor out of the Library Cache when the execution plan causes performance problems due to an unlucky bind variable value. However this is only a temporary solution. The definitive solution is Adaptive Cursor Sharing which is introduced in Oracle11g.
SQL> create table skew(c1 number(6), c2 char(20));
Table created.
SQL> insert into skew select 1,1 from dual connect by level <= 10000;
10000 rows created.
SQL> update skew set c1 = 2 where rownum <= 10;
10 rows updated.
SQL> create index skew_idx on skew(c1);
Index created.
SQL> exec dbms_stats.gather_table_stats(null,'skew', -
> method_opt => 'for all columns size 2')
PL/SQL procedure successfully completed.
SQL> select c1, count(*) from skew group by c1;
C1 COUNT(*)
---------- ----------
1 9990
2 10
We now have an indexed table with skewed data in it with current object statistics in place including a histogram on the skewed column. Lets execute a query using a bind variable on the skewed column and see what the query optimizer expects and what execution plan it considers optimal.
SQL> var x number
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
9990
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 568322376
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| SKEW | 9990 |
-------------------------------------------
The example above shows that the query optimizer predicted the cardinality correctly and choosed the optimal execution plan based upon this information. It could do so because there is a histogram available to describe the data skew in the table. Now see what happens if we bind the value 2 and execute the query again:
SQL> exec :x := 2;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
10
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 568322376
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| SKEW | 9990 |
-------------------------------------------
Because the statement is not hard parsed again the same execution plan is used based on the prediction of 9990 rows. Because the query only returns 10 rows this execution plan is no longer optimal for the given value for the bind variable. If this query gets executed many times with this value of the bind variable we do have a performance problem for as long as this execution plan remains in the library cache. If this is indeed the case it might be beneficial to flush this cursor out of the shared pool. Starting with 10.2.0.4.0 this can be done using the PURGE procedure in the DBMS_SHARED_POOL package as demonstrated below:
SQL> @?/rdbms/admin/dbmspool
Package created.
Grant succeeded.
View created.
Package body created.
SQL> select address, hash_value from v$sqlarea
2 where sql_text = 'select count(c2) from skew where c1 = :x';
ADDRESS HASH_VALUE
-------- ----------
27308318 2934790721
SQL> exec sys.dbms_shared_pool.purge('&address, &hash_value','c')
PL/SQL procedure successfully completed.
Because the DBMS_SHARED_POOL package is not installed at database creation time, it has to be installed manually as shown above. The PURGE procedure needs the ADDRESS and HASH_VALUE of the cursor being flushed and the flag ‘C’ to indicate that we are flushing a cursor. This knowledge comes out of the dbmspool.sql script. The ADDRESS and HASH_VALUE can be retrieved from V$SQLAREA as shown in the example. A successful execution of the PURGE procedure indicates that the parent cursor is gone among with its children. A next execution of the query will force a hard parse and the creation of a new execution plan as we can see below:
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
10
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 3493361220
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 10 |
| 3 | INDEX RANGE SCAN | SKEW_IDX | 10 |
---------------------------------------------------------
This time the query optimizer predicted the correct number of rows for the given value of the bind variable and selected the optimal execution plan for the given situation. The difficulty is of course to detect these situations before we can correct them. An indication could be a difference in the predicted number of rows and the actual number of rows in an execution plan, but therefore we need to set the STATISTICS_LEVEL parameter to ALL or add the GATHER_PLAN_STATISTICS hint to all possible affected statements which might be difficult to do. Once a possible affected statement has been found we can see the used bind value in the execution plan by using the PEEKED_BINDS options in the format specifier in the call to DBMS_XPLAN.
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
SQL> select count(c2) from skew where c1 = :x;
COUNT(C2)
----------
9990
SQL> select *
2 from table(dbms_xplan.display_cursor(null,null,'basic rows peeked_binds'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(c2) from skew where c1 = :x
Plan hash value: 3493361220
---------------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 10 |
| 3 | INDEX RANGE SCAN | SKEW_IDX | 10 |
---------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 2
In this final example we bounded the value 1 again and executed the query which retrieved 9990 rows whilst the execution plan shows a prediction of only 10 rows. By using PEEKED_BINDS we receive extra information from DBMS_XPLAN telling us that this particular execution plan is based on the value 2 of the first bind variable in the statement which is named ‘:x’ and is a number data type.
Conclusion: By using the PURGE procedure in the DBMS_SHARED_POOL package we can flush a cursor out of the Library Cache when the execution plan causes performance problems due to an unlucky bind variable value. However this is only a temporary solution. The definitive solution is Adaptive Cursor Sharing which is introduced in Oracle11g.
In oracle9i R2, dynamic sampling query is introduced and it has ability to sample the table and collect the better statistics dynamically during the hard parse. This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled.
Oracle optimizer use the default statistic values when table does not have any statistics and dynamic sampling query feature is disabled.
How do we set Dynamic sampling parameter? There are couple of ways, we can set the parameter.
1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level.
1. We can set this in session level or instance level.
2. Dynamic sampling query hint in query level.
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING = 2;
Session altered.
SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;
System altered.
SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;
Session altered.
SQL> alter system set OPTIMIZER_DYNAMIC_SAMPLING = 2;
System altered.
SQL> select /*+ dynamic_sampling(emp 0) */ * from scott.emp;
When does dynamic sampling is useful?
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.
Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10.
1. It is useful when table data is drastically changing and statistic becomes stale very often.
2. Global temporary table is good place to use dynamic sampling feature.
Dynamic sampling Levels? Optimizer Dynamic Sampling has 11 levels between 0 to 10.
In Oracle9i R2, the default setting for dynamic sampling level is 1. At this setting, the optimizer will tend to use dynamic sampling only in some cases when a table is unanalyzed. This includes the case of global temporary tables without statistics but doesn’t ensure that dynamic sampling will be used against all unanalyzed tables. In Oracle Database 10g Release 1 and above, the default setting is 2. This setting ensures that the optimizer will have a good estimate whenever it optimizes a query that accesses a table with no statistics.
Before oracle8.1.6, When application sends a SQL, oracle first check the shared pool to see if there is an exact statement in the shared pool. If the exact statement is not found, then a hard parse is necessary to generate the executable form of the statement. Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared
Let me demonstrate CURSOR_SHARING=EXACT
SQL> select count(*),empno from emphist group by empno;
COUNT(*) EMPNO
---------- ----------
1 7369
1 7902
4194304 7934
SQL> create index idxemphist on emphist(empno);
Index created.
SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMPHIST',ESTIMATE_PERCENT
=> 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly explain
SQL> alter session set cursor_sharing='EXACT';
Session altered.
SQL> select count(ename) from emphist where empno=7934;
COUNT(ENAME)
------------
4194304
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2297 Card=1 Bytes=11
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMPHIST' (Cost=2297 Card=4193270
Bytes=46125970)
SQL> select count(ename) from emphist where empno=7902;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7902;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7369;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7369;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> set autotrace off
SQL> select sql_text
from v$sql
where sql_text like 'select count(ename) from emphist where empno=%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=7369
select count(ename) from emphist where empno=7902
select count(ename) from emphist where empno=7934
Conclusion : Oracle does not use bind variable and every unique SQL statement, execution plan will be generated. It forced to hardparse every uniqe SQL statement and it leads to consume lot of CPU cycles. Oracle can not hardparse hundreds of SQL statement concurrently and it end up waiting for shared pool. To overcome this problem, oracle introduced FORCE paramter to use bind variable.
In oracle8.1.6, Oracle introduced new parameter FORCE. This is accomplished by transforming the statement into a new one by replacing the literals with system generated bind variables. The problem with bind variables is that the optimizer cannot predict precise selectivity.
Using cursor_sharing = force will parse the FIRST sql statement loaded into memory and will use this execution plan for the subsequent similar sql statements. This might be a problem if the FIRST statement's literals have good selectivity but those of the subsequent statements don't. In this case the statements with poor selectivity will also use the index whereas a full table scan would perform better. However, if the firstly run statement doesn't have good selectivity and a full table scan is used, the subequent similar statements will also use FTS.
Let me demonstrate CURSOR_SHARING=FORCE
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152
SQL>
The below plan is extracted from tkprof output file. Optimizer use the FTS which is right choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)
Let us run the another query and see the execution plan.
SQL> select count(ename) from employee where empno=7902;
COUNT(ENAME)
------------
1
SQL>
The below plan is extracted from tkprof output file. Optimizer again use the FTS for this subsequent query which is bad choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL OBJ#(30800)
SQL> select sql_text from v$sql
where sql_text like 'select count(ename) from emphist where empno=%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"
SQL>
Conclusion : Oracle generate only one plan and use for all the SQL code. This would turn the hard parse into soft parse. It would require fewer resource. FORCE option is good when the query table is not skewed and all the queries require same execution plan. But in real world, this is not a good option.
To overcome this problem, In Oracle9i we can use CURSOR_SHARING=SIMILAR setting which makes the right plan based on the statistics.
Let me demonstrate CURSOR_SHARING=SIMILAR
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL>
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152
Let us run the query and see the actual execution plan. Optimizer use the FTS which is right choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)
Let us run the another query and see the actual execution plan.
SQL> select count(ename) from employee where empno=7902;
COUNT(ENAME)
------------
1
SQL>
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID OBJ#(30800)
1 INDEX RANGE SCAN OBJ#(30801) (object id 30801)
SQL> select sql_text
from v$sql
where sql_text like 'select count(ename)%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"
select count(ename) from emphist where empno=:"SYS_B_0"
SQL>
Optimizer use the Index range scan which is right choice. Optimizer makes the right choice based on the statistics.
Conclusion : Optimizer replace the literal with bind variable and check if it requires different plan for the query based on the current statistics. If so then, optimizer mark the bind variable as unsafe and store the value in the signature of the query. Next time, user submit the query with the same bind variable value, then oracle resuse the plan. Optimizer would use the same execution plan based on the statistics if user submit the same query with different literal .
SIMILAR option resolve the issues which we had in EXACT. Exact does not use bind variable. But here it is using bind variable. In FORCE option, optimizer use the same execution plan for same query with different literal. But SIMILAR option, it use the different plan for the same query with different literal values based on the current statistics.
Please read the below link if you need more info.
http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html
Let me demonstrate CURSOR_SHARING=EXACT
SQL> select count(*),empno from emphist group by empno;
COUNT(*) EMPNO
---------- ----------
1 7369
1 7902
4194304 7934
SQL> create index idxemphist on emphist(empno);
Index created.
SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMPHIST',ESTIMATE_PERCENT
=> 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly explain
SQL> alter session set cursor_sharing='EXACT';
Session altered.
SQL> select count(ename) from emphist where empno=7934;
COUNT(ENAME)
------------
4194304
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2297 Card=1 Bytes=11
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMPHIST' (Cost=2297 Card=4193270
Bytes=46125970)
SQL> select count(ename) from emphist where empno=7902;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7902;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7369;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> select count(ename) from emphist where empno=7369;
COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)
3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)
SQL> set autotrace off
SQL> select sql_text
from v$sql
where sql_text like 'select count(ename) from emphist where empno=%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=7369
select count(ename) from emphist where empno=7902
select count(ename) from emphist where empno=7934
Conclusion : Oracle does not use bind variable and every unique SQL statement, execution plan will be generated. It forced to hardparse every uniqe SQL statement and it leads to consume lot of CPU cycles. Oracle can not hardparse hundreds of SQL statement concurrently and it end up waiting for shared pool. To overcome this problem, oracle introduced FORCE paramter to use bind variable.
In oracle8.1.6, Oracle introduced new parameter FORCE. This is accomplished by transforming the statement into a new one by replacing the literals with system generated bind variables. The problem with bind variables is that the optimizer cannot predict precise selectivity.
Using cursor_sharing = force will parse the FIRST sql statement loaded into memory and will use this execution plan for the subsequent similar sql statements. This might be a problem if the FIRST statement's literals have good selectivity but those of the subsequent statements don't. In this case the statements with poor selectivity will also use the index whereas a full table scan would perform better. However, if the firstly run statement doesn't have good selectivity and a full table scan is used, the subequent similar statements will also use FTS.
Let me demonstrate CURSOR_SHARING=FORCE
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152
SQL>
The below plan is extracted from tkprof output file. Optimizer use the FTS which is right choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)
Let us run the another query and see the execution plan.
SQL> select count(ename) from employee where empno=7902;
COUNT(ENAME)
------------
1
SQL>
The below plan is extracted from tkprof output file. Optimizer again use the FTS for this subsequent query which is bad choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL OBJ#(30800)
SQL> select sql_text from v$sql
where sql_text like 'select count(ename) from emphist where empno=%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"
SQL>
Conclusion : Oracle generate only one plan and use for all the SQL code. This would turn the hard parse into soft parse. It would require fewer resource. FORCE option is good when the query table is not skewed and all the queries require same execution plan. But in real world, this is not a good option.
To overcome this problem, In Oracle9i we can use CURSOR_SHARING=SIMILAR setting which makes the right plan based on the statistics.
Let me demonstrate CURSOR_SHARING=SIMILAR
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL>
SQL> select count(ename) from employee where empno=7934;
COUNT(ENAME)
------------
2097152
Let us run the query and see the actual execution plan. Optimizer use the FTS which is right choice.
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)
Let us run the another query and see the actual execution plan.
SQL> select count(ename) from employee where empno=7902;
COUNT(ENAME)
------------
1
SQL>
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID OBJ#(30800)
1 INDEX RANGE SCAN OBJ#(30801) (object id 30801)
SQL> select sql_text
from v$sql
where sql_text like 'select count(ename)%';
SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"
select count(ename) from emphist where empno=:"SYS_B_0"
SQL>
Optimizer use the Index range scan which is right choice. Optimizer makes the right choice based on the statistics.
Conclusion : Optimizer replace the literal with bind variable and check if it requires different plan for the query based on the current statistics. If so then, optimizer mark the bind variable as unsafe and store the value in the signature of the query. Next time, user submit the query with the same bind variable value, then oracle resuse the plan. Optimizer would use the same execution plan based on the statistics if user submit the same query with different literal .
SIMILAR option resolve the issues which we had in EXACT. Exact does not use bind variable. But here it is using bind variable. In FORCE option, optimizer use the same execution plan for same query with different literal. But SIMILAR option, it use the different plan for the same query with different literal values based on the current statistics.
Please read the below link if you need more info.
http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html
Categories
- Backups
- Joins
- Performance Tuning
- Points to Ponder
- RMAN