so we dont need to do anything
December 24, 2007 - 5am Central time zone
Reviewer: AMIR RIAZ
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
Followup December 24, 2007 - 9am Central time zone:
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 ?
July 4, 2008 - 5am Central time zone
Reviewer: Nicosa from Paris, France
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.
Followup July 7, 2008 - 11am Central time zone:
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 ?
July 7, 2008 - 12pm Central time zone
Reviewer: Nicosa from Paris, France
(^_^)
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")
Followup July 7, 2008 - 12pm Central time zone:
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.
February 13, 2009 - 12pm Central time zone
Reviewer: A reader
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?
Followup February 16, 2009 - 11am Central time zone:
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
)_
February 17, 2009 - 1pm Central time zone
Reviewer: A reader
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*."
Followup February 17, 2009 - 2pm Central time zone:
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!
February 17, 2009 - 4pm Central time zone
Reviewer: A reader
So this implies that in many cases we would use virtual private database to implement the security
at the row-level?
Followup February 17, 2009 - 4pm Central time zone:
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
September 8, 2009 - 12pm Central time zone
Reviewer: Mark Brady from Baltimore MD
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?
Followup September 8, 2009 - 9pm Central time zone:
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"
March 13, 2010 - 1pm Central time zone
Reviewer: Stew Ashton from Paris, France
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.
Followup March 15, 2010 - 10am Central time zone:
well, I've posted a comment, we'll see if it appears...
When default statistics gathering is insufficient
March 16, 2010 - 10am Central time zone
Reviewer: Kim Berg Hansen from Middelfart, Denmark
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
Followup March 16, 2010 - 10am Central time zone:
... (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 ...
March 29, 2010 - 1am Central time zone
Reviewer: Sokrates
doesn't seem to appear, did you get any feedback ?
Followup April 5, 2010 - 9am Central time zone:
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 ...
June 29, 2010 - 4pm Central time zone
Reviewer: Richard
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
Followup July 6, 2010 - 1pm Central time zone:
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
July 14, 2010 - 6pm Central time zone
Reviewer: A reader
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
October 18, 2010 - 10am Central time zone
Reviewer: Sam Reddy from Reston, VA
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
Followup October 25, 2010 - 9am Central time zone:
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
October 27, 2010 - 6am Central time zone
Reviewer: Amir Riaz
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.
Followup October 27, 2010 - 6am Central time zone:
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
December 7, 2010 - 7am Central time zone
Reviewer: A reader
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.
Followup December 7, 2010 - 11am Central time zone:
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
December 8, 2010 - 12pm Central time zone
Reviewer: A reader
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?
Followup December 8, 2010 - 12pm Central time zone:
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
December 21, 2010 - 4pm Central time zone
Reviewer: Mark from USA
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?
Followup December 22, 2010 - 1pm Central time zone:
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
June 2, 2011 - 2pm Central time zone
Reviewer: Khalid Rahim from MD
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
Followup June 2, 2011 - 8pm Central time zone:
as it is not documented, it is nothing you ever need/should use. So, that is all.
is /* ACS_1 */ really a hint ?
June 3, 2011 - 2am Central time zone
Reviewer: Sokrates
I am missing the + sign in /* ACS_1 */
shouldn't it be /*+ ACS_1 */ ?
( I promise, I'll never use it )
Followup June 3, 2011 - 12pm Central time zone:
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
June 3, 2011 - 2am Central time zone
Reviewer: Sokrates
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
June 4, 2011 - 12pm Central time zone
Reviewer: Khalid from md
Yes, I noticed the missing '+' sign, and assumed that was meant to be a hint but they forgot the
'+', I actually cut and pasted directly from oracle doc:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optimops.htm#i21299
Btw socrates, what is a BC?
kr
I got it
June 4, 2011 - 5pm Central time zone
Reviewer: A reader
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
November 7, 2011 - 11am Central time zone
Reviewer: A reader
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
Followup November 7, 2011 - 12pm Central time zone:
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.
November 7, 2011 - 1pm Central time zone
Reviewer: A reader
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 ?
Followup November 7, 2011 - 3pm Central time zone:
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.
November 8, 2011 - 8am Central time zone
Reviewer: A reader
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 ?
Followup November 8, 2011 - 8am Central time zone:
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
November 8, 2011 - 8am Central time zone
Reviewer: A reader
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)
Followup November 8, 2011 - 8am Central time zone:
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.
November 10, 2011 - 2pm Central time zone
Reviewer: A reader
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
Followup November 10, 2011 - 5pm Central time zone:
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
November 10, 2011 - 6pm Central time zone
Reviewer: A reader
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 ?
Followup November 10, 2011 - 6pm Central time zone:
yes, correct.
ACS working only one way ?
March 26, 2012 - 8am Central time zone
Reviewer: Vincent from Marseille,France
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
Followup March 26, 2012 - 10am Central time zone:
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
March 27, 2012 - 1am Central time zone
Reviewer: A reader
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?
Followup March 27, 2012 - 7am Central time zone:
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.
March 27, 2012 - 9am Central time zone
Reviewer: A reader
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..
Followup March 27, 2012 - 9am Central time zone:
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.
March 27, 2012 - 10am Central time zone
Reviewer: A reader
Thanks for the reply
is this is a related to what jonathan pointed out as bug?
http://jonathanlewis.wordpress.com/2012/03/21/acs/
Followup March 27, 2012 - 11am Central time zone:
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)
March 27, 2012 - 10am Central time zone
Reviewer: Vincent from Marseille,France
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
Followup March 28, 2012 - 8am Central time zone:
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)
March 29, 2012 - 7am Central time zone
Reviewer: Vincent from Marseille,France
Hi Tom,
I filed a SR and will post the reply when I get it.
Thanks
Followup March 29, 2012 - 7am Central time zone:
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)
April 6, 2012 - 10am Central time zone
Reviewer: Vincent from Marseille, france
Hi Tom,
Support filed a bug. I will post the final status.
Regards
cursor_sharing=similar
April 6, 2012 - 1pm Central time zone
Reviewer: A reader
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...
Followup April 8, 2012 - 5pm Central time zone:
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.
|