History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-31
Type: Oracle - SQL Tuning Oracle - SQL Tuning
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

How did Oracle compute the selectivity on index ?

Created: 15/Sep/07 11:09 AM   Updated: 30/Sep/15 02:34 PM
Fix Version/s: None

File Attachments: 1. File prod_ora_537_SELECT_PROD_I1_10053.trc (41 kb)
2. Zip Archive sqlt_s4880_prod_fsthqdr2_I1_main.zip (177 kb)

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  « Hide
The customer wanted to know how Oracle computes the selectivity on index IC_TRAN_PNDI1. They're not sure if Oracle optimizer computes correct.

 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 11:11 AM
Event 10053 trace file.

[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 11:14 AM
SQLTXPLAIN report.

[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 11:15 AM
SQLTXPLAIN report.

[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 11:44 AM - edited
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.
...


[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 11:54 AM - edited
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

[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 12:12 PM - edited
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.


[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 12:59 PM - edited
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


[ Permlink | « Hide ]
ubTools Support - 15/Sep/07 01:33 PM - edited
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.


[ Permlink | « Hide ]
ubTools Support - 30/Sep/15 02:32 PM - edited
"Cost Based Oracle: Fundamentals" book of Jonathan Lewis was used in the calculations above.


Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators