Tuesday, December 19, 2006

CURSOR_SHARING - Do we use it?

Are our cursors in shared pool shared at all?
How many DBA’s uses this feature of 9i (introduced in 8i but enhanced in 9i?)?

Actually, lot of us doesn’t use it all. Let’s first understand this feature and implement this in our systems.

CURSOR_SHARING is an init.ora parameter which decides whether a SQL send from user is a candidate for fresh parsing or will use an existing plan.

This parameter has 3 values.

1. CURSOR_SHARING = Exact (Default)

Definition: Share the plan only if text of SQL matches exactly with the text of SQL lying in shared pool

Let’s take an example

SQL> create table test1 (t1 number);

Table created.

SQL> insert into test1 values(1);

1 row created.

SQL> insert into test1 values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1 where t1=1;

T1
----------
1

SQL> select * from test1 where t1=2;

T1
----------
2

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from test1%'
4 order by sql_text;

SQL_TEXT
-----------------------------------------------------

select * from test1 where t1=1
select * from test1 where t1=2


As you see there were 2 statements in V$sql, so it generated 2 plans. Oracle had to do the same work again to generate the plan even when the difference between the two SQL was just literal value.


2. CURSOR_SHARING = Force (Introduced in 8.1.6)


Definition: Share the plan (forcibly) of a SQL if the text of SQL matches (except the literal values) with text of SQL in shared pool

This means if 2 SQL’s are same except their literal values, share the plan.

Let’s take an example:

I’m using the same table and data which is used in case of above example.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select * from test1 where t1=1;

T1
----------
1

SQL> select * from test1 where t1=2;

T1
----------
2

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from test1%'
4 order by sql_text;

SQL_TEXT
---------------------------------------------------

select * from test1 where t1=:"SYS_B_0"


You can see for both the statements there was only one entry in V$sql. This means for second occurrence, oracle did not generate a new plan.

This not only helps in savings DB server engine time for generating the plan but also helps in reducing the number of plans shared pool can hold.

Important note:

Cursor_sharing = force can have some flip behavior as well, so you must be careful to use this. Using this we are forcing oracle to use the same plan for 2(or more) SQL’s even when using the same plan may not be good for similar SQL’s.

Example: “where t1=2” may be a good candidate for index scan while “where t1=10” should use a full table scan because 90% of the rows in the table has t1=10 (assumption).



3. CURSOR_SHARING = SIMILAR (Introduced in 9i)

This is the tricky one, but most used.


Definition: SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. (Source: Oracle documentation)


Let’s understand this.
Re-quoting the example above > “where t1=2” may be a good candidate for index scan while “where t1=10” should use a full table scan because 90% of the rows in the table has t1=10 (assumption).

To avoid 2 statements using the same plan when the same plan is not good for one of them, we have cursor_sharing=similar

Let’s take an example:

SQL> alter system flush shared_pool;

System altered.

SQL> drop table test1;

Table dropped.

SQL> create table test1 (t1 number,t2 number);

Table created.


SQL>
1 begin
2 for i in 1 .. 100 loop
3 insert into test1 values(1,i);
4 end loop;
5 commit;
6 update test1 set t1=2 where rownum <> /

PL/SQL procedure successfully completed.


In this case t1 has value “2” in first row and “1” in rest 99 rows
SQL> create index tt_indx on test1(t1);

Index created.

SQL> alter session set cursor_sharing=similar;

Session altered.

SQL> select * from test1 where t1=2;

1 row selected.

SQL> select * from test1 where t1=1;

99 rows selected.

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from test1%'
4 order by sql_text;

SQL_TEXT
----------------------------------------------------

select * from test1 where t1=:"SYS_B_0"
select * from test1 where t1=:"SYS_B_0"

This tells us that even though the 2 statements were similar, Oracle opted for a different plan. Now even if you put t1=30 (0 rows), Oracle will create another plan.

SQL> select * from test1 where t1=30; -- (0 rows)


SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from test1%'
4 order by sql_text;

SQL_TEXT
---------------------------------------------------

select * from test1 where t1=:"SYS_B_0"
select * from test1 where t1=:"SYS_B_0"
select * from test1 where t1=:"SYS_B_0"

This is because the first time when the SQL ran, oracle engine found the literal value as “unsafe” because using the same literal value can cause bad plans for other similar SQL’s. So along with the PLAN, optimizer stored the literal value also. This will ensure the reusability of the plan only in case the same lieteral is provided. In case of any change, optimizer will generate a new plan.

But this doesn’t mean that SIMILAR and EXACT are same.

See this:

SQL> alter system flush shared_pool;

System altered.

SQL> select * from test1 where t1=2 and t1=22;

no rows selected

SQL> select * from test1 where t1=2 and t1=23;

no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from test1%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------

select * from test1 where t1=:"SYS_B_0" and t1=:"SYS_B_1"

Optimizer used single plan for both.

Conclusions:

1. Use CURSOR_SHARING=similar only when you have library cache misses and/or most of the SQL statements differ only in literal values


