]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-36834
Sat, 24 Jul 2010 10:04:52 +0000http://jonathanlewis.wordpress.com/?p=90#comment-36834If you can’t find a way of getting the same plan, the simplest thing is to get the test run repeated, and monitor the session until it executes the query, then pull the plan from memory. This will still leave a gap because the in-memory plans won’t record predicates that include subquerier properly, but it should help you get a lot closer to the full detail.

If you can’t do this live, then generating the trace at level 4 will give you the bind variable values and types, which may help you to fake the plan. Note that you may have to change the code so that all references to bind variables have explicit conversion functions applied. You may end up having to hint the SQL to force the plan.

Inevitably there will be cases where it is extremely difficult to get “explain plan” to reproduce exactly the same path (down to the predicate level) even if you put in hints that make the plan look the same. But since it’s usually the statistics or the variable types that make the differences, you can often fiddle around and get a pretty good idea.

If you’ve got a closed cursor from your tkprof output, you’ll have the row counts returned as well – which gives you extra information about which execution lines did most work, and that’s important information that you can use to confirm your suspicions.

While reading an explain plan output i find predicate information very useful for checking the columns on which access & filter operations are happening. But in the execution plan printed in the tkprof output there is no predicate information. How do we make up for this missing piece of information, if we are given a tkprof output to analyze ?

What i generally try to do is that take explain plan of the query and see the predicate stuff from there. But in many of the cases the execution plan in the tkprof (actual execution plan) & the plan given by explain plan differ. Would like to know that how to troubleshoot the expensive steps in the execution plan in such cases ?

Thanks !

Regards,
Amardeep Sidhu

]]>By: Show Me a Picture! « So Many Oracle Manuals, So Little Timehttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-33004
Fri, 15 May 2009 05:48:51 +0000http://jonathanlewis.wordpress.com/?p=90#comment-33004[…] download the PL/SQL package and the SQL query mentioned in the article. I got this idea from a comment left on Jonathan Lewis’ blog. The SQL query generates Graphviz commands that produce a […]
]]>By: Andy Helmhttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-608
Tue, 19 Dec 2006 14:21:28 +0000http://jonathanlewis.wordpress.com/?p=90#comment-608OK, 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
/

]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-578
Mon, 18 Dec 2006 21:12:27 +0000http://jonathanlewis.wordpress.com/?p=90#comment-578Ian, 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.

]]>By: windenhttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-523
Sat, 16 Dec 2006 18:02:56 +0000http://jonathanlewis.wordpress.com/?p=90#comment-523I’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 :)

]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-451
Thu, 14 Dec 2006 20:31:42 +0000http://jonathanlewis.wordpress.com/?p=90#comment-451Dave, 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;
oralter session set “_rowsource_execution_statistics”=true;
before executing a query.
]]>By: Jonathan Lewishttps://jonathanlewis.wordpress.com/2006/12/12/plans-in-memory/#comment-450
Thu, 14 Dec 2006 20:13:07 +0000http://jonathanlewis.wordpress.com/?p=90#comment-450Ian, 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.