<< Back to previous view

[QA-31] How did Oracle compute the selectivity on index ? Created: 15/Sep/07  Updated: 30/Sep/15

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - SQL Tuning Priority: Major
Reporter: ubTools Support Assignee: ubTools Support
Resolution: Answered Votes: 0

File Attachments: File prod_ora_537_SELECT_PROD_I1_10053.trc     Zip Archive sqlt_s4880_prod_fsthqdr2_I1_main.zip    
Product Version: 9.2.0.7.0
Operating System: HP-UX
Operating System Version: B.11.11
SQL_TEXT: CREATE OR REPLACE VIEW IC_ITEM_INV_V
(ITEM_ID, LOT_NO, SUBLOT_NO, LOT_ID, LOT_STATUS,
 LOT_CREATED, EXPIRE_DATE, QC_GRADE, WHSE_CODE, LOCATION,
 LOCT_ONHAND, LOCT_ONHAND2, COMMIT_QTY, COMMIT_QTY2)
AS
SELECT l.item_id, l.lot_no, l.sublot_no, l.lot_id, s.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, b.whse_code, b.LOCATION,
          b.loct_onhand, b.loct_onhand2, 0, 0
     FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s
    WHERE l.item_id = b.item_id
      AND l.inactive_ind = 0
      AND l.lot_id = b.lot_id
      AND b.lot_status = s.lot_status(+)
      AND NVL (s.order_proc_ind, 1) = 1
      AND NVL (s.rejected_ind, 0) = 0
      AND b.loct_onhand > 0
   UNION ALL
   SELECT /*+ INDEX(t IC_TRAN_PNDI1) */ t.item_id, l.lot_no, l.sublot_no, t.lot_id, t.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, t.whse_code, t.LOCATION,
          0, 0, t.trans_qty commit_qty, t.trans_qty2 commit_qty2
     FROM ic_lots_mst l, ic_tran_pnd t, ic_item_mst i
    WHERE i.item_id = l.item_id
      AND i.item_id = t.item_id
      AND l.inactive_ind = 0
      AND t.lot_id = l.lot_id
      AND t.delete_mark = 0
      AND t.completed_ind = 0
      AND t.trans_qty < 0
/



SELECT SUM (loct_onhand), SUM (loct_onhand2), SUM (commit_qty),
         SUM (commit_qty2), SUM (loct_onhand) + SUM (commit_qty), lot_no,
         sublot_no, lot_id, lot_status, lot_created, LOCATION, expire_date,
         qc_grade
    FROM xtdba.ic_item_inv_v_sil x
   WHERE item_id = 5125
     AND whse_code = '350'
     AND loct_onhand >= 0
     AND expire_date >
                    TO_DATE ('06-SEP-2007, 11:59:59', 'DD-MON-YYYY, HH:MI:SS')
     AND lot_id > 0
     AND LOCATION <> 'NONE'
GROUP BY lot_no,
         sublot_no,
         lot_id,
         lot_status,
         lot_created,
         LOCATION,
         expire_date,
         qc_grade
  HAVING SUM (loct_onhand) + SUM (commit_qty) > 0
ORDER BY lot_created

 Description   
The customer wanted to know how Oracle computes the selectivity on index IC_TRAN_PNDI1. They're not sure if Oracle optimizer computes correct.

 Comments   
Comment by ubTools Support [ 15/Sep/07 11:11 AM ]
Event 10053 trace file.
Comment by ubTools Support [ 15/Sep/07 11:14 AM ]
SQLTXPLAIN report.
Comment by ubTools Support [ 15/Sep/07 11:15 AM ]
SQLTXPLAIN report.
Comment by ubTools Support [ 15/Sep/07 11:44 AM ]
BASE STATISTICAL INFORMATION 
 
***********************
Table stats    Table: IC_TRAN_PND   Alias:  T
  (Using composite stats)
  TOTAL ::  CDN: 34357548  NBLKS:  737250  AVG_ROW_LEN:  143
-- Index stats
  INDEX NAME: IC_TRAN_PNDI1  COL#: 2 7 6 8 
    TOTAL ::  LVLS: 3   #LB: 341316  #DK: 113700  LB/K: 3  DB/K: 248  CLUF: 28283677
...
***********************

Definition of BASE STATISTICAL INFORMATION

CDN: Cardinality, number of rows.
NBLKS: Number of blocks.
AVG_ROW_LEN: Average row length.

COL#: Column numbers in order.
LVLS: Index depth.
#LB: Number of leaf blocks.
#DK: Number of distinct keys.
LB/K: Leaf blocks per key.
DB/K: Data bloks per key.
CLUF: Clustering factor.

SINGLE TABLE ACCESS PATH

Column: DELETE_MAR  Col#: 28     Table: IC_TRAN_PND   Alias:  T
    NDV: 3         NULLS: 0         DENS: 3.3333e-01 LO:  0  HI: 2
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: COMPLETED_  Col#: 24     Table: IC_TRAN_PND   Alias:  T
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  0  HI: 1
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:  TRANS_QTY  Col#: 16     Table: IC_TRAN_PND   Alias:  T
    NDV: 62267     NULLS: 0         DENS: 1.6060e-05 LO:  -3116050  HI: 150907016871
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:    ITEM_ID  Col#: 2      Table: IC_TRAN_PND   Alias:  T
    NDV: 4527      NULLS: 0         DENS: 2.2090e-04 LO:  3  HI: 9816
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:  WHSE_CODE  Col#: 6      Table: IC_TRAN_PND   Alias:  T
    NDV: 105       NULLS: 0         DENS: 9.5238e-03
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:     LOT_ID  Col#: 7      Table: IC_TRAN_PND   Alias:  T
    NDV: 13443     NULLS: 0         DENS: 7.4388e-05 LO:  0  HI: 46635
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:   LOCATION  Col#: 8      Table: IC_TRAN_PND   Alias:  T
    NDV: 31        NULLS: 0         DENS: 3.2258e-02
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: IC_TRAN_PND     ORIG CDN: 34357548  ROUNDED CDN: 1  CMPTD CDN: 0
...

Definition of SINGLE TABLE ACCESS PATH

NDV: Number of distinct values.
NULLS: Number of NULLs.
DENS: Density.
LO: Lowest value for numeric columns.
HI: Highest value for numeric columns.
...

Comment by ubTools Support [ 15/Sep/07 11:54 AM ]
According to the execation plan, these are the predicates:

Access Predicates:

T.ITEM_ID=5125
AND T.LOT_ID=L.LOT_ID
AND T.WHSE_CODE='350'

Filter Predicates:

B.ITEM_ID=T.ITEM_ID
AND T.LOT_ID>0
AND T.LOCATION<>'NONE'

Column order of IC_TRAN_PNDI1:

  • ITEM_ID
  • LOT_ID
  • WHSE_CODE
  • LOCATION
Comment by ubTools Support [ 15/Sep/07 12:12 PM ]
According to the execution plan, T.LOT_ID is joined with L.LOT_ID. That means T.LOT_ID gets values in the join. So, accessing the index consists of the following columns:
  • ITEM_ID
  • LOT_ID
  • WHSE_CODE

That's why the access predicates consist of these columns. T.LOCATION<>'NONE' is not included in access predicates. Because, <> can not be used accessing index.

After accessing index by access predicates, filter operation starts by filter predicates in order to eliminate rows on index without going to table. Additionally, T.LOCATION<>'NONE' is used in filter predicates to filter index keys on index.

Comment by ubTools Support [ 15/Sep/07 12:59 PM ]
Note: Since there is no NULL/histogram in our IC_TRAN_PNDI1 index columns and all predicates are ANDed, we did not cover other situations for selectivity computations.

Selectivity of access predicates:

Column Operation Formula Value
ITEM_ID = 1/NDV=DENS 2.2090e-04
LOT_ID = 1/NDV=DENS 7.4388e-05
WHSE_CODE = 1/NDV=DENS 9.5238e-03

Since the columns are ANDed, combined selectivity means:

= Sel(ITEM_ID)*Sel(LOT_ID)*Sel(WHSE_CODE)
= 2.2090e-04*7.4388e-05*9.5238e-03
= 1.5649e-10

Selectivity of filter predicates:

After accessing the index, filter operation will start. In our case, access predicates will also be used in filter operation to eliminate rows in the index. Because, their values are known, and can be used in filter operation.

But, their selectivity will not be re-computed, since they are already computed to access the index. So, T.LOT_ID>0 in filter predicates doesn't make sense even if its operation is not an equal operation as in access predicates.

