|
Replies:
16
-
Pages:
2
[
1
2
| Next
]
-
Last Post:
May 27, 2009 1:11 AM
Last Post By: DBA-ES
|
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 5:24 AM
|
|
|
Hello,
We have a Peoplesoft DB and we have been obliged to use cursor_sharing=similar to solve some of our problems. We have just noticed that with Oracle 9.2.0.7, when a table is partitioned, many versions of the same sql are created in the SGA. Here is the demo
CREATE TABLE T5
(
ID NUMBER(2),
NAME VARCHAR2(15 BYTE)
)
PARTITION BY RANGE (ID)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION P3 VALUES LESS THAN (30),
PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE));
alter session set cursor_sharing=similar;
select count(*) from t5 where id = 1;
select count(*) from t5 where id = 10;
select count(*) from t5 where id = 30;
select count(*) from t5 where id = 5;
select count(*) from t5 where id = 15;
Now we check the SGA:
SQL> select hash_value,CHILD_NUMBER,EXECUTIONS,sql_text from v$sql where SQL_TEXT like '%t5%';
HASH_VALUE CHILD_NUMBER EXECUTIONS SQL_TEXT
---------- ------------ ---------- -----------------------------------------------------------------------------
602987019 0 1 select count(*) from t5 where id = :"SYS_B_0"
602987019 1 1 select count(*) from t5 where id = :"SYS_B_0"
602987019 2 1 select count(*) from t5 where id = :"SYS_B_0"
602987019 3 1 select count(*) from t5 where id = :"SYS_B_0"
602987019 4 1 select count(*) from t5 where id = :"SYS_B_0"
Is there a way to avoid all these child cursors?
Has anyone had this same problem before? |
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 6:31 AM
in response to: DBA-ES
|
|
|
|
The same happens with 10.2.0.1
|
|
|
Posts:
2,581
Registered:
06/27/99
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 6:35 AM
in response to: DBA-ES
|
|
|
|
Why do you consider these a problem & require some action?
|
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 6:45 AM
in response to: sb92075
|
|
|
|
Because they cause latches, and sqls are not shared.
|
|
|
Posts:
81
Registered:
05/20/09
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 6:50 AM
in response to: DBA-ES
|
|
|
|
The setting "SIMILAR" does rewrites of the query, replacing the literals with bind variables, but can set up different plans for different bind variable combinations. So it will not create a one-size-fits-all execution plan as the "FORCE" setting does. The number of actual plans you observe in the shared pool will not increase.
The setting choosen for the database from a People Soft point of view is done to prevent to many parses of sql only differing on filled in literal variables. The option "SIMILAR" makes the application run smoother without too much parsing and benefits from the differently generated plans for other variables, and not just one plan for all situations.
So as mentioned in the post above, this is expected and harmless behaviour.
Regards, Gerwin
|
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 6:52 AM
in response to: Gerwin Hendriksen
|
|
|
|
What do you mean by actual plans?
The version_count does increase...
|
|
|
Posts:
648
Registered:
10/05/07
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 25, 2009 9:27 PM
in response to: DBA-ES
|
|
|
The SIMILAR cursor sharing has this meaning.
- Whenever there is a chance of having better plan with new child cursor, create another child cursor instead of sharing the previous one.
For instannce,
- histogram
- range operation
-- paritition key -- <-- This is not verified!
has a great potential to have better plan without bind variable, but with literal value. For this reason, Oracle simply ignores sharing the child cursor for above pattern of predicates.
This is a designed behavior.
The important question is how many distinct values are used in the predicate for the partition key column. Because it is the partition key, the reasonable max distinct count would be distinct count of the partition key value, which would be no real problem for the resonabley designed application.
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================
Edited by: Dion_Cho on May 26, 2009 12:30 AM
Correction |
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:18 AM
in response to: Dion_Cho
|
|
|
Well, I just tried it, but this time with a table NOT partitioned, no histograms and no range operation, with Oracle version 10.2.0.1 and I got the same results:
SQL> CREATE TABLE T6
(
ID NUMBER(2),
NAME VARCHAR2(15 BYTE)
);
SQL> alter session set cursor_sharing=similar;
Session altered.
SQL>
SQL> select count(*) from t6 where id = 1;
COUNT(*)
----------
0
SQL> select count(*) from t6 where id = 10;
COUNT(*)
----------
0
SQL> select count(*) from t6 where id = 30;
COUNT(*)
----------
0
SQL> select count(*) from t6 where id = 5;
COUNT(*)
----------
0
SQL> select count(*) from t6 where id = 15;
COUNT(*)
----------
0
SQL> select hash_value,CHILD_NUMBER,EXECUTIONS,sql_text
from v$sql where SQL_TEXT like '%t6%';
HASH_VALUE CHILD_NUMBER EXECUTIONS SQL_TEXT
---------- ------------ ---------- ------------------------------
2780697141 0 1 select count(*) from t6 where
id = :"SYS_B_0"
2780697141 1 1 select count(*) from t6 where
id = :"SYS_B_0"
2780697141 2 1 select count(*) from t6 where
id = :"SYS_B_0"
2780697141 3 1 select count(*) from t6 where
id = :"SYS_B_0"
HASH_VALUE CHILD_NUMBER EXECUTIONS SQL_TEXT
---------- ------------ ---------- ------------------------------
2780697141 4 1 select count(*) from t6 where
id = :"SYS_B_0"
2776262046 0 2 select hash_value,CHILD_NUMBER
,EXECUTIONS,sql_text from v$sq
l where SQL_TEXT like '%t6%'
6 rows selected.
So? |
|
|
Posts:
648
Registered:
10/05/07
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:37 AM
in response to: DBA-ES
|
|
|
Add following code to your test case.
exec dbms_stats.gather_table_stats(user, '<table_name>');
Then you might see what I described.
It seems that Oracle has some holes in the similar cursor sharing implementation, especially when you have no permanent statistics. Not sure why.
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================ |
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:48 AM
in response to: Dion_Cho
|
|
|
|
Yes indeed, just one version now with a table not partitioned.
Why did you change your previous post to partition key <-- not verified?
|
|
|
Posts:
648
Registered:
10/05/07
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:49 AM
in response to: DBA-ES
|
|
|
In addition, always use {code} tag when posting SQL things, like this:
{code}
select * from dual;
{code}
Then, it would look like this.
select * from dual;
==================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================== |
|
|
Posts:
648
Registered:
10/05/07
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:51 AM
in response to: DBA-ES
|
|
|
Because I've never tried similar cursor sharing with partition key myself in the production system.
But a simple test case verified it. 
==================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================== |
|
|
Posts:
77
Registered:
04/12/06
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 12:59 AM
in response to: Dion_Cho
|
|
|
|
Changed the {code} thing, thank you.
So a partition key is also one of them...
|
|
|
Posts:
648
Registered:
10/05/07
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 1:02 AM
in response to: DBA-ES
|
|
|
Yes, exactly.
==================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================== |
|
|
Posts:
1,634
Registered:
07/03/08
|
|
|
|
Re: SQL versions with cursor_sharing=similar
Posted:
May 26, 2009 4:37 AM
in response to: Dion_Cho
|
|
|
Add following code to your test case.
exec dbms_stats.gather_table_stats(user, '<table_name>');
Then you might see what I described.
It seems that Oracle has some holes in the similar cursor sharing implementation, especially when you have no permanent statistics. Not sure why.
Dion,
it doesn't look like a hole, but may be a reasonable approach: If the statistics are missing, then in 10g by default the dynamic sampling will be activated. So each execution of the cursor using different literal values theoretically could lead to a different execution plan depending on the results of the dynamic sampling.
And indeed: If deactivating dynamic sampling using ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0 (or 1 in this case is also sufficient), then these multiple child cursors disappear and the cursor is shared. So it looks more like a feature, not like a bug/hole.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
