Plans in Memory

Plans in Memory
http://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/
Filed under: Execution plans, Infrastructure, Performance — Jonathan Lewis @ 8:42 pm UTC Dec 12,2006

In an earlier article I described how dbms_xplan.display_cursor() could be used to query memory to find the execution plans (and row source statistics) of recently executed SQL.

In 9i, you have to use your own SQL to get the equivalent results. If you do, you should avoid using the “traditional” hierarchical type of query that you would use against the plan table, as it can be a little brutal on the library cache latches. Instead, you query the v$sql_plan view by hash_value and child_number with a simple “order by id” using the new, internally calculated, depth column instead of the psuedo-column level as the means of indenting the operations.

If you do want to carry on using the hierarchical style of query - and in 10g, I’ve found a couple of cases where the depth is calculated incorrectly, so you may want to keep you old scripts handy for a while - you should think about using subquery factoring - for example (with just a very simple and far from complete plan output):



with plan_subq as (
select
/*+ materialize */
child_number,
id,
parent_id,
depth,
operation || ' ' ||
object_name || ' ' ||
options simple_plan
from
V$sql_plan
where
hash_value = &m_hash_value
)
select
child_number,
id,
parent_id,
depth,
lpad(' ',2 * level) || simple_plan simple_plan
from
plan_subq
start with
id = 0
connect by
parent_id = prior id
and child_number = prior child_number
order by
child_number,
id
;


With this strategy, you are protecting the library cache latches, but remember that Oracle will be creating a global temporary table (and dumping it to your temporary tablespace) as a consequence.
13 Comments »

1.

Jonathan, Tom Kyte published a way to use dbms_xplan.display on v$sql_plan in 9i in his book.
You can find an excerpt at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10353453905351#35112843143549
I don’t know its impact on library cache latches but it is a convenient method.

Comment by Michel Cadot — December 13, 2006 @ 8:19 am UTC Dec 13,2006
2.

I like to get the access and filter predicates out too:

select id,decode(access_predicates,null,’filter: ‘||filter_predicates,’access: ‘
||access_predicates) predicates
from v$sql_plan
where (access_predicates is not null or filter_predicates is not null)
and hash_value = &&m_hash_value
/

Comment by Andy Helm — December 13, 2006 @ 9:41 am UTC Dec 13,2006
3.

Michel, it’s interesting what a simple posting can produce as follow-up (looking at the thread).
Tom’s solution is a very convenient “developer solution”, but I wouldn’t want to use it for a heavy trouble-shooting session on a big system because it does hammer the library cache latches - as Tom points out - especially since it starts (implicitly) with a scan of v$sql to find the information needed, and then accesses v$sql_plan by address tather than hash_value.

It also has the defect that a single text in v$sql can be repeated with many child cursors, which means that the query used for the second parameter to dbms_xplan.display could return multiple rows, presumably crashing - although I note a later poster seems to survive an array return for the second parameter and gets a multiply-cloned plan as a result.

There’s also a follow-on about joining to the correct child in v$sql - not possible directly from v$session, or even v$open_cursor in the earlier versions but x$kgllk (which is underneath v$open_cursor) includes a column that will get you to v$sql (in 9i, and x$kglcursor in 8i, which is the thing underneath v$sql) to pick you the correct child. I’ll have to write that up some time.

Comment by Jonathan Lewis — December 13, 2006 @ 6:58 pm UTC Dec 13,2006
4.

Andy, absolutely - an execution plan without the filter_predicates and access_predicates is only the structure indication of what happened. For detailed understanding of what’s really going on you really need the list of predicates supplied, generated, and eliminated.

Your script seems to have lost some bits. It is possible to have both access_predicates and filter_predicates on one line, and your script only seems to report one or the other. Watch out, too, for the fact that either could get up to 4,000 characters - at which point your concatenation (||) would cause on Oracle error.

Comment by Jonathan Lewis — December 13, 2006 @ 7:05 pm UTC Dec 13,2006
5.

Jonathan, a bit OT I know - but where did you find the materialize hint documented? Or the cardinality hint come to that. I can find no reference to either of them in the 9i or 10g Performance Guides nor Mentalink.

Any pointers as to where to find this information gratefully received.

BTW - Just got your new Book - crackingly good.

Regards

Ian

Comment by Ian — December 14, 2006 @ 10:50 am UTC Dec 14,2006
6.

