Table of contents
What to do/not do
CURSOR_SHARING is a well know session/system parameter but testing it in real world could drives you crazy.
Before going further in testing let me list two very important point that could keep you busy multiple days trying to understand why it does not work. Even if those two points could look obvious it is very complex to find good references on Internet:
- Clearly listed in Thomas Kyte book (Expert Oracle): EXPLAIN PLAN command and so AUTOTRACE in SQL*Plus do not work with CURSOR_SHARING. So obviously you cannot rely on their display and query plans are very difficult to study.
- Only few references of this one, you may need to flush the shared pool twice (!!) after having changed the CURSOR_SHARING value (even at session level).
CURSOR_SHARING parameter, since it has been released in 8.1.6, has always been linked to many bug. So in case of strange behavior always refer to reference Oracle support note: Init.ora Parameter “CURSOR_SHARING” Reference Note [ID 94036.1] for any potential bug. A classic one, rated a expected side effect, is column lengths that may change with CURSOR_SHARING=similar|force
Testing
Testing done on HPUX IA 11.23 64 bits with Oracle 10.2.0.4.1.
| CURSOR_SHARING | Memory used (in shared pool) | Performance |
|---|---|---|
| EXACT | Worst case, each statement has a parent cursor | Best case |
| SIMILAR (without histograms) | Best case, only on single parent cursor | Worst situation has one single plan for all situations |
| SIMILAR (with histograms) | Less than EXACT, each statement is a child of a single parent cursor (which uses less memory) | Same as Exact |
| FORCE | Best case, only on single parent cursor | Worst situation has one single plan for all situations |
All testing will be done with the same test table created with:
DROP TABLE test1; CREATE TABLE test1(id NUMBER, descr VARCHAR(50)) TABLESPACE users; DECLARE i NUMBER; nbrows NUMBER; BEGIN i:=1; nbrows:=50000; LOOP EXIT WHEN i>nbrows; IF (i=1) THEN INSERT INTO test1 VALUES(1,RPAD('A',49,'A')); ELSE INSERT INTO test1 VALUES(nbrows,RPAD('A',49,'A')); END IF; i:=i+1; END LOOP; COMMIT; END; / CREATE INDEX test1_idx_id ON test1(id) TABLESPACE users; |
CURSOR_SHARING=exact (default value)
For EXACT having or not histogram has no impact as cursor will not be be shared (see explanation below). To test it, in your session issue:
SQL> ALTER SESSION SET cursor_sharing=exact; SESSION altered. |
Than in another SYSDBA session issue:
SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. |
Then back to your own session issue:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, - > method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL PROCEDURE successfully completed. SQL> SET lines 130 SQL> SET autotrace traceonly STATISTICS SQL> SELECT /* Yannick */ * FROM test1 WHERE id=1; STATISTICS ---------------------------------------------------------- 1259 recursive calls 0 db block gets 501 consistent gets 0 physical reads 0 redo SIZE 622 bytes sent via SQL*Net TO client 488 bytes received via SQL*Net FROM client 2 SQL*Net roundtrips TO/FROM client 46 sorts (memory) 0 sorts (disk) 1 ROWS processed SQL> SELECT /* Yannick */ * FROM test1 WHERE id=50000; 49999 ROWS selected. STATISTICS ---------------------------------------------------------- 1 recursive calls 0 db block gets 3583 consistent gets 0 physical reads 0 redo SIZE 860598 bytes sent via SQL*Net TO client 37151 bytes received via SQL*Net FROM client 3335 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 49999 ROWS processed SQL> SELECT /* Yannick */ * FROM test1 WHERE id=25000; no ROWS selected STATISTICS ---------------------------------------------------------- 1 recursive calls 0 db block gets 251 consistent gets 0 physical reads 0 redo SIZE 382 bytes sent via SQL*Net TO client 477 bytes received via SQL*Net FROM client 1 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 0 ROWS processed SQL> |
From a SYSDBA session you should see:
SQL> SET lines 130 SQL> col sql_text FOR a80 SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ -------------------------------------------------------------------------------- 2g6mxqa5a8uj9 0 SELECT /* Yannick */ * FROM test1 WHERE id=1 gw2mtv099xtcf 0 SELECT /* Yannick */ * FROM test1 WHERE id=50000 8whsg1t7kbmd4 0 SELECT /* Yannick */ * FROM test1 WHERE id=25000 |
As expected on parent cursor per statement, if you check in V$SQL_PLAN_STATISTICS_ALL you will see… the same plan…:
sql_id=2g6mxqa5a8uj9 AND child_number=0 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (750000 bytes, 25000 ROWS) io COST=94,cpu_cost=12906491 sql_id=gw2mtv099xtcf AND child_number=0 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (750000 bytes, 25000 ROWS) io COST=94,cpu_cost=12906491 sql_id=8whsg1t7kbmd4 AND child_number=0 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (750000 bytes, 25000 ROWS) io COST=94,cpu_cost=12906491 |
This is one of the mystery of the SIZE AUTO option of DBMS_STATS for histograms. The first time you gather statistics on the table Oracle does not take any histograms on the id columns, this can be controlled with:
SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE owner='YJAQUIER' AND table_name='TEST1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------------------------ -------------------- ------------ ----------- --------------- TEST1 ID 2 1 NONE |
As clearly demonstrated in https://easyteam.fr/blog/ blog and more precisely http://sites.google.com/site/arkzoyd/sqlamer%233 Oracle will learn from its mistake and after few select on the table the same DBMS_STATS command will create histograms on the ID columns (!!). We would have create histograms directly by specifying SIZE 2 (only two distinct values for the column) or explicitly forbid histogram creation with SIZE 1.
CURSOR_SHARING=similar
Without histograms
As we don’t have histogram on the column it’s a good opportunity to test SIMILAR without histograms.
Issue the following in your session:
SQL> ALTER SESSION SET cursor_sharing=similar; SESSION altered. |
And then the following in a SYSDBA session:
SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. |
Then re-execute the three queries and you should see the following in V$SQL:
SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ -------------------------------------------------------------------------------- c76qnzbdg1tbs 0 SELECT /* Yannick */ * FROM test1 WHERE id=:"SYS_B_0" |
As expected one parent cursor for all statements and so one explain plan for all queries:
sql_id=c76qnzbdg1tbs AND child_number=0 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (750000 bytes, 25000 ROWS) io COST=94,cpu_cost=12906491 |
With histograms
As explained above if we re-issue DBMS_STATS with SIZE AUTO option we should now see te creation of an histogram on the ID column:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'yjaquier', tabname=>'test1', estimate_percent=>NULL, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO', degree=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE); PL/SQL PROCEDURE successfully completed. SQL> SELECT table_name, column_name, num_distinct, num_buckets, histogram FROM DBA_TAB_COL_STATISTICS WHERE owner='YJAQUIER' AND table_name='TEST1'; TABLE_NAME COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM ------------------------------ -------------------- ------------ ----------- --------------- TEST1 ID 2 2 FREQUENCY |
All is in place to test SIMILAR with histograms:
Issue the following in a SYSDBA session:
SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. |
And then re-issue the three query in your own session, you should now see in V$SQL:
SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ -------------------------------------------------------------------------------- c76qnzbdg1tbs 0 SELECT /* Yannick */ * FROM test1 WHERE id=:"SYS_B_0" c76qnzbdg1tbs 1 SELECT /* Yannick */ * FROM test1 WHERE id=:"SYS_B_0" c76qnzbdg1tbs 2 SELECT /* Yannick */ * FROM test1 WHERE id=:"SYS_B_0" |
So, as expected, only one parent cursor and one child per distinct statement. Each child can have its own explain plan different from the others:
sql_id=c76qnzbdg1tbs AND child_number=0 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS BY INDEX ROWID...........(YJAQUIER.TEST1) COST:2 (30 bytes, 1 ROWS) io COST=2,cpu_cost=20706 (2 1) | INDEX RANGE SCAN....................(YJAQUIER.TEST1_IDX_ID) COST:1 ( bytes, 1 ROWS) io COST=1,cpu_cost=10793 sql_id=c76qnzbdg1tbs AND child_number=1 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS BY INDEX ROWID...........(YJAQUIER.TEST1) COST:2 (30 bytes, 1 ROWS) io COST=2,cpu_cost=20706 (2 1) | INDEX RANGE SCAN....................(YJAQUIER.TEST1_IDX_ID) COST:1 ( bytes, 1 ROWS) io COST=1,cpu_cost=10793 sql_id=c76qnzbdg1tbs AND child_number=2 PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (1499970 bytes, 49999 ROWS) io COST=94,cpu_cost=13406471 |
To understand why cursors have not been shared there is an interesting view called V$SQL_SHARED_CURSOR:
SQL> SELECT * FROM v$sql_shared_cursor WHERE sql_id='c76qnzbdg1tbs'; SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D ------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - L D B P C S R P T M B M R O P M F L - - - - - - - - - - - - - - - - - - c76qnzbdg1tbs C00000052ECE7F50 C00000051EE0D0A0 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N c76qnzbdg1tbs C00000052ECE7F50 C00000053DA7BBA0 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N c76qnzbdg1tbs C00000052ECE7F50 C00000052ED645D8 2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N |
Unfortunately the column that would tell us why cursors are not shared in available in 11g only (HASH_MATCH_FAILED)…
CURSOR_SHARING=force
For FORCE having or not histogram has no impact and cursor will be shared. To test it, from your session issue:
SQL> ALTER SESSION SET cursor_sharing=force; SESSION altered. |
Then issue the following in a SYSDBA session:
SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. SQL> ALTER SYSTEM flush shared_pool; SYSTEM altered. |
And as expected:
SQL> SELECT sql_id,child_number,sql_text FROM v$sql WHERE sql_text LIKE 'SELECT /* Yannick%'; SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ -------------------------------------------------------------------------------- c76qnzbdg1tbs 0 SELECT /* Yannick */ * FROM test1 WHERE id=:"SYS_B_0" |
The explain plan is the following:
PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS BY INDEX ROWID...........(YJAQUIER.TEST1) COST:2 (30 bytes, 1 ROWS) io COST=2,cpu_cost=20706 (2 1) | INDEX RANGE SCAN....................(YJAQUIER.TEST1_IDX_ID) COST:1 ( bytes, 1 ROWS) io COST=1,cpu_cost=10793 |
Please note that with FORCE (few chance you remain in this situation for a long time) the first query that is issued, with id=1 or id=50000 determines the explain plan that will be used by all further queries. By issuing id=50000 first we would have seen as explain plan:
PLAN ----------------------------------------------------------------------------------------------------------------------------- (1 0) | TABLE ACCESS FULL.....................(YJAQUIER.TEST1) COST:95 (1499970 bytes, 49999 ROWS) io COST=94,cpu_cost=13406471 |
Conclusion
The best option seems to be SIMILAR with histograms but strangely Oracle is going to dismiss it… Thomas Kyte is from his side suggesting to test the following option:
| Application | CURSOR_SHARING |
|---|---|
| OLTP | FORCE |
| Mixed workload | SIMILAR (where you need a different plan for some of the queries) |
| DSS/DW | EXACT |
But whatever you plan to change it must be tested carefully mainly because of all the bug associated with the parameter and all the side effects you may have. Keeping in mind that nothing will replace a well written application with bind variables. At that time you may hit side effect from bind variables peeking, that should be solved in 11g with adaptive cursor sharing that we will see in another post.
References
- ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]
- Re: Multiple SQL version count with cusror_sharing=similar
- “cursor_sharing=similar” et “method_opt=>’FOR ALL COLUMNS SIZE AUTO'” (French)
- SQL Amer #3 (French)
- On Sharing, Splitting, and Deleting
- Handling and resolving unshared cursors/large version_counts [ID 296377.1]
- Unsafe Literals or Peeked Bind Variables [ID 377847.1]
- When Cursor_Sharing=Similar/Force do not Share Cursors When Literals are Used? [ID 364845.1]
- High Version Count with CURSOR_SHARING = SIMILAR or FORCE [ID 261020.1]
- Troubleshooting: High Version Count Issues [ID 296377.1]
- Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g
