Sub-optimal Execution Plan For Query Against V$Datafile if using FIRST_ROWS (Doc ID 311832.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.4 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.

Symptoms

QUERY AGAINST V$DATAFILE TAKES LONGER AND FOLLOWS A SUB-OPTIMAL  EXPLAIN PLAN SHOWING MERGE-JOIN CARTESIAN WITH FIRST_ROWS OPTIMIZER

Explain Plan with FIRST_ROWS ( BAD)    
    SQL> alter session set optimizer_mode=first_rows;
   
    Session altered.
    
    SQL> select * from V$datafile;
   
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=68 Card=1 Bytes=
              909)
   
       1    0   NESTED LOOPS (Cost=68 Card=1 Bytes=909)
       2    1     MERGE JOIN (CARTESIAN) (Cost=51 Card=1 Bytes=883)
       3    2       NESTED LOOPS (Cost=34 Card=1 Bytes=586)
       4    3         FIXED TABLE (FULL) OF 'X$KCCFE' (Cost=17 Card=1 Byte
              s=276)
   
       5    3         FIXED TABLE (FIXED INDEX) OF 'X$KCCFN (ind:1)' (Cost
              =17 Card=1 Bytes=310)
   
       6    2       BUFFER (SORT) (Cost=34 Card=100 Bytes=29700)
       7    6         FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=17 Card=100 By
              tes=29700)
   
       8    1     FIXED TABLE (FULL) OF 'X$KCVFH' (Cost=17 Card=1 Bytes=26
              )
   
    Explain Plan with FIRST_ROWS_10 ( GOOD)

   SQL> alter session set optimizer_mode=first_rows_10;
   
    Session altered.
   
    SQL> select * from V$datafile;

    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=68 Card=1 Bytes=
              909)
   
       1    0   NESTED LOOPS (Cost=68 Card=1 Bytes=909)
       2    1     NESTED LOOPS (Cost=51 Card=1 Bytes=612)
       3    2       NESTED LOOPS (Cost=34 Card=1 Bytes=586)
       4    3         FIXED TABLE (FULL) OF 'X$KCCFE' (Cost=17 Card=1 Byte
              s=276)
   
       5    3         FIXED TABLE (FIXED INDEX) OF 'X$KCCFN (ind:1)' (Cost
              =17 Card=1 Bytes=310)
   
       6    2       FIXED TABLE (FIXED INDEX) OF 'X$KCVFH (ind:1)' (Cost=1
              7 Card=1 Bytes=26)
   
       7    1     FIXED TABLE (FULL) OF 'X$KCCFN' (Cost=17 Card=1 Bytes=29
              7)
   

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms