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):

 

 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.