8 Replies Latest reply on Dec 15, 2018 3:45 PM by 3204324

    Index full scan cardinaliry misestimates for the Plan

    3204324

      Hi

       

      Below is the query  and the database is 12.1

       

      var SYS_B_00 varchar2(32);

      var SYS_B_01 varchar2(32);

      var SYS_B_02 varchar2(32);

      var SYS_B_03 varchar2(32);

      var SYS_B_04 varchar2(32);

      var SYS_B_05 varchar2(32);

      var SYS_B_06 number;

      var SYS_B_07 varchar2(32);

      var SYS_B_08 varchar2(32);

      var SYS_B_09 varchar2(32);

      var SYS_B_10 number;

      var SYS_B_11 number;

      var SYS_B_12 varchar2(32);

      exec :SYS_B_00:='01/';

      exec :SYS_B_01:='/';

      exec :SYS_B_02:='dd/MM/yyyy';

      exec :SYS_B_03:='10/04/2018';

      exec :SYS_B_04:='MM/dd/yyyy';

      exec :SYS_B_05:='q';

      exec :SYS_B_06:=12;

      exec :SYS_B_07:='10/04/2018';

      exec :SYS_B_08:='MM/dd/yyyy';

      exec :SYS_B_09:='q';

      exec :SYS_B_10:=1;

      exec :SYS_B_11:=1;

      exec :SYS_B_12:='PRIMARY';

      WITH household_ids

           AS ( SELECT DISTINCT household_id

                FROM   ( SELECT household_id,

                                To_date( :"SYS_B_00"

                                         || month

                                         || :"SYS_B_01"

                                         || year, :"SYS_B_02" ) AS Quarter_Date

                         FROM   actprof.IMPL_HOUSEHOLD_GDC )

                WHERE  quarter_date BETWEEN Add_months( Trunc( To_date( :"SYS_B_03", :"SYS_B_04" ), :"SYS_B_05" ), -:"SYS_B_06" )

        AND ( Trunc( To_date( :"SYS_B_07", :"SYS_B_08" ), :"SYS_B_09" ) - :"SYS_B_10" ) )

      SELECT DISTINCT hh.master_id,

                      hh.household_id,

                      hh.account_number,

                      r.ssntin

      FROM   ( SELECT rltn.master_id,

                      rltn.household_id,

                      rltn.account_number

               FROM   actprof.IMPL_ADV_HOUSEHOLD_ACCT_RELTN rltn

                      inner join ( SELECT DISTINCT master_id,

                                                   household_id

                                   FROM   ( SELECT reltn.master_id,

                                                   reltn.household_id,

                                                   reltn.account_number,

                                                   Rank( )

                                                     over (

                                                       PARTITION BY reltn.master_id, reltn.household_id

                                                       ORDER BY reltn.account_number) RANK

                                            FROM   actprof.IMPL_ADV_HOUSEHOLD_ACCT_RELTN reltn

                                                   inner join household_ids hi

                                                           ON reltn.household_id = hi.household_id )

                                   WHERE  rank > :"SYS_B_11" ) r

                              ON rltn.master_id = r.master_id AND

                                 rltn.household_id = r.household_id ) hh

             inner join actprof.IMPL_LPL_BETA_CUST_RLTN r

                     ON hh.master_id = r.master_id AND

                        hh.account_number = r.accountno AND

                        r.relationshiptype = :"SYS_B_12"

      ORDER  BY hh.master_id,hh.household_id,hh.account_number

      /

       

      Global Stats

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

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

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

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

      |     320 |      76 |      140 |       39 |       66 |     8M | 257K |   2GB |  1528 | 306MB |

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

       

       

      SQL Plan Monitoring Details (Plan Hash Value=3210215320)

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

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

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

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

      |  -> 0 | SELECT STATEMENT                |                         |         |       |       180 |   +142 |     1 |        0 |       |       |       |       |      |      |          |                             |          |

      |  -> 1 |   SORT UNIQUE                   |                         |    1093 | 52574 |       180 |   +142 |     1 |        0 |       |       |   534 | 107MB |   2M | 113M |     0.94 | Cpu (3)                     |          |

      |  -> 2 |    NESTED LOOPS                 |                         |    1093 | 52573 |       180 |   +142 |     1 |       3M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |

      |  -> 3 |     NESTED LOOPS                |                         |    1118 | 52573 |       180 |   +142 |     1 |       3M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |

      |  -> 4 |      HASH JOIN RIGHT SEMI       |                         |    1118 | 52238 |       189 |   +133 |     1 |       3M |       |       |       |       | 153M |      |     1.57 | Cpu (5)                     |          |

      |     5 |       VIEW                      |                         |    157K | 31145 |         9 |   +134 |     1 |       2M |       |       |       |       |      |      |          |                             |          |

      |     6 |        WINDOW SORT              |                         |    157K | 31145 |        57 |    +86 |     1 |       4M |  3777 | 199MB |   994 | 199MB |      |      |     3.14 | Cpu (5)                     |     100% |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | direct path read temp (5)   |          |

      |     7 |         HASH JOIN               |                         |    157K | 29653 |        50 |    +85 |     1 |       4M |       |       |       |       |      |      |     1.26 | Cpu (4)                     |          |

      |     8 |          VIEW                   |                         |   81771 | 23273 |         1 |    +86 |     1 |       1M |       |       |       |       |      |      |          |                             |          |

      |     9 |           HASH UNIQUE           |                         |   81771 | 23273 |        75 |    +12 |     1 |       1M |       |       |       |       |      |      |     1.89 | Cpu (6)                     |          |

      |    10 |            FILTER               |                         |         |       |        78 |     +9 |     1 |      11M |       |       |       |       |      |      |     0.31 | Cpu (1)                     |          |

      |    11 |             INDEX FULL SCAN     | PK_HOUSEHOLD_GDC        |   83917 | 22799 |        86 |     +1 |     1 |      11M |     9 | 73728 |       |       |      |      |    24.21 | Cpu (77)                    |          |

      |    12 |          INDEX FULL SCAN        | PK_ADV_HOUSEHOLD_ACCT   |      8M |  6332 |        49 |    +86 |     1 |       8M |       |       |       |       |      |      |    12.58 | gc cr block 2-way (37)      |          |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | gc current block 2-way (3)  |          |

      | -> 13 |       INDEX FULL SCAN           | PK_ADV_HOUSEHOLD_ACCT   |      8M |  6332 |       180 |   +142 |     1 |       7M |       |       |       |       |      |      |     0.63 | Cpu (2)                     |          |

      | -> 14 |      INDEX RANGE SCAN           | IDX4_LPL_BETA_CUST_RLTN |       1 |     1 |       181 |   +141 |    3M |       3M | 75759 | 592MB |       |       |      |      |    23.27 | gc current grant 2-way (1)  |          |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | Cpu (21)                    |          |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file parallel read (52)  |          |

      | -> 15 |     TABLE ACCESS BY INDEX ROWID | IMPL_LPL_BETA_CUST_RLTN |       1 |     1 |       180 |   +142 |    3M |       3M |  177K |   1GB |       |       |      |      |    29.56 | Cpu (12)                    |          |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file parallel read (81)  |          |

      |       |                                 |                         |         |       |           |        |       |          |       |       |       |       |      |      |          | db file sequential read (1) |          |

       

      On plan line id 11--Index full scan estimating 83k but it is getting 11M rows .

       

      Below is the Plan

       

      Plan hash value: 3210215320

       

       

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

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

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

      |   0 | SELECT STATEMENT              |                         |       |       |       | 52576 (100)|          |

      |   1 |  SORT UNIQUE                  |                         |  1093 | 95091 |       | 52574   (2)| 00:00:03 |

      |   2 |   NESTED LOOPS                |                         |  1093 | 95091 |       | 52573   (2)| 00:00:03 |

      |   3 |    NESTED LOOPS               |                         |  1118 | 95091 |       | 52573   (2)| 00:00:03 |

      |*  4 |     HASH JOIN RIGHT SEMI      |                         |  1118 | 60372 |  6288K| 52238   (2)| 00:00:03 |

      |*  5 |      VIEW                     |                         |   156K|  4445K|       | 31145   (3)| 00:00:02 |

      |   6 |       WINDOW SORT             |                         |   156K|  5518K|  7400K| 31145   (3)| 00:00:02 |

      |*  7 |        HASH JOIN              |                         |   156K|  5518K|       | 29653   (3)| 00:00:02 |

      |   8 |         VIEW                  |                         | 81771 |   878K|       | 23273   (3)| 00:00:01 |

      |   9 |          HASH UNIQUE          |                         | 81771 |  1437K|  2320K| 23273   (3)| 00:00:01 |

      |* 10 |           FILTER              |                         |       |       |       |            |          |

      |* 11 |            INDEX FULL SCAN    | PK_HOUSEHOLD_GDC        | 83917 |  1475K|       | 22799   (3)| 00:00:01 |

      |  12 |         INDEX FULL SCAN       | PK_ADV_HOUSEHOLD_ACCT   |  8207K|   195M|       |  6332   (1)| 00:00:01 |

      |  13 |      INDEX FULL SCAN          | PK_ADV_HOUSEHOLD_ACCT   |  8207K|   195M|       |  6332   (1)| 00:00:01 |

      |* 14 |     INDEX RANGE SCAN          | IDX4_LPL_BETA_CUST_RLTN |     1 |       |       |     1   (0)| 00:00:01 |

      |* 15 |    TABLE ACCESS BY INDEX ROWID| IMPL_LPL_BETA_CUST_RLTN |     1 |    33 |       |     1   (0)| 00:00:01 |

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

       

       

      Predicate Information (identified by operation id):

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

       

       

         4 - access("RLTN"."MASTER_ID"="MASTER_ID" AND "RLTN"."HOUSEHOLD_ID"="HOUSEHOLD_ID")

         5 - filter("RANK">:SYS_B_11)

         7 - access("RELTN"."HOUSEHOLD_ID"="HI"."HOUSEHOLD_ID")

        10 - filter(TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10>=ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03

                    ,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)))

        11 - filter((TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)>=ADD_MONTHS(TRU

                    NC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)) AND

                    TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)<=TRUNC(TO_DATE(:SYS_B_07,:SYS_

                    B_08),:SYS_B_09)-:SYS_B_10))

        14 - access("RLTN"."ACCOUNT_NUMBER"="R"."ACCOUNTNO" AND "R"."RELATIONSHIPTYPE"=:SYS_B_12)

        15 - filter("RLTN"."MASTER_ID"="R"."MASTER_ID")

       

       

      Below is the DDL for the index

       

      CREATE UNIQUE INDEX "ACTPROF"."PK_HOUSEHOLD_GDC" ON "ACTPROF"."IMPL_HOUSEHOLD_GDC" ("MASTER_ID", "HOUSEHOLD_ID", "YEAR", "MONTH")

        PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

        STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

        TABLESPACE "ACTSTG_DATA"

       

      column stats on the table "IMPL_HOUSEHOLD_GDC"

       

      COLUMN_NAME                    DATA_TYPE       NUM_DISTINCT  DENSITY  NUM_NULLS LAST_ANALYZED       HISTOGRAM

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

      BATCH_ID                       NUMBER                     1        1          0 2018-12-02 09:04:55 NONE

      QUARTER                        NUMBER                     4        0          0 2018-12-02 09:04:55 FREQUENCY

      YEAR                           NUMBER                     5        0          0 2018-12-02 13:19:10 FREQUENCY

      MONTH                          NUMBER                    12        0          0 2018-12-02 13:19:10 FREQUENCY

      HOUSEHOLD_MAX_AGE              NUMBER                   120        0     599259 2018-12-02 09:04:55 NONE

      NO_OF_TRADES                   NUMBER                   583        0          0 2018-12-02 09:04:55 NONE

      MASTER_ID                      VARCHAR2               18646        0          0 2018-12-02 13:19:10 HEIGHT BALANCED

      GDC                            NUMBER                521984        0          0 2018-12-02 09:04:55 NONE

      HOUSEHOLD_ID                   VARCHAR2             1539936        0          0 2018-12-02 13:19:10 HEIGHT BALANCED

        • 1. Re: Index full scan cardinaliry misestimates for the Plan
          John Thorton

          Thanks for sharing.

           

          What exactly do you expect or desire from here; since we can not change how Oracle behaves for you & your DB?

           

          Please click on URL below & respond accordingly

          1. Where can I find Oracle Documentation?

          2. How do I ask a question on the forums?
          3. How to  improve the performance of my query? / My query is running slow.


          • 2. Re: Index full scan cardinaliry misestimates for the Plan
            3204324

            You are welcome

             

            I expect some one else to offer help since all you do is post the same response to most of the questions  Forums

             

             

            PLEASE STOP SPAMMING

            • 3. Re: Index full scan cardinaliry misestimates for the Plan
              AndrewSayer

              The filter for that line is:

              11 - filter((TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)>=ADD_MONTHS(TRU

                            NC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)) AND

                            TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)<=TRUNC(TO_DATE(:SYS_B_07,:SYS_

                            B_08),:SYS_B_09)-:SYS_B_10))

               

              That's tricky for the CBO to do anything with for obvious reasons. You should keep your filters simple, having to apply functions to data in the table and then compare that to other expressions means that the CBO ends up having to use bad hardcoded selectivity estimates (I think it's 5% for a range filter on an expression but don't quote me on that).

               

              For this particular SQL, I don't think line 11 is a huge mistake, even if it had a higher estimate, you're not going to see a different plan - you're already getting bulky hash joins. I believe the real problem starts occurring from line 4 - that semi join is estimated at producing 1118 rows when it's really 3M which then drives a nested loop which is responsible for over 50% of the activity.

               

              Line 4 is joining on

              4 - access("RLTN"."MASTER_ID"="MASTER_ID" AND "RLTN"."HOUSEHOLD_ID"="HOUSEHOLD_ID")

              Which probably has bad estimates as you have height balanced histograms on at least IMPL_HOUSEHOLD_GDC.HOUSEHOLD_ID and IMPL_HOUSEHOLD_GDC.MASTER_ID. Do you really want these? They're very rarely a good idea.

               

              I would suggest you investigate having normal statistics on these columns (no histogram) and seeing if that gives you a better cardinality and better plan.

              Alternatively, you can use an increased degree of dynamic sampling so that Oracle looks at your actual data when creating the execution plan.

               

               

              I'll note that your bind variables are the result of using cursor_sharing=force.

               

              • 4. Re: Index full scan cardinaliry misestimates for the Plan
                3204324

                Line 4 is joining on

                4 - access("RLTN"."MASTER_ID"="MASTER_ID" AND "RLTN"."HOUSEHOLD_ID"="HOUSEHOLD_ID")

                Which probably has bad estimates as you have height balanced histograms on at least IMPL_HOUSEHOLD_GDC.HOUSEHOLD_ID and IMPL_HOUSEHOLD_GDC.MASTER_ID. Do you really want these? They're very rarely a good idea

                 

                Hi Andrew

                 

                We dont actually have the histograms on the columns MASTER_ID and HOUSEHOLD_ID columns on the table

                 

                COLUMN_NAME                    DATA_TYPE       NUM_DISTINCT  DENSITY  NUM_NULLS LAST_ANALYZED       HISTOGRAM

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

                BATCH_ID                       NUMBER                     1        0          0 2018-12-05 01:06:47 FREQUENCY

                MASTER_ID                      VARCHAR2               29826        0          0 2018-12-05 01:06:47 NONE

                HOUSEHOLD_ID                   VARCHAR2             4276736        0          0 2018-12-05 01:06:47 NONE

                ACCOUNT_NUMBER                 VARCHAR2             8195072        0          0 2018-12-05 01:06:47 NONE

                 

                 

                 

                • 5. Re: Index full scan cardinaliry misestimates for the Plan
                  Jonathan Lewis

                  Three points to consider:

                   

                  First, it's odd that you see "index full scan" when Oracle is estimating millions of rows from the index and no table access - I would have expected "index fast full scan". Have you set some index-related optimizer parameters in a way that fools the optimizer, e.g. optimizer_index_cost_adj ?  It would help if you supplied the "OUTLINE" section of the exection plan.

                   

                  Second: the complicated expression to convert year/month into things that look like dates and then apply the filter predicate at operation 11 is (as Andrew indicated) using a standard optimizer guess - it's actually 0.25% (5% of 5%) because you have two comparisons in the compound predicate.  You might be better off if you created a virtual column that modelled that conversion of the two columns, then you could gather stats on the virtual date and that would (in general) help the optimizer. Because the estimate is so low the optimizer thinks it's a good idea to use the result set of operation 11 as the build table of a hash join when it might be better are the probe table; more importantly it thinks the join result will be in the 10's of thousands so does a nested loop join as the next step when a hash join mighe be better (and possibly the join order should be different anyway). Since you're on 12c you can declare the virtual column to be invisible so that it doesn't cause problems with code that does inserts to the table without using a values() clause - you can, of course, also index the virtual column if it helps, but it looks like you're trying to access about 30% of the total data set so you probably don't want to use an index.

                   

                   

                  Finally, you're trying to crunch millions of rows - the result set before the sort unique is 3M rows: when operating at scale you don't really want to make like difficult for the optimizer by enabling cursor sharing.  If it's set at the session level or system level and you can't do an "alter session" to modify it then you could consider adding a /*+ cursor_sharing_exact */ hint to the text.   (The combination of a virtual column - with stats gathered - and a visible literal for the date range should make it possible for the optimizer to get a good estimate of the rows at operation 11.)

                   

                   

                   

                  It looks (from the RANK > 1 predicate) as if you're trying to do some sort of duplicate listing. If that's the case then you might want to see if you can think of a completely different strategy rather than using analytic functions: they can be very effective, sometimes they increase the workload rather than decreasing it. I haven't tried to examine the detail of your code, though, so the suggestion may be worthless.

                   

                  Regards

                  Jonathan Lewis

                   

                   

                  P.S.  In passing - you did have histograms on the columns Andrew mentioned when you did the original posting - but you've gathered new stats since then.

                  1 person found this helpful
                  • 6. Re: Index full scan cardinaliry misestimates for the Plan
                    3204324

                    Thank you Jonathon

                     

                    We have a weird setting with the optimizer_index_cost_adj parameter(Which we didnt know before)

                     

                    in container it is set to 10 and in pluggable database it is set to 100 and we only have one pluggable database .

                     

                    SQL> sho parameter optimizer_index_cost_adj

                     

                    optimizer_index_cost_adj             integer     10

                     

                    SQL> alter session set container=pdb1;

                    Session altered.

                     

                    SQL> sho parameter optimizer_index_cost_adj

                     

                    optimizer_index_cost_adj             integer     100

                     

                    i have set this parameter to 100 at session level and now oracle is doing the Full table scan

                     

                    hdhulip@cdb01prdx1:ACTPRD>@x9all

                     

                     

                    PLAN_TABLE_OUTPUT

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

                    Plan hash value: 116020737

                     

                     

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

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

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

                    |   0 | SELECT STATEMENT              |                               |     1 |    87 |       | 99678   (6)| 00:00:04 |

                    |   1 |  SORT UNIQUE                  |                               |     1 |    87 |       | 99677   (6)| 00:00:04 |

                    |   2 |   NESTED LOOPS                |                               |     1 |    87 |       | 99676   (6)| 00:00:04 |

                    |   3 |    NESTED LOOPS               |                               |     2 |    87 |       | 99676   (6)| 00:00:04 |

                    |*  4 |     HASH JOIN RIGHT SEMI      |                               |     2 |   108 |  6472K| 99670   (6)| 00:00:04 |

                    |*  5 |      VIEW                     |                               |   161K|  4577K|       | 74383   (8)| 00:00:03 |

                    |   6 |       WINDOW SORT             |                               |   161K|  5682K|  7616K| 74383   (8)| 00:00:03 |

                    |*  7 |        HASH JOIN              |                               |   161K|  5682K|       | 72845   (8)| 00:00:03 |

                    |   8 |         VIEW                  |                               | 84213 |   904K|       | 62279   (9)| 00:00:03 |

                    |   9 |          HASH UNIQUE          |                               | 84213 |  1480K|  2384K| 62279   (9)| 00:00:03 |

                    |* 10 |           FILTER              |                               |       |       |       |            |          |

                    |* 11 |            TABLE ACCESS FULL  | IMPL_HOUSEHOLD_GDC            | 86287 |  1516K|       | 61790   (9)| 00:00:03 |

                    |  12 |         TABLE ACCESS FULL     | IMPL_ADV_HOUSEHOLD_ACCT_RELTN |  8208K|   195M|       | 10517   (2)| 00:00:01 |

                    |  13 |      TABLE ACCESS FULL        | IMPL_ADV_HOUSEHOLD_ACCT_RELTN |  8208K|   195M|       | 10517   (2)| 00:00:01 |

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

                    |* 15 |    TABLE ACCESS BY INDEX ROWID| IMPL_LPL_BETA_CUST_RLTN       |     1 |    33 |       |     3   (0)| 00:00:01 |

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

                     

                     

                    Query Block Name / Object Alias (identified by operation id):

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

                     

                     

                       1 - SEL$4DF5D030

                       5 - SEL$9834E3F4 / from$_subquery$_006@SEL$3

                       6 - SEL$9834E3F4

                       8 - SEL$8A3193DA / HI@SEL$4

                       9 - SEL$8A3193DA

                      11 - SEL$8A3193DA / IMPL_HOUSEHOLD_GDC@SEL$8

                      12 - SEL$9834E3F4 / RELTN@SEL$4

                      13 - SEL$4DF5D030 / RLTN@SEL$2

                      14 - SEL$4DF5D030 / R@SEL$1

                      15 - SEL$4DF5D030 / R@SEL$1

                     

                     

                    Outline Data

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

                     

                     

                      /*+

                          BEGIN_OUTLINE_DATA

                          USE_HASH_AGGREGATION(@"SEL$8A3193DA")

                          FULL(@"SEL$8A3193DA" "IMPL_HOUSEHOLD_GDC"@"SEL$8")

                          USE_HASH(@"SEL$9834E3F4" "RELTN"@"SEL$4")

                          LEADING(@"SEL$9834E3F4" "HI"@"SEL$4" "RELTN"@"SEL$4")

                          FULL(@"SEL$9834E3F4" "RELTN"@"SEL$4")

                          NO_ACCESS(@"SEL$9834E3F4" "HI"@"SEL$4")

                          PARTIAL_JOIN(@"SEL$4DF5D030" "from$_subquery$_006"@"SEL$3")

                          SWAP_JOIN_INPUTS(@"SEL$4DF5D030" "from$_subquery$_006"@"SEL$3")

                          NLJ_BATCHING(@"SEL$4DF5D030" "R"@"SEL$1")

                          USE_NL(@"SEL$4DF5D030" "R"@"SEL$1")

                          USE_HASH(@"SEL$4DF5D030" "from$_subquery$_006"@"SEL$3")

                          LEADING(@"SEL$4DF5D030" "RLTN"@"SEL$2" "from$_subquery$_006"@"SEL$3" "R"@"SEL$1")

                          INDEX(@"SEL$4DF5D030" "R"@"SEL$1" ("IMPL_LPL_BETA_CUST_RLTN"."ACCOUNTNO"

                                  "IMPL_LPL_BETA_CUST_RLTN"."RELATIONSHIPTYPE"))

                          NO_ACCESS(@"SEL$4DF5D030" "from$_subquery$_006"@"SEL$3")

                          FULL(@"SEL$4DF5D030" "RLTN"@"SEL$2")

                          OUTLINE(@"SEL$3")

                          OUTLINE(@"SEL$2")

                          MERGE(@"SEL$3")

                          OUTLINE(@"SEL$335DD26A")

                          OUTLINE(@"SEL$6")

                          MERGE(@"SEL$335DD26A")

                          OUTLINE(@"SEL$4862017B")

                          OUTLINE(@"SEL$1")

                          MERGE(@"SEL$4862017B")

                          OUTLINE(@"SEL$8AB48FED")

                          OUTLINE(@"SEL$9")

                          OUTLINE(@"SEL$4")

                          OUTLINE(@"SEL$5")

                          OUTLINE(@"SEL$8")

                          OUTLINE(@"SEL$7")

                          MERGE(@"SEL$8AB48FED")

                          OUTLINE_LEAF(@"SEL$4DF5D030")

                          MERGE(@"SEL$4")

                          OUTLINE_LEAF(@"SEL$9834E3F4")

                          MERGE(@"SEL$8")

                          OUTLINE_LEAF(@"SEL$8A3193DA")

                          ALL_ROWS

                          OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')

                          OPT_PARAM('_optimizer_gather_feedback' 'false')

                          OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')

                          OPT_PARAM('_px_adaptive_dist_method' 'off')

                          OPT_PARAM('_optimizer_use_feedback' 'false')

                          OPT_PARAM('optimizer_dynamic_sampling' 11)

                          DB_VERSION('12.1.0.2')

                          OPTIMIZER_FEATURES_ENABLE('12.1.0.2')

                          IGNORE_OPTIM_EMBEDDED_HINTS

                          END_OUTLINE_DATA

                      */

                     

                     

                    Predicate Information (identified by operation id):

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

                     

                     

                       4 - access("RLTN"."MASTER_ID"="MASTER_ID" AND "RLTN"."HOUSEHOLD_ID"="HOUSEHOLD_ID")

                       5 - filter("RANK">TO_NUMBER(:SYS_B_11))

                       7 - access("RELTN"."HOUSEHOLD_ID"="HI"."HOUSEHOLD_ID")

                      10 - filter(TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10>=ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03,:SYS_

                                  B_04),:SYS_B_05),(-TO_NUMBER(:SYS_B_06))))

                      11 - filter(TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)<=TRUNC(TO_DATE(:SYS_B_

                                  07,:SYS_B_08),:SYS_B_09)-:SYS_B_10 AND TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_0

                                  2)>=ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-TO_NUMBER(:SYS_B_06))))

                      14 - access("RLTN"."ACCOUNT_NUMBER"="R"."ACCOUNTNO" AND "R"."RELATIONSHIPTYPE"=:SYS_B_12)

                      15 - filter("RLTN"."MASTER_ID"="R"."MASTER_ID")

                     

                     

                    Column Projection Information (identified by operation id):

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

                     

                     

                       1 - (#keys=4) "RLTN"."MASTER_ID"[VARCHAR2,50], "RLTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RLTN"."ACCOUNT_NUMBER"[VARCHAR2,50], "R"."SSNTIN"[CHARACTER,9]

                       2 - (#keys=0) "RLTN"."MASTER_ID"[VARCHAR2,50], "RLTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RLTN"."ACCOUNT_NUMBER"[VARCHAR2,50], "R"."SSNTIN"[CHARACTER,9]

                       3 - (#keys=0) "RLTN"."MASTER_ID"[VARCHAR2,50], "RLTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RLTN"."ACCOUNT_NUMBER"[VARCHAR2,50], "R".ROWID[ROWID,10]

                       4 - (#keys=2) "RLTN"."MASTER_ID"[VARCHAR2,50], "RLTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RLTN"."ACCOUNT_NUMBER"[VARCHAR2,50]

                       5 - "MASTER_ID"[VARCHAR2,50], "HOUSEHOLD_ID"[VARCHAR2,50], "RANK"[NUMBER,22]

                       6 - (#keys=3) "RELTN"."MASTER_ID"[VARCHAR2,50], "RELTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RELTN"."ACCOUNT_NUMBER"[VARCHAR2,50], "HI"."HOUSEHOLD_ID"[VARCHAR2,50], RANK() OVER ( PARTITION BY

                           "RELTN"."MASTER_ID","RELTN"."HOUSEHOLD_ID" ORDER BY "RELTN"."ACCOUNT_NUMBER")[22]

                       7 - (#keys=1) "HI"."HOUSEHOLD_ID"[VARCHAR2,50], "RELTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RELTN"."MASTER_ID"[VARCHAR2,50], "RELTN"."ACCOUNT_NUMBER"[VARCHAR2,50]

                       8 - "HI"."HOUSEHOLD_ID"[VARCHAR2,50]

                       9 - (#keys=1) "HOUSEHOLD_ID"[VARCHAR2,50]

                      10 - "HOUSEHOLD_ID"[VARCHAR2,50]

                      11 - "HOUSEHOLD_ID"[VARCHAR2,50]

                      12 - (rowset=200) "RELTN"."MASTER_ID"[VARCHAR2,50], "RELTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RELTN"."ACCOUNT_NUMBER"[VARCHAR2,50]

                      13 - "RLTN"."MASTER_ID"[VARCHAR2,50], "RLTN"."HOUSEHOLD_ID"[VARCHAR2,50],

                           "RLTN"."ACCOUNT_NUMBER"[VARCHAR2,50]

                      14 - "R".ROWID[ROWID,10]

                      15 - "R"."SSNTIN"[CHARACTER,9]

                     

                     

                    133 rows selected.

                    • 7. Re: Index full scan cardinaliry misestimates for the Plan
                      Jonathan Lewis

                      I see rp0428 has managed to delete his embarrassingly erroneous reply. I wasn't aware that it was possible for an ordinary user to delete their posts, but this isn't the first time he's managed to do it in the last couple of weeks.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: Index full scan cardinaliry misestimates for the Plan
                        3204324

                        Yup ----its frustrating Even though I posted most of the information that is required(which you told the same in your blog posting) --with out reading the content some one(john) told me to read some manual on how to ask questions in forum's) and I don't recall the rp's reply ---I don't understand why they do this on many posts ---

                         

                        Thank you Jonathon,Andrew and others  for your help on this