Dear Tom,

Here pptmizer is not choosing the correct index.
All statistics are upto date.
Looks like we missed histograms or number of bukets are not good enough.

BUSUK(# of records=244558) is more 50% records in the table but still 
it is going for index scan instead of FTS.

Many of the queries use this column in their predicate and we see very poor performance.
Could you please advise us, how to indentify the number of buckets for the given scenario?
Your valuable advise is appreciated as always.


Table Name: PROFILEDUSER

INDEX_NAME        COLUMN_NAME        COLUMN_POSITION
=======================    =====================   =====
PROFILEDUSER_I01    USER_CATEGORY        1
PROFILEDUSER_I02    BUSINESS_COUNTRY_ID    1
PROFILEDUSER_IX01    SEARCH_FIRST_NAME    1
PROFILEDUSER_IX02    SEARCH_LAST_NAME    1
PROFILEDUSER_IX03    SEARCH_COMPANY_NAME    1

PROFILEDUSER_IX04    USERID            1
PROFILEDUSER_IX04    PROFILEDUSERID        2

PROFILEDUSER_FK10    CAMLEVELID        1
PROFILEDUSER_PK        PROFILEDUSERID        1
PROFILEDUSER_AK1    USERID            1
PROFILEDUSER_IX05    REGION            1
PROFILEDUSER_IX06    PROCESSED_BY        1


SQL> select USER_CATEGORY,count(*) from profileduser group by USER_CATEGORY;
 
USER_CATEGORY                         COUNT(*)
---------------------------------------- ----------
BUSCA                                          8420
BUSHK                                         39688
BUSHKHANGSENG                                 12526
BUSSG                                           863
BUSUK                                        244558
BUSUS                                         18657
DOTCOM                                       135216
GIB                                            5188
HIBM                                          12752
                                               4738
                                         ===========
                                         482639
                                         ===========
10 rows selected.

SQL> l
  1  SELECT endpoint_number,endpoint_value,endpoint_actual_value
  2  FROM dba_histograms
  3  WHERE owner='CRMF'
  4  AND          table_name='PROFILEDUSER'
  5* AND column_name='USER_CATEGORY'
SQL> /
 
ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
--------------- -------------- ----------
              0     3.4418E+35
              1     3.7533E+35


SQL> select count(*) from profileduser where USER_CATEGORY='BUSSG';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=682 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'PROFILEDUSER_I01' (NON-UNIQUE) (Cost=682 Card=52745 Bytes=316470)
 
 
 
 
SQL> select count(*) from profileduser where USER_CATEGORY='BUSUK';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=682 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'PROFILEDUSER_I01' (NON-UNIQUE) (Cost=682 Card=52745 Bytes=316470) 

Dear Tom,

Thanks for your continuous help and advise.

Could you please explain how to interpret ENDPOINT_NUMBER and ENDPOINT_VALUE column?

SQL> l
  1  select ENDPOINT_NUMBER,ENDPOINT_VALUE,ENDPOINT_ACTUAL_VALUE
  2  from dba_histograms
  3  where table_name='MY_TAB'
  4* and column_name='COL_NAME'
/

ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
--------------- -------------- -----------------------------
          89443     3.4418E+35 BUSHK 
         109247     3.4418E+35 BUSHKHANGSENG 
         109428     3.4418E+35 BUSSG 
         369305     3.4418E+35 BUSUK
         388535     3.4418E+35 BUSUS 
         617934     3.5469E+35 DOTCOM 
         620589     3.7014E+35 GIB 
         651972     3.7533E+35 HIBM 
            471     3.4418E+35 BUSCA 
 
9 rows selected.

Thanks in advance. 

Dear Tom,

Could you please give some advice on this issue? Thanks for all your help in the past.

Table has upto date statistics. All the predicate columns are indexed.


SQL>select region,count(*) from profileduser group by region;

    REGION   COUNT(*)
---------- ----------
      4047         87
      4048        523
      4049      50627
      4050      29420
      4051       1991
               574073 <== NULL values count
=====================
           656721
=====================

We have non-unique index on region column. The below query works fine and it is doing only 396 LIO.
But if I add REGION predicate to the same query, then it doing  29054 LIO and 19681 PIO.
I understand that the execution plan got changed after adding REGION predicate.

I know somewhere you mentioned that the COST doesn't matter. 

Without REGION predicate, COST=19681 but response time 18secs.
With REGION predicate, COST=2966 but response time 1 Min 25 secs.

My questions are 
1. since Region column has 575K records with NULL values, should I drop the index on that column?
(optimizer is using region index and coming up with the worse plan)

2.The region column has HISTOGRAM too.

3. Is there anything I should do so that optimizer will chose the best plan (less LIO and PIO)


With NO  REGION predicate
==========================

SQL>l
  1  select count(first_name) from profileduser p,extendedattributes e
  2  where p.profileduserid=e.profileduserid
  3  and p.hsbc_user_category in ('VAL1','VAL2')
  4* and e.lastupdatedate >= sysdate -30
SQL> /
 
              102
 
Elapsed: 00:00:18.41
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3287 Card=1 Bytes=90) 
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=3287 Card=1533 Bytes=137970)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EXTENDEDATTRIBUTES' (Cost=221 Card=1533 Bytes=65919) 
   4    3         INDEX (RANGE SCAN) OF 'EXTENDEDATTRIBUTES_IX03' (NON-UNIQUE) (Cost=15 Card=1533) 
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'PROFILEDUSER' (Cost=2 Card=34158 Bytes=1605426) 
   6    5         INDEX (UNIQUE SCAN) OF 'PROFILEDUSER_PK' (UNIQUE) (Cost=1 Card=34158)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        396  consistent gets
          0  physical reads
          0  redo size
        377  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

With REGION predicate
======================

SQL>l
  1  select count(first_name) from profileduser p,extendedattributes e
  2  where p.profileduserid=e.profileduserid
  3  and p.hsbc_user_category in ('VAL1','VAL2')
  4  and e.lastupdatedate >= sysdate -30
  5* and p.region =4049
SQL>  
 
               19
 
Elapsed: 00:01:25.64
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2966 Card=1 Bytes=92) 
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=2966 Card=1533 Bytes=141036)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EXTENDEDATTRIBUTES' (Cost=221 Card=1533 Bytes=65919) 
   4    3         INDEX (RANGE SCAN) OF 'EXTENDEDATTRIBUTES_IX03' (NON-UNIQUE) (Cost=15 Card=1533) 
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'PROFILEDUSER' (Cost=2741 Card=2634 Bytes=129066)
   6    5         INDEX (RANGE SCAN) OF 'PROFILEDUSER_IX05' (NON-UNIQUE) (Cost=378 Card=2634) 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      29054  consistent gets
      19681  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
        431  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> 
 

Hi Tom, 

Great stuff.

How could you overcome an incorrect cardinality estimate forcing the optimiser down an index?

Example:

I have the following table that is used to control the batches into a datawarehouse:

SELECT *
FROM   META_ETL_BATCHES_TO_LOAD;

  BATCH_ID ETL_BATCH_NO B DATE_LOAD
---------- ------------ - ---------
         1           87 N

I am loading from a table called DS_LEGAL_ENTITY:

SELECT COUNT(*) 
FROM   DS_LEGAL_ENTITY
WHERE  ETL_BATCH_NO = 87;

  COUNT(*)
----------
  14797299

The majority of the loads us an IN query to find the batches to load:

SELECT * 
FROM   DS_LEGAL_ENTITY
WHERE  etl_batch_no IN  ( SELECT ETL_BATCH_NO
                FROM   META_ETL_BATCHES_TO_LOAD
              WHERE  BATCH_LOADED = 'N' );

The plan for this however shows:

Execution PLAN
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (COST=34034 Card=583615 BYtes=81122485)

   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'DS_LEGAL_ENTITY' (COST=34023 Card=583615 Bytes=77037180)

   2    1     NESTED LOOPS (COST=34034 Card=583615 Bytes=81122485)
   3    2       SORT (UNIQUE)
   4    3         TABLE ACCESS (FULL) OF 'META_ETL_BATCHES_TO_LOAD' (COST=5 Card=1 Bytes=7)

   5    2       INDEX (RANGE SCAN) OF 'DS_LEGAL_ENTITY_IDX_4' (NON-UNIQUE) (COST=1112 Card=583615)

This suggests that only 583615 rows will come from the DS_legal_entity table, hence the nested loops and index range scan.

In reality of course there are 15 million.  

How can I get the plan to reflect the reality.  I have created a histogram on the etl_batch_no in the ds_legal_entity table with 254 buckets.  When you query the data like this:

SQL> SELECT *
  2  FROM   DS_LEGAL_ENTITY
  3  WHERE  ETL_BATCH_NO = 87;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=43352 Card=15174001
          Bytes=2002968132)

   1    0   PARTITION RANGE (ALL)
   2    1     TABLE ACCESS (FULL) OF 'DS_LEGAL_ENTITY' (Cost=43352 Car
          d=15174001 Bytes=2002968132)

The optimiser has a better estimate of the number of rows to return.

How can I get the orignal query with an IN closer to this estimate?