Oracle Scratchpad

September 25, 2013

Autotrace trick

Filed under: Execution plans,Oracle — Jonathan Lewis @ 11:58 pm BST Sep 25,2013

(… as in “trick or treat”)

Here’s an important point I learned from Maria Colgan’s “10 tips” presentation on Tuesday of Open World. It comes in two steps – the bit that most people know, and an unexpected consequence:

  1. autotrace can give misleading execution plans for queries that use bind variables – because autotrace doesn’t peek
  2. if you run a query after testing it with autotrace, the plan generated by autotrace can be shared by the later execution

Here’s a simple script I’ll be using to demonstrate the behaviour:


set autotrace traceonly explain

select id from t1 where skew = 1;
select id from t1 where skew = 80;

variable b1 number
exec :b1 := 1

select id from t1 where skew = :b1;

set autotrace off

select id from t1 where skew = :b1;
select * from table(dbms_xplan.display_cursor(null,null,'peeked_binds'));

The table t1 has one row where skew = 1, two where skew = 2, and so on with 80 rows where skew = 80: a total of 3240 rows. I have a perfect frequency histogram on the column, and an index. The clustering_factor of the index is so large that it is only suitable for queries which will return a small number of rows.

In my test case, the predicted plan for the first query uses an index range scan with an estimated cardinality of 1, the second query shows a full tablescan with an estimated cardinality of 80. The third query shows a full tablescan with an estimated cardinality of 41 (the “average” number of rows per distinct value) because autotrace doesn’t peek the bind variable.

Looking at the fourth query – where we’ve finally turned off autotrace – we might assume that the execution plan would be an index range scan because the supplied bind variable has a value of 1 and we might think that the optimizer is going to optimize the statement – but the call to dbms_xplan.display_cursor() shows us that Oracle re-uses the plan previously generated by autotrace.

If you flush the shared pool and repeat the test but exclude the third query, the final query will use the range scan we expect to see.

Here’s a copy of the output from the initial test – with a little cosmetic editing:


Execution Plan
----------------------------------------------------------
Plan hash value: 4175407225

-----------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     7 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     7 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_SKEW |     1 |       |     1 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SKEW"=1)

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    80 |   560 |    20 |
|*  1 |  TABLE ACCESS FULL| T1   |    80 |   560 |    20 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SKEW"=80)

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    41 |   287 |    20 |
|*  1 |  TABLE ACCESS FULL| T1   |    41 |   287 |    20 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SKEW"=TO_NUMBER(:B1))

==================================================
autotrace off, with an actual bind value available
We re-use the autotrace cursor, and don't report
a bind value (since we didn't peek to optimize).
==================================================

        ID
----------
      3240

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4a0ywc60thj4a, child number 0
-------------------------------------
select id from t1 where skew = :b1

Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    20 |
|*  1 |  TABLE ACCESS FULL| T1   |    41 |   287 |    20 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SKEW"=:B1)

If you comment out the third query, flush the shared pool and repeat, the final call to dbms_xplan gives the following output:


PLAN_TABLE_OUTPUT
-----------------
SQL_ID  4a0ywc60thj4a, child number 0
-------------------------------------
select id from t1 where skew = :b1

Plan hash value: 4175407225

-----------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     7 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_SKEW |     1 |       |     1 |
-----------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (NUMBER): 1

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SKEW"=:B1)

If you want to repeat the test in your own environment, here’s the code to generate the table and index:

create table t1 (
	id, skew, padding
)
as
select rownum, r1, rpad('x',400)
from
(
	select /*+ no_merge */
		rownum r1
	from all_objects
	where rownum <= 80
)	v1,
(
	select /*+ no_merge */
		rownum r2
	from all_objects
	where rownum <= 80
)	v2
where r2 <= r1
order by r2,r1
;

alter table t1 modify skew not null;

create index t1_skew on t1(skew);

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 254'
	);
end;
/

9 Comments »

  1. Very good to know. I was also surprised when SQLMaria documented this (in one of the best OOW sessions ever JL/SQLMaria alternating with high energy!) Now, in additional to recognizing it, I *wonder* whether a) autotrace should be made to peek, b) autotrace should warn that it has left a sharable plan in cache that will not peek, c) autotrace should be changed to not leave a shareable plan in memory, or d) autotrace should just be left alone. I’m really not sure which would be best. Certainly *knowing* about it is much better than *not knowing* about it.

    Perhaps leaving it alone, but giving an easy to use handle to evict that particular plan would help.

    Congratulations on a tour de force performance (even for you!) and on blogging this distinctly and clearly.

    Comment by rsiz — September 26, 2013 @ 1:00 am BST Sep 26,2013 | Reply

    • Mark,

      Thanks for the comments on the presentation – I hope the audience enjoyed it as much as I did.

      It would nice to think that the developers could find an easy way to get ensure that autotrace knows it has to generate a new child cursor, and could then identify its own child cursor and invalidate or flush it.

      Comment by Jonathan Lewis — September 27, 2013 @ 6:29 pm BST Sep 27,2013 | Reply

  2. Hi Jonathan,

    I’ve already blogged about this some time ago – also with a little bit of background info what happens behind the scenes and why this is possible.

    Autotrace Polluting The Shared Pool?

    Randolf

    Comment by Randolf Geist — September 26, 2013 @ 8:29 am BST Sep 26,2013 | Reply

  3. Perhaps autotrace should really be marking the optimizer_env as “_optim_peek_user_binds”=false and then unset when turned off?

    Comment by Dom Brooks — September 26, 2013 @ 12:34 pm BST Sep 26,2013 | Reply

    • Dom,

      My first thought was that this was a most elegant solution. It took me a few minutes to get to my second thought, which was: “what about people who have already set that parameter to false ?” In some cases they might be okay – in other cases I think there may be a potential for data type mismatch between autotrace and the end-user that would still lead to an unsuitable plan being used.

      Comment by Jonathan Lewis — September 27, 2013 @ 6:30 pm BST Sep 27,2013 | Reply

  4. […] variables 的行为:Autotrace trick 之前我们也发现过这个问题具体SR在这里:something seems […]

    Pingback by VMCD.ORG » Blog Archive » [转] Autotrace with bind variables — September 27, 2013 @ 7:16 am BST Sep 27,2013 | Reply

  5. […] variables 的行为:Autotrace trick 之前我们也发现过这个问题具体SR在这里:something seems […]

    Pingback by yhddba.com » [转] Autotrace with bind variables — September 27, 2013 @ 7:17 am BST Sep 27,2013 | Reply

  6. […] variables 的行为:Autotrace trick 之前我们也发现过这个问题具体SR在这里:something seems […]

    Pingback by [转] Autotrace with bind variables | louis liu 的镜像blog — September 27, 2013 @ 7:18 am BST Sep 27,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,514 other followers