Thursday, September 20, 2007

Sql_trace=true, Part Two...

I got a few questions on my last entry Tuning with sql_trace=true.  The two I'll address here are:

  1. Ok, I'm using 9ir2 not 10g so the sql_id bit doesn't exist and DBMS_XPLAN doesn't have the entry points you used - so how do we do this in 9i?
  2. Ok, so we identified the cause (sql_trace causing a new child cursor, bind variable peeking being the ultimate culprit) - what do we do then?

Using 9iR2

The following code snippet is what you would use in 9iR2 - this picks up from the previous example (following the create table, autotrace demonstration to show different plans, and the turning on and off of sql_trace).  We read v$sql_plan to populate the plan_table with our two child cursors - and then dbms_xplan can display that easily.  We query V$SQL_SHARED_CURSOR using an address instead of sql_id.  We also note that the column queried from v$sql_shared_cursor is different in 9i than in 10g:

ops$tkyte%ORA9IR2> delete from plan_table;
5 rows deleted.

ops$tkyte%ORA9IR2> insert into plan_table
2 ( STATEMENT_ID, TIMESTAMP, REMARKS, OPERATION,
3 OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME,
4 OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID,
5 POSITION, COST, CARDINALITY, BYTES, OTHER_TAG,
6 PARTITION_START, PARTITION_STOP, PARTITION_ID,
7 OTHER, DISTRIBUTION, CPU_COST,
8 IO_COST, TEMP_SPACE )
9 select 'QUERY_'||child_number,
10 sysdate, null, operation, options,
11 object_node, object_owner, object_name,
12 optimizer, search_columns, id, parent_id,
13 position, cost, cardinality, bytes, other_tag,
14 partition_start, partition_stop, partition_id,
15 other, distribution, cpu_cost, io_cost,
16 temp_space
17 from v$sql_plan
18 where (address,child_number) in
19 ( select address, child_number
20 from v$sql
21 where sql_text = 'select * from t where id = :id' );
5 rows created.

ops$tkyte%ORA9IR2> select * from
table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_0' ) );

PLAN_TABLE_OUTPUT
--------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 30353 | 2964K| 43 |
--------------------------------------------------------------------
Note: cpu costing is off
9 rows selected.

ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_1' ) );

PLAN_TABLE_OUTPUT
--------------------------

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 100 | 2 |
| 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 |
---------------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.

ops$tkyte%ORA9IR2> select address, OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar = ( select address
4 from v$sql
5 where sql_text =
6 'select * from t where id = :id'
7 and rownum = 1 );

ADDRESS O
-------- -
5BA2A6A4 N
5BA25B18 Y



So, that is the answer to number 1...


So, what the heck do we do about it?


Well, fortunately (for me) I address this in the "All about binds" talk as well.  There are a number of things you can do - this is the slide I talk to during the seminar (click to read):



binds


 


 Most of the time, almost all of the time in fact - bind variable peeking works as the designers of the feature intended - in a positive, helpful manner.  The query in the application uses bind values that drive it to one plan or the other consistently.  It is only when the plan flip flops between two radically different execution paths and for some segment of users - there exists a really bad plan.


So, when it doesn't work 'nicely', what can you do to get around it?  I'll go through the ideas one by one...


Don't bind that query, that is a possibility


Yes, you read that correctly, don't use a bind variable there (and I wrote that - do not use a bind variable :) ).  Look at my example above - ID has two values - 1 and 99.  Let's say ID wasn't ID but rather was "processed_flag" and had two values - Y and N.  Most of the records are processed_flag=Y, some of them are N.  Some people query the processed records (Y=yes).  They want a full scan.  Others query the unprocessed records in order to process them (N=no).  They would like an index range scan.


Here, my suggestion would be do not bind against processed_flag, use literals.  That way we have two queries, two plans.


This is also why I hate cursor_sharing=force, because when a programmer does this (uses literals) and the DBA turns on cursor sharing = force, we are back to the original problem!!! Bind variable peeking comes into play once again - there is one plan for all and it depends on who runs the query first. (cursor_sharing similar would not have this side effect by the way, it would choose to not bind against processed_flag based on the statistics).


Don't gather statistics that cause the plan to flip flop


If there is only one plan that the optimizer would ever choose - then bind variable peeking will have no effect.  Consider the following, notice that I gathered NO HISTOGRAMS in this case - so the plan would never flip flop.  We still have the child cursors (sql_trace will do that) but the plan is consistent:


ops$tkyte%ORA9IR2> 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%ORA9IR2> create index t_idx on t(id);
Index created.

ops$tkyte%ORA9IR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=> 'for all columns size repeat' );
5 end;
6 /
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t where id = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=15177 Bytes=1456992)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=15177 Bytes=1456992)

ops$tkyte%ORA9IR2> select * from t where id = 99;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=43 Card=15177 Bytes=1456992)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=43 Card=15177 Bytes=1456992)

 


Notice the plan however!  One plan for ID=1, one plan for ID=99.  The optimizer doesn't know anything about the values in the column this time - guesses "about half of the data" (we have 30,000 rows in T in this 9i example).  It'll always full scan.  This is why we have histograms (column statistics) and further, why we do bind peeking - without histograms, without bind peeking - the only plan for this query would be FULL SCAN.  

ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> variable id number
ops$tkyte%ORA9IR2> set autotrace traceonly statistics
ops$tkyte%ORA9IR2> exec :id := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> select * from t where id = :id;
30353 rows selected.
... 2422 consistent gets ....

ops$tkyte%ORA9IR2> exec :id := 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

ops$tkyte%ORA9IR2> alter session set sql_trace=true;
Session altered.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

ops$tkyte%ORA9IR2> alter session set sql_trace=false;
Session altered.
ops$tkyte%ORA9IR2> select * from t where id = :id;
... 432 consistent gets ...

...
ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_0' ) );
PLAN_TABLE_OUTPUT
-----------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 15177 | 1422K| 43 |
--------------------------------------------------------------------

ops$tkyte%ORA9IR2> select *
from table( dbms_xplan.display( 'PLAN_TABLE', 'QUERY_1' ) );
PLAN_TABLE_OUTPUT
-------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 43 |
| 1 | TABLE ACCESS FULL | T | 15177 | 1422K| 43 |
--------------------------------------------------------------------

ops$tkyte%ORA9IR2> select address, OPTIMIZER_MISMATCH
2 from v$sql_shared_cursor
3 where kglhdpar = ( select address
4 from v$sql
5 where sql_text =
6 'select * from t where id = :id'
7 and rownum = 1 );

ADDRESS O
-------- -
5BA2A6A4 N
5BA25B18 Y


Use your domain knowledge of the data


If you know that when a certain query is executed with a date within the last 30 days - the volume of data is small and "indexes would be used" and that if the date is way in the past - the volume of data is large and "full scans are us" is true - then just use an if/then/else


if ( p_date > sysdate-30 )
then
open l_cursor for
select * from t less_than_30 where ......
else
open l_cursor for
select * from t more_than_30 where ...
end if;
loop
fetch l-cursor into ....;
.....


This works when you have a good knowledge of your data.  If the code is in ('a','b','c' ) then open this cursor, else open that cursor - and so on.


You can use cursor_sharing=similar


And let Oracle decide what to bind and what not to bind.  Consider the following (we'll use the above create table T, the index T_IDX and the original gather stats with method_opt=> for all indexed columns size 254.


ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_object_id where id = 1 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_object_id where id = 1 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 1 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 2 and object_id = 100;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 3 and object_id = 100;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 3
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"


 


Notice here how there are three copies of the sql in v$sql when we vary the ID - Oracle decided it was not safe to bind against ID - it looks like it used a bind, but it really didn't - the three copies are one each for ID=1, 2 and 3. When we vary the object_id, Oracle has no statistics that would cause it to consider alternate plans, so it was safe to bind that value - and it did, resulting in a single child cursor.


 

ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_id where id = 1 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 2 and object_id = 101;
ops$tkyte%ORA9IR2> select * from t vary_id where id = 3 and object_id = 101;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 3
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"

 


Notice here how there are three copies of the sql in v$sql still! That is because we already had the plans for ID=1,2,3 - regardless of what the object_id was, however if we change ID again:

ops$tkyte%ORA9IR2> alter session set cursor_sharing=similar;
Session altered.

ops$tkyte%ORA9IR2> select * from t vary_id where id = 4 and object_id = 101;

ops$tkyte%ORA9IR2> alter session set cursor_sharing=exact;
Session altered.

ops$tkyte%ORA9IR2> select sql_text, count(*)
2 from v$sql
3 where sql_text like 'select * from t vary_% where id = % and object_id = %'
4 group by sql_text;

SQL_TEXT COUNT(*)
-------------------------------------------------- ----------
select * from t vary_id where id = :"SYS_B_0" and 4
object_id = :"SYS_B_1"

select * from t vary_object_id where id = :"SYS_B_ 1
0" and object_id = :"SYS_B_1"


You see a fourth copy pop into v$sql - for ID=4.


Hence, for every unique value of ID we use - there will be a new child cursor.


Note that with cursor_sharing=force, there would be one plan - but remember bind variable peeking - there would be on plan that could flip flop over time.


You could use stored outlines


Also known as query plan stability.  In a test environment, you would exercise the application using "appropriate" or "representative" inputs.  Additionally you would be capturing stored outlines into the outline tables.  You would then move these into production and have the application issue the alter session to use these stored outlines.


You in effect freeze the plans (you may stop gathering statistics now as well, you just froze the plans...)


You can disable the bind peeking feature


Using an undocumented init.ora parameter - but, since this really only affects you if you have statistics that cause plans to flip flop - I would say stop gathering those statistics, save the time and resources that takes and you'll have achieved basically the same goal.

POST A COMMENT

15 Comments:

Blogger John Ridgway said....

Thanks for explaining this. I had pesky package in an application that would go crazy every now and then. When it had problems doing stats fixed the issue temporarily. Now I know that the query in question was over bound. The developer had a habit of declaring tons of constants in their procedures and then using these constants in the queries. Another version of the query existed using different constants in another procedure in the package.

Thu Sep 20, 12:20:00 PM EDT  

Blogger Coskan Gundogar said....

Thank you very much for enlighten us about all possible the solutions of bind variable peeking now I can feel more comfortable when I face this problem

Thu Sep 20, 06:45:00 PM EDT  

Blogger Gary Myers said....

Under "Don't gather statistics that cause the plan to flip flop"
You state
"The optimizer doesn't know anything about the values in the column this time - guesses "about half of the data" (we have 30,000 rows in T in this 9i example). It'll always full scan. "
That's not quite true. It still has the high and low values for the column and if you do a
select * from t where id = 1000;
you can get the index access (at least in XE 10gR2). It would be a rare case though (eg wildly out of date stats or a keying error).

Fri Sep 21, 12:28:00 AM EDT  

Blogger Thomas Kyte said....

gary meyers said...

Yes, that is true, the outliers would be recognized like that.

Fri Sep 21, 06:45:00 AM EDT  

Blogger Greg said....

TK said: Yes, that is true, the outliers would be recognized like that.

So why did I suddenly have this flashback to "The Langoliers"?

Scary...

Greg

Fri Sep 21, 05:27:00 PM EDT  

Blogger bstav said....

I have done a lot of research about binds & histograms in the Oracle documentation. 9i is quite clear about it: "Histograms are not useful for columns..." when "All predicates on the column use bind variables". I have searched a lot in the 10g docs for this, but there is no information about it.

Maybe this information is omitted because of the automatic statistics gathering of in 10g. But this means that 10g might use binds with histograms causing performance problems by default. Am I missing something ? Is there a difference between 9i and 10g in behaviour at bind peeking ?

9i info about binds & histograms:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#27065

Sun Sep 30, 02:58:00 AM EDT  

Blogger Thomas Kyte said....

The 9i documentation would be in error in that regards.

As demonstrated, on this page. 9i and 10g behave the same with regards to bind variable peeking.

Bind variable peeking relies on histograms (and other statistics, for example - local partition statistics versus global partition statistics)

And
11g documents
bind peeking and describes a new method used by the optimizer when histograms could affect the plan in that release.

Sun Sep 30, 08:20:00 AM EDT  

Blogger r said....

I am using Oracle 10.2.0.2 with cursor sharing=FORCE.
Why is the v$sql view still showing statments with literals?
Why is oracle not replacing string literals with system generated binds?

We know we have a problem with the application not using binds, but I thought setting cursor_sharing to FORCE would temporarily get us through. For most of the SQL statements, it is working, but for many statments, I still see literal strings when I look in v$sql view and our shared_pool is growing and reached 6 gb!
(we are using automatic memory management with SGA_TARGET around 25 GB).

Fri Dec 07, 02:05:00 PM EST  

Anonymous John Hollings said....

Tom,

Understand the concept of wanting to get a different execution plan for the same bit of sql. But you don't solve a real world example.

I'm consulting on system using SIEBEL (Oracle BI EE) in which the data is partitioned by day, but searches can span a few days or many months. SIEBEL is using bind variables and so may not perform the correct execution for queries with different date/time ranges.

I can get a different execution plan (well actually the same, but new version of plan is created) by altering text in the hint e.g select /*+ 1234 */ x,y,z from mytable where mydate between :startdt and :enddt and ..;
change to
select /*+ 4321 */ x,y,z from mytable where mydate between :startdt and enddt and ...;

The problem is putting a dynamic literal in the select statement in a Oracle BI report. I've not come across an example of SIEBEL/BI using functions returning ref cursors which is what I'd do using crystal, i.e write the code in plsql which is what I'd like to suggest as a solution, if I can't get execution plan for some reports recalculated on the fly.

Anyway Thanks for the enlightenment.

if you can point me to something that hints at a solution it would be nice.

Thu Jan 10, 07:54:00 AM EST  

Anonymous Anonymous said....

Hi Tom,

I checked the 11g document as mentioned by you
..

And
11g documents bind peeking and describes a new method used by the optimizer when histograms could affect the plan in that release.
...

Document specifically mentions that new bind-aware cursor approach applies to
"user_defined bind variables". But it doesn't say it also applies to "system_defined bind variables" caused by cursor_sharing=similar. Is this bind-aware cursor matching for "user-defined bind variables" ONLY ?

thanks

Sat Apr 17, 07:13:00 AM EDT  

Blogger Thomas Kyte said....

@anonymous

it works for cursor sharing =force bind variables as well.

Sat Apr 17, 09:45:00 AM EDT  

Anonymous Anonymous said....

Thanks Tom, I was reading "Bind peeking" in Ch. 5 in your book "Effective Oracle By Design"
...
This shows that a common misconception about bind variable peeking, that the optimizer looks at the bind variable values before each parse, is in fact not true. The optimizer peeks only when the optimizer is invoked! That is only during the hard parse of a query
..
My questions :-
Your statement is true only when histogram is missing ?

And if there is histogram present in that case optimizer will be invoked and peek at bind variable before each parse ?

And if histogram is present in that case hard parse will still remain hard parse even in presence of bind variable and will not be soft parse ?

thanks

Tue Apr 20, 06:02:00 PM EDT  

Blogger Thomas Kyte said....

@anonymous

My questions :-
Your statement is true only when histogram is missing ?


No, it is true for any case in 10gr2 and before. In 11g r1 and above there is a new feature "adaptive cursor sharing" that changes entirely the way bind peeking works.


And if there is histogram present in that case optimizer will be invoked and peek at bind variable before each parse ?

No, not true, else bind peeking as described would not have been a problem as you would have gotten the right plan each time. Bind peeking as written here causes a problem whereby sometimes you get a full scan for everyone or an index for everyone - but the key word here is "everyone" - if they peeked every time - that would not happen.

Tue Apr 20, 07:04:00 PM EDT  

Anonymous Anonymous said....

Thanks Tom. I am not sure if I understood completely the behaviour of bind peeking with and without histograms in 10g. I was reading at this link
http://optimizermagic.blogspot.com/2009/05/why-do-i-have-hundreds-of-child-cursors.html
It says :-
..The presence of the histogram tells the optimizer that there is a data skew in that column. The data skew means that there could potentially be multiple execution plans for this statement depending on the literal value used. In order to ensure we don't impact the performance of the application, we will peek at the bind variable values and create a new child cursor for each distinct value. Thus ensuring each bind variable value will get the most optimal execution plan
..
I might be misinterpreting this statement But Doesn't that mean optimizer will be invoked to generate "optimal execution plan" at the time of bind peeking when there is histogram present ?

thanks

Wed Apr 21, 09:05:00 AM EDT  

Blogger Thomas Kyte said....

@anonymous

they were describing cursor_sharing=similiar.

Something you might use if you ARE NOT using binds.

That is, just to make it clear, a feature you would use when you DO NOT USE BIND VARIABLES.

So, you cannot really mix a discussion of that, with a discussion on bind peeking as it relates to SQL that was prepared using binds!

Wed Apr 21, 09:09:00 AM EDT  

POST A COMMENT

<< Home