Oracle Database Discussions

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL : select * from GC_FULFILLMENT_ITEMS where MARKETPLACE_ID=:b1 and GC_FULFILLMENT_STATUS_ID=:b2;

Plan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |   474K|    99M|   102  (85)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| GC_FULFILLMENT_ITEMS        |   474K|    99M|   102  (85)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_GCFI_GCFS_ID_SDOC_MKTPLID |   474K|       |    91  (95)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GC_FULFILLMENT_STATUS_ID"=TO_NUMBER(:B2) AND "MARKETPLACE_ID"=TO_NUMBER(:B1))
       filter("MARKETPLACE_ID"=TO_NUMBER(:B1))

If i use literals than CBO uses cardinality =1 (I believe this is due it fix control :5483301 which i set to off In my environment)

select * from GC_FULFILLMENT_ITEMS where MARKETPLACE_ID=5 and GC_FULFILLMENT_STATUS_ID=2;

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |     1 |   220 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| GC_FULFILLMENT_ITEMS        |     1 |   220 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_GCFI_GCFS_ID_SDOC_MKTPLID |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("GC_FULFILLMENT_STATUS_ID"=2 AND "MARKETPLACE_ID"=5)
       filter("MARKETPLACE_ID"=5)

Here is column distribution and histogram information

Enter value for column_name: MARKETPLACE_ID

COLUMN_NAME          ENDPOINT_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY ENDPOINT_ACTUAL_VALU
-------------------- -------------- --------------------- ---------- --------------------
MARKETPLACE_ID                    1                     1          1
MARKETPLACE_ID                    3                  8548       8547
MARKETPLACE_ID                    4                 15608       7060
MARKETPLACE_ID                    5                 16385        777   --->
MARKETPLACE_ID                35691                 16398         13
MARKETPLACE_ID                44551                 16407          9

6 rows selected.
Enter value for column_name: GC_FULFILLMENT_STATUS_ID

COLUMN_NAME                    ENDPOINT_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY ENDPOINT_ACTUAL_VALU
------------------------------ -------------- --------------------- ---------- --------------------
GC_FULFILLMENT_STATUS_ID                    5                 19602      19602
GC_FULFILLMENT_STATUS_ID                    6                 19612         10
GC_FULFILLMENT_STATUS_ID                    8                 19802        190

3 rows selected.

Actual distribution
select MARKETPLACE_ID,count(*) from GC_FULFILLMENT_ITEMS group by MARKETPLACE_ID order by 1;

MARKETPLACE_ID   COUNT(*)
-------------- ----------
             1       2099
             3   16339936
             4   13358682
             5    1471839   --->
         35691      33623
         44551      19881
         78931      40273
        101611          1
                  6309408

9 rows selected.
BHAVIK_DBA: GC1EU> select GC_FULFILLMENT_STATUS_ID,count(*) from GC_FULFILLMENT_ITEMS group by GC_FULFILLMENT_STATUS_ID order by 1;

GC_FULFILLMENT_STATUS_ID   COUNT(*)
------------------------ ----------
                       1        880
                       2         63   --->
                       3         24
                       5   37226908
                       6      22099
                       7         18
                       8     325409
                       9        343

8 rows selected.

10053 trace

  SINGLE TABLE ACCESS PATH
  Table: GC_FULFILLMENT_ITEMS  Alias: GC_FULFILLMENT_ITEMS
    Card: Original: 36703588.000000  Rounded: 474909  Computed: 474909.06  Non Adjusted: 474909.06

  Best:: AccessPath: IndexRange
  Index: I_GCFI_GCFS_ID_SDOC_MKTPLID
         Cost: 102.05  Degree: 1  Resp: 102.05  Card: 474909.06  Bytes: 0
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_fix_control' '5483301:0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$F5BB74E1" "GC_FULFILLMENT_ITEMS"@"SEL$2" ("GC_FULFILLMENT_ITEMS"."GC_FULFILLMENT_STATUS_ID" "GC_FULFILLMENT_ITEMS"."SHIP_DELIVERY_OPTION_CODE" "GC_FULFILLMENT_ITEMS"."MARKETPLACE_ID"))
    END_OUTLINE_DATA
  */

Is there any reason why CBO is using card=474909.06 ? Having fix control () in place, it should have set card=1 if it is considering GC_FULFILLMENT_STATUS_ID= 2 as "rare" value..isn't it ?