Column Operation Formula Value
LOCATION <> 1-(1/NDV=DENS) 1-3.2258e-02=0.967742

Since the columns are ANDed, combined selectivity means:

= Sel(ITEM_ID)*Sel(LOT_ID)*Sel(WHSE_CODE)*Sel(LOCATION)
= 2.2090e-04*7.4388e-05*9.5238e-03*0.967742
= 1.5144e-10

Comment by ubTools Support [ 15/Sep/07 01:33 PM ]
Interpreting Event 10053 trace file is need to see if optimizer computation and ours match.

Final cost at the bottom:

Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 29  CDN: 2  RSC: 28  RSP: 28  BYTES: 308
  IO-RSC: 28  IO-RSP: 28  CPU-RSC: 0  CPU-RSP: 0

The final cost is 29.

Going backward lines to break down the final cost of 29:

 
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: IC_ITEM_INV_V_SIL   Alias:  X
  TOTAL ::  (NOT ANALYZED)    CDN: 0  NBLKS:  0  AVG_ROW_LEN:  0
_OPTIMIZER_PERCENT_PARALLEL = 0
  BEST_CST: 13.00  PATH: 2  Degree:  1

The cost of IC_ITEM_INV_V_SIL is 13.

 
GENERAL PLANS
***********************
Join order[1]:  IC_ITEM_INV_V_SIL[X]#0
GROUP BY sort
GROUP BY cardinality:  1, TABLE cardinality:  2
HAVING selectivity:  5.0000e-02  -> GROUPS:  1
    SORT resource      Sort statistics
      Sort width:          299 Area size:     1048576 Max Area size:   104857600   Degree: 1
      Blocks to Sort:        1 Row size:          180 Rows:          2
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         30
      Total IO sort cost: 16
      Total CPU sort cost: 0
      Total Temp space used: 0
Best so far: TABLE#: 0  CST:         29  CDN:          2  BYTES:        308
    SORT resource      Sort statistics
      Sort width:          299 Area size:     1048576 Max Area size:   104857600   Degree: 1
      Blocks to Sort:        1 Row size:          180 Rows:          2
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         30
      Total IO sort cost: 16
      Total CPU sort cost: 0
      Total Temp space used: 0
..

The cost of sorting IC_ITEM_INV_V_SIL is 16.

Total cost (29) = Accessing IC_ITEM_INV_V_SIL (13) + Sorting IC_ITEM_INV_V_SIL (16)

Going backward lines to break down the cost of 13:

Join result: cost: 7  cdn: 1  rcz: 98
Best so far: TABLE#: 0  CST:          1  CDN:          1  BYTES:          4
Best so far: TABLE#: 1  CST:          1  CDN:          1  BYTES:         11
Best so far: TABLE#: 3  CST:          3  CDN:          1  BYTES:         65
Best so far: TABLE#: 2  CST:          7  CDN:          1  BYTES:         98
Final - All Rows Plan:
  JOIN ORDER: 2
  CST: 7  CDN: 1  RSC: 7  RSP: 7  BYTES: 98
  IO-RSC: 7  IO-RSP: 7  CPU-RSC: 0  CPU-RSP: 0

JOIN ORDER: 2 is selected with the cost of 7.

Going backward lines to break down the cost of 7:

Join order[2]:  IC_ITEM_MST_B[B]#0  IC_ITEM_MST_TL[T]#1  IC_LOTS_MST[L]#3  IC_TRAN_PND[T]#2
...
Now joining: IC_TRAN_PND[T]#2 *******
NL Join
  Outer table: cost: 3  cdn: 1  rcz: 65  resp:  3
  Access path: index (scan)
      Index: IC_TRAN_PNDI1
  TABLE: IC_TRAN_PND
      RSC_CPU: 0   RSC_IO: 4
  IX_SEL:  1.5650e-10  TB_SEL:  1.5144e-10
    Join:  resc: 7  resp: 7
  Best NL cost: 7  resp: 7

Our index IC_TRAN_PNDI1 appears here. So, here is the stop point for our case.

Here is the selectivity comparison table which includes Oracle-computed selectivity values and manually computed selectivity values.

  Oracle Manual
IX_SEL(Access predicates) 1.5650e-10 1.5649e-10
TB_SEL(Access+Filter Predicates) 1.5144e-10 1.5144e-10

No computation errors found.

Comment by ubTools Support [ 30/Sep/15 02:32 PM ]
"Cost Based Oracle: Fundamentals" book of Jonathan Lewis was used in the calculations above.



[QA-17] Which parameters affect CBO ? Created: 15/Jul/07  Updated: 16/Sep/07

Status: Closed
Project: Questions & Answers
Fix Version/s: None

Type: Oracle - SQL Tuning Priority: Major
Reporter: ubTools Support Assignee: ubTools Admin
Resolution: Answered Votes: 0

Product Version: Generic
Operating System: Generic

 Description   
Which parameters affect CBO ?

 Comments   
Comment by ubTools Support [ 15/Jul/07 02:37 PM ]
The parameters affecting CBO are included in Event 10053 trace files.

Sample:

 
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> select f1 from test10053 where f1=23;

       F1
----------
       23

Trace file is generated under USER_DUMP_DEST. Here is an excerpt from the trace file:

 
*** 2005-01-10 13:09:03.010
*** SESSION ID:(8.1072) 2005-01-10 13:09:03.008
QUERY
select f1 from test10053 where f1=23
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
OPTIMIZER_FEATURES_ENABLE = 9.2.0
OPTIMIZER_MODE/GOAL = Choose
_OPTIMIZER_PERCENT_PARALLEL = 101
HASH_AREA_SIZE = 1048576
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
SORT_AREA_SIZE = 524288
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = TRUE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = TRUE
_PUSH_JOIN_PREDICATE = TRUE
PARALLEL_BROADCAST_ENABLED = TRUE
OPTIMIZER_MAX_PERMUTATIONS = 2000
OPTIMIZER_INDEX_CACHING = 0
_SYSTEM_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
OPTIMIZER_DYNAMIC_SAMPLING = 1
_OPTIMIZER_DYN_SMP_BLKS = 32
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = TRUE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = TRUE
ALWAYS_ANTI_JOIN = CHOOSE
ALWAYS_SEMI_JOIN = CHOOSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = TRUE
_PUSH_JOIN_UNION_VIEW = TRUE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = TRUE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = TRUE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
_OPTIMIZER_COST_MODEL = CHOOSE
_GSETS_ALWAYS_USE_TEMPTABLES = FALSE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
_NEW_SORT_COST_ESTIMATE = TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED = TRUE
_CPU_TO_IO = 0
_PRED_MOVE_AROUND = TRUE
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: TEST10053   Alias: TEST10053
 TOTAL ::  CDN: 1000  NBLKS:  2  AVG_ROW_LEN:  7
-- Index stats
 INDEX NAME: I_TEST10053  COL#: 1
   TOTAL ::  LVLS: 1   #LB: 3  #DK: 1000  LB/K: 1  DB/K: 1  CLUF: 2
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column:         F1  Col#: 1      Table: TEST10053   Alias: TEST10053
   NDV: 1000      NULLS: 0         DENS: 1.0000e-03 LO:  1  HI: 1000
   NO HISTOGRAM: #BKT: 1 #VAL: 2
 TABLE: TEST10053     ORIG CDN: 1000  ROUNDED CDN: 1  CMPTD CDN: 1
 Access path: tsc  Resc:  2  Resp:  2
 Access path: index (iff)
     Index: I_TEST10053
 TABLE: TEST10053
     RSC_CPU: 0   RSC_IO: 2
 IX_SEL:  0.0000e+00  TB_SEL:  1.0000e+00
 Access path: iff  Resc:  2  Resp:  2
 Access path: index (equal)
     Index: I_TEST10053
 TABLE: TEST10053
     RSC_CPU: 0   RSC_IO: 1
 IX_SEL:  0.0000e+00  TB_SEL:  1.0000e-03
 BEST_CST: 1.00  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TEST10053 [TEST10053]
Best so far: TABLE#: 0  CST:          1  CDN:          1  BYTES:          3
Final:
 CST: 1  CDN: 1  RSC: 1  RSP: 1  BYTES: 3
 IO-RSC: 1  IO-RSP: 1  CPU-RSC: 0  CPU-RSP: 0

Warnings:

  • To generate Event 10053 data, statement must be HARD PARSED.
  • RBO doesn't generate Event 10053 data.



Generated at Thu Apr 25 17:41:16 UTC 2024 using JIRA Standard Edition, Version: 3.12.3-#302.