Oracle Illustrated

Jun 25 2010   6:19AM GMT

Migrating from 9i to 11g – Adaptive cursor sharing



Posted by: Lakshmi Venkatesh
Tags:

Feature available from : Oracle 11g Release 1

Cursor_Sharing enables to notice similar SQL statements that are already parsed and available in SQL area. When the query is issued for the first time, the same is stored in the SQL area. Later, on issuing same / similar SQL statements, the query in the memory would be processed – parsing will not take place again. Default cursor sharing parameter is EXACT. Only if the SQL statement is exactly similar the query will be used otherwise it will be parsed again.

Setting cursor_sharing to FORCE or SIMILAR enables similar statements to share the SQL.
FORCE – forces similar SQL statements to share the SQL area, detoriating the explain plans.
SIMILAR – similar SQL statements to share the SQL area, without detoriating the explain plans.
EXACT – only exact SQL statements share the SQL area. This is the default value.

Caution: Setting CURSOR_SHARING to FORCE or SIMILAR prevents any outlines generated with literals from being used if they were generated with CURSOR_SHARING set to EXACT.

All the above three modes will work similarly for bind variables and literals. This results in bind variable peaking problem – it will choose the same explain plan however the data is distributed without considering the percentage of data returned by the predicate.

Oracle 11g has introduced a new feature adaptive cursor sharing to to choose different plan for queries containing bind variables on skewed data .

1. Literals

create index emp_idx1 on emp (deptno)

exec dbms_stats.gather_index_stats(ownname => ‘SYSTEM’, indname => ‘emp_idx1′)

exec dbms_stats.gather_table_stats ( ownname => ‘SYSTEM’, tabname => ‘EMP’, method_opt => ‘for all indexed columns size skewonly’, cascade => TRUE );

Oracle 9i

SQL> Select * from emp where deptno = 630;

500 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3085206398
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 8108 | 356K | 118 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 8108 | 356K | 118 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | EMP_IDX1 | 8108 | | 17 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“DEPTNO”=630)

Statistics
———————————————————-
1 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
31937 bytes sent via SQL*Net to client
779 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500 rows processed

Select * from emp where deptno = 550

23929 rows selected.

Elapsed: 00:00:00.29

Execution Plan
———————————————————-
Plan hash value: 3085206398
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 8108 | 356K| 118 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 8108 | 356K| 118 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | EMP_IDX1 | 8108 | | 17 (0)| 00:00:01 |

—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————

2 – access(“DEPTNO”=550)

Statistics
———————————————————-
1 recursive calls
0 db block gets
3517 consistent gets
0 physical reads
0 redo size
1564144 bytes sent via SQL*Net to client
17961 bytes received via SQL*Net from client
1597 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23929 rows processed

Note: For both the cases INDEX scan was performed.

Oracle 11g

alter session set “_optimizer_adaptive_cursor_sharing”=true

SQL> Select * from emp where deptno = 630;

500 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3085206398
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 517 | 25333 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 517 | 25333 | 9 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_IDX1 | 517 | | 2 (0)| 00:00:01|
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

2 – access(“DEPTNO”=630)

Statistics
———————————————————-
1 recursive calls
0 db block gets
79 consistent gets
0 physical reads
0 redo size
31937 bytes sent via SQL*Net to client
779 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500 rows processed

alter session set “_optimizer_adaptive_cursor_sharing”=true

Select * from emp where deptno = 550

SQL> Select * from emp where deptno = 550;

23929 rows selected.

Execution Plan
———————————————————-
Plan hash value: 3956160932
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 23977 | 1147K| 143 (1) | 00:00:02|
|* 1 | TABLE ACCESS FULL| EMP | 23977 | 1147K| 143 (1)| 00:00:02|
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“DEPTNO”=550)

Statistics
———————————————————-
1 recursive calls
0 db block gets
2099 consistent gets
0 physical reads
0 redo size
1272097 bytes sent via SQL*Net to client
17961 bytes received via SQL*Net from client
1597 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23929 rows processed

With adaptive cursor sharing for the first query retrieving 500 records index scan; second query where 23929 records were fetched – FTS was performed.

2. Bind Variable Peeking

There could be columns with heavily skewed data or otherwise. It should be the case that heavily skewed data should use a different execution plan as opposed to normal data.

If a query needs to be tuned that has bind variable peeking issue then, we can deactivate bind peeking. It uses bind-aware cursor sharing.
Adaptive cursor sharing is the solution for bind variable peeking problem. It only shares the plan only if bind variables are equal. If the bind variables are equal and falls within the range then, it uses the same plan. If the bind values are not equivalent then it creates a new plan.

Oracle 9i

variable l_value number
exec :l_value := 5

SQL> Select * from test_bind where a = :l_value;
Elapsed: 00:00:00.00

Execution Plan
———————————————————-
Plan hash value: 317434058

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 11 | 33 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| A_IDX1 | 11 | 33 | 1 (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”=TO_NUMBER(:L_VALUE))
variable l_value number
exec :l_value := 501

Select * from test_bind where a = :l_value;

Execution Plan
———————————————————-
Plan hash value: 317434058

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 11 | 33 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| A_IDX1 | 11 | 33 | 1 (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“A”=TO_NUMBER(:L_VALUE))

Notes: In the above example even though the SQL returned more than 40% of data it goes for RANGE SCAN – just reuses the same plan.

Oracle 11g
CREATE TABLE bind_test (
object_id NUMBER,
object_type varchar2(100),
CONSTRAINT bind_test_pk PRIMARY KEY (object_id));

CREATE INDEX bind_test_idx ON bind_test(object_type);

BEGIN
FOR cur IN (select dummy_seq.nextval id, object_type from all_objects) LOOP
INSERT INTO bind_test VALUES (cur.id, cur.object_type);
end loop;
COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, ‘bind_test’, method_opt=>’for all indexed columns size skewonly’, cascade=>TRUE);

alter session set “_optimizer_adaptive_cursor_sharing”=true
ALTER SESSION SET “_optim_peek_user_binds”=FALSE;

set autotrace traceonly

VARIABLE l_obj_type VARCHAR2(100)
EXEC :l_obj_type := ‘EDITION’

SELECT COUNT(object_id) FROM bind_test where object_type = :l_obj_type

Execution Plan
———————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 9 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| BIND_TEST_IDX | 1781 | 16029 | 9 (0)| 00:00:01 |
———————————————————————————–

VARIABLE l_obj_type VARCHAR2(100)
EXEC :l_obj_type := ‘PACKAGE’

SELECT COUNT(object_id) FROM bind_test where object_type = :l_obj_type

Execution Plan
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 9 | 51 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| BIND_TEST | 1781 | 16029 | 51 (2)| 00:00:01 |
——————————————————————————–

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: