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:
>
>
>
>
>
> > On Feb 22, 2:04 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > > I am not certain if Oracle 9.2 supports this syntax, but you should be
> > > able to execute something like the following after executing a query
> > > (no need to execute EXPLAIN PLAN):
> > > SELECT
> > > *
> > > FROM
> > > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
>
> > > On Oracle 10.2.0.2, if statistics_level is set to ALL, the output
> > > might look like this for a SQL statement:
> > > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
> > > | Buffers | OMem | 1Mem | O/1/M |
> > > ---------------------------------------------------------------------------ญญญ----------------------------------------
> > > |* 1 | FILTER | | 1 | | 12 |00:00:00.01
> > > | 14 | | | |
> > > | 2 | MERGE JOIN CARTESIAN| | 1 | 36 | 12 |
> > > 00:00:00.01 | 14 | | | |
> > > |* 3 | VIEW | | 1 | 6 | 2 |00:00:00.01
> > > | 7 | | | |
> > > | 4 | COUNT | | 1 | | 6 |00:00:00.01
> > > | 7 | | | |
> > > | 5 | TABLE ACCESS FULL| T2 | 1 | 6 | 6 |
> > > 00:00:00.01 | 7 | | | |
> > > | 6 | BUFFER SORT | | 2 | 6 | 12 |
> > > 00:00:00.01 | 7 | 9216 | 9216 | 1/0/0|
> > > | 7 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 |
> > > 00:00:00.01 | 7 | | | |
>
> > > Note that both estimated and actual rows are returned.
>
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> > > - Show quoted text -
>
> > After viewing the link from asktom, I put together my own test with
> > sql_trace=true and then using sqlplus setting the bind variable and
> > then tkprof to see what I get.
> > I'm not for certain but it looks to me like it generated two plans.
>
> > SQL> alter session set sql_trace=true;
>
> > Session altered.
>
> > SQL> set autotrace traceonly statistics
> > SQL> exec :key1 := ' '
>
> > PL/SQL procedure successfully completed.
>
> > SQL> select * from proddta.f47027 where szedsp = :key1;
>
> > no rows selected
>
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 141 consistent gets
> > 0 physical reads
> > 0 redo size
> > 6397 bytes sent via SQL*Net to client
> > 232 bytes received via SQL*Net from client
> > 1 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 0 rows processed
>
> > SQL> exec :key1 := 'Y'
>
> > PL/SQL procedure successfully completed.
>
> > SQL> select * from proddta.f47027 where szedsp = :key1;
>
> > 41891 rows selected.
>
> > Statistics
> > ----------------------------------------------------------
> > 0 recursive calls
> > 0 db block gets
> > 14987 consistent gets
> > 10003 physical reads
> > 0 redo size
> > 9887130 bytes sent via SQL*Net to client
> > 19817 bytes received via SQL*Net from client
> > 2794 SQL*Net roundtrips to/from client
> > 0 sorts (memory)
> > 0 sorts (disk)
> > 41891 rows processed
>
> > SQL> set autotrace off
> > SQL>
> > SQL> alter session set sql_trace=false;
>
> > Session altered.
>
> > And here is the tkprof output....
>
> > select *
> > from
> > proddta.f47027 where szedsp = :key1
>
> > call count cpu elapsed disk query
> > current rows
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > Parse 2 0.01 0.00 0 0
> > 0 0
> > Execute 2 0.00 0.00 0 0
> > 0 0
> > Fetch 2795 3.81 42.80 10003 15128
> > 0 41891
> > ------- ------ -------- ---------- ---------- ---------- ----------
> > ----------
> > total 2799 3.82 42.81 10003 15128
> > 0 41891
>
> > 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 -
>
> - Show 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.
Received on Thu Feb 22 2007 - 15:40:36 CST
