Re: flush share pool for one sql to force new plan

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.7319544765 "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. Received on Thu Feb 22 2007 - 15:22:00 CST