This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 17, 2012 11:11 AM by Randolf Geist RSS

Explain Plan vs. V$SQL_PLAN

883641 Newbie
Currently Being Moderated
Hello everyone,
I'm trying to understand the difference between those two, I'm relying on the following Tom Kyte article :
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html

In my following example I didn't use TKPROF as he did but AUTOTRACE / V$SQL_PLAN instead (since EXPLAIN PLAN shows a theoretical plan that can be used if this statement were to be executed and V$SQL_PLAN contains the actual plan used)

That's my code :

>

HR> ALTER SYSTEM FLUSH shared_pool ;

HR>
HR> create table test
2 as
3 select a.*, 1 id
4 from all_objects a
5 where rownum = 1;

Table created.

HR>
HR> create index t_idx on test(id);

Index created.

HR>
HR> -- AUTOTRACE vs V$SQL round 1 ...
HR> ---------------------------------
HR>
HR> SET AUTOTRACE ON EXPLAIN
HR>
HR> select id, object_name from test where id = 1;

ID OBJECT_NAME
---------- ------------------------------
1 ICOL$


Execution Plan
----------------------------------------------------------
Plan hash value: 2783519773

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

HR>
HR> SET AUTOTRACE OFF
HR>


HR>
HR> select operation, options, object_name, cost
2 from v$sql_plan
3 where hash_value= ( SELECT hash_value
4 FROM v$sqlarea
5 WHERE sql_text LIKE 'select id, object_name from test where id = 1'
6 AND sql_text NOT LIKE '%v_sql%');

OPERATION OPTIONS OBJECT_NAME COST
------------------------------ ------------------------------ ------------------------------ ----------
SELECT STATEMENT 2
TABLE ACCESS BY INDEX ROWID TEST 2
INDEX RANGE SCAN T_IDX 1

>

Ok so in round 1, the optimizer decided to get the 1 row back using Index Range Scan both in "theory" and in "reality", it make sense.

Now its time for round 2 ... :)

>

HR> insert into test select a.*, 1 from all_objects a where rownum < 1001 ;

1000 rows created.

HR>
HR> commit ;

Commit complete.

HR>
HR>
HR> -- AUTOTRACE vs V$SQL round 2 ...
HR> ---------------------------------
HR>
HR> SET AUTOTRACE ON EXPLAIN
HR>
HR> select id, object_name from test where id = 1;

ID OBJECT_NAME
---------- ------------------------------
1 ICOL$
1 I_VIEWTRCOL1
...

1001 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2783519773

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 30030 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1001 | 30030 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1001 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Note
-----
- dynamic sampling used for this statement (level=2)


HR>
HR> select operation, options, object_name, cost
2 from v$sql_plan
3 where hash_value= ( SELECT hash_value
4 FROM v$sqlarea
5 WHERE sql_text LIKE 'select id, object_name from test where id = 1'
6 AND sql_text NOT LIKE '%v_sql%');

OPERATION OPTIONS OBJECT_NAME COST
------------------------------ ------------------------------ ------------------------------ ----------
SELECT STATEMENT 2
TABLE ACCESS BY INDEX ROWID TEST 2
INDEX RANGE SCAN T_IDX 1

HR>

>

since explain plan is using always Hard Parse (and it used dynamic sampling) I would expect to see FTS in "theory"

can anyone explain me why in round 2 they both presented Index Range Scan.

Thanks ! :)
  • 1. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    SET AUTOTRACE ON EXPLAIN doesn't force hard parse of consequent statements, it simply displays explain plan for each statement. Since you didn't flush the shared pool before runs 1 and 2, the optimizer simply reused the plan cached during the 1st run in the 2nd run.

    Best regards,
    Nikolay
  • 2. Re: Explain Plan vs. V$SQL_PLAN
    883641 Newbie
    Currently Being Moderated
    Hi Nikolay.

    Please correct me if I'm wrong - AUTOTRACE and EXPLAIN PLAN FOR.. will always display the same results as AUTOTRACE is just an EXPLAIN PLAN undercover
    You suggested I flush the shared pool after each round and I did (First round both - FTS, second round both - Index Range Scan) how come the results are totally different Tom's article and that without any shared pool flushing ?

    Thank you
  • 3. Re: Explain Plan vs. V$SQL_PLAN
    Dom Brooks Guru
    Currently Being Moderated
    Explain plan can lie, autotrace - which just does an explain plan - can lie.
    See:
    http://oracle-randolf.blogspot.com/2012/01/autotrace-polluting-shared-pool.html
    http://kerryosborne.oracle-guy.com/2010/02/autotrace-lies/
    http://hoopercharles.wordpress.com/2010/01/11/explain-plan-lies-autotrace-lies-tkprof-lies-what-is-the-plan/

    V$SQL_PLAN is the truth.
    You didn't mention version but DBMS_XPLAN is the most convenient way to get your plan.

    If the plan is cached, inserting 1000 rows is not going to change the plan.
    SQL> create table test
      2  as
      3  select a.*, 1 id
      4  from all_objects a
      5  where rownum = 1;
    
    Table created.
    
    SQL> 
    SQL> create index t_idx on test(id);
    
    Index created.
    
    SQL> 
    SQL> select id, object_name from test where id = 1;
    
            ID OBJECT_NAME
    ---------- ------------------------------
             1 ORA$BASE
    
    SQL> 
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
    SQL_ID  3qan6s0j3uab5, child number 0
    -------------------------------------
    select id, object_name from test where id = 1
    
    Plan hash value: 2783519773
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    30 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("ID"=1)
    
    Note
    -----
       - dynamic sampling used for this statement (level=4)
    
    
    23 rows selected.
    
    SQL> insert into test select a.*, 1 from all_objects a where rownum < 1001 ;
    
    1000 rows created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select id, object_name from test where id = 1;
    ....
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------
    SQL_ID  3qan6s0j3uab5, child number 0
    -------------------------------------
    select id, object_name from test where id = 1
    
    Plan hash value: 2783519773
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    30 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("ID"=1)
    
    Note
    -----
       - dynamic sampling used for this statement (level=4)
    
    
    23 rows selected.
    
    SQL> 
  • 4. Re: Explain Plan vs. V$SQL_PLAN
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Autotrace calls explain plan, with a different literal in the "set statement_id" option. This means the explained statement is re-optimised each time. Note, then, that you don't have statistics on your table, and the optimizer has used dynamic sampling to collect temporary stats when it explains the statement. This should be sufficient information to answer your question.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Author: <b><em>Oracle Core</em></b>
  • 5. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,


    yes you are right about AUTOTRACE and EXPLAIN PLAN FOR.

    I didn't have a chance to go through Tom'a article, but I ran your test and found out that flushing shared pool won't help: the optimizer still goes for an INDEX RANGE SCAN.

    I think the reason for that is that dynamic sampling has certain counter-intuitive limitations which render it useless in this particular case. Once you collect the stats, the plan changes to FULL TABLE SCAN immediately.

    If you are interested in details of dynamic sampling responsible for this behavior, you may find useful links in my blogpost on dynamic sampling pitfalls: http://savvinov.wordpress.com/2011/12/21/dynamic-sampling-pitfalls/.

    Best regards,
    Nikolay
  • 6. Re: Explain Plan vs. V$SQL_PLAN
    871018 Explorer
    Currently Being Moderated
    >
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |     1 |    30 |     2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |     1 |    30 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("ID"=1)
    Now its time for round 2 ... :)
    -------------------------------------------------------------------------------------
    | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |       |  1001 | 30030 |     6   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TEST  |  1001 | 30030 |     6   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | T_IDX |  1001 |       |     5   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("ID"=1)
    can anyone explain me why in round 2 they both presented Index Range Scan.
    CBO is cost based optimizer. Oracle chose Index Range Scan because it considers this plan as cheaper than plan with FTS.
    To determine what exactly is going on, try
    1) to explaim plan with forced FTS (hint full) and look where cost is higher
    2) look at the trace 10053
  • 7. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Jonathan,

    I wonder why dynamic sampling fails to see that index access is inefficient here?..

    Best regards,
    Nikolay
  • 8. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Alexander,

    I looked in the 10053 trace file, and it still baffles me... the CBO sees selectivity = 1.00 and still goes for an index range scan, estimating its cost at 6... how come?

    ** Using dynamic sampling card. : 1001
    ** Dynamic sampling updated table card.
    ** Using single table dynamic sel. est. : 1.00000000
      Table: TEST  Alias: TEST
        Card: Original: 1001.000000  Rounded: 1001  Computed: 1001.00  Non Adjusted: 1001.00
      Access Path: TableScan
        Cost:  8.04  Resp: 8.04  Degree: 0
          Cost_io: 8.00  Cost_cpu: 664293
          Resp_io: 8.00  Resp_cpu: 664293
      Access Path: index (AllEqRange)
        Index: T_IDX
        resc_io: 6.00  resc_cpu: 43379
        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
        Cost: 6.00  Resp: 6.00  Degree: 1
      Best:: AccessPath: IndexRange
      Index: T_IDX
             Cost: 6.00  Degree: 1  Resp: 6.00  Card: 1001.00  Bytes: 0
    Best regards,
    Nikolay

    Edited by: Nikolay Savvinov on Feb 16, 2012 2:31 AM
  • 9. Re: Explain Plan vs. V$SQL_PLAN
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Nikolay Savvinov wrote:
    Hi Alexander,

    I looked in the 10053 trace file, and it still baffles me... the CBO sees selectivity = 1.00 and still goes for an index range scan, estimating its cost at 6... how come?
    The optimizer is obviously confused by the existing index statistics. Remember that from 10g you can't prevent Oracle from gathering index statistics when creating an index (there is no NOCOMPUTE STATISTICS clause or similar for the CREATE INDEX command, you could only lock statistics to achieve that).

    Hence you end up with a table without statistics, but an analyzed index.

    In this case it looks like the optimizer simply (more or less) ignores the cost estimate for the table access (a bug I would say which I could reproduce on 11.2.0.2 and 11.2.0.3).

    Deleting the index statistics (and ensuring that the statement gets re-optimized in case of already cached cursors) shows a more reasonable costing for the table access, which might lead to a plan change.

    And surprisingly, although the optimizer doesn't have a clue about the clustering factor of the index with deleted index statistics (Dynamic Sampling doesn't provide that information), the costing of the table access in such a case changes significantly between 11.2.0.2 and 11.2.0.3 - it might be a side effect of another optimizer bug fix / enhancement.

    Hope this helps,
    Randolf

    Edited by: Randolf Geist on Feb 16, 2012 3:00 PM

    See my below reply / comment - I don't think this is a buggy behaviour
  • 10. Re: Explain Plan vs. V$SQL_PLAN
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Randolf Geist wrote:
    In this case it looks like the optimizer simply (more or less) ignores the cost estimate for the table access (a bug I would say which I could reproduce on 11.2.0.2 and 11.2.0.3).
    Thinking about it again I'm not so sure this can be called a bug - after all the clustering factor of the index in this case is 0, so the resulting cost of the table access according to the formula will be 0...

    Hope this helps,
    Randolf
  • 11. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Randolf,

    thanks a lot for the explanation.

    This behavior sounds like a bug to me because it would be reasonable to expect the optimizer to make an effort to estimate the clustering factor, otherwise what is dynamic sampling for?...

    Best regards,
    Nikolay
  • 12. Re: Explain Plan vs. V$SQL_PLAN
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Nikolay Savvinov wrote:
    This behavior sounds like a bug to me because it would be reasonable to expect the optimizer to make an effort to estimate the clustering factor, otherwise what is dynamic sampling for?...
    Hi Nikolay,

    Oracle never said that Dynamic Sampling offers the same level of statistics information as regular object statistics. For example column min and max values are also not determined by Dynamic Sampling (can be relevant for join cardinality calculations), and other index related statistics is another information that is not covered by Dynamic Sampling. Furthermore Dynamic Sampling (like the Cardinality Feedback feature added in 11.2) covers only single table access paths, so joins are not covered either (although the most relevant join column statistics like number of distinct values and nulls are sampled).

    Dynamic Sampling is particularly useful for complex predicates local to a table that cannot be covered (yet) by regular statistics, like combinations of expressions and correlated column values which cannot be covered (yet) even with Extended Statistics in 11g. Due to the sampling however, real-life data distributions might still lead to poor estimates or even rejection of the Dynamic Sampling results.

    I'll try to cover all these things in my Dynamic Sampling series on "AllThingsOracle.com".

    Hope this helps,
    Randolf
  • 13. Re: Explain Plan vs. V$SQL_PLAN
    tkyte Employee ACE
    Currently Being Moderated
    dynamic sampling is for getting the gist of an unanalyzed segment as fast as possible.

    It'll get things like a guess at the number of rows, high/low values and the like.

    It does not, it cannot get the breadth of statistics dbms_stats.gather_xxxx would get. Since a dynamic sample is done during every hard parse - the overhead of doing a full blown gather statistics would render it useless in real life.

    So, dynamic sampling is there to get the gist of a table- or fill in gaps in the statistics - http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html - it is NOT a replacement for having full on statistics.

    Why would it be reasonable for the optimizer, at run time, before processing a query, to spend more time gathering statistics than actually running the query? Dynamic sampling is a stop-gap measure to try to fill in some of the blanks when you have forgotten to do so yourself.

    Edited by: tkyte on Feb 16, 2012 6:18 AM
  • 14. Re: Explain Plan vs. V$SQL_PLAN
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Tom,

    thanks for replying.

    I never ment to say that dynamic sampling must necessarily same level of thoroughness as dbms_stats. However, assuming clustering_factor = 0 looks wrong to me. Wouldn't assuming clustering_factor = num_table_blocks be more reasonable?

    Best regards,
    Nikolay
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points