2. CURSOR_SHARING=force/similar significantly reduces the number of plans in shared pool


Note:

1. Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE

2. Setting CURSOR_SHARING to SIMILAR or FORCE causes an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals (in a SELECT statement). However, the actual length of the data returned does not change.

8 comments:

Anonymous said...

Describe clearly. Good effort.

Amila Kularatna

Anonymous said...

This was really helpful

Anonymous said...

SQL> create table test as select * from user_objects;

Table created.

SQL> /

35584 rows created.

SQL> commit;

Commit complete.

SQL> select count(*),status from test group by status;

COUNT(*) STATUS
---------- -------
4096 INVALID
1134592 VALID

SQL> update test set status='BAD' where rownum < 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> update test set status='GOOD' where status='VALID'
2 and rownum < 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select count(*),status from test group by status;

COUNT(*) STATUS
---------- -------
1 BAD
1 GOOD
4096 INVALID
1134590 VALID

SQL>

SQL> create index idx on test(status);

Index created.

SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'TEST',ESTIMA
TE_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> alter session set cursor_sharing='SIMILAR';

Session altered.

SQL> select sql_text from v$sql where
2 sql_text like '%select count(*) from test where status=%';

no rows selected

SQL> select count(*) from test where status='BAD';

COUNT(*)
----------
1

SQL> select count(*) from test where status='GOOD';

COUNT(*)
----------
1

SQL> select count(*) from test where status='INVALID';

COUNT(*)
----------
4096

SQL> select count(*) from test where status='VALID';

COUNT(*)
----------
1134590

SQL> select sql_text from v$sql where
2 sql_text like '%select count(*) from test where status=%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(*) from test where status=:"SYS_B_0"


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL>

As per your post, v$sql should show 4 statement... But it is not.

Any thought??

Sachin said...

In your case - all the 4 statement used the same execution plan i.e. all 4 used the index you created.

Since you were doing "select count() .." - it is making the sql to use index. So effectively there is no diff in all 4 statements.

I would suggest you to check "select *" OR "select (cols) .." rather than doing "select count(*) .." which will give optimizer a choice of using or rejecting an index.

In your case - i feel optimizer is forced to use an index.

- Sachin

Anonymous said...

Thanks for your reply Sachin. I agree with you. I should not have used count(*).
Now i am using column name object_name. This is not indexed.


SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'TEST',ESTIMAT
E_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> alter session set cursor_sharing='SIMILAR';

Session altered.

SQL> select count(object_name) from test where status='GOOD';

COUNT(OBJECT_NAME)
------------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte
s=24)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt
es=13557840)


SQL> select count(object_name) from test where status='BAD';

COUNT(OBJECT_NAME)
------------------
1


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte
s=24)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt
es=13557840)


SQL> select count(object_name) from test where status='VALID';

COUNT(OBJECT_NAME)
------------------
1134590


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte
s=24)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt
es=13557840)

SQL> select count(object_name) from test where status='INVALID';

COUNT(OBJECT_NAME)
------------------
4096


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte
s=24)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt
es=13557840)


SQL> select sql_text from v$sql where sql_text like '%select count(object_name) from test
where status=%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(object_name) from test where status=:"SYS_B_0"
EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t
est where status='VALID'

EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t
est where status='BAD'

EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t
est where status='INVALID'

EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t
est where status='GOOD'


For some reason, optimizer is using full table scan for all queries. It should not use
full table scan. But it is using Full tablescan. That is okay.

But here is my whole point. My understanding for cursor_sharing=similar is,

optimizer decides to use the new plan or exisitng plan based on the statistics and
histogram. Reusing the plan not only based on the literal values. In the below
paragrah, you are saying that optimizer will ensure the reusablity of the plan only in case
the same literal is provided. My statement is, oracle will resuse the plan
even if the use the different literal. If both the sql has different literal and
it returns exact/similar number of records, then optimizer use the same plan.

Do you agree?

=============
This is because the first time when the SQL ran, oracle engine found
the literal value as “unsafe” because using the same literal value
can cause bad plans for other similar SQL’s. So along with the PLAN,
optimizer stored the literal value also. This will ensure the
reusability of the plan only in case the same lieteral is provided.
In case of any change, optimizer will generate a new plan.
============

Sachin said...

I feel you are ALMOST right. However there are few reasons - why i feel i still have some point to shared

For that I advice you to read:
" For example, if the predicate WHERE X=6 implies that I would want to use a full scan but the predicate WHERE X=5 implies that I would want to use an index range scan, the database would recognize that and set up different plans for me. In the case of different plans, you mark the bind variable as unsafe and add its value to the signature of the query, so to reuse this cursor, you must not only have the same SQL statement but also the same value for that particular bind variable."

Source: http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html

It keeps the value of the bind variable in the signature and only allows to use the same cursor space only if the same bund value is used.

Thx,
Sachin

Anonymous said...

Thanks Sachin. It makes more sense after i read the Tom's link

Unknown said...

Thanks

Really i appriciating your knowledge............reg Oracle concept