Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> selectivity of predicates with LIKE - diff between 8i and 9i

selectivity of predicates with LIKE - diff between 8i and 9i

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Wed, 11 Jun 2003 09:49:36 -0700
Message-ID: <F001.005AF3F5.20030611092100@fatcity.com>


Dear List,

Is there any difference between 8i and 9i in how selectivity of the predicates with LIKE are estimated by CBO?
We are migrating some apps running on 8.1.7.4 on HP-UX 11.0 into 9.2.0.3 on the same box and some queries choose completely different execution plans - HJ with FTS vs original NL with IRS.
After simplifying the real query to a primitive one-liner it looks like predicates with LIKE are estimated differently in 9i:

[EMAIL PROTECTED]> @target

  COUNT(1)


       291

[EMAIL PROTECTED]> l
  1* select count(1) from DIS_TAB_ALBUM_TITRE ALT where ALT.ait_ds_titre LIKE 'LOVE%'

  Id Par CST CDN Plan
---- ---- ---------- ----------


   0               3          1   SELECT STATEMENT

(choose) Cost (3,1,20)
1 0 1 SORT
(aggregate)
2 1 3 2 INDEX (analyzed)
NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) Cost (3,2,40)

  Id Par CST CDN Plan
---- ---- ---------- ----------


   0              39          1   SELECT STATEMENT

(choose) Cost (39,1,19)
1 0 1 SORT
(aggregate)
2 1 39 8415 INDEX (analyzed)
NON-UNIQUE OPS$DEVDIS0 DIS_IND_ALBUM_TITRE_1 (range scan) (Columns 1 Cost (39,8415,159885)

In 8i assuming a filter factor to be simply 1/NDV, CST is understandably equals to 3 (given the data below):

  INDEX#: 307169 COL#: 3
    TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204303 Column: AIT_DS_TIT Col#: 3 Table:
DIS_TAB_ALBUM_TITRE Alias: ALT

    NDV: 161254    NULLS: 0         DENS: 6.2014e-06
  TABLE: DIS_TAB_ALBUM_TITRE     ORIG CDN: 241286 
CMPTD CDN: 2 IRS CST= blevel+ff*lb+ff*cf=2 + 6.2*10^-6 * (1035 + 204303) ~ 3.3 -> 3

But in 9i CBO probably uses something else as a FF for this predicate with LIKE, since CST becomes 39:

  INDEX NAME: DIS_IND_ALBUM_TITRE_1 COL#: 3     TOTAL :: LVLS: 2 #LB: 1035 #DK: 161254 LB/K: 1 DB/K: 1 CLUF: 204338 Column: AIT_DS_TIT Col#: 3 Table:
DIS_TAB_ALBUM_TITRE Alias: ALT

    NDV: 157906 NULLS: 0 DENS: 6.3329e-06     NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: DIS_TAB_ALBUM_TITRE ORIG CDN: 241286 ROUNDED CDN: 8415 CMPTD CDN: 8415 IRS CST= ??? = 39 Questions:
1) Does anybody know what CBO uses for a FF calcualation for predicates with LIKE in 9i? How does it get 39?
2) Is there a simple way to get it "back on track" to CST=2 without hints or stored outlines - some spfile parameter would be ideal?
3) Both computed cardinalities seem to be way off (2 in 8i, 8415 in 9i - while the real number of rows returned is 291).

   Would histograms be the right way to get CMPTD CDN closer to the reality in this case?

Not sure if it's important, but we are using automatic PGA management here (worksize_policy_area is TRUE, pga_aggreagate_target is a 100M)

Thanks for any help,
Boris Dali.



Post your free ad now! http://personals.yahoo.ca
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jun 11 2003 - 11:49:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US