RE: problem consistently getting explain plan with 10046 trace
- From: ryan_gaffuri@xxxxxxxxxxx
- To: cary.millsap@xxxxxxxxxx, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 04 Aug 2006 18:26:03 +0000
we will try exiting the session. We just had something odd happen. One of our
developers used 10046 trace on a set of procedures inside of a package.
The sql that was just selects got us the explain plan with the row by row LIO
and PIO which is what we want, but the array inserts did not. We just got the
explain plan dump.
we will also try v$sql_plan.
thanks.
-------------- Original message --------------
From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
Ryan,
The Oracle kernel emits the STAT lines to the trace only when the application
closes a cursor. Exiting before disabling the trace is usually the surest way
to make the close event occur. So do this instead:
alter session set time_statistics=true;
alter session set events '10046 trace name context forever, level 12';
<run stuff>
disconnect;
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Hotsos Symposium 2007 / March 4?8 / Dallas
Visit www.hotsos.com for curriculum and schedule details...
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of ryan_gaffuri@xxxxxxxxxxx
Sent: Friday, August 04, 2006 10:20 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: problem consistently getting explain plan with 10046 trace
We have found that we do not always get the 10046 explain plan that gives us
the LIO and PIO for each row source with a level 12 report.
This is what we are doing.
alter session set time_statistics=true
alter session set events '10046 trace name context forever, level 12';
<run stuff>
alter session set events '10046 trace name context off';
We find that we get the plan more often if we do a level 8 and if we exit the
session the session before running tkprof.
tkprof *.trc *.rep sys=no explain=<username/password>
I don't really want the 'explain plan' you get in sqlplus. I need the one
specific to the trace file that gives LIOs and PIOs by row source.
anyone else having this problem?
Other related posts:
