| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: flush share pool for one sql to force new plan
On Feb 22, 4:40 pm, "Ben" <bal..._at_comcast.net> wrote:
> On Feb 22, 4:22 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > On Feb 22, 3:06 pm, "Ben" <bal..._at_comcast.net> wrote:
> > > Misses in library cache during parse: 1
> > > Optimizer goal: CHOOSE
> > > Parsing user id: 5 (SYSTEM)
>
> > > Rows Row Source Operation
> > > ------- ---------------------------------------------------
> > > 0 TABLE ACCESS BY INDEX ROWID F47027
> > > 0 INDEX RANGE SCAN F47027_11 (object id 744882)
>
> > > Rows Execution Plan
> > > ------- ---------------------------------------------------
> > > 0 SELECT STATEMENT GOAL: CHOOSE
> > > 0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'F47027'
>
> > > ***************************************************************************ญญญ*****
>
> > > I'm a little confused here as to why the Row Source Operation shows an
> > > index scan and the Execution Plan shows a fts.
>
> >http://www.jlcomp.demon.co.uk/testing_02.html
> > "...It is the 'proper' output, by the way, as it is the Row Source
> > Operation section. The Execution Plan section is the one that may be
> > misleading"
>
> >http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2006-11-18.7...
> > "I stumbled across the titles 'Row Source Operation' and 'Execution
> > Plan'. I noticed that the access paths were different. Hmm, how could
> > that be? I began to dig into the Oracle 9i documentation for TKPROF
> > and found that 'Row Source Operation' is the access path that was
> > taken during the statement's execution and the 'Explain Plan' (sic) is
> > the access path that Oracle is predicting the statement will take
> > before it executes."
>
> > "Expert Oracle Database Architecture" by Tom Kyte pg 384
> > "The cr=96384 in the TKPROF Row Source Operation line shows us exactly
> > how many consistent reads were done..."
>
> > >From the above:
>
> > Execution Plan = Best Guess Prior to Execution
> > Row Source Operation = How the Data was Retrieved
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
>
> And then this tells me that it is indeed 'peeking' at the bind
> variable, and that if I flush the shared_pool then the next time the
> ERP system calls that select it should indeed use the index.
>
I don't know for certain that you can tell that it is peeking at the bind variable from the above. The cost based optimizer in Oracle assumes that 5% of the rows will be returned when bind variables are used, if peeking is not permitted. The predicted 5% is small enough so that Oracle will probably choose to use an index to access the data, even if that is not the best access method. A 10053 trace will tell you for certain. If you still want to experiment:
In SQLPlus:
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
Set up your SQL statement to use bind variables as you did before.
Make a small change to the SQL statement to make certain that Oracle
performs a hard parse (capitalization, spacing, etc). Then execute
your SQL statement. Then:
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'; Open the trace file using a text editor: Notepad, EDIT, vi, jed, etc. See if the trace file contains something that looks like this (this is from a 10053 trace on 10.2.0.2):
If you see something like the above, you will know for certain that Oracle peeked at the bind variables.
It has been a while since I have looked at the output of TKPROF. I usually dig directly in the 10046 trace file for the information of interest. Based on "Optimizing Oracle Performance" by Cary Millsap (pg 83), the "Row Source Operations" indicated in the TKPROF output are identified by STAT lines in the trace file.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Thu Feb 22 2007 - 16:59:52 CST
 |
 |