Ian, “materialize” hint is not documented but often use.
If you don’t want to use it you can get the same effect (that is materialization of the result set) with “rownum > 0″ in the where clause.
Have a look at Tom Kyte’s answer at http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:52733181746448#52821944767865

Comment by Michel Cadot — December 14, 2006 @ 12:46 pm UTC Dec 14,2006
7.

After attending your seminar in London a couple of months ago I wrote this, http://www.davemanton.com/dump_plan_by_hash.sql, which gives you the plan and the predicates. IS there any more info I can get to? I remember you mentioning v$sql_plan_statistics, but it did not seem that useful

Comment by Dave — December 14, 2006 @ 2:00 pm UTC Dec 14,2006
8.

Ian, Finding such things - I use use “strings -a” on the Oracle executable for Unix and search for any interesting text. Looking for “star_transformation” was a good way of finding the list of possible hints. This is no longer so simple in 10g as the hints are now just words in the reserved words list.

Materialize and Cardinality I first saw (I think) in SQL dumped from the executable and then I searched Metalink although I’m sure I found cardinality somewhere in the documentation once.

Materialize is actually given as a workaround to a bug (3528916.8). So it is semi-official.

I can’t find the original documentation reference I had for the cardinality hint (which was incomplete anyway) - but it arrived in 9i and there was a document somewhere on metalink which says something about its existence and the disappearance of the selectivity hint in 10g.

Comment by Jonathan Lewis — December 14, 2006 @ 8:13 pm UTC Dec 14,2006
9.

Dave, take a look at the article on dbms_xplan.display_cursor referenced above; it mentions the benefit of the data you can get from v$sql_plan_statistics, but for 9i you don’t have a convenient hint to enable collection. Instead, you can execute:
alter session set statistics_level = all;
or
alter session set “_rowsource_execution_statistics”=true;
before executing a query.

Comment by Jonathan Lewis — December 14, 2006 @ 8:31 pm UTC Dec 14,2006
10.

I’ve used sometimes this script on OSX to have graphical display of the query (10g tested, I dont remember if I had coded this when testing the 9i prerelease…). You need graphviz instaled so that the .dot file is loaded and displayed ok… and it sure helps a lot to visualize computations that way :)

set heading off
set pagesize 0

delete from plan_table;
explain plan for
select * from drawscan10; /* yeah */

set linesize 3000
spool plan.dot

select ‘digraph a {’ from dual union all
select op from(
select pid, prior op||’->’||op||’;’ op
from (
select id, parent_id pid,
‘”‘||id||
‘ ‘||operation||
‘\n’||options||
‘\n’||object_name||’ (’||cardinality||’)'||
‘\nACCESS: ‘||replace(replace(access_predicates,’”‘,”),’AND’,'\nAND’)||
‘\nFILTER: ‘||replace(replace(filter_predicates,’”‘,”),’AND’,'\nAND’)||
‘”‘ op
from plan_table
)
start with pid is null
connect by pid = prior id
) where pid is not null
union all
select ‘};’ from dual;

spool off

spool plan.txt
select * from table(dbms_xplan.display);
spool off

set heading on
set pagesize 20

host open plan.dot

set linesize 90

Comment by winden — December 16, 2006 @ 6:02 pm UTC Dec 16,2006
11.

Ian, I’ve just rediscovered the reference on Metalink about cardinality and selectivity in 10g. It’s bug number: 4121077 - SELECTIVITY HINT NOT WORKING IN 10G BUT WORKING IN 9206.

Highlights are:
There is no SELECTIVITY hint in 10g.
There is a CARDINALITY hint in 10g and 9.2

As the SELECTIVITY hint is not a documented item there is no bug here but you can use the undocumented CARDINALITY hint instead, but it too may be obsoleted in future.

Comment by Jonathan Lewis — December 18, 2006 @ 9:12 pm UTC Dec 18,2006
12.

Jonathan, Thanks for the info on the hints. Much appreciated.

Nice Blog BTW.

Regards

Ian

Comment by Ian — December 19, 2006 @ 11:44 am UTC Dec 19,2006
13.

OK, to show your predicates, access first then filter, ordered by plan step id:

select id,acc_or_fil,predicates
from (
select id,’access’ acc_or_fil ,access_predicates predicates
from plan_table
where access_predicates is not null
union all
select id,’filter’,filter_predicates
from plan_table
where filter_predicates is not null
) order by 1,2
/

Comment by Andy Helm — December 19, 2006 @ 2:21 pm UTC Dec 19,2006