1 2 Previous Next 18 Replies Latest reply on Apr 1, 2016 12:14 PM by 3024242

    Need to know regarding Explain Plan for a query

    3024242

      Hi All,

       

      I am getting different explain plans for the same query in different servers. Point to be noted is the table has the same structure (including index) in both servers and almost same data.

      I have attached below explain plan output for both servers below. First in which it is using Full table scan and the second where it is using index:

       

      1st Server (Full table scan)

      PLAN_TABLE_OUTPUT

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Plan hash value: 4082614991

       

       

      --------------------------------------------------------------------------------------------------

      | Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

      --------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT               |                 |     1 |   105 |   602   (2)| 00:00:08 |

      |   1 |  NESTED LOOPS                  |                 |     1 |   105 |   602   (2)| 00:00:08 |

      |   2 |   NESTED LOOPS                 |                 |     1 |   105 |   602   (2)| 00:00:08 |

      |   3 |    NESTED LOOPS                |                 |     1 |    82 |   601   (2)| 00:00:08 |

      |*  4 |     TABLE ACCESS FULL          | SLCCCAISS       |     1 |    44 |   600   (2)| 00:00:08 |

      |*  5 |     TABLE ACCESS BY INDEX ROWID| SLCAWBHDR       |     1 |    38 |     1   (0)| 00:00:01 |

      |*  6 |      INDEX UNIQUE SCAN         | SLCAWBHDR_UIND1 |     1 |       |     1   (0)| 00:00:01 |

      |*  7 |    INDEX UNIQUE SCAN           | GENLOCHDR_UIND1 |     1 |       |     1   (0)| 00:00:01 |

      |*  8 |   TABLE ACCESS BY INDEX ROWID  | GENLOCHDR       |     1 |    23 |     1   (0)| 00:00:01 |

      --------------------------------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

       

         4 - filter((:P_CCASERNUM IS NULL OR :P_CCASERNUM IS NOT NULL AND

                    "CCA"."CCASERNUM"=TO_NUMBER(:P_CCASERNUM)) AND (:P_DOCNUM IS NULL OR :P_DOCNUM IS NOT

                    NULL AND "CCA"."DOCNUM"=TO_NUMBER(:P_DOCNUM)) AND "CCA"."BILSTA"='NC' AND

                    "CCA"."RATAUDIND"='Y' AND "CCA"."EBCSALIND"='E' AND "CCA"."CCAISSDAT"<=:P_BILUPT AND

                    "CCA"."MSTUPDIND"='Y' AND "CCA"."TRNIND"='N' AND NVL("CCA"."EXCBILIND",'N')='N')

         5 - filter((:P_LOCCOD IS NULL OR :P_LOCCOD IS NOT NULL AND "AWB"."LOCCOD"=:P_LOCCOD)

                    AND (:P_SALCURCOD IS NULL OR :P_SALCURCOD IS NOT NULL AND "AWB"."CURCOD"=:P_SALCURCOD)

                    AND "AWB"."RATAUDIND"='Y' AND "GEN2007"."VAL_LOC"("AWB"."LOCCOD")='Y' AND

                    ("CCA"."CARNUMCOD"=:P_CARNUMCOD AND :P_CARNUMCOD IS NOT NULL OR

                    "AWB"."CARNUMCOD"=:G_CARNUMCOD))

         6 - access("AWB"."DOCNUM"="CCA"."DOCNUM" AND "AWB"."CARNUMCOD"="CCA"."CARNUMCOD")

         7 - access("LOC"."LOCCOD"="AWB"."LOCCOD")

         8 - filter((:P_AGPCOD IS NULL OR :P_AGPCOD IS NOT NULL AND "LOC"."AGPCOD"=:P_AGPCOD)

                    AND (:P_LOCTYP IS NULL OR :P_LOCTYP IS NOT NULL AND "LOC"."LOCTYP"=:P_LOCTYP))

       

      2nd Server (Using Index)

      PLAN_TABLE_OUTPUT

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Plan hash value: 1955665079

       

       

      ----------------------------------------------------------------------------------------------------

      | Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

      ----------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                 |                 |     2 |   208 |   534   (6)| 00:00:01 |

      |   1 |  CONCATENATION                   |                 |       |       |            |          |

      |   2 |   NESTED LOOPS                   |                 |     1 |   104 |   220   (5)| 00:00:01 |

      |   3 |    NESTED LOOPS                  |                 |     1 |   104 |   220   (5)| 00:00:01 |

      |   4 |     NESTED LOOPS                 |                 |     1 |    60 |   219   (5)| 00:00:01 |

      |*  5 |      TABLE ACCESS BY INDEX ROWID | SLCAWBHDR       |     1 |    37 |   218   (5)| 00:00:01 |

      |*  6 |       INDEX SKIP SCAN            | SLCAWBHDR_UIND2 |    10 |       |   217   (5)| 00:00:01 |

      |*  7 |      TABLE ACCESS BY INDEX ROWID | GENLOCHDR       |     1 |    23 |     1   (0)| 00:00:01 |

      |*  8 |       INDEX UNIQUE SCAN          | GENLOCHDR_UIND1 |     1 |       |     1   (0)| 00:00:01 |

      |*  9 |     INDEX RANGE SCAN             | SLCCCAISS_IND1  |     1 |       |     1   (0)| 00:00:01 |

      |* 10 |    TABLE ACCESS BY INDEX ROWID   | SLCCCAISS       |     1 |    44 |     1   (0)| 00:00:01 |

      |* 11 |   FILTER                         |                 |       |       |            |          |

      |  12 |    NESTED LOOPS                  |                 |     1 |   104 |   315   (7)| 00:00:01 |

      |  13 |     NESTED LOOPS                 |                 |     1 |   104 |   315   (7)| 00:00:01 |

      |  14 |      NESTED LOOPS                |                 |     1 |    81 |   314   (7)| 00:00:01 |

      |* 15 |       TABLE ACCESS BY INDEX ROWID| SLCCCAISS       |     1 |    44 |   312   (7)| 00:00:01 |

      |* 16 |        INDEX SKIP SCAN           | SLCCCAISS_IND1  |  4016 |       |     8  (13)| 00:00:01 |

      |* 17 |       TABLE ACCESS BY INDEX ROWID| SLCAWBHDR       |     1 |    37 |     1   (0)| 00:00:01 |

      |* 18 |        INDEX UNIQUE SCAN         | SLCAWBHDR_UIND1 |     1 |       |     1   (0)| 00:00:01 |

      |* 19 |      INDEX UNIQUE SCAN           | GENLOCHDR_UIND1 |     1 |       |     1   (0)| 00:00:01 |

      |* 20 |     TABLE ACCESS BY INDEX ROWID  | GENLOCHDR       |     1 |    23 |     1   (0)| 00:00:01 |

      ----------------------------------------------------------------------------------------------------

       

       

      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

       

         5 - filter((:P_LOCCOD IS NULL OR :P_LOCCOD IS NOT NULL AND "AWB"."LOCCOD"=:P_LOCCOD) AND

                    (:P_SALCURCOD IS NULL OR :P_SALCURCOD IS NOT NULL AND "AWB"."CURCOD"=:P_SALCURCOD) AND

                    "AWB"."RATAUDIND"='Y')

         6 - access("AWB"."CARNUMCOD"=:G_CARNUMCOD)

             filter("AWB"."CARNUMCOD"=:G_CARNUMCOD AND "GEN2007"."VAL_LOC"("AWB"."LOCCOD")='Y')

         7 - filter((:P_AGPCOD IS NULL OR :P_AGPCOD IS NOT NULL AND "LOC"."AGPCOD"=:P_AGPCOD) AND

                    (:P_LOCTYP IS NULL OR :P_LOCTYP IS NOT NULL AND "LOC"."LOCTYP"=:P_LOCTYP))

         8 - access("LOC"."LOCCOD"="AWB"."LOCCOD")

         9 - access("AWB"."DOCNUM"="CCA"."DOCNUM" AND "AWB"."CARNUMCOD"="CCA"."CARNUMCOD")

        10 - filter((:P_CCASERNUM IS NULL OR :P_CCASERNUM IS NOT NULL AND

                    "CCA"."CCASERNUM"=TO_NUMBER(:P_CCASERNUM)) AND (:P_DOCNUM IS NULL OR :P_DOCNUM IS NOT NULL

                    AND "CCA"."DOCNUM"=TO_NUMBER(:P_DOCNUM)) AND "CCA"."BILSTA"='NC' AND

                    "CCA"."CCAISSDAT"<=:P_BILUPT AND "CCA"."RATAUDIND"='Y' AND "CCA"."MSTUPDIND"='Y' AND

                    "CCA"."EBCSALIND"='E' AND "CCA"."TRNIND"='N' AND NVL("CCA"."EXCBILIND",'N')='N')

        11 - filter(:P_CARNUMCOD IS NOT NULL)

        15 - filter((:P_CCASERNUM IS NULL OR :P_CCASERNUM IS NOT NULL AND

                    "CCA"."CCASERNUM"=TO_NUMBER(:P_CCASERNUM)) AND (:P_DOCNUM IS NULL OR :P_DOCNUM IS NOT NULL

                    AND "CCA"."DOCNUM"=TO_NUMBER(:P_DOCNUM)) AND "CCA"."BILSTA"='NC' AND

                    "CCA"."CCAISSDAT"<=:P_BILUPT AND "CCA"."RATAUDIND"='Y' AND "CCA"."MSTUPDIND"='Y' AND

                    "CCA"."EBCSALIND"='E' AND "CCA"."TRNIND"='N' AND NVL("CCA"."EXCBILIND",'N')='N')

        16 - access("CCA"."CARNUMCOD"=:P_CARNUMCOD)

             filter("CCA"."CARNUMCOD"=:P_CARNUMCOD)

        17 - filter((:P_LOCCOD IS NULL OR :P_LOCCOD IS NOT NULL AND "AWB"."LOCCOD"=:P_LOCCOD) AND

                    (:P_SALCURCOD IS NULL OR :P_SALCURCOD IS NOT NULL AND "AWB"."CURCOD"=:P_SALCURCOD) AND

                    "GEN2007"."VAL_LOC"("AWB"."LOCCOD")='Y' AND "AWB"."RATAUDIND"='Y')

        18 - access("AWB"."DOCNUM"="CCA"."DOCNUM" AND "AWB"."CARNUMCOD"="CCA"."CARNUMCOD")

             filter(LNNVL("AWB"."CARNUMCOD"=:G_CARNUMCOD))

        19 - access("LOC"."LOCCOD"="AWB"."LOCCOD")

        20 - filter((:P_AGPCOD IS NULL OR :P_AGPCOD IS NOT NULL AND "LOC"."AGPCOD"=:P_AGPCOD) AND

                    (:P_LOCTYP IS NULL OR :P_LOCTYP IS NOT NULL AND "LOC"."LOCTYP"=:P_LOCTYP))

       

       

       

      Please let me know why for the first server it is using full table scan?

       

      Thanks,

      Subhajit

        • 1. Re: Need to know regarding Explain Plan for a query
          ddf_dba

          Please let us know how the data is different on the first server.

           

           

          David Fitzjarrell

          • 2. Re: Need to know regarding Explain Plan for a query
            Tubby

            3024242 wrote:

             

            Please let me know why for the first server it is using full table scan?

             

            Thanks,

            Subhajit

             

            Likely because from the predicate information you supplied it looks like you have a poorly written SQL.

             

               5 - filter((:P_LOCCOD IS NULL OR :P_LOCCOD IS NOT NULL AND "AWB"."LOCCOD"=:P_LOCCOD) AND

                          (:P_SALCURCOD IS NULL OR :P_SALCURCOD IS NOT NULL AND "AWB"."CURCOD"=:P_SALCURCOD) AND

                          "AWB"."RATAUDIND"='Y')

             

            So the query looks to be attempting to be a jack of all trades, either give me a P_LOCCOD or give me a P_SALCURCOD. How would you optimize that if I asked you to? That's like me asking you to get me a book from the Library, one time I'll ask for something starting with A and the next time I'll ask you for something ending in Z ... how would you optimize that before knowing which I ask for?

             

            https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1669972300346534908

             

            Provides a method for dealing with "generic searches" which is what it seems you have.

             

            Cheers,

            • 3. Re: Need to know regarding Explain Plan for a query
              3024242

              The table structure is same for both servers with all the indexes same.

              The only difference is that for the first server the table SLCCCAISS has 55024 rows and for the second server 16197 rows. I don't think that matters however.

              • 4. Re: Need to know regarding Explain Plan for a query
                3024242

                Hi Tubby,

                 

                Then my question goes as how come the same query is using indexes in the second server.

                • 5. Re: Need to know regarding Explain Plan for a query
                  ddf_dba

                  3024242 wrote:

                   

                  The table structure is same for both servers with all the indexes same.

                  The only difference is that for the first server the table SLCCCAISS has 55024 rows and for the second server 16197 rows. I don't think that matters however.

                  Think again, it certainly DOES matter.  If a query returns more than, say, 30% of the table data then the optimizer will likely choose a full table scan as it's less 'work' than an index scan.  You have to have the same data in both servers before you can actually compare why plans are different.  You've answered your own question with this response.

                   

                   

                  David Fitzjarrell

                  • 6. Re: Need to know regarding Explain Plan for a query
                    3024242

                    Thanks David for your reply.

                    So you mean to say that if a table has more rows so the probability of using full table scan is more?

                    I do not quite understand this logic. Kindly elaborate.

                    • 7. Re: Re: Need to know regarding Explain Plan for a query
                      ddf_dba

                      You are returning a higher percentage of the total data in the table on the first server, therefore Oracle is choosing the full table scan as it's 'cheaper' than the double work an index scan and table fetch by rowid would create.  It's not the volume, it's the nature of the data.  As an example:

                       

                      SQL> create table fts_idx(

                        2  mykey      number not null,

                        3  mydata     varchar2(40));

                       

                      Table created.

                       

                      SQL>

                      SQL> create index fts_idx_idx1 on fts_idx(mykey);

                       

                      Index created.

                       

                      SQL>

                      SQL> begin

                        2          for i in 1..1000 loop

                        3                  insert into fts_idx

                        4                  values(i, 'Row '||i);

                        5          end loop;

                        6

                        7          commit;

                        8  end;

                        9  /

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL>

                      SQL> exec dbms_stats.gather_table_stats(user, 'FTS_IDX', cascade=>true)

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL>

                      SQL> select count(*)

                        2  from fts_idx

                        3  where mykey = 3;

                       

                        COUNT(*)

                      ----------

                               1

                       

                      SQL>

                      SQL> set autotrace on

                      SQL>

                      SQL> select *

                        2  from fts_idx

                        3  where mykey = 3;

                       

                           MYKEY MYDATA

                      ---------- ----------------------------------------

                               3 Row 3

                       

                       

                      Execution Plan

                      ----------------------------------------------------------

                      Plan hash value: 3083093184

                       

                      --------------------------------------------------------------------------------------------

                      | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

                      --------------------------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT            |              |     1 |    12 |     2   (0)| 00:00:01 |

                      |   1 |  TABLE ACCESS BY INDEX ROWID| FTS_IDX      |     1 |    12 |     2   (0)| 00:00:01 |

                      |*  2 |   INDEX RANGE SCAN          | FTS_IDX_IDX1 |     1 |       |     1   (0)| 00:00:01 |

                      --------------------------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                         2 - access("MYKEY"=3)

                       

                       

                      Statistics

                      ----------------------------------------------------------

                                1  recursive calls

                                0  db block gets

                                4  consistent gets

                                0  physical reads

                                0  redo size

                              664  bytes sent via SQL*Net to client

                              514  bytes received via SQL*Net from client

                                2  SQL*Net roundtrips to/from client

                                0  sorts (memory)

                                0  sorts (disk)

                                1  rows processed

                       

                      SQL>

                      SQL> set autotrace off

                      SQL>

                      SQL> truncate table fts_idx;

                       

                      Table truncated.

                       

                      SQL>

                      SQL> begin

                        2          for i in 1..1000 loop

                        3                  insert into fts_idx

                        4                  values(mod(i,3)+1, 'Row '||i);

                        5          end loop;

                        6

                        7          commit;

                        8  end;

                        9  /

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL>

                      SQL> exec dbms_stats.gather_table_stats(user, 'FTS_IDX', cascade=>true)

                       

                      PL/SQL procedure successfully completed.

                       

                      SQL>

                      SQL> select count(*)

                        2  from fts_idx

                        3  where mykey = 3;

                       

                        COUNT(*)

                      ----------

                             333

                       

                      SQL>

                      SQL> set autotrace on

                      SQL>

                      SQL> select *

                        2  from fts_idx

                        3  where mykey = 3;

                       

                           MYKEY MYDATA

                      ---------- ----------------------------------------

                               3 Row 2

                               3 Row 5

                               3 Row 8

                               3 Row 11

                               3 Row 14

                               3 Row 17

                               3 Row 20

                               3 Row 23

                               3 Row 26

                               3 Row 29

                               3 Row 32

                      ...

                       

                      333 rows selected.

                       

                       

                      Execution Plan

                      ----------------------------------------------------------

                      Plan hash value: 902935812

                       

                      -----------------------------------------------------------------------------

                      | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

                      -----------------------------------------------------------------------------

                      |   0 | SELECT STATEMENT  |         |   333 |  3663 |     3   (0)| 00:00:01 |

                      |*  1 |  TABLE ACCESS FULL| FTS_IDX |   333 |  3663 |     3   (0)| 00:00:01 |

                      -----------------------------------------------------------------------------

                       

                      Predicate Information (identified by operation id):

                      ---------------------------------------------------

                       

                         1 - filter("MYKEY"=3)

                       

                       

                      Statistics

                      ----------------------------------------------------------

                                1  recursive calls

                                0  db block gets

                               29  consistent gets

                                0  physical reads

                                0  redo size

                             9258  bytes sent via SQL*Net to client

                              910  bytes received via SQL*Net from client

                               24  SQL*Net roundtrips to/from client

                                0  sorts (memory)

                                0  sorts (disk)

                              333  rows processed

                       

                      SQL>

                       

                      As the number of rows that meet the condition increase the likelihood of using an index goes down.  The table didn't change, the index didn't change, the data did.  That's what's happening in your case, I believe.

                       

                       

                      David Fitzjarrell

                      • 8. Re: Need to know regarding Explain Plan for a query
                        John Brady - UK

                        ddf_dba_ifox wrote:

                        3024242 wrote:

                         

                        The table structure is same for both servers with all the indexes same.

                        The only difference is that for the first server the table SLCCCAISS has 55024 rows and for the second server 16197 rows. I don't think that matters however.

                        Think again, it certainly DOES matter.  If a query returns more than, say, 30% of the table data then the optimizer will likely choose a full table scan as it's less 'work' than an index scan.  You have to have the same data in both servers before you can actually compare why plans are different.  You've answered your own question with this response.

                         

                        David Fitzjarrell

                        The cutover point from a full table scan to using an index is much, much lower than 30%.  It will be about 0.5% or lower of the data in the table being retrieved depending on the row size and the number of rows per block. I've blogged about this before:

                        http://databaseperformance.blogspot.co.uk/2016/03/full-table-scan-friend-or-foe.html

                         

                        But as you say, a full table scan can be cheaper than using an index and that is what had happened here.

                         

                        John Brady

                        • 9. Re: Need to know regarding Explain Plan for a query
                          Andrew Sayer

                          If you've gathered representative statistics then oracle has used them to determine it is faster to execute the statement using Afull tablescan, this is entirely plausable. Have you proven that the other plan is better in this situation? Is there something you're not telling the optimizer?Do you need to gather statistics?

                          • 10. Re: Re: Need to know regarding Explain Plan for a query
                            Jonathan Lewis

                            John,

                             

                            I pointed out the flaw in your model a few days ago when you made the same 0.5% claim in answer to another question on OTN.

                            You model is too simplistic, and depends on several factors you have either failed to consider or failed to state explicitly.

                             

                            Consider the following - from 11.2.0.4, default state after creation.  Create a table, populate it, add an index, gather stats (no histograms):

                             

                            select  num_rows, blocks, avg_row_len, round(num_rows/blocks) rows_per_block

                            from    user_tables

                            where   table_name = 'T1'

                            ;

                             

                            set autotrace on explain

                            select count(n1) from t1 where id between 1 and 245000;

                            set autotrace off

                             

                            Results:

                             

                             

                              NUM_ROWS     BLOCKS AVG_ROW_LEN ROWS_PER_BLOCK

                            ---------- ---------- ----------- --------------

                               1000000      25641         180             39

                             

                            COUNT(N1)

                            ----------

                                245000

                             

                            Execution Plan

                            ----------------------------------------------------------

                            Plan hash value: 269862921

                             

                            --------------------------------------------------------------------------------------

                            | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

                            --------------------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT             |       |     1 |    10 |  6841   (1)| 00:01:23 |

                            |   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |

                            |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |   245K|  2392K|  6841   (1)| 00:01:23 |

                            |*  3 |    INDEX RANGE SCAN          | T1_I1 |   245K|       |   552   (1)| 00:00:07 |

                            --------------------------------------------------------------------------------------

                             

                            Predicate Information (identified by operation id):

                            ---------------------------------------------------

                               3 - access("ID">=1 AND "ID"<=245000)

                             

                             

                            That's 24.5% of the table - and the optimizer knows it, and it's still an index range scan.

                             

                             


                            Regards

                            Jonathan Lewis

                            • 11. Re: Re: Need to know regarding Explain Plan for a query
                              Jonathan Lewis

                              Subhajit,

                               

                              In the second case Oracle has split your query into two pieces - one that assumes that :P_CARNUMCOD is null, and one that assumes that :P_CARNUMCOD is not null (see the filter predicate at operation 11).  I am a little surprised that I don't see a filter operation between operation 1 and 2 with the filter predicate (:p_carnumcod is null), but I assume that the optimizer has done something clever to recognise that it's not needed.

                               

                              By making this assumption each of the two plans can operate more efficiently than a single plan with a driving tablescan.  As David points out - when you change the data (and the relevant stats) paths can change, and it doesn't necessarily need a large change in the content, pattern, or input parameters for a dramatic change in the plan to appear. Ideally, of course, the performance should be appropriate for the amount of data actually requested. You have, however, made it impossible for Oracle to find a perfect plan for every combination of inputs by writing SQL with so many "if then else" decisions that should have been handled by the front-end code.

                               

                              There's a very simple version of the problem here ( http://jonathanlewis.wordpress.com/2007/02/14/conditional-sql-2/  ) that may help you understand what's going on.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Re: Re: Need to know regarding Explain Plan for a query
                                JohnWatson2

                                If you have a moment - was your result using a well clustered index? What about your index, John Brady, that led you to 0.5% as a rule of thumb? The reason I ask is that I have been doing some work related to this: trying to see if use of attribute clustering with conventional storage can result in performance similar to that achievable with Exadata smart scan, or with the in-memory column store.

                                Using this test case, I get a switch from index range scan to full table scan at 14% for a perfectly clustered index and at 0.06% for an unclustered index:

                                 

                                create table t1(c1 number,c2 number);

                                create index c1i on t1(c1);

                                create index c2i on t1(c2);

                                insert into t1 select rownum,round(dbms_random.value(1,1000000)) from dual connect by level < 1000000;

                                exec dbms_stats.gather_table_stats(user,'t1');

                                 

                                I am not suggesting that this test is in any way scientific. The fact that it is on my laptop with SSD is going to make it irrelevant to the real world, never mind system stats and serial or parallel  direct or indirect read. But it does show that making assumptions about a %age crossover point is problematic.

                                 

                                I would be interested in knowing if anyone else is working with attribute clustering. It seems to me that it might be an alternative to some expensive options.

                                 

                                Results:

                                orclz> set autot trace exp

                                orclz> select count(c2) from t1 where c1 between 1 and 145000;

                                 

                                 

                                Execution Plan

                                ----------------------------------------------------------

                                Plan hash value: 3724264953

                                 

                                 

                                ---------------------------------------------------------------------------

                                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

                                ---------------------------------------------------------------------------

                                |  0 | SELECT STATEMENT  |      |    1 |    10 |  586  (2)| 00:00:01 |

                                |  1 |  SORT AGGREGATE    |      |    1 |    10 |            |          |

                                |*  2 |  TABLE ACCESS FULL| T1  |  145K|  1416K|  586  (2)| 00:00:01 |

                                ---------------------------------------------------------------------------

                                 

                                 

                                Predicate Information (identified by operation id):

                                ---------------------------------------------------

                                 

                                 

                                  2 - filter("C1"<=145000 AND "C1">=1)

                                 

                                 

                                orclz> select count(c2) from t1 where c1 between 1 and 135000;

                                 

                                 

                                Execution Plan

                                ----------------------------------------------------------

                                Plan hash value: 359387059

                                 

                                 

                                ---------------------------------------------------------------------------------------------

                                | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time    |

                                ---------------------------------------------------------------------------------------------

                                |  0 | SELECT STATEMENT                    |      |    1 |    10 |  552  (1)| 00:00:01 |

                                |  1 |  SORT AGGREGATE                      |      |    1 |    10 |            |          |

                                |  2 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1  |  135K|  1318K|  552  (1)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN                  | C1I  |  135K|      |  273  (1)| 00:00:01 |

                                ---------------------------------------------------------------------------------------------

                                 

                                 

                                Predicate Information (identified by operation id):

                                ---------------------------------------------------

                                 

                                 

                                  3 - access("C1">=1 AND "C1"<=135000)

                                 

                                 

                                orclz>

                                orclz>

                                orclz> select count(c1) from t1 where c2 between 1 and 550;

                                 

                                 

                                Execution Plan

                                ----------------------------------------------------------

                                Plan hash value: 632084968

                                 

                                 

                                ---------------------------------------------------------------------------------------------

                                | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time    |

                                ---------------------------------------------------------------------------------------------

                                |  0 | SELECT STATEMENT                    |      |    1 |    10 |  555  (0)| 00:00:01 |

                                |  1 |  SORT AGGREGATE                      |      |    1 |    10 |            |          |

                                |  2 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1  |  551 |  5510 |  555  (0)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN                  | C2I  |  551 |      |    4  (0)| 00:00:01 |

                                ---------------------------------------------------------------------------------------------

                                 

                                 

                                Predicate Information (identified by operation id):

                                ---------------------------------------------------

                                 

                                 

                                  3 - access("C2">=1 AND "C2"<=550)

                                 

                                 

                                orclz> select count(c1) from t1 where c2 between 1 and 650;

                                 

                                 

                                Execution Plan

                                ----------------------------------------------------------

                                Plan hash value: 3724264953

                                 

                                 

                                ---------------------------------------------------------------------------

                                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

                                ---------------------------------------------------------------------------

                                |  0 | SELECT STATEMENT  |      |    1 |    10 |  586  (2)| 00:00:01 |

                                |  1 |  SORT AGGREGATE    |      |    1 |    10 |            |          |

                                |*  2 |  TABLE ACCESS FULL| T1  |  651 |  6510 |  586  (2)| 00:00:01 |

                                ---------------------------------------------------------------------------

                                 

                                 

                                Predicate Information (identified by operation id):

                                ---------------------------------------------------

                                 

                                 

                                  2 - filter("C2"<=650 AND "C2">=1)

                                 

                                 

                                orclz>

                                • 13. Re: Need to know regarding Explain Plan for a query
                                  Jonathan Lewis

                                  John,

                                   

                                  Exactly.

                                   

                                  I can't remember how many times over the last 15 or 20 years I've explained in articles, newsgroups, forums, listservers, user group conferences, and even a BOOK that it's not just the volume of data that matters, the pattern is important too; so it's pretty depressing that someone can still come out with a silly idea like "the breakpoint is X percent".

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: Need to know regarding Explain Plan for a query
                                    John Brady - UK

                                    Jonathan Lewis wrote:

                                     

                                    John,

                                     

                                    I pointed out the flaw in your model a few days ago when you made the same 0.5% claim in answer to another question on OTN.

                                    You model is too simplistic, and depends on several factors you have either failed to consider or failed to state explicitly.

                                     

                                    Consider the following - from 11.2.0.4, default state after creation.  Create a table, populate it, add an index, gather stats (no histograms):

                                     

                                    set autotrace on explain

                                    select count(n1) from t1 where id between 1 and 245000;

                                    set autotrace off

                                     

                                    Results:

                                     

                                      NUM_ROWS     BLOCKS AVG_ROW_LEN ROWS_PER_BLOCK

                                    ---------- ---------- ----------- --------------

                                       1000000      25641         180             39

                                     

                                    COUNT(N1)

                                    ----------

                                        245000

                                     

                                    Execution Plan

                                    ----------------------------------------------------------

                                    Plan hash value: 269862921

                                     

                                    --------------------------------------------------------------------------------------

                                    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

                                    --------------------------------------------------------------------------------------

                                    |   0 | SELECT STATEMENT             |       |     1 |    10 |  6841   (1)| 00:01:23 |

                                    |   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |

                                    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |   245K|  2392K|  6841   (1)| 00:01:23 |

                                    |*  3 |    INDEX RANGE SCAN          | T1_I1 |   245K|       |   552   (1)| 00:00:07 |

                                    --------------------------------------------------------------------------------------

                                     

                                    Predicate Information (identified by operation id):

                                    ---------------------------------------------------

                                       3 - access("ID">=1 AND "ID"<=245000)

                                     

                                    That's 24.5% of the table - and the optimizer knows it, and it's still an index range scan.

                                     

                                    Regards

                                    Jonathan Lewis

                                    Jonathan,

                                     

                                    I did not see your response to the other question in the other post.  There was one question where a lot of replies seemed to disappear.  Maybe the OP deleted some or maybe OTN had a problem? Otherwise I would have responded to that earlier post if I had seen it.

                                     

                                    I agree that my model is simplistic, but I don't think it is wrong for the majority of cases.  I made it clear in my blog post what my assumptions were, and I was clear about Clustering Factor being part of the cost model for index access.  I also posted all the SQL statements used in my testing so that other people could replicate them or do variations if desired.

                                     

                                    That said, it cuts both ways.  While my model may well work in many cases, it will not work in all cases, as in your example, and also that from John Watson.  That is always a danger of simplification and rules of thumb - while it can work in many cases it may not work in some cases.  Unfortunately not everybody who posts a question on OTN is willing to put in the effort to read up on and understand how Oracle and the Optimizer actually works, so sometimes simplified explanations are the only way to get across to these people what is happening to their query execution.

                                     

                                    The main emphasis of the point I was trying to make was that when the Optimizer is seen to be choosing a full table scan it is probably the right thing and not the wrong thing.  There is often a kind of shock from people when the Optimizer chooses a full table scan, as if it is the last thing it should be doing.  I was trying to point out that if the volume of data being requested is high enough, subject to various factors, then a full table scan will be cheaper than other access methods such as using an index, making a FTS the right thing.  And if someone is seeing a FTS in an execution plan then they should be checking the volume of data the query is requesting before making claims that the Optimizer is doing the wrong thing.  I was not trying to give an absolute cutover point, but to show how low it can be, in what I consider to be normal circumstances.

                                     

                                    I accept that "as low as 0.5%" is not always true, and I'll stop using that phrase in future.  What about "as low as 0.5% if the Clustering Factor value is nearly the same as the number of rows in the table"?  Either way, I'll refrain from using phrases like that.  I feel another blog post coming on to point out how the assumptions used in my model can sometimes not apply, and the results can be very different.  It is never simple with Oracle, is it? 

                                     

                                    John Brady

                                    1 2 Previous Next