Forum Stats

  • 3,734,270 Users
  • 2,246,935 Discussions
  • 7,857,216 Comments

Discussions

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

2768805
2768805 Member Posts: 3
edited Sep 1, 2016 10:38AM in General Database Discussions

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             :  [email protected] (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

Tagged:
perfdbaDom Brooks

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Sep 1, 2016 4:08AM Accepted Answer

    NAME                         

    |TYPE |VALUE

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

    optimizer_index_caching       |integer|90
    optimizer_index_cost_adj      |integer|1

    Bingo!  (For those who don't have the relevant 1960's background, that's Englilsh for "I win!"

    With a leaf-block count of 5,219,000 the cost of a full scan would normally be about 5.2M+; with optimizer_index_cost_adj = 1 (%) that drops to about 52,000 (as seen).

    Assuming you've left lots of other parameters to default the cost of the parallel index fast full scan would be roughly (5,200,000  / 8) * (26/12) / (10 * 0.9) = 156,500+

    If you've bumped the db_file_multiblock_read_count up to 128 (which is fairly common still) then it would be roughly (5200000 / 128) * (266/12) /(10 * 0.9) = 100,000+

    Basically your settings for the optimizer_index_xxx parameters invite Oracle to do serial index range/full scans all the time.

    Regards

    Jonathan Lewis

    perfdbaDom Brooks

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 30, 2016 5:51PM

    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.

  • 2768805
    2768805 Member Posts: 3
    edited Aug 30, 2016 5:55PM

    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.

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 30, 2016 6:04PM
    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 4:15AM

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 4:29AM

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 11:49AM

    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

  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 12:04PM

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

  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 12:05PM

    Thanks Andrew. i am reviewing your reply..

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Aug 31, 2016 12:14PM
    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 12:50PM

    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.

  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 1:09PM

    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

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Aug 31, 2016 1:22PM

    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)

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 1:26PM
    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

  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 1:51PM

    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
  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 1:55PM

    Hi Jonathan,

    ** 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.

    For the index below are stats:

    BLOCKS( from dba_segments) - 5088000

    LEAF_BLOCKS(from dba_indexes) - 5219040

    For the table below are the stats:

    BLOCKS(from dba_segments) - 4409600

    BLOCKS(from dba_tables) - 4353190

  • jgarry
    jgarry Member Posts: 13,842
    edited Aug 31, 2016 1:59PM
    Jonathan Lewis wrote: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. F_BLOCKS.

    These are the knobs that try men's souls.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Sep 1, 2016 4:08AM Accepted Answer

    NAME                         

    |TYPE |VALUE

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

    optimizer_index_caching       |integer|90
    optimizer_index_cost_adj      |integer|1

    Bingo!  (For those who don't have the relevant 1960's background, that's Englilsh for "I win!"

    With a leaf-block count of 5,219,000 the cost of a full scan would normally be about 5.2M+; with optimizer_index_cost_adj = 1 (%) that drops to about 52,000 (as seen).

    Assuming you've left lots of other parameters to default the cost of the parallel index fast full scan would be roughly (5,200,000  / 8) * (26/12) / (10 * 0.9) = 156,500+

    If you've bumped the db_file_multiblock_read_count up to 128 (which is fairly common still) then it would be roughly (5200000 / 128) * (266/12) /(10 * 0.9) = 100,000+

    Basically your settings for the optimizer_index_xxx parameters invite Oracle to do serial index range/full scans all the time.

    Regards

    Jonathan Lewis

    perfdbaDom Brooks
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Aug 31, 2016 3:07PM
    Jonathan Lewis wrote:Randolf,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.

    Jonathan,

    apologies, now that I see how my reply could have been read... It wasn't my intention to question the reference to your post, although it looks like that's pretty much what I did resp. wrote. Sometimes I get caught by my attempts of being smart (there is a suitable expression for that but it's not suited for a public forum I guess). Not being a native speaker doesn't make things better.

    Randolf

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Aug 31, 2016 3:31PM

    Randolf,

    There's nothing to apologise for - I thought your point was valid, and one that I could have made clearer in my own comment. It's also useful for those who are prepared to read these threads carefully to see that one expert can pick up and expand on the ideas put forward by another.

    Regards

    Jonathan Lewis

    Dom Brooks
  • perfdba
    perfdba Member Posts: 150
    edited Aug 31, 2016 7:57PM

    Thanks a lot!!! After setting optimizer_index_cost_adj to 100 at the session level for stats collection, the plans are fine now

    Not sure how to make the question answered. anyone can pl suggest?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited Sep 1, 2016 4:17AM

    You seem to be logging in with a different id (unless you've changed your name from one meaningless number to another).

    I assume the id that created a question is the only id that can mark it answered.

    Regards

    Jonathan Lewis

  • 2768805
    2768805 Member Posts: 3
    edited Sep 1, 2016 10:38AM

    Oh, you are right. i was switching between a different id to check progress on one SR. Marking it answered now.

This discussion has been closed.