CURSOR_SHARING initialization parameter tips and tricks

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

IT World

CURSOR_SHARING initialization parameter tips and tricks

Table of contents

What to do/not do

Testing

CURSOR_SHARING=exact (default value)

CURSOR_SHARING=similar

Without histograms

With histograms

CURSOR_SHARING=force

Conclusion

References

About Post Author

Yannick Jaquier

Related Posts:

One thought on “CURSOR_SHARING initialization parameter tips and tricks”

Leave a Reply Cancel Reply