|
|
|
|
|
|
The Power of Cursor Sharing in 9i
By: Szabo Imre
Sql statements differ only in literals can be forced to share a cursor. This is a new feature in 8i and can be switched on with the parameter setting cursor_sharing=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.
To overcome this problem in 9i we can use cursor_sharing=similar setting which makes the optimizer examine the histograms associated with the columns.
We can examine the execution plans of parsed sqls with the use of v$sql_plan view. I used this view to check the cursor sharing behavior.
Short example:
SQL> desc bigemp Name Null? Type ----------------------------------------- -------- ------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) PKCOL NUMBER 220000 rows are in the table. 110000 rows have unique values for PKCOL and 110000 rows have the value of 100 (half of the table). PKCOL column has a non-unique index with histogram.
The statements to be run are:
select * from bigemp where pkcol = 100;
select * from bigemp where pkcol = 1000;
Autotrace shows:
SQL> set autotrace traceonly explain SQL> select * from bigemp where pkcol = 100; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=814 Card=119276 Bytes=4890316) 1 0 TABLE ACCESS (FULL) OF 'BIGEMP' (Cost=814 Card=119276 Bytes=4890316)
Full table scan is advantageous because half of the table data will be returned.
SQL> select * from bigemp where pkcol = 1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=41)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIGEMP' (Cost=6 Card=1 Bytes=41)
2 1 INDEX (RANGE SCAN) OF 'PK_NU' (NON-UNIQUE) (Cost=4 Card=1)
Small amount of data (1 row), index access is beneficial.
Select statement to examine parsed sqls' execution plan:
select s.sql_text, p.operation, p.options, p.object_name, p.optimizer
from v$sql s, v$sql_plan p
where s.address = p.address
and s.hash_value = p.hash_value
and s.child_number = p.child_number
and sql_text like 'select * from bigemp%';
cursor_sharing = exact
SQL_TEXT -------------------------------------------------------------------------- OPERATION OPTIONS OBJECT_NAM OPTIMIZER ------------------------------ -------------------- ---------- ---------- select * from bigemp where pkcol = 1000 SELECT STATEMENT CHOOSE select * from bigemp where pkcol = 1000 TABLE ACCESS BY INDEX ROWID BIGEMP ANALYZED select * from bigemp where pkcol = 1000 INDEX RANGE SCAN PK_NU ANALYZED SQL_TEXT -------------------------------------------------------------------------- OPERATION OPTIONS OBJECT_NAM OPTIMIZER ------------------------------ -------------------- ---------- ---------- select * from bigemp where pkcol = 100 SELECT STATEMENT CHOOSE select * from bigemp where pkcol = 100 TABLE ACCESS FULL BIGEMP ANALYZED
As it can be seen above using cursor_sharing = exact leaves cursors intact.
cursor_sharing = force
SQL_TEXT -------------------------------------------------------------------------- OPERATION OPTIONS OBJECT_NAM OPTIMIZER ------------------------------ -------------------- ---------- ---------- select * from bigemp where pkcol = :"SYS_B_0" SELECT STATEMENT CHOOSE select * from bigemp where pkcol = :"SYS_B_0" INDEX RANGE SCAN PK_NU ANALYZED select * from bigemp where pkcol = :"SYS_B_0" TABLE ACCESS BY INDEX ROWID BIGEMP ANALYZED
Cursor_sharing=force transforms literals into binds and all similar statments (differ only in literals) use the same execution plans. In this case 'select * from bigemp where pkcol = 1000;' was run first, this is why index access is used for all 'select * from bigemp where pkcol = :"SYS_B_0"' queries.
cursor_sharing = similar
SQL_TEXT -------------------------------------------------------------------------- OPERATION OPTIONS OBJECT_NAM OPTIMIZER ------------------------------ -------------------- ---------- ---------- select * from bigemp where pkcol = :"SYS_B_0" SELECT STATEMENT CHOOSE select * from bigemp where pkcol = :"SYS_B_0" TABLE ACCESS BY INDEX ROWID BIGEMP ANALYZED select * from bigemp where pkcol = :"SYS_B_0" INDEX RANGE SCAN PK_NU ANALYZED SQL_TEXT -------------------------------------------------------------------------- OPERATION OPTIONS OBJECT_NAM OPTIMIZER ------------------------------ -------------------- ---------- ---------- select * from bigemp where pkcol = :"SYS_B_0" SELECT STATEMENT CHOOSE select * from bigemp where pkcol = :"SYS_B_0" TABLE ACCESS FULL BIGEMP ANALYZED
Cursor_sharing = similar also transforms the literals into binds, but the optimizer examines the statistics and may use different execution plans.
Make sure that statistics are available on the colulmns (PKCOL) to let optimizer make good decisions.
Be careful when setting this parameter it affects other optimization steps.
RevealNet Pipeline SYSOP Remarks:
Andrew Simkovsky
This looks pretty good. I did want to throw in one thing, but I don't know how relevant it is, since the article talks mainly to 9i. There is some bugs with CURSOR_SHARING that I know of up to at least 8.1.6.3 which causes ORA-0600 errors. Here's one bug for 8.1.6: [BUG:1365873] Here's another for 8.1.7:
fact: Oracle Server - Enterprise Edition 8.1.7
fact: INIT.ORA parameter CURSOR_SHARING=FORCE
symptom: Error performing a SELECT statement
symptom: ORA-12704 character set mismatch
change: Upgraded from Oracle Server 8.1.6 to Oracle Server 8.1.7
cause: Bug:1561329
fix:
Workaround: Change the INIT.ORA parameter CURSOR_SHARING to EXACT.
This maintains pre 8.1.6 behaviour for literal SQL statements in that literals are not replaced with bind variables and the literal values can be used by the CBO.
To make the change, edit the INIT.ORA file to include the line:
CURSOR_SHARING = EXACT
Note : This change can have an impact on performance. See Oracle8i Reference Guide, Page 1-23 CURSOR_SHARING Parameter, for more information.
