so we dont need to do anything
AMIR RIAZ, December 24, 2007 - 5:31 am UTC
Hi tom.
thanks of reply
Previously the condition i consider for generating histograms was if the data is unevenly distributed(9i) and as you said oracle 10g calculate histograms by default. Now since 10g does not have adaptive cursor sharing for bind variable so why 10g calculates histograms even under oltp environments.
in 11g if we have histograms enabled by default. Then under oltp we can have adaptive cursor sharing enabled for all the table even those whose data is distribute evenly. My thinking is under oltp environment we should enable histograms only for queries whose data is not distributed even. if we enable adaptive cursor sharing for all tables we can have a lot of child cursors. Also in your example above you have calculate histogram only on one column. dont you think creating histograms on all columns(as default in 10g so assuming same in 11g) will result alot of child cursor in shared pool.
regards
Amir Riaz
December 24, 2007 - 9:21 am UTC
because by default it does.
My opinion is that most large systems, with a DBA watching over it, will not use the rather simple default statistics gathering, rather the DBA will have submitted their own job to gather statistics in a predicable manner that they themselves developed.
Ain't this subject to more unbound queries ?
Nicosa, July 04, 2008 - 5:00 am UTC
Hi Tom,
The article is very interesting and clearly explained (as you always do).
But the following sentence kind of amazed me :
"We hope that in the future, this feature will persuade people to set cursor_sharing to force."
I perfectly understand that it doesn't mean that developpers can give up on using relevant binds, but it's like giving them arguments not to bind anymore (I can already hear the "No, we don't bind anymore, the adaptative cursor sharing feature will do the correct stuff for us".).
- Can I have your point of view on that ?
- Does that mean the default value will be FORCE instead of EXACT ?
Thanks in advance.
July 07, 2008 - 11:07 am UTC
cursor sharing force/similar will never be able to save you from sql injection bugs - therefore, this will never make it so that you would not, will not, should not bind.
The main reason to bind is security. There, I've said it, forget performance, forget scalability - it is all about security. If you do not bind, you have a security hole the size of a space shuttle in your application. If you do not bind, you have to submit your code to review to at least five people that do not like you at all (so they review it critically and find your sql injection bugs). Anytime you concatenate user supplied inputs into your query - you are subject to sql injection. You have developed highly suspect code, code that shouldn't be run by any serious shop in production for the fear of sql injection that it brings.
cursor sharing force and similar should be outlawed in the init.ora - now and forever. Use it as a stopgap measure for the performance reasons but remember - you need to fix it for security.
I've been having better luck scaring managers into having the code fixed once I demonstrate sql injection, than I have with performance as the basis for this. So even though I personally want it for the performance and scalability factors, I'll just put the fear of SQL INJECTION (boo!!!) into everyone.
Tongue out of cheek - seriously, sql inject is serious business, if you do not bind, you are SUBJECT TO IT and your code is very fragile. It is seriously bad stuff and hard to spot.
Sure ! but what about the picked sentence ?
Nicosa, July 07, 2008 - 12:07 pm UTC
(^_^)
As an addicted reader of this very site, I do know your point of view about binding for both performance and security reasons.
I was more interested in your point of view regarding the following sentence picked from the article :
"We hope that in the future, this feature will persuade people to set cursor_sharing to force."
I'm asking because it really bugs me out, as it seems to be the total opposite of all your mantras.
(or maybe, it is to be understood the following way : "...this feature will persuade people FROM setting cursor_sharing to force")
July 07, 2008 - 12:17 pm UTC
I do not agree with their statement at all.
It is, to me, very wrong.
This adaptive cursor sharing makes it so that the bad side effects of force/similar are lessened.
but we should never be in a place where we have to or want to set it on - it is a bandage for a sick application, while we make it better and get it secure.
A reader, February 13, 2009 - 12:23 pm UTC
So what's the "perfect" solution for a system that is primarily a reporting/data warehouse application and security is of the primary importance - i.e. it has to guard against SQL injection at all cost? Should it use bind for all inputs that come from users and use 11g's adaptive cursor sharing to undo the effect of bind peeking?
February 16, 2009 - 11:43 am UTC
you could go with another option.
If you permit people to actually log into the database using their OWN credentials - sql injection is *impossible*.
Oracle forms - built to support sql injection *as a FEATURE*, as an advertised feature - why? Because when you log in with your own credentials as you would with forms - it is 100% safe.
SQLPlus - the only thing it knows how to do - SQL INJECTION. Why is that safe? Because the database knows you, knows who you are, knows what you can do.
Enterprise Manager - SQL Injection heaven, "let us build the command and then you can modify it and run it". Why is it safe? Because you are you, you are NOT a generic account.
How about that as an option, it would have many other positive effects - like making auditing work, accountability work and be A LOT more secure than anything you could build in your own middle tier.
(and yes, we can still connection pool and do this, search documentation for
n-tier proxy authentication
)_
A reader, February 17, 2009 - 1:07 pm UTC
Can you please elaborate on this statement (from above)? I don't quite understand what you mean.
"If you permit people to actually log into the database using their OWN credentials - sql injection is *impossible*."
February 17, 2009 - 2:43 pm UTC
sql injection is only possible when you use a common username/password
eg: web browser connects to application server, web browser authenticates to application server, application server knows the user "TKYTE" is running the application.
application server connects to the database using the credentials APPLICATION_SUPER_USER/SECRET_PASSWORD
So the application knows who you are and the application is going to reinvent security from the ground it. It will permit you see only the data you are allowed to see.
That assumes the application has 100% control over the SQL it submits. If the application takes INPUTS from me (tkyte) and concatenates it into a sql query (does not bind) and executes the sql - I (tkyte) can CHANGE the meaning of that sql statement. I can sql inject that sql and bypass the application security.
Now, if instead of the application logging in as APPLICATION_SUPER_USER to run SQL for me - the application says to the database "Hey, this is APPLICATION_SUPER_USER, I've got TKYTE on the other end of the line, would you please enforce the security for TKYTE - don't let him touch anything he shouldn't, don't let him run code he shouldn't"
Now, since the database is using MY credentials, when it runs SQL, it will only run SQL TKYTE is allowed to perform. I cannot in any way shape or form bypass security - no matter HOW I modify your SQL.
If I modify your SQL - and I was given the permission to run that SQL in the database, I will run it.
If I modify your SQL - and that results in a statement I'm not allowed to execute in the database, I will NOT be able to run it.
If I'm logged into the database as APPLICATION_SUPER_USER - I can modify your sql and do ANYTHING the user APPLICATION_SUPER_USER is allowed to do (like drop your application tables, for example - something TKYTE cannot do but APPLICATION_SUPER_USER sure can).
So, if you actually push my identify into the database and have the database enforce security (as it rightly should), sql injection is not a worry. It is impossible to have it happens - binds or not. I can only do what I've been granted the ability to do!
A reader, February 17, 2009 - 4:06 pm UTC
So this implies that in many cases we would use virtual private database to implement the security at the row-level?
February 17, 2009 - 4:52 pm UTC
doesn't have to be VPD (virtual private database), it can be, but doesn't have to be.
You have grant
You have stored procedures (which can make it so a user is granted the ability to perform transaction X against tables T1, T2, T3 without having ACCESS to tables T1, T2, T3)
You have views
In the olden days, we did really cool things just with that.
VPD is just another tool.
Like secure application roles.
Like n-tier proxy authentication.
Like grant
Like the ALL_OBJECTS view (it does 'vpd' before 'vpd' was cool - all_objects, one view - but you only see what you are supposed to see - we should have called it "almost all objects" or "all objects you are allowed to see"
and so on
n-tier other than JDBC
Mark Brady, September 08, 2009 - 12:42 pm UTC
I did what you suggested -- searched the 10gR2 docs for "n-tier proxy authentication".
I got one hit, in the Java Developer's Guide.
n-tier authentication only had 2 hits, and the other was OCI docs and just in the intro -- no supporting info.
I'm very interested in convincing developers this is the way to go but we're using .Net but will be migrating to ODP.Net since the MS dropping of their ORacle provider.
Is there another search term I could try?
September 08, 2009 - 9:41 pm UTC
it is a java/oci thing - not sure if odp.nyet does it or not. I'd suggest the forums on otn.oracle.com - the guys that write the odp stuff monitor those forums and would be able to let you know if it is a feature supported with that api
"Inside the Oracle Optimizer" doesn't look "outside"
Stew Ashton, March 13, 2010 - 1:17 pm UTC
Tom, the Optimizer team just blogged again about "adaptive cursor sharing behavior with cursor_sharing = similar and force."
http://optimizermagic.blogspot.com/2010/03/explain-adaptive-cursor-sharing.html This is great stuff, but it hardly mentions the proper use of bind variables in the user query itself.
Now suppose some Java programmer reads their blog and decides "I don't need to use bind variables anymore, I'll just tell the DBA to use cursor_sharing=force and gather statistics appropriately." This is what I think would happen; could you tell me if my understanding is correct?
1) Even if a JDBC statement cache is used, it will be useless because just about every statement will be different and JDBC will do a parse call almost every time.
2) The SQL writer will probably forget to do explicit data conversion on his literals, whereas the way JDBC handles bind variables promotes explicit data typing.
3) The session cursor cache will be useless because just about every parse call will involve a different statement and there will be almost no cursor cache hits.
4) The Optimizer will get a chance to do its thing and will no doubt provide a terrific execution plan.
In other words, for almost every execution there will be at least a soft parse (not a "softer soft"). The Optimizer will be called way too often.
Please correct me if I am being too pessimistic here.
March 15, 2010 - 10:12 am UTC
well, I've posted a comment, we'll see if it appears...
When default statistics gathering is insufficient
Kim Berg Hansen, March 16, 2010 - 10:03 am UTC
Hi, Tom
In one of your followup responses you state:
My opinion is that most large systems, with a DBA watching over it, will not use the rather simple default statistics gathering, rather the DBA will have submitted their own job to gather statistics in a predicable manner that they themselves developed.
Would you have a few pointers/criteria (or perhaps links) how to determine when the simple defaults are insufficient and how to discover what settings will be the best for statistics gathering?
(Hiring an experienced DBA is probably a good first step :-)
Thanks in advance
March 16, 2010 - 10:27 am UTC
... (Hiring an experienced DBA is probably a good first step :-) ...
that would be a starter, sure.
You need to understand how statistics are used and applied by the optimizer to come up with a custom plan (if you don't, just use the default job).
It is not so much that they defaults are insufficient as they probably over do it for many. They tend to gather histograms for example - when you probably don't need them (think OLTP system using mostly binds, histograms can get in the way).
They tend to gather using queries, rather then already knowing the answer. For example, if you are a DBA/developer and you just bulk loaded a table might you already... know the size of the data you just loaded, know the number of rows, know the high/low values for many columns - maybe even the number of distinct values and so on. Perhaps you can SET the statistics rather than gather. And so on.
your comment ...
Sokrates, March 29, 2010 - 1:07 am UTC
doesn't seem to appear, did you get any feedback ?
April 05, 2010 - 9:19 am UTC
no, i did not, they decided to not publish it I guess.
I basically said, in more words than this, that:
cursor sharing should be left at EXACT. cursor sharing force/similar can only be used to turn a HARD parse problem into a SOFT parse problem - it does not correct the underlying problem, that of over parsing.
The real answer is: correct the bug in the code, use cursor sharing similar/force as a temporary "work around until we get the HUGE security bug and performance bug fixed in the application"
My fear is people will use cursor sharing = force/similar and feel (incorrectly) that they are secure because it uses binds (but they are not, the binding happens AFTER the sql is injected) and that they have fixed the performance issue (but they haven't, they've made it *better*, but they haven't fixed the problem at all)
Disagree with the impossibility of sql injections ...
Richard, June 29, 2010 - 4:45 pm UTC
Hi Tom,
Love the blog but I strongly disagree with your statement:
If you permit people to actually log into the database using their OWN credentials - sql injection is *impossible*.
Here is an example of a common user, using SQL*PLUS, who can execute a SQL Injection and grant DBA to them-self.
http://www.red-database-security.com/exploits/oracle-sql-injection-oracle-dbms_export_extension.html
Below, I didn't perform the execution but I'm showing that -- if my system wasn't patched -- I have all of the access I need to attempt the exploit.
SQL> create user test identified by test1234;
User created.
SQL> grant connect, resource to test;
Grant succeeded.
SQL> connect test/test1234
Connected.
SQL> desc SYS.DBMS_EXPORT_EXTENSION
PROCEDURE BEGIN_IMPORT_DOMAIN_INDEX
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IDXSCHEMA VARCHAR2 IN
IDXNAME VARCHAR2 IN
....
I don't see the connection between "permit[ing] people to actually log into the database using their OWN credentials" and preventing SQL injections. Bad code is bad code... whether I write it, you write it, or the Oracle dev team writes it. Granted, who the author is and what privileges are allowed determines the "level/amount of risk" -- for lack of a better phrase. There are methods to mitigate the risks but I don't agree with your statement that logging in with your own credentials nullifies the SQL injection risk.
Thanks!
Regards,
Rich
July 06, 2010 - 1:14 pm UTC
allow me to clarify a bit then, I can see a fuzzy distinction here, yes:
If you write an application that connects to the database are the actual user running the application - not as a common username and password - then your application - your developed code - is not subject to sql injection. However, if there is some code that your user is allowed to invoke (allowed by the database itself), and that other code is subject to sql injection, than you would be subject to sql injection.
Your code, if run by the actual user, as the actual user, is not subject to sql injection.
for example, the application sqlplus is not subject to sql injection itself - it (sqlplus) would not be the cause of sql injection bug, because sqlplus relies on the database to dictate what the user can and cannot do - it is not running as some super user.
plsql definers rights routines that use dynamic sql are subject to sql injection, always.
I agree with you and will make that distinction clear from now on
Thanks
A reader, July 14, 2010 - 6:00 pm UTC
plsql definers rights routines that use dynamic sql are subject to sql injection, always.
I follow you now! Appreciate the response and clarification :-)
Regards,
Rich
How about EBS 'apps' user
Sam Reddy, October 18, 2010 - 10:50 am UTC
Tom,
"Common User" is always Oracle applications gateway user "apps" user. Anything can be developed in custom source and let "apps" run the code.
How secure it is? No direct conncetion to "database" and everything is being run by applications super user.
Your comments/feedback scares me to death on 'sql injection' to 'Oracel Applications' product though there are some measures that can be taken to audit. Common user is always 'common user', not database users credentials are distinct and clear.
Thank you
Sam Reddy
October 25, 2010 - 9:06 am UTC
It is as 'secure' as every other application that uses a common user to the database is.
It is as secure as the people developing the code made it.
advantage of adaptive cursor sharing
Amir Riaz, October 27, 2010 - 6:03 am UTC
Hi Tom,
one of the benefit we can get from adaptive cursor sharing is through partitioning data even under OLTP environments. in OLTP environments under huge tables. Sometimes we can have full table scan on whole table and indexes dont help. with partitioning and adaptive cursor sharing partition elimination can take place if the query predicates contain the column on which the table have been partition. Since cost of full table is more than the cost of full scaning the partition on which the data resides oracle can build a new plan based only on that partition instead of full table.
I hope you will confirm my theory.
October 27, 2010 - 6:32 am UTC
you can do that without adaptive cursor sharing, that feature is not necessary for partition pruning to happen with or without bind variables. We can partition prune anytime the partitioning column is used in the predicate (in a manner that allows us to figure out "only some partitions will be accessed")
ACS v$sql_cs_histogram
A reader, December 07, 2010 - 7:49 am UTC
Hi Tom,
Could you please explain the meaning of v$sql_cs_histogram and how I would use it in diagnosing sql performance. I've read several articles on ACS, they mention this view, but no explanation.
December 07, 2010 - 11:48 am UTC
I don't see how you would use it to diagnose performance. It is filled with information the kernel uses to show if it has decided that it should create new cursors for different bind values or just use the general plan. It really isn't anything we need.
v$sql_cs_histogram
A reader, December 08, 2010 - 12:09 pm UTC
Tom,
What I'm looking for is some info on how to interpret the data in this view and how the kernel uses it. Querying this view shows for each child cursor 3 buckets with execution counts, but what do those buckets represent?
December 08, 2010 - 12:27 pm UTC
it is not documented - sorry, there just isn't anything relevant to them to speak about. It is just a set of flags we are using to tell us "reparse this query next time, it has been found to be sensitive to bind variables in real life".
I would just look at is_bind_sensitive and is_bind_aware in v$sql - they have Y/N flags that tell us what we need to know.
what triggers bind aware
Mark, December 21, 2010 - 4:10 pm UTC
hi tom,
How exactly does the optimizer decide that the child cursor should be set to bind aware? In all examples i found, it seems that the difference in buffer gets is the triggering factor, or at least one of them. Are there others? Also, is there any threshold in buffer gets number that tells optimizer that the child cursor is the candidate for bind aware?
December 22, 2010 - 1:54 pm UTC
it would predominantly be the buffer gets and the cpu - that is what determines the cost of a query (f(IO and CPU) = cost). There are no documented thresholds.
Basically - the optimizer came up with a plan and had expectations that the plan would perform with a cost of X. If the average observed cost of executing that plan deviates from X by some margin - it will invalidate that plan and start creating bind aware plans.
ACS_1 hint
Khalid Rahim, June 02, 2011 - 2:14 pm UTC
But I'm confused, if ACS is enabled by default, then why do we really need a /* ACS_1 */ hint? what purpose does it serve?
Thanks,
kr
June 02, 2011 - 8:49 pm UTC
as it is not documented, it is nothing you ever need/should use. So, that is all.
is /* ACS_1 */ really a hint ?
Sokrates, June 03, 2011 - 2:17 am UTC
I am missing the + sign in
/* ACS_1 */
shouldn't it be
/*+ ACS_1 */
?
( I promise, I'll never use it )
June 03, 2011 - 12:31 pm UTC
it doesn't matter to me, I don't care if it is a hint or not, it is not relevant - it isn't a documented hint and we won't really be discussing it.
but if you are seeing it as /* acs_1 */, it is not a hint, it is just a comment.
lol
Sokrates, June 03, 2011 - 2:59 am UTC
definitely it's not a hint
nobody but BC call it that
it was only used be optimizermagic as a marker comment to demonstrate ACS
later on, it was adopted by BC as a "11g adaptive cursor sharing tips"
rofl
Thanks for clearing the confusion
Khalid, June 04, 2011 - 12:45 pm UTC
I got it
A reader, June 04, 2011 - 5:21 pm UTC
Never mind socrates, I tried all kinds of things like "Bad Compiler", "British Columbia" etc. I now know that you are referring to that website which I think is a massive copy and paste operation of Oracle documentation, I wonder where the added value is. But you did ruin part of my saturday :-)
kr
Adaptive cursor sharing
A reader, November 07, 2011 - 11:16 am UTC
Hi Tom,
Could you please correct if my below understanding is wrong :
1,If we use ‘bind variables’ itself in the SQL (application) – CURSOR_SHARING does not come into picture
CURSOR_SHARING is for the SQL having literals in the WHERE clause ? they will turn hard parses to soft parses.
This is temporary solution – permanent fix is to use bind variable.
2.Adaptive cursor sharing’ in 11g -this feature is for the SQLs with bind variables – just to avoid the side effect of ‘bind variable peeking’
This will attach multiple plans for the SQL for various bind values.
3. ”SQL with literals AND Cursor_sharing=similar” = “SQL with bind variables AND Adaptive cursor sharing”
Both do the same thing
Many thanks
November 07, 2011 - 12:10 pm UTC
1) cursor sharing = force or similar MIGHT turn a hard parse problem into a soft parse problem. This is a band aid that does not address any of the obvious security issues (think sql injection, if you are using literals and string concatenation to build your sql you are subject to it).
The only fix is to fix the bug in the developed code and use bind variables where appropriate.
2) yes
3) cursor sharing similar is deprecated and will be removed in the next release. In 11g, do not use it.
cursor sharing similar only works with sql that does not already use bind variables.
A reader, November 07, 2011 - 1:25 pm UTC
Hi Tom,
3) cursor sharing similar is deprecated and will be removed in the next release. In 11g, do not use it.
why should we not use in 11g ? is thre any other feature that will work nicely with SQLs with literals ?
November 07, 2011 - 3:53 pm UTC
if you MUST set cursor_sharing to a value other than "exact" (the default), just set it for force and let Adaptive cursor sharing take care of the potential bind issues that cursor sharing force attempted to.
A reader, November 08, 2011 - 8:04 am UTC
Thanks Tom,
In 11g -Adaptive cursor sharing works with ALL SQL with binds and it will generate multiple plans depends on the input for the bind values.
<=11g ----> we have cursor sharing similar which does the same thing as Adaptive cursor sharing but it works only with SQLs with literals ..
so you advise that -> JUST 'AUTO BIND' THE SQL WITH LITERAL
WITH 'FORCE' AND ADAPTIVE CURSOR SHARING WILL TAKE CARE OF IT ....NO NEED OF 'SIMILAR' ANY MORE ...
Correct Tom ?
November 08, 2011 - 8:08 am UTC
I advise that you leave cursor sharing to the default value of EXACT and fix the bug in the developed code - that is what I've always advised.
You can use cursor_sharing=force as a very very short term band-aid - but remember it
a) only turns a hard parse problem into a soft parse problem
b) does nothing to fix the security bugs you have as a result of not using binds appropriately
A reader, November 08, 2011 - 8:12 am UTC
Hi Tom,
thanks for that , I understood that already (fix the issue in the code and all about security , sql injection etc - the benefits of binds ) and surely we wil floow that ,,
but my point above is about 'using similar in 11G'
(assume that we don’t have access to code for the moment)
November 08, 2011 - 8:48 am UTC
and I answered that
a) do not use similar
b) use exact
c) if you are forced to turn a hard parse problem into a soft parse, still insecure problem, use force.
period.
A reader, November 10, 2011 - 2:55 pm UTC
Hi Tom,
1.Does Adaptive cursor (ACS) sharing work for all SQLS from PL/SQL?
2.What are the conditions that Adaptive cursor sharing does NOT work ? (ex: ACS wont work with SQL having more than 14 bind variables)
3.Are Bind variable peeking and ACS dependent and how CBO decides that -'yes this SQL is candidate for ACS ) ?
Cheers
November 10, 2011 - 5:51 pm UTC
1) not currently, plsql cursor caching can get in the way from time to time. That is a work in progress.
2) learn something new...
Yes, it seems to be 14 binds and below - it is OK, 16 and above, it doesn't kick in anymore, you can play with this to see it:
ops$tkyte%ORA11GR2> create table t
2 as
3 select case when rownum = 1 then 1 else 99 end id, a.*
4 from all_objects a
5 /
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx on t(id);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent=>100, method_opt=> 'for all indexed columns size 254' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select histogram from user_tab_columns where table_name = 'T' and column_name = 'ID';
HISTOGRAM
---------------
FREQUENCY
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1789076273
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:0
| 1 | SORT AGGREGATE | | 1 | 20 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:0
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=1)
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = 99;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 297 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | TABLE ACCESS FULL| T | 72272 | 1411K| 297 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=99)
ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable b1 number;
ops$tkyte%ORA11GR2> variable b2 number;
ops$tkyte%ORA11GR2> variable b3 number;
ops$tkyte%ORA11GR2> variable b4 number;
ops$tkyte%ORA11GR2> variable b5 number;
ops$tkyte%ORA11GR2> variable b6 number;
ops$tkyte%ORA11GR2> variable b7 number;
ops$tkyte%ORA11GR2> variable b8 number;
ops$tkyte%ORA11GR2> variable b9 number;
ops$tkyte%ORA11GR2> variable b10 number;
ops$tkyte%ORA11GR2> variable b11 number;
ops$tkyte%ORA11GR2> variable b12 number;
ops$tkyte%ORA11GR2> variable b13 number;
ops$tkyte%ORA11GR2> variable b14 number;
ops$tkyte%ORA11GR2> variable b15 number;
ops$tkyte%ORA11GR2> variable b16 number;
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :b1 := 1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :b1 and object_id in (:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16 */ ) ;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6j0722m3b1v55, child number 0
-------------------------------------
select count(subobject_name) from t where id = :b1 and object_id in
(:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16
*/ )
Plan hash value: 1789076273
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | 33 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 33 | 2 (0)| 00:0
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID"=:B2 OR "OBJECT_ID"=:B3 OR "OBJECT_ID"=:B4 OR
"OBJECT_ID"=:B5 OR "OBJECT_ID"=:B6 OR "OBJECT_ID"=:B7 OR "OBJECT_I
"OBJECT_ID"=:B9 OR "OBJECT_ID"=:B10 OR "OBJECT_ID"=:B11 OR "OBJECT
OR "OBJECT_ID"=:B13 OR "OBJECT_ID"=:B14 OR "OBJECT_ID"=:B15))
3 - access("ID"=:B1)
26 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :b1 := 99;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :b1 and object_id in (:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16 */ ) ;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6j0722m3b1v55, child number 0
-------------------------------------
select count(subobject_name) from t where id = :b1 and object_id in
(:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16
*/ )
Plan hash value: 1789076273
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | 33 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 33 | 2 (0)| 00:0
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID"=:B2 OR "OBJECT_ID"=:B3 OR "OBJECT_ID"=:B4 OR
"OBJECT_ID"=:B5 OR "OBJECT_ID"=:B6 OR "OBJECT_ID"=:B7 OR "OBJECT_I
"OBJECT_ID"=:B9 OR "OBJECT_ID"=:B10 OR "OBJECT_ID"=:B11 OR "OBJECT
OR "OBJECT_ID"=:B13 OR "OBJECT_ID"=:B14 OR "OBJECT_ID"=:B15))
3 - access("ID"=:B1)
26 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :b1 and object_id in (:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16 */ ) ;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6j0722m3b1v55, child number 0
-------------------------------------
select count(subobject_name) from t where id = :b1 and object_id in
(:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15 /* ,:b16
*/ )
Plan hash value: 1789076273
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | 33 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 33 | 2 (0)| 00:0
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID"=:B2 OR "OBJECT_ID"=:B3 OR "OBJECT_ID"=:B4 OR
"OBJECT_ID"=:B5 OR "OBJECT_ID"=:B6 OR "OBJECT_ID"=:B7 OR "OBJECT_I
"OBJECT_ID"=:B9 OR "OBJECT_ID"=:B10 OR "OBJECT_ID"=:B11 OR "OBJECT
OR "OBJECT_ID"=:B13 OR "OBJECT_ID"=:B14 OR "OBJECT_ID"=:B15))
3 - access("ID"=:B1)
26 rows selected.
I'm not aware of other restrictions.
3) yes, you need to have bind peeking happen for adaptive cursor sharing to even be considered
A reader, November 10, 2011 - 6:00 pm UTC
Many thanks Tom for your time and help for clarifying the above doubts .
ACS does not work for the SQL from PL/SQL ..
Is it becuase of pl/sql cursor cache ? ACS kicks in when it sees a 'parse' call but becuase of pl/sql cursor cache , there will be only one parse call and many execution so NO ACS happens .. Am I correct ?
November 10, 2011 - 6:00 pm UTC
yes, correct.
ACS working only one way ?
Vincent, March 26, 2012 - 8:25 am UTC
Hi Tom,
It seems that sometimes ACS works only one way.
If the statement is parsed with a selective bind value and later executed with less selective value then a new child cursor is created with a plan using full table scans and hash joins instead of index range scan and nested loops but if we swap the values it keeps using the full table scan plan.
In our real life case, the statement is executed mostly with selective values so, if it happens to be parsed with a poorly selective value, it is very important that ACS detects that this plan is not suitable for most of the following executions.
Can you explain this behavior ?
Here is an example :
SQL>
SQL> drop table acs_test_1 purge;
Table supprimée.
SQL> drop table acs_test_2 purge;
Table supprimée.
SQL>
SQL> create table acs_test_1(id number primary key, status number, text1 varchar2(50));
Table créée.
SQL>
SQL> insert into acs_test_1(id, status, text1)
2 select level, decode(mod(level, 2), 0, 0, level), 'Text 1 for '||to_char(level)
3 from dual
4 connect by level <= 100000;
100000 ligne(s) créée(s).
SQL>
SQL> commit;
Validation effectuée.
SQL>
SQL> create index acst1_status on acs_test_1(status);
Index créé.
SQL>
SQL> create table acs_test_2(id number primary key, text2 varchar2(50));
Table créée.
SQL>
SQL> insert into acs_test_2(id, text2)
2 select level, 'Text 2 for '||to_char(level)
3 from dual
4 connect by level <= 100000;
100000 ligne(s) créée(s).
SQL>
SQL> commit;
Validation effectuée.
SQL>
SQL>
SQL> execute dbms_stats.gather_table_stats('SYSTEM', 'ACS_TEST_1', method_opt=>'for all indexed columns size skewonly', cascade=>true);
Procédure PL/SQL terminée avec succès.
SQL> execute dbms_stats.gather_table_stats('SYSTEM', 'ACS_TEST_2', method_opt=>'for all indexed columns size skewonly', cascade=>true);
Procédure PL/SQL terminée avec succès.
SQL>
SQL>
SQL> alter system flush shared_pool;
Système modifié.
SQL>
SQL> variable status number
SQL>
SQL> execute :status := 1
Procédure PL/SQL terminée avec succès.
SQL>
SQL> select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5 and B.id (+) = A.id;
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 1 Text 2 for 1
SQL>
SQL> execute :status := 0
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 99998 Text 2 for 99998
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 99998 Text 2 for 99998
SQL>
SQL> execute :status := 1
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 1 Text 2 for 1
SQL>
SQL> select child_number, plan_hash_value, is_bind_sensitive B, is_bind_aware A, is_shareable S
2 from v$sql
3 where sql_id='&sql_id'
4 order by 1, 2;
ancien 3 : where sql_id='&sql_id'
nouveau 3 : where sql_id='8s3p3jcfw01xw'
CHILD_NUMBER PLAN_HASH_VALUE B A S
------------ --------------- - - -
0 2171460944 Y N N
1 1722651216 Y Y Y
2 2171460944 Y Y Y
SQL>
SQL> select child_number, bucket_id, count
2 from v$sql_cs_histogram
3 where sql_id = '&sql_id'
4 order by child_number, bucket_id;
ancien 3 : where sql_id = '&sql_id'
nouveau 3 : where sql_id = '8s3p3jcfw01xw'
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 0
1 1 1
1 2 0
2 0 1
2 1 0
2 2 0
9 ligne(s) sélectionnée(s).
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('8s3p3jcfw01xw',0,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(A.text1), max(B.text2) from acs_test_1 A,
acs_test_2 B where A.status = :status and B.id (+) = A.id
Plan hash value: 2171460944
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS OUTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_1 |
| 4 | INDEX RANGE SCAN | ACST1_STATUS |
| 5 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_2 |
| 6 | INDEX UNIQUE SCAN | SYS_C00124715 |
-------------------------------------------------------
19 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('8s3p3jcfw01xw',1,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(A.text1), max(B.text2) from acs_test_1 A,
acs_test_2 B where A.status = :status and B.id (+) = A.id
Plan hash value: 1722651216
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN OUTER | |
| 3 | TABLE ACCESS FULL| ACS_TEST_1 |
| 4 | TABLE ACCESS FULL| ACS_TEST_2 |
------------------------------------------
17 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('8s3p3jcfw01xw',2,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(A.text1), max(B.text2) from acs_test_1 A,
acs_test_2 B where A.status = :status and B.id (+) = A.id
Plan hash value: 2171460944
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | NESTED LOOPS OUTER | |
| 3 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_1 |
| 4 | INDEX RANGE SCAN | ACST1_STATUS |
| 5 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_2 |
| 6 | INDEX UNIQUE SCAN | SYS_C00124715 |
-------------------------------------------------------
19 ligne(s) sélectionnée(s).
SQL>
SQL>
SQL> alter system flush shared_pool;
Système modifié.
SQL>
SQL> variable status number
SQL>
SQL> execute :status := 0
Procédure PL/SQL terminée avec succès.
SQL>
SQL> select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5 and B.id (+) = A.id;
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 99998 Text 2 for 99998
SQL>
SQL> execute :status := 1
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 1 Text 2 for 1
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 1 Text 2 for 1
SQL>
SQL> execute :status := 0
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1 select max(A.text1), max(B.text2)
2 from acs_test_1 A,
3 acs_test_2 B
4 where A.status = :status
5* and B.id (+) = A.id
MAX(A.TEXT1) MAX(B.TEXT2)
-------------------------------------------------- --------------------------------------------------
Text 1 for 99998 Text 2 for 99998
SQL>
SQL> select child_number, plan_hash_value, is_bind_sensitive B, is_bind_aware A, is_shareable S
2 from v$sql
3 where sql_id='&sql_id'
4 order by 1, 2;
ancien 3 : where sql_id='&sql_id'
nouveau 3 : where sql_id='8s3p3jcfw01xw'
CHILD_NUMBER PLAN_HASH_VALUE B A S
------------ --------------- - - -
0 1722651216 Y N Y
SQL>
SQL> select child_number, bucket_id, count
2 from v$sql_cs_histogram
3 where sql_id = '&sql_id'
4 order by child_number, bucket_id;
ancien 3 : where sql_id = '&sql_id'
nouveau 3 : where sql_id = '8s3p3jcfw01xw'
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 0
0 1 4
0 2 0
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('8s3p3jcfw01xw',0,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(A.text1), max(B.text2) from acs_test_1 A,
acs_test_2 B where A.status = :status and B.id (+) = A.id
Plan hash value: 1722651216
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN OUTER | |
| 3 | TABLE ACCESS FULL| ACS_TEST_1 |
| 4 | TABLE ACCESS FULL| ACS_TEST_2 |
------------------------------------------
17 ligne(s) sélectionnée(s).
Regards
Vincent
March 26, 2012 - 10:39 am UTC
it is not so much that is only works one was as much as it is that it is triggered mostly by IO's performed.
When you used the index first and then it gets a non-selective input, the IO's that were 'expected' to have happened greatly changed.
However, in your case, when the non-selective inputs are first - the expected IO's are the same for selective as well as non-selective - so the plan is never changed.
Why never change
A reader, March 27, 2012 - 1:13 am UTC
Hi Tom,
Per your last reply, not quite clear, could you elaborate?
'When you used the index first and then it gets a non-selective input, the IO's that were 'expected' to have happened greatly changed. '
So the change means increase, right? as non-selective one will need much more IO than selective one?
'However, in your case, when the non-selective inputs are first - the expected IO's are the same for selective as well as non-selective - so the plan is never changed'
How comes here the expected IO is same? Anyway, they are expecting totally un-comparable rows, so IO should not be same per my understanding.
Is it due to non-selective inputs first result in ALL rows being cached?
I tried to flush buffer cache after non-selective, then issue the selective one, but still got FULL TABLE SCAN.
If plan 'never' changed, does it mean the problem will exist forever, how to overcome it, use SQL PROFILE?
March 27, 2012 - 7:52 am UTC
So the change means increase, right? as non-selective one will need much more
IO than selective one?
change means change - in this case, yes, they increased - we expected four or five IO's but then got many more.
How comes here the expected IO is same?
because a plan includes an expectation of the IO's to be performed, the cost is heavily weighted by that.
so IO should not be same per my understanding.
run it, measure the IO's of the selective vs non-selective using the "bad" plan. The IO's will be identical.
Is it due to non-selective inputs first result in ALL rows being cached?
it would not matter - caching doesn't affect the consistent gets (the IO's in question).
You can use a query plan baseline and/or sql plan management if you want the index plan to be preferred in all cases.
A reader, March 27, 2012 - 9:23 am UTC
Hi Tom,
you said....
"run it, measure the IO's of the selective vs non-selective using the "bad" plan. The IO's will be identical."
(1) but that is obivous question is why it does not create new child cursor for selective column i.e. index access?
(2) Also as per data generated what is the point of outer join?
Thanks..
March 27, 2012 - 9:46 am UTC
1) because the main trigger - unexpected difference between expected IOs and actual IOs - did not happen in this case.
2) it doesn't matter, it was just an example presented by the original poster to look a little bit like their problem. There are no constraints in place to make the parent child relation be mandatory and their requirement was to report all parent records regardless of the existence of a child I suppose.
A reader, March 27, 2012 - 10:39 am UTC
March 27, 2012 - 11:39 am UTC
no, the cursor is fully fetched from here in all of the examples.
a *trigger* for adaptive cursor sharing (actual IO wasn't different from expected IO) wasn't encountered in this particular case.
ACS working only one way ? (continued)
Vincent, March 27, 2012 - 10:50 am UTC
Hi Tom,
Thank you for your answer.
I guessed it could be the explaination and I'm disapointed as I expected ACS to solve my bind related plan stability issue.
However, it seems that in simpler cases like the one from http://www.oracle-base.com/articles/11g/AdaptiveCursorSharing_11gR1.php ACS is able to switch from a full table scan to an index range scan even if the buffer gets count of the full table scan is not changed by a more selective bind value.
Here is the same test but with their query :
SQL>
SQL>
SQL> -- sample from http://www.oracle-base.com/articles/11g/AdaptiveCursorSharing_11gR1.php
SQL>
SQL> DROP TABLE acs_test_tab;
Table supprimée.
SQL>
SQL> CREATE TABLE acs_test_tab (
2 id NUMBER,
3 record_type NUMBER,
4 description VARCHAR2(50),
5 CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
6 );
Table créée.
SQL>
SQL> CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);
Index créé.
SQL>
SQL> DECLARE
2 TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
3 l_tab t_acs_test_tab := t_acs_test_tab();
4
5 BEGIN
6 FOR i IN 1 .. 100000 LOOP
7 l_tab.extend;
8 IF MOD(i,2)=0 THEN
9 l_tab(l_tab.last).record_type := 2;
10 ELSE
11 l_tab(l_tab.last).record_type := i;
12 END IF;
13
14 l_tab(l_tab.last).id := i;
15 l_tab(l_tab.last).description := 'Description for ' || i;
16 END LOOP;
17
18 FORALL i IN l_tab.first .. l_tab.last
19 INSERT INTO acs_test_tab VALUES l_tab(i);
20
21 COMMIT;
22 END;
23 /
Procédure PL/SQL terminée avec succès.
SQL>
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for all indexed columns size skewonly',
cascade=>TRUE);
Procédure PL/SQL terminée avec succès.
SQL>
SQL>
SQL> alter system flush shared_pool;
Système modifié.
SQL>
SQL> VARIABLE l_record_type NUMBER;
SQL>
SQL> EXEC :l_record_type := 1;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
MAX(ID)
----------
1
SQL>
SQL> EXEC :l_record_type := 2;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
100000
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
100000
SQL>
SQL> EXEC :l_record_type := 1;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
1
SQL>
SQL> select child_number, plan_hash_value, is_bind_sensitive B, is_bind_aware A, is_shareable S
2 from v$sql
3 where sql_id='&sql_id'
4 order by 1, 2;
ancien 3 : where sql_id='&sql_id'
nouveau 3 : where sql_id='9bmm6cmwa8saf'
CHILD_NUMBER PLAN_HASH_VALUE B A S
------------ --------------- - - -
0 3987223107 Y N N
1 509473618 Y Y Y
2 3987223107 Y Y Y
SQL>
SQL> select child_number, bucket_id, count
2 from v$sql_cs_histogram
3 where sql_id = '&sql_id'
4 order by child_number, bucket_id;
ancien 3 : where sql_id = '&sql_id'
nouveau 3 : where sql_id = '9bmm6cmwa8saf'
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 0
1 1 1
1 2 0
2 0 1
2 1 0
2 2 0
9 ligne(s) sélectionnée(s).
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',0,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB |
| 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I |
-------------------------------------------------------------------
15 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',1,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| ACS_TEST_TAB |
-------------------------------------------
14 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',2,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB |
| 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I |
-------------------------------------------------------------------
15 ligne(s) sélectionnée(s).
SQL>
SQL>
SQL> alter system flush shared_pool;
Système modifié.
SQL>
SQL> VARIABLE l_record_type NUMBER;
SQL>
SQL> EXEC :l_record_type := 2;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
MAX(ID)
----------
100000
SQL>
SQL> EXEC :l_record_type := 1;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
1
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
1
SQL>
SQL> EXEC :l_record_type := 2;
Procédure PL/SQL terminée avec succès.
SQL>
SQL> run
1* SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
MAX(ID)
----------
100000
SQL>
SQL> select child_number, plan_hash_value, is_bind_sensitive B, is_bind_aware A, is_shareable S
2 from v$sql
3 where sql_id='&sql_id'
4 order by 1, 2;
ancien 3 : where sql_id='&sql_id'
nouveau 3 : where sql_id='9bmm6cmwa8saf'
CHILD_NUMBER PLAN_HASH_VALUE B A S
------------ --------------- - - -
0 509473618 Y N N
1 3987223107 Y Y Y
2 509473618 Y Y Y
SQL>
SQL> select child_number, bucket_id, count
2 from v$sql_cs_histogram
3 where sql_id = '&sql_id'
4 order by child_number, bucket_id;
ancien 3 : where sql_id = '&sql_id'
nouveau 3 : where sql_id = '9bmm6cmwa8saf'
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 1
1 1 0
1 2 0
2 0 0
2 1 1
2 2 0
9 ligne(s) sélectionnée(s).
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',0,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',0,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| ACS_TEST_TAB |
-------------------------------------------
14 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',1,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',1,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 3987223107
-------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| ACS_TEST_TAB |
| 3 | INDEX RANGE SCAN | ACS_TEST_TAB_RECORD_TYPE_I |
-------------------------------------------------------------------
15 ligne(s) sélectionnée(s).
SQL> select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'));
ancien 1 : select * from table(dbms_xplan.display_cursor('&sql_id',2,'BASIC'))
nouveau 1 : select * from table(dbms_xplan.display_cursor('9bmm6cmwa8saf',2,'BASIC'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| ACS_TEST_TAB |
-------------------------------------------
14 ligne(s) sélectionnée(s).
SQL>
Regards
Vincent
March 28, 2012 - 8:26 am UTC
I think it might have to do with the row estimates not being far enough off as well.
In the simple case above, if you run with statistics level = 'all' and use dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) - you would find that we get output like this:
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type
Plan hash value: 509473618
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 499 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 499 |
|* 2 | TABLE ACCESS FULL| ACS_TEST_TAB | 1 | 49213 | 1 |00:00:00.01 | 499 |
---------------------------------------------------------------------------------------------
we expected a total of 49,213 rows (e-rows) and got 1 (a-rows). That would trigger it.
However, with your example - with the join, we get:
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B
where A.status = :status and B.id (+) = A.id
Plan hash value: 1722651216
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.22 | 815 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.22 | 815 | | | |
|* 2 | HASH JOIN OUTER | | 1 | 48425 | 1 |00:00:00.22 | 815 | 855K| 855K| 480K (0)|
|* 3 | TABLE ACCESS FULL| ACS_TEST_1 | 1 | 48425 | 1 |00:00:00.01 | 442 | | | |
| 4 | TABLE ACCESS FULL| ACS_TEST_2 | 1 | 100K| 100K|00:00:00.05 | 373 | | | |
-----------------------------------------------------------------------------------------------------------------------
the 100k estimate is "dead on" - the 48,425 is off - but is that enough to trigger the change. In this case, apparently it is not.
I'm not sure if this is a bug or expected at this point. If you have access to support, you could consider filing an SR/tar/whatever they call them today... A test case would be:
drOP TABLE acs_test_tab;
CREATE TABLE acs_test_tab (
id NUMBER,
record_type NUMBER,
description VARCHAR2(50),
CONSTRAINT acs_test_tab_pk PRIMARY KEY (id)
);
CREATE INDEX acs_test_tab_record_type_i ON acs_test_tab(record_type);
DECLARE
TYPE t_acs_test_tab IS TABLE OF acs_test_tab%ROWTYPE;
l_tab t_acs_test_tab := t_acs_test_tab();
BEGIN
FOR i IN 1 .. 100000 LOOP
l_tab.extend;
IF MOD(i,2)=0 THEN
l_tab(l_tab.last).record_type := 2;
ELSE
l_tab(l_tab.last).record_type := i;
END IF;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ' || i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO acs_test_tab VALUES l_tab(i);
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'acs_test_tab', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);
SET LINESIZE 200
set serveroutput off
alter session set statistics_level = 'ALL';
VARIABLE l_record_type NUMBER;
EXEC :l_record_type := 2;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets from v$sql where sql_text like 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :%';
EXEC :l_record_type := 1;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets from v$sql where sql_text like 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :%';
SELECT MAX(id) FROM acs_test_tab WHERE record_type = :l_record_type;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets from v$sql where sql_text like 'SELECT MAX(id) FROM acs_test_tab WHERE record_type = :%';
that bit shows the flip from full scan to index with all of the relevant metrics, this bit shows your part:
drop table acs_test_1 purge;
drop table acs_test_2 purge;
create table acs_test_1(id number primary key, status number, text1 varchar2(50));
insert into acs_test_1(id, status, text1)
select level, decode(mod(level, 2), 0, 0, level), 'Text 1 for '||to_char(level)
from dual
connect by level <= 100000;
commit;
create index acst1_status on acs_test_1(status);
create table acs_test_2(id number primary key, text2 varchar2(50));
insert into acs_test_2(id, text2)
select level, 'Text 2 for '||to_char(level)
from dual
connect by level <= 100000;
commit;
execute dbms_stats.gather_table_stats(user, 'ACS_TEST_1', method_opt=>'for all indexed columns size skewonly', cascade=>true);
execute dbms_stats.gather_table_stats(user, 'ACS_TEST_2', method_opt=>'for all indexed columns size skewonly', cascade=>true);
variable status number
set autotrace traceonly explain
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = 0 and B.id (+) = A.id;
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = 1 and B.id (+) = A.id;
set autotrace off
alter system flush shared_pool;
alter session set statistics_level = 'ALL';
exec :status := 0
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = :status and B.id (+) = A.id;
select * from table( dbms_xplan.display_cursor );
select executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select max(A.text1), max(B.text2) from acs_test_1 A%';
exec :status := 1
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = :status and B.id (+) = A.id;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select max(A.text1), max(B.text2) from acs_test_1 A%';
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = :status and B.id (+) = A.id;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select max(A.text1), max(B.text2) from acs_test_1 A%';
select max(A.text1), max(B.text2) from acs_test_1 A, acs_test_2 B where A.status = :status and B.id (+) = A.id;
select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) );
select executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select max(A.text1), max(B.text2) from acs_test_1 A%';
If you do not have access to support, I'm willing to file a bug to see what they say (but it is best if you can do it)
ACS working only one way ? (continued)
Vincent, March 29, 2012 - 7:08 am UTC
Hi Tom,
I filed a SR and will post the reply when I get it.
Thanks
March 29, 2012 - 7:09 am UTC
Just fyi: I patched up to 11.2.0.3 (been meaning to do that) and the issue reproduces.
ACS working only one way ? (continued)
Vincent, April 06, 2012 - 10:13 am UTC
Hi Tom,
Support filed a bug. I will post the final status.
Regards
cursor_sharing=similar
A reader, April 06, 2012 - 1:42 pm UTC
Tom,
We have our flagship application which relies on cursor_sharing=similar in the database. Currently the database is at 10g. We are soon upgrading the database to 11gr2. I understand that Oracle strongly discourages the use of cursor_sharing=similar and has even discontinued this setting in version 12.
We have done testing with this parameter set but the transaction volume on the test system is not comparable to the actual production database and test system does not test every nook and cranny of the application.
I have read many MOS notes/articles/blog posts but there is no real life example where someone says that "yes, I set cursor_sharing=similar in 11gr2 and my database got killed".
My question is: If we go into production with 11gr2 and cursor_sharing=similar, is it going to kill the database with high CPU usage or some other bad thing is going to happen? We do not create histograms.
You have seen lot of real life situatons so I ask your invaluable opinion.
Thanks...
April 08, 2012 - 5:38 pm UTC
In 11gr2 - it (similar) will work just like it did in 10g
Just beware that it doesn't exist in the next release.
Adaptive cursor sharing has replaced it entirely.
Adaptive cursor sharing and multiple version counts
Lal, June 13, 2012 - 8:33 am UTC
In one of our production environments we have a query with 347 child cursors. When i checked the v$sql_shared_cursor table the column BIND_EQUIV_FAILURE was 'Y' for all the child cursors.
From Oracle documentation BIND_EQUIV_FAILURE means
"The bind value's selectivity does not match that used to optimize the existing child cursor"
BIND_EQUIV_FAILURE seems to be a new reason for mutiple child cursors in 11.2.
The distinct plan_hash_value for 347 child cursors is 6.
I have the following queries
1)347 hard parses were done for this case?
2)Is there any V$view which captures the peeked bind variable values for which a hard parse was done, so that i can cross check the data for those values?
June 14, 2012 - 5:45 am UTC
1) yes
2) dbms_xplan can display that for you from v$sql_plan
ops$tkyte%ORA11GR2> create table t
2 as
3 select 99 id, a.*
4 from stage a
5 where rownum <= 20000;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> update t set id = 1 where rownum = 1;
1 row updated.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx on t(id);
Index created.
ops$tkyte%ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=>'for all indexed columns size 254',
5 estimate_percent => 100,
6 cascade=>TRUE );
7 end;
8 /
PL/SQL procedure successfully completed.
<b>I ran a few queries here.... will query up their sql ids and child #'s below...</b>
ops$tkyte%ORA11GR2> begin
2 for x in (select sql_id, child_number from v$sql
3 where sql_text = 'select count(object_type) from t n_is_1_first where id = :n')
4 loop
5 for y in (select PLAN_TABLE_OUTPUT
6 from table(dbms_xplan.display_cursor(x.sql_id,x.child_number,
7 'typical +peeked_binds')) )
8 loop
9 dbms_output.put_line( '.' || y.plan_table_output );
10 end loop;
11 for i in 1 .. 5
12 loop
13 dbms_output.put_line( '.' );
14 end loop;
15 end loop;
16 end;
17 /
.SQL_ID 2x454rqkvtpbc, child number 0
.-------------------------------------
.select count(object_type) from t n_is_1_first where id = :n
.
.Plan hash value: 1789076273
.
.--------------------------------------------------------------------------------------
.| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
.--------------------------------------------------------------------------------------
.| 0 | SELECT STATEMENT | | | | 2 (100)| |
.| 1 | SORT AGGREGATE | | 1 | 14 | | |
.| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 2 (0)| 00:00:01 |
.|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
.--------------------------------------------------------------------------------------
.
.Peeked Binds (identified by position):
.--------------------------------------
.
. 1 - :N (NUMBER): 1
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
. 3 - access("ID"=:N)
.
.
.
.
.
.
.SQL_ID 2x454rqkvtpbc, child number 1
.-------------------------------------
.select count(object_type) from t n_is_1_first where id = :n
.
.Plan hash value: 2966233522
.
.---------------------------------------------------------------------------
.| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
.---------------------------------------------------------------------------
.| 0 | SELECT STATEMENT | | | | 82 (100)| |
.| 1 | SORT AGGREGATE | | 1 | 14 | | |
.|* 2 | TABLE ACCESS FULL| T | 19999 | 273K| 82 (0)| 00:00:01 |
.---------------------------------------------------------------------------
.
.Peeked Binds (identified by position):
.--------------------------------------
.
. 1 - :N (NUMBER): 99
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
. 2 - filter("ID"=:N)
.
.
.
.
.
.
PL/SQL procedure successfully completed.
ACS Vs SQL Plan baselines
Rajeshwaran, Jeyabal, June 17, 2012 - 12:07 pm UTC
Tom:
I was reading about sql-plan baselines from oracle magazine.
http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29spm-092092.html But i dont understand its usefulness over Adaptive cursor sharing.
1) Both generated the optimal plan based on bind variables.
2) ACS will do a mistake once and learn from its mistake and come up with a best plan again for subsequent execution. But sqlplan baselines will always uses only the ACCEPTED plan's from the plan_history (that is sql-plan baselines). The un-accepeted plans has to evolved later point to add this plan to plan baselines.
Can you provide some guidelines, When should we consider for sql-plan baselines, while ACS is readily available (without any changes to db-parameters) in the 11g optimizer ? (or) When are the advantage of sqlplan baseline over ACS ?
June 18, 2012 - 8:47 am UTC
1) no, sql plan baselines do not "generate" plan. a sql plan baseline is like a stored plan for a query. It will be used. the baseline does not "generate" anything, the baseline controls which set of plan may or may not be used.
adaptive cursor sharing on the other hand, permits s potentially large (but hopefully small) set of plans to be generated for a single sql statement based on the bind inputs.
2) bet plan for a set of bind variable inputs, it may come up with several "best plans".
... When are the advantage of sqlplan baseline over ACS ? ...
this is perhaps the real meat of the question here.
SQL Plan Baselines are excellent during an upgrade for example, you are going from 11.1 to 11.2 - you would like it so that no plans "regress" (get much worse). You know you are using a new version of the optimizer - you know that plans can and more importantly WILL change. When a plan changes - one of three things will happen:
1) the query will run faster
2) the query will not run faster, nor slower
3) the query will run much much slower, taking much longer than before
All three are possible - only #1 is something you really want. SQL Plan baselines can help you achieve that. You save the set of plans from 11.1 while 11.1 is running. when you upgrade to 11.2 - you tell us "only use plans from the baseline - even if adaptive cursor sharing comes up with a new plan that we've never see before - don't use it YET". We'll put plans that are not in the current baseline into a holding area and later, during your maintenance window, we'll evaluate that new plan and see if it uses significantly less resources than the current plan in the baseline. If it does - we'll promote it into the baseline and start using it. If not, we'll NOT use it.
So adaptive cursor sharing will allow us to generate an optimal set of plans and sql plan baselines will allow us to evaluate that plan before actually inflicting on the end user. The upgrade should happen with no query plan running slower after the upgrade and over time - plans would "evolve" to be better. So, you get a seamless, stress free upgrade and the potential for improved performance as time goes on.
SQL plan not stable
James Su, November 06, 2012 - 10:23 am UTC
Hi Tom,
We found this happened many times on our 11gR2 DB. When a SQL is run the first time----it's fast; then it becomes slow from the second run. If we look at V$SQL we can see two child numbers 0 and 1. There's no bind variable used in the SQL.
For example we have this table:
CREATE TABLE FX_RATE
(
FX_RATE_ID NUMBER NOT NULL PRIMARY KEY,
RATE_DTE DATE NOT NULL,
RATE_DELAY NUMBER,
DVDR_RATE NUMBER(17,9) NOT NULL,
PROVIDER VARCHAR2(4000 BYTE),
BASE_CURR_ID NUMBER NOT NULL,
QUOTE_CURR_ID NUMBER NOT NULL,
CREATED_DTE TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP,
CREATED_BY VARCHAR2(4000 BYTE),
UPDATED_DTE TIMESTAMP(6) WITH TIME ZONE,
UPDATED_BY VARCHAR2(4000 BYTE)
);
CREATE UNIQUE INDEX FX_DATE_CURR_UN ON FX_RATE(RATE_DTE, BASE_CURR_ID, QUOTE_CURR_ID);
Number of rows in table:
COUNT(*)
---------
526365
The SQL:
SELECT *
FROM (
SELECT /*+ use_nl index(quote_fx1 FX_DATE_CURR_UN) index(quote_fx2 FX_DATE_CURR_UN) */
quote_fx1.DVDR_RATE r1
,quote_fx2.DVDR_RATE r2
FROM fx_rate quote_fx1,fx_rate quote_fx2
where quote_fx1.quote_curr_id = 144
AND quote_fx2.rate_dte <= TO_DATE ('02/01/2012', 'mm/dd/yyyy')
and quote_fx1.RATE_DTE = quote_fx2.RATE_DTE
and quote_fx2.QUOTE_CURR_ID = 145
ORDER BY quote_fx2.rate_dte DESC
)
WHERE ROWNUM=1;
And here's what we see from v$sql:
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware,IS_SHAREABLE,BUFFER_GETS,EXECUTIONS from v$sql where sql_id ='cx5nazdta45ta';
SQL_ID CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE BUFFER_GETS EXECUTIONS
cx5nazdta45ta 0 N N N 13 1
cx5nazdta45ta 1 N N Y 195134 13
You can see the second plan is more expensive, but it stays.
Below is the plan:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID cx5nazdta45ta, child number 1
-------------------------------------
SELECT * FROM ( SELECT /*+ use_nl index(quote_fx1
FX_DATE_CURR_UN) index(quote_fx2 FX_DATE_CURR_UN) */
quote_fx1.DVDR_RATE r1 ,quote_fx2.DVDR_RATE r2 FROM
fx_rate quote_fx1,fx_rate quote_fx2 where quote_fx1.quote_curr_id
= 144 AND quote_fx2.rate_dte <= TO_DATE ('02/01/2012',
'mm/dd/yyyy') and quote_fx1.RATE_DTE = quote_fx2.RATE_DTE
and quote_fx2.QUOTE_CURR_ID = 145 ORDER BY
quote_fx2.rate_dte DESC ) WHERE ROWNUM=1
Plan hash value: 1276507294
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 2048 | 2048 | 2048 (0)|
| 4 | NESTED LOOPS | | | | | |
| 5 | NESTED LOOPS | | 1 | | | |
|* 6 | TABLE ACCESS FULL | FX_RATE | 1 | | | |
|* 7 | INDEX RANGE SCAN | FX_DATE_CURR_UN | 1 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| FX_RATE | 1 | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
3 - filter(ROWNUM=1)
6 - filter(("QUOTE_FX1"."QUOTE_CURR_ID"=144 AND "QUOTE_FX1"."RATE_DTE"<=TO_DATE('
2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
7 - access("QUOTE_FX1"."RATE_DTE"="QUOTE_FX2"."RATE_DTE" AND
"QUOTE_FX2"."QUOTE_CURR_ID"=145)
filter(("QUOTE_FX2"."QUOTE_CURR_ID"=145 AND "QUOTE_FX2"."RATE_DTE"<=TO_DATE('
2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
46 rows selected.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID cx5nazdta45ta, child number 0
-------------------------------------
SELECT * FROM ( SELECT /*+ use_nl index(quote_fx1
FX_DATE_CURR_UN) index(quote_fx2 FX_DATE_CURR_UN) */
quote_fx1.DVDR_RATE r1 ,quote_fx2.DVDR_RATE r2 FROM
fx_rate quote_fx1,fx_rate quote_fx2 where quote_fx1.quote_curr_id
= 144 AND quote_fx2.rate_dte <= TO_DATE ('02/01/2012',
'mm/dd/yyyy') and quote_fx1.RATE_DTE = quote_fx2.RATE_DTE
and quote_fx2.QUOTE_CURR_ID = 145 ORDER BY
quote_fx2.rate_dte DESC ) WHERE ROWNUM=1
Plan hash value: 4196933963
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | COUNT STOPKEY | | |
| 2 | VIEW | | 2 |
| 3 | NESTED LOOPS | | |
| 4 | NESTED LOOPS | | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID | FX_RATE | 3395 |
|* 6 | INDEX RANGE SCAN DESCENDING| FX_DATE_CURR_UN | 2 |
|* 7 | INDEX RANGE SCAN | FX_DATE_CURR_UN | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | FX_RATE | 1 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
6 - access("QUOTE_FX1"."RATE_DTE"<=TO_DATE(' 2012-02-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "QUOTE_FX1"."QUOTE_CURR_ID"=144)
filter("QUOTE_FX1"."QUOTE_CURR_ID"=144)
7 - access("QUOTE_FX1"."RATE_DTE"="QUOTE_FX2"."RATE_DTE" AND
"QUOTE_FX2"."QUOTE_CURR_ID"=145)
filter(("QUOTE_FX2"."QUOTE_CURR_ID"=145 AND
"QUOTE_FX2"."RATE_DTE"<=TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
46 rows selected.
You can see there's TABLE ACCESS FULL in child number 1, while it's INDEX RANGE SCAN DESCENDING in child number 0.
Can you tell me why Oracle decides to generate the second plan and let it stay even it's worse? Thanks!
November 06, 2012 - 10:52 am UTC
- cardinality feedback used for this statement
it used cardinality feedback to develop the second plan - but it looks like a mistake since the estimated cardinality was just 1, less than before.
the reason for the second plan: cardinality feedback (google that term, it has been written about - examples on this site as well).
as for it being worse, that sounds like a "product issue", I'll ask you to utilize support for that, they may suggest disabling cardinality feedback while it is worked on.
Adaptive cursor sharing in XMLDB
Gokul, January 12, 2013 - 7:29 pm UTC
Tom,
As you might know XPaths in XMLDB are more like columns of table and they are required for the optimizer to choose indexes. But they are mentioned as string literals in XMLQuery , XMLExists and XMLTable constructs. So if i use anything other than CURSOR_SHARING=EXACT, will Optimizer understand the real XPath query? Will adaptive sharing help it in anyway? Definitely Histograms cannot be collected on XMLType columns.
Has Oracle put some code at parser to treat XMLFunctions differently?
Thanks,
Gokul.
January 15, 2013 - 10:13 am UTC
cursor sharing force will sometimes appear to bind things that are not bindable - the easiest example I can show you what I mean by that is this:
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter session set cursor_sharing=force;
Session altered.
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> select * from t order by 1;
no rows selected
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID a4wvswt9426hp, child number 0
-------------------------------------
select * from t order by :"SYS_B_0"
Plan hash value: 961378228
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT ORDER BY | | 1 | 13 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
the number one above is the ordinal postition of the sort column - that is something that cannot be bound - in fact if it were bound, it would be a bug - consider:
ops$tkyte%ORA11GR2> create table t ( x int );
Table created.
ops$tkyte%ORA11GR2> insert into t values ( 2 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> exec :x := 1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t order by 1;
X
----------
1
2
ops$tkyte%ORA11GR2> select * from t order by :x;
X
----------
2
1
ops$tkyte%ORA11GR2>
ordering by :x is very very different than order by 1 (ordering by a constant is very different from ordering by a column position)
so, you can see things that look like binds that are not with cursor sharing=force/similar
reason 4,134,232 why using cursor sharing force/similar should be frowned upon entirely - used only as a crutch while the developers fix their bug - and used only around the bits of code that need it (not at the system level, hopefully not even at the session level)
brillant, but
Sokrates, January 15, 2013 - 2:10 pm UTC
very interesting example !
...cursor sharing force will sometimes appear to bind things that are not bindable ... the number one above is the ordinal postition of the sort column - that is something that cannot be bound...
yes, I agree, that sounds very reasonible.
However, even autotrace shows me a BIND:
...
PARSING IN CURSOR #47596352305168 len=40 dep=0 uid=203 oct=3 lid=203 tim=1358279535090697 hv=1075380375 ad='2ebf83c18' sqlid='12ahzkt01k04r'
select * from t order by :"SYS_B_0" desc
END OF STMT
...
BINDS #47596352305168:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0500 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2f9d8d9f8 bln=22 avl=02 flg=09
value=1
....
So that means, autotrace is lying ?
January 15, 2013 - 2:47 pm UTC
sort of, yes and no. trace isn't really lying - it just doesn't know that it is carrying a bind that isn't truly a bind.
the cursor knows this is not bindable. it also knows it has been tricked into having to think it is bindable. It knows it is being "lied" to - the column has been flagged as "not safe to bind". The plan does not consider it for binding.
see how the plan just drops the order by entirely for the "really using binds" query - but preserves an order by step for the order by ordinal column query:
ops$tkyte%ORA11GR2> create table t ( x int, y int, z int );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec gen_data( 'T', 5 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> exec :x := 1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> alter session set cursor_sharing=force;
Session altered.
ops$tkyte%ORA11GR2> select * from t order by 1;
X Y Z
---------- ---------- ----------
1348325717 9277928083 9820299282
1981728826 3362440226 7513734294
3004622407 2363921353 3452943798
4714386150 9810403062 9811567814
6273878771 2084469958 8806798632
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a4wvswt9426hp, child number 0
-------------------------------------
select * from t order by :"SYS_B_0"
Plan hash value: 961378228
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 5 | 195 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 5 | 195 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
ops$tkyte%ORA11GR2> select * from t order by :x;
X Y Z
---------- ---------- ----------
3004622407 2363921353 3452943798
1348325717 9277928083 9820299282
4714386150 9810403062 9811567814
6273878771 2084469958 8806798632
1981728826 3362440226 7513734294
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2m1j1ftqjsrnb, child number 0
-------------------------------------
select * from t order by :x
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| T | 5 | 195 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
sorry
Sokrates, January 15, 2013 - 2:24 pm UTC
meant to say sql_trace instead of autrotrace in my last post
0 != -0
Sokrates, January 15, 2013 - 3:29 pm UTC
see how the plan just drops the order by entirely for the "really using binds" query - but preserves an order by step for the order by ordinal column queryyes, very nice demonstration.
by the way:
according to
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2168299 ...The position value must be an integer...However,
select * from t order by 1.1
does an order by ( by what ? )
select * from t order by -0
does no order by
and
select * from t order by 0
raises
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
sokrates@11.2 > set autotr on explain
sokrates@11.2 > select * from t order by 1.1;
X
----------
1
2
Execution Plan
----------------------------------------------------------
Plan hash value: 961378228
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 26 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 2 | 26 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
sokrates@11.2 > select * from t order by -0;
X
----------
2
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 2 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
sokrates@11.2 > select * from t order by 0;
select * from t order by 0
*
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
January 16, 2013 - 11:43 am UTC
interesting, it seems to do a truncate on the number (empirical observation).
If negative, it just ignores it entirely.
the -0 versus 0 is interesting.
I just filed Bug 16178733 - ORDER BY ALLOWS NON-INTEGERS AND NEGATIVE NUMBERS regarding this.
ACS bind sensitivity in function
Steve M, April 24, 2013 - 6:29 pm UTC
Is there something special about sql in function calls that prevents a cursor from being marked as bind sensitive for adaptive cursor sharing?
Example:
PRFACME1@perf01>variable p_object_name varchar2(30)
PRFACME1@perf01>exec :p_object_name := 'USER_OBJECTS'
PL/SQL procedure successfully completed.
PRFACME1@perf01>
PRFACME1@perf01>select /*+ XXX SQL */ count(*) from all_objects where object_name = :p_object_name;
COUNT(*)
----------
2
PRFACME1@perf01>declare
2 p_object_name varchar2(30) := 'USER_OBJECTS';
3 p_object_cnt number;
4 begin
5 select /*+ XXX ANON */ count(*) into p_object_cnt from all_objects where object_name = p_object_name;
6 end;
7 /
PL/SQL procedure successfully completed.
PRFACME1@perf01>create or replace function cas_test
2 return number
3 is
4 p_object_name varchar2(30) := 'USER_OBJECTS';
5 p_object_cnt number;
6 p_create_dt date := sysdate;
7 begin
8 select /*+ XXX FUNC */ count(*) into p_object_cnt from all_objects where object_name = p_object_name;
9 return p_object_cnt;
10 end;
11 /
Function created.
PRFACME1@perf01>select cas_test from dual;
CAS_TEST
----------
2
PRFACME1@perf01>col sql_text format a36 trunc
PRFACME1@perf01>col sql_id format a20
PRFACME1@perf01>col bs format a2
PRFACME1@perf01>col ba format a2
PRFACME1@perf01>col first_load_time format a24
PRFACME1@perf01>select sql_id, child_number, first_load_time, is_bind_sensitive as bs, is_bind_aware as ba, sql_text
2 from v$sql
3 where upper(sql_fulltext) like 'SELECT /*+ XXX %'
4 order by first_load_time desc;
SQL_ID CHILD_NUMBER FIRST_LOAD_TIME BS BA SQL_TEXT
-------------------- ------------ ------------------------ -- -- ------------------------------------
dbzyygpvaq01z 0 2013-04-24/13:20:01 N N SELECT /*+ XXX FUNC */ COUNT(*) FROM
7bb4su39j8vmw 0 2013-04-24/13:19:46 Y N SELECT /*+ XXX ANON */ COUNT(*) FROM
9rtnzmz72ypuw 0 2013-04-24/13:08:35 Y N select /*+ XXX SQL */ count(*) from
I have a pipelined function that I've been trying to have ACS work with, but my test here shows that all my work may have been for naught.
Thanks,
Steve
April 24, 2013 - 6:51 pm UTC
plsql cursor caching is coming into play here... right now adaptive cursor sharing and plsql cursor caching will not work together...
Are Histograms required for ACS to work
Sk, May 03, 2013 - 6:54 am UTC
Hi Tom,
Can you clarify that for ACS to create new plans for different bind variable values, there must be a histogram for the column that the bind variable relates to?
Thanks
May 06, 2013 - 6:17 pm UTC
no, there could be any sort of statistic that would cause us to create different plans for different literal values.
such as local partition statistics for example...
it would be safe to say that histograms are the prevalent cause, but they are not the sole cause.
bind peeking always?
Alex, May 19, 2013 - 11:51 am UTC
Hi Tom
In order for Adapative Cursor Sharing to work does the database need to peek bind variables for every single SQL statement executions? I think so, otherwise how does it know weather or not the cursor is bind sensitive?
So my question is from 11g onwards does Oracle do bind peeking always for all sql executions?
Thanks
Alex
May 21, 2013 - 2:42 pm UTC
it only needs to peek to find out which of the generated plans to use. Once a cursor becomes "bind aware", we'll peek at the binds to decided which of the generated plans to use - yes.
It just means that the bind values become part of the "signature" of a query - like the optimizer environment, the authorization schema, the NLS settings and so on.
it is similar to bind peeking - that is a term used to describe how the statement is optimized. but it isn't really "bind peeking" in the classic sense as it is more a part of the signature of a sql statement once the plans have already become bind aware and generated.
bind peeking
Alex, May 21, 2013 - 10:08 pm UTC
Thanks Tom
I dont understand 100% the answer.
For example if I run a query
select *
from t1
where c1 = :a1
c1 is a column with many distinct values and it has histogram statistics (so cursor is bind aware), when the query is run for many different bind variables does the optimizer convert the bind into literal and search the histogram to calculate the selectivity (and more)? For every single distinct c1 value?
You talk about signature but different values yields different signatures, doesn't it?
Thanks
May 22, 2013 - 1:20 pm UTC
when the query is run for many different bind variables
does the optimizer convert the bind into literal and search the histogram to
calculate the selectivity (and more)?no, it doesn't. it can categorize bind values, and share plans. It doesn't have to create a plan per bind - like the evil setting "cursor_sharing=similar" could do.
Notice how in the following - adaptive cursor sharing kicks in on the third query (we ran with :n = 1, then :n = 99 and then :n = 99). The database recognized that :n = 1 should use and index, :n = 99 should not.
So, we have 3 cursors at that point, child=0 is never going to be used again - it is not a bind aware plan. We have child=1 for id = 99, child=2 for id = 1.
then we run id = 2, 3, 4...
they create a fourth child cursor, child=3. They all share that one. We are not calculating a plan each time, we are sharing the existing plan. they all resulted in the same "signature", the same child cursor.
ops$tkyte%ORA11GR2> create table t
2 as
3 select 99 id, a.*
4 from all_objects a;
Table created.
ops$tkyte%ORA11GR2> update t set id = 1 where rownum = 1;
1 row updated.
ops$tkyte%ORA11GR2> create index t_idx on t(id);
Index created.
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent => 100, method_opt => 'for all indexed columns' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> variable n number
ops$tkyte%ORA11GR2> exec :n := 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 0
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2> select sql_text, child_number, is_bind_aware iba, is_bind_sensitive ibs
2 from v$sql
3 where sql_id = '98mx7jbn7jpm8';
SQL_TEXT CHILD_NUMBER IBA IBS
-------------------------------------------------- ------------ --- ---
select count(subobject_name) from t where id = :n 0 N Y
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :n := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
4717
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 0
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
4717
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 1
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 321 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | TABLE ACCESS FULL| T | 76932 | 1502K| 321 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:N)
19 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :n := 1;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 2
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :n := 2;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 3
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select sql_text, child_number, is_bind_aware iba, is_bind_sensitive ibs
2 from v$sql
3 where sql_id = '98mx7jbn7jpm8';
SQL_TEXT CHILD_NUMBER IBA IBS
-------------------------------------------------- ------------ --- ---
select count(subobject_name) from t where id = :n 0 N Y
select count(subobject_name) from t where id = :n 1 Y Y
select count(subobject_name) from t where id = :n 2 Y Y
select count(subobject_name) from t where id = :n 3 Y Y
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec :n := 3;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 3
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2> exec :n := 4;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select count(subobject_name) from t where id = :n;
COUNT(SUBOBJECT_NAME)
---------------------
0
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 98mx7jbn7jpm8, child number 3
-------------------------------------
select count(subobject_name) from t where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 20 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
20 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select sql_text, child_number, is_bind_aware iba, is_bind_sensitive ibs
2 from v$sql
3 where sql_id = '98mx7jbn7jpm8';
SQL_TEXT CHILD_NUMBER IBA IBS
-------------------------------------------------- ------------ --- ---
select count(subobject_name) from t where id = :n 0 N Y
select count(subobject_name) from t where id = :n 1 Y Y
select count(subobject_name) from t where id = :n 2 Y Y
select count(subobject_name) from t where id = :n 3 Y Y
ops$tkyte%ORA11GR2> spool off
ops$tkyte%ORA11GR2>
peeking
Peter, May 24, 2013 - 5:45 am UTC
Hi
In order to categorize we must know what is the bind value no? I think that is what Alex is referring to if the database peeks or finds out the bind variable's value always when adaptive cursor sharing is in use. I think so otherwise how does the database know which category should the bind variable belong to?
At the end of the day there isn't much choice for a cursor, it either uses an index or full table scan, there isn´t much to choose from. Am I wrong?
Regards
May 29, 2013 - 4:45 pm UTC
it isn't peeking in the conventional sense, it isn't the bind peeking done for optimization. I don't want to confuse the two - it shouldn't imply "we are peeking and optimizing for each run", that isn't what is happening. Bind peeking has for so long been tied to "optimization" (since 9i). This isn't bind peeking in the conventional sense.
The bind values are added to the signature of the SQL statement - like the optimizer environment is - in order to get associated to the correct, already existing, plan.
At the end of the day there isn't much choice for a cursor, it either uses an
index or full table scan, there isn´t much to choose from. Am I wrong?
index range
index skip
index fast full
index full
full table scan
partition elimination
parallel plans
index join
hash access
b*tree cluster access
and so on, there are lots to choose from.