1 2 Previous Next 22 Replies Latest reply on Sep 1, 2016 2:38 PM by 2768805

    index full scan and no parallel degree when running stats collection on application table

    2768805

      Hi Friends,

      I am facing a wierd problem.

      We are running stats collection by using below command:

       

      BEGIN dbms_stats.gather_table_stats(ownname => 'SA', tabname => 'MTM_SITE_PART76_X_NEED3', degree => 10, cascade => TRUE,ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1') ; END;

       

      It is calling below sql , which you can see is doing index full scan on K15_76_2000_3 . Problem is it is running for hours without finishing.

       

      SQL_ID  c9j2kcmpd6jtt, child number 0

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

      select /*+  parallel(t,10) parallel_index(t,10) dbms_stats

      cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)

      no_monitoring no_substrb_pad  */count(*), count(distinct

      "X_SITE_PART2X_NEED"), sum(sys_op_opnsize("X_SITE_PART2X_NEED")),

      substrb(dump(min("X_SITE_PART2X_NEED"),16,0,32),1,120),

      substrb(dump(max("X_SITE_PART2X_NEED"),16,0,32),1,120), count(distinct

      "X_NEED2X_SITE_PART"), sum(sys_op_opnsize("X_NEED2X_SITE_PART")),

      substrb(dump(min("X_NEED2X_SITE_PART"),16,0,32),1,120),

      substrb(dump(max("X_NEED2X_SITE_PART"),16,0,32),1,120) from

      "SA"."MTM_SITE_PART76_X_NEED3" sample (  5.0000000000)  t

       

      Plan hash value: 2842455981

       

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

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

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

      |   0 | SELECT STATEMENT |               |       |       | 52251 (100)|          |

      |   1 |  SORT GROUP BY   |               |     1 |    14 |            |          |

      |*  2 |   INDEX FULL SCAN| K15_76_2000_3 |    44M|   595M| 52251   (1)| 00:10:28 |

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

       

      Predicate Information (identified by operation id):

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

       

         2 - filter(ORA_HASH(ROWID,0,1065600607,'SYS_SAMPLE',0)<214748365)

       

      Below is the output from

       

      Global Information

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

      Status              :  EXECUTING

      Instance ID         :  1

      Session             :  SA (20065:9591)

      SQL ID              :  c9j2kcmpd6jtt

      SQL Execution ID    :  16777216

      Execution Started   :  08/30/2016 05:05:46

      First Refresh Time  :  08/30/2016 05:05:52

      Last Refresh Time   :  08/30/2016 13:40:10

      Duration            :  30865s

      Module/Action       :  SQL*Plus/-

      Service             :  p2clf1d1

      Program             :  sqlplus@ylpi043 (TNS V1-V3)

       

      Global Stats

      =================================================================================================

      | Elapsed |   Cpu   |    IO    | Concurrency | Cluster  | Buffer | Read | Read  | Write | Write |

      | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes |

      =================================================================================================

      |   31136 |    1246 |    29506 |        0.00 |      384 |     9M |   4M |  31GB |  4464 | 932MB |

      =================================================================================================

       

      SQL Plan Monitoring Details (Plan Hash Value=2842455981)

      ================================================================================================================================================================================================

      | Id   |     Operation      |     Name      |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read | Read  | Write | Write | Mem | Temp | Activity |         Activity Detail         |

      |      |                    |               | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs | Bytes | Reqs  | Bytes |     |      |   (%)    |           (# samples)           |

      ================================================================================================================================================================================================

      |    0 | SELECT STATEMENT   |               |         |       |           |        |     1 |          |      |       |       |       |     |      |          |                                 |

      | -> 1 |   SORT GROUP BY    |               |       1 |       |     30861 |     +6 |     1 |        0 |      |       |  4464 | 932MB | 42M | 978M |     0.99 | Cpu (302)                       |

      | -> 2 |    INDEX FULL SCAN | K15_76_2000_3 |     45M | 52251 |     30866 |     +1 |     1 |      38M |   4M |  31GB |       |       |     |      |    99.00 | gc cr grant 2-way (254)         |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | gc cr grant congested (2)       |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | gc cr request (3)               |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | gc current grant 2-way (136)    |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | gc current grant congested (1)  |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | Cpu (656)                       |

      |      |                    |               |         |       |           |        |       |          |      |       |       |       |     |      |          | db file sequential read (28969) |

      ================================================================================================================================================================================================

       

       

      My question is:

      1. Why it is picking up "INDEX FULL SCAN" instead of "INDEX FAST FULL SCAN" . It is happening in all the DBs that we have. Problem is it is running in production for past 8 hours?

      2. How to change the plan to "INDEX FAST FULL SCAN" without resorting to outline/baseline/profile?  Are we hitting any bug or limitation which is resulting in oracle taking "INDEX FAST FULL SCAN" for this index.

       

      Details:

      1. DB version: 11.2.0.4.0

      2. index size is 39GB and table size is 35 GB. Table has only 2 columns and index is on both columns( there is a long history behind but i will skip that for now).

      3. This 'INDEX FULL SCAN'  problem is happening for about 80 indexes. there are about 1200 other indexes for which 'INDEX FAST FULL SCAN' is being used.

      One example of such a 'good query'

       

      SQL_ID  ddbmtduxu4v7v, child number 0

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

      select /*+  parallel_index(t, "TABLE_X_NBA_OFFER_4IX",4)  dbms_stats

      cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)

      no_monitoring no_substrb_pad  no_expand

      index_ffs(t,"TABLE_X_NBA_OFFER_4IX") */ count(*) as nrw,count(distinct

      sys_op_lbid(3298701,'L',t.rowid)) as nlb,count(distinct

      "X_NBA_OFFR2X_CMP_SPLT") as ndk,sys_op_countchg(substrb(t.rowid,1,15),1)

      as clf from "SA"."TABLE_X_NBA_OFFER" t where "X_NBA_OFFR2X_CMP_SPLT"

      is not null

       

      Plan hash value: 3936289613

       

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

      | Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

      |   0 | SELECT STATEMENT             |                       |       |       |   144 (100)|          |        |      |            |

      |   1 |  SORT GROUP BY               |                       |     1 |    19 |            |          |        |      |            |

      |   2 |   PX COORDINATOR             |                       |       |       |            |          |        |      |            |

      |   3 |    PX SEND QC (RANDOM)       | :TQ10001              |     1 |    19 |            |          |  Q1,01 | P->S | QC (RAND)  |

      |   4 |     SORT GROUP BY            |                       |     1 |    19 |            |          |  Q1,01 | PCWP |            |

      |   5 |      PX RECEIVE              |                       |     1 |    19 |            |          |  Q1,01 | PCWP |            |

      |   6 |       PX SEND HASH           | :TQ10000              |     1 |    19 |            |          |  Q1,00 | P->P | HASH       |

      |   7 |        SORT GROUP BY         |                       |     1 |    19 |            |          |  Q1,00 | PCWP |            |

      |   8 |         PX BLOCK ITERATOR    |                       |   156K|  2899K|   144   (1)| 00:00:02 |  Q1,00 | PCWC |            |

      |*  9 |          INDEX FAST FULL SCAN| TABLE_X_NBA_OFFER_4IX |   156K|  2899K|   144   (1)| 00:00:02 |  Q1,00 | PCWP |            |

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

       

      Predicate Information (identified by operation id):

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

       

         9 - access(:Z>=:Z AND :Z<=:Z)

             filter("X_NBA_OFFR2X_CMP_SPLT" IS NOT NULL)

       

       

       

      Regds,

      Sachin

        • 1. Re: index full scan and no parallel degree when running stats collection on application table
          Andrew Sayer

          Are you sure you only want stats on columns that have indexes on? Usually they're not the only columns you have filters (or joins) on.

           

          Oracle will automatically gather index statistics when you create the index, has there been enough changes to warrant regathering index statistics?

           

          You may find that a parallel path is chosen when you gather statistics explicitly on the indexes rather than relying on the cascade option. Judging by the reported predicates, I think the FULL scan has something to do with the sample clause, perhaps 100% sampling would do the trick.

          • 2. Re: index full scan and no parallel degree when running stats collection on application table
            2768805

            Hi Andrew,

            Yes we want to stats only on the indexed columns as those are the ones used in most join queries.

            Actually we have seen in past that even slightly stale statistics result in bad plans , so we gather the stats on a regular basis(once every  few weeks).

             

            You may find that a parallel path is chosen when you gather statistics explicitly on the indexes rather than relying on the cascade option.

            One question. How does Oracle decide which index to select for 'INDEX FULL SCAN' and which index for 'INDEX FAST FULL SCAN' . I didnt notice any specific pattern in our application.

            • 3. Re: index full scan and no parallel degree when running stats collection on application table
              Andrew Sayer

              2768805 wrote:

               

              Hi Andrew,

              Yes we want to stats only on the indexed columns as those are the ones used in most join queries.

              Actually we have seen in past that even slightly stale statistics result in bad plans , so we gather the stats on a regular basis(once every few weeks).

               

              You may find that a parallel path is chosen when you gather statistics explicitly on the indexes rather than relying on the cascade option.

              One question. How does Oracle decide which index to select for 'INDEX FULL SCAN' and which index for 'INDEX FAST FULL SCAN' . I didnt notice any specific pattern in our application.

              Slightly stale statistics will have an even worse effect when you don't have statistics on all the columns that get used. Have you considered letting the automatic overnight job just take care of these for you? Tim Hall's put together a helpful post about it https://oracle-base.com/articles/11g/automated-database-maintenance-task-management-11gr1

               

              Everything is cost based, Uncle CBO would have calculated the cost of full scanning an index, the cost of fast full scanning indexes, the cost of sorting certain indexes, etc. If the filter requires an expression to be calculated against the rowids (like what you've got with the sampling clause) then it looks like a full scan needs to be done rather than a fast full scan - it won't cost the fast full scan because it can't be done. It will use the index that it's gathering stats on - it needs to read that index because that's where a lot of the data it needs lives.

              • 4. Re: index full scan and no parallel degree when running stats collection on application table
                Jonathan Lewis

                I think you've been caught by a combination of circumstances.

                 

                First, note that the two pieces of SQL you posted are not similar. The first is the form of statement that Oracle uses to gather table stats, the second is the form it uses to gather index stats - so the latter specifies an index to use and supplies the index_ffs() hint to make sure it is used in the desired way.

                 

                Without running through a few tests it's not possible to give a definition answer to why you got a serial full scan for the first query, but I think it's a costing anomaly combined with your choice for "for all indexed columns" comibined with the 5% sample.

                 

                I see that Oracle had to scan 4M blocks to do the full scan - but its estimated cost was only 52,000 when it should have KNOWN that there were roughly 4M blocks in the index and given the full scan a cost of around 4M. I think it may have decided (using generic code) that the sampling predicate would introduce the standard 1% selectivity and scaled the cost of the full scan down to 1%.

                 

                The 1% factor (probably) won't get into the COSTING calculation for the index_ffs and the tablescan - so parallel 10 isn't likely to be sufficient to reduce the serial cost of the other paths to less than the maladjusted cost of the index full scan.  I have an idea I blogged about this pattern some time ago (or maybe described it in my book) and someone subsequently found a patch for it.

                 

                 

                A couple of side notes:

                If you only collect stats on index columns then the costs of sorting or building hash tables to do sort/merge or hash joins will have to guess about the lengths of the rest of the columns in the query - that's why it was a very bad idea for Oracle to give us the "for all indexed columns" option.

                 

                With 11g you should migrate to auto_sample_size of gathering table stats (especially when you gather stats for all columns -- your 'size 1' is a good idea, by the way, though you may find that you get optimum query performance by following up a gather with creating a very few histograms).

                 

                Oracle still hasn't optimized gathering index stats, so tends to use a relatively small sample size when you cascade from auto_sample_size, but this isn't often a problem, and you may decide to add a little code to gather stats on a few specific indexes with your 5% sample if you spot any problems.

                 

                Regards

                Jonathan Lewis

                 

                 

                 

                 

                 

                 

                Regards

                Jonathan Lewis

                • 5. Re: index full scan and no parallel degree when running stats collection on application table
                  Jonathan Lewis

                  Just a couple of follow-up thoughts:

                   

                  What do your system stats look like ?

                  Have you previously run the calibrate_io procedure ?

                  What are your settings for the optimizer_index_cost_adj and optimizer_index_caching parameters ?

                   

                   

                  It's just occurred to me that the costing anomaly I was thinking of should have been fixed by 11.2.0.4. So, given that your index is larger than your table you should have seen a parallel tablescan as the least cost plan, index_ffs() second, and index full scan third unless there's something a little odd about the index or the factors affecting costing. Given your description it doesn't sound as if the index is likely to be largely empty leaf blocks - which, I think, would allow the full scan to cost lower than the fast full scan in recent versions of Oracle .

                   

                   

                  Regards

                  Jonathan Lewis

                  • 6. Re: index full scan and no parallel degree when running stats collection on application table
                    Jonathan Lewis

                    I've dug up my notes about an oddity with the parallel_index() hint and turned them into a blog: http://jonathanlewis.wordpress.com/2016/08/31/parallel_index-hint/

                    It may be relevant to your example.

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: index full scan and no parallel degree when running stats collection on application table
                      perfdba

                      Thanks Jonathan for the observations. i am reviewing it..

                      • 9. Re: index full scan and no parallel degree when running stats collection on application table
                        Randolf Geist

                        Jonathan Lewis wrote:

                         

                        It may be relevant to your example.

                         

                        Jonathan,

                         

                        since the query in question contains both the PARALLEL and PARALLEL_INDEX hint the costing anomaly demonstrated in your post shouldn't occur - so I'm not sure this is relevant to this case here. On the other hand some anomaly seems to apply anyway, so maybe it's some variation of it.

                         

                        Randolf

                        • 10. Re: index full scan and no parallel degree when running stats collection on application table
                          Jonathan Lewis

                          Randolf,

                           

                          In the previous post I asked about (particularly) the optimizer_index_XXX parameters because an obvious explanation for the anomaly would be that the optimizer_index_cost_adj had been set to a value of about 1. As I pointed out, on the supplied information it looks as if parallel tablescan ought to have been prefered over parallel IFFS, over serial full scan.

                           

                          The reference to the post is there because (a) I had written it and (b) it's still possible that it has some relevance**, and (c) it shows that in at least once case the hints don't behave as expected.

                           

                          Regards

                          Jonathan Lewis

                           

                          ** We've been told that the table is 31G and the index 39G - but we haven't been told the values of BLOCKS and LEAF_BLOCKS.

                          • 11. Re: index full scan and no parallel degree when running stats collection on application table
                            perfdba

                            I think you've been caught by a combination of circumstances.

                             

                            First, note that the two pieces of SQL you posted are not similar. The first is the form of statement that Oracle uses to gather table stats, the second is the form it uses to gather index stats - so the latter specifies an index to use and supplies the index_ffs() hint to make sure it is used in the desired way.

                            [sachin] You are right. i missed to notice it. So my question should have been  "Why isnt it taking parallel full table scan "

                             

                             

                             

                            The 1% factor (probably) won't get into the COSTING calculation for the index_ffs and the tablescan - so parallel 10 isn't likely to be sufficient to reduce the serial cost of the other paths to less than the maladjusted cost of the index full scan.  I have an idea I blogged about this pattern some time ago (or maybe described it in my book) and someone subsequently found a patch for it.

                            [sachin] Ohh , i didnt know that 1% factor wont get into costing calculation for index_ffs & the tablescan.

                             

                            With 11g you should migrate to auto_sample_size of gathering table stats (especially when you gather stats for all columns -- your 'size 1' is a good idea, by the way, though you may find that you get optimum query performance by following up a gather with creating a very few histograms).

                            [sachin] We had histograms in past and because changing data all the time we saw many change of plans(and it was becoming a headache from plan change monitoring perspective ) and so we stopped using them . Because we want stability of execution plans we avoid using histograms and we are doing fine. We never had any issue because of "NO HISTOGRAMS".

                             

                            [sachin] One a side note, below is the plan for different similar queries  as your blog.

                             

                            select /*+ index_ffs(t1 K15_76_2000_3) */ count(9) from MTM_SITE_PART76_X_NEED3 t1;

                             

                            Plan hash value: 1176009669

                             

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

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

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

                            |   0 | SELECT STATEMENT      |               |     1 |  1009K  (1)| 03:21:58 |

                            |   1 |  SORT AGGREGATE       |               |     1 |            |          |

                            |   2 |   INDEX FAST FULL SCAN| K15_76_2000_3 |   891M|  1009K  (1)| 03:21:58 |

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

                             

                            select /*+ parallel_index(t1 K15_76_2000_3 20)   */ count(9) from MTM_SITE_PART76_X_NEED3 t1;

                             

                            Plan hash value: 3996329588

                             

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

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

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

                            |   0 | SELECT STATEMENT |               |     1 | 52251   (1)| 00:10:28 |

                            |   1 |  SORT AGGREGATE  |               |     1 |            |          |

                            |   2 |   INDEX FULL SCAN| K15_76_2000_3 |   891M| 52251   (1)| 00:10:28 |

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

                             

                            select /*+ index_ffs(t1 K15_76_2000_3) parallel_index(t1 K15_76_2000_3 20)   */ count(9) from MTM_SITE_PART76_X_NEED3 t1;

                             

                             

                            Plan hash value: 3996329588

                             

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

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

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

                            |   0 | SELECT STATEMENT |               |     1 | 52251   (1)| 00:10:28 |

                            |   1 |  SORT AGGREGATE  |               |     1 |            |          |

                            |   2 |   INDEX FULL SCAN| K15_76_2000_3 |   891M| 52251   (1)| 00:10:28 |

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

                             

                            select /*+ parallel_index(t1 K15_76_2000_3 20) parallel(t1 10) */ count(9) from MTM_SITE_PART76_X_NEED3 t1;

                             

                            Plan hash value: 1750292194

                             

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

                            | Id  | Operation                 | Name          | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

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

                            |   0 | SELECT STATEMENT          |               |     1 |  1009K  (1)| 03:21:58 |        |      |            |

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

                            |   2 |   PX COORDINATOR          |               |       |            |          |        |      |            |

                            |   3 |    PX SEND QC (RANDOM)    | :TQ10000      |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |

                            |   4 |     SORT AGGREGATE        |               |     1 |            |          |  Q1,00 | PCWP |            |

                            |   5 |      PX BLOCK ITERATOR    |               |   891M|  1009K  (1)| 03:21:58 |  Q1,00 | PCWC |            |

                            |   6 |       INDEX FAST FULL SCAN| K15_76_2000_3 |   891M|  1009K  (1)| 03:21:58 |  Q1,00 | PCWP |            |

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

                             

                             

                            Still checking further on your comments

                             

                            Regds,

                            Sachin

                            • 12. Re: index full scan and no parallel degree when running stats collection on application table
                              Andrew Sayer

                              Don't forget the sample clause to fully replicate what the stats job is doing.

                              from
                              "SA"."MTM_SITE_PART76_X_NEED3" sample (  5.0000000000)

                               

                              (still not 100% sure if it's relevant but if it is it'll be version dependent and I dont have an 11.2.0.4 instance to play on)

                               

                              It will use the index in some way rather than the full table scan because the stats procedure needs to know statistics that can only be found by reading the index (height, leaf blocks .. Possibly a few others)

                              • 13. Re: index full scan and no parallel degree when running stats collection on application table
                                Jonathan Lewis

                                878710 wrote:

                                 

                                The 1% factor (probably) won't get into the COSTING calculation for the index_ffs and the tablescan - so parallel 10 isn't likely to be sufficient to reduce the serial cost of the other paths to less than the maladjusted cost of the index full scan. I have an idea I blogged about this pattern some time ago (or maybe described it in my book) and someone subsequently found a patch for it.

                                [sachin] Ohh , i didnt know that 1% factor wont get into costing calculation for index_ffs & the tablescan.

                                 

                                 

                                 

                                You missed the "I think it may have ... " that started the preceding paragraph. I've done a couple of quick tests and it's not relevant. That's why I've moved on to your configuration - where you may have done something to reduce the index full scan cost dramatically.

                                 

                                Regards

                                Jonathan Lewis

                                • 14. Re: index full scan and no parallel degree when running stats collection on application table
                                  perfdba

                                  Although you said it doenst apply, but still just fyi below information

                                   

                                  What do your system stats look like ?

                                  [sachin]

                                   

                                  ---system statistics.

                                  SQL> SELECT pname, pval1

                                  FROM sys.aux_stats$

                                  WHERE sname = 'SYSSTATS_MAIN';

                                   

                                  PNAME                         |     PVAL1

                                  ------------------------------|----------

                                  CPUSPEED                      |

                                  CPUSPEEDNW                    |3074.07407

                                  IOSEEKTIM                     |        10

                                  IOTFRSPEED                    |      4096

                                  MAXTHR                        |

                                  MBRC                          |

                                  MREADTIM                      |

                                  SLAVETHR                      |

                                  SREADTIM                      |

                                   

                                  Have you previously run the calibrate_io procedure ?

                                   

                                  [sachin] not in this Database i guess. But i have run it in few other databases.

                                   

                                  What are your settings for the optimizer_index_cost_adj and optimizer_index_caching parameters ?

                                   

                                  [sachin]

                                   

                                  NAME                            |TYPE   |VALUE

                                  ------------------------------------|-----------|------------------------------

                                  optimizer_index_caching         |integer|90
                                  optimizer_index_cost_adj        |integer|1
                                  1 2 Previous Next