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%'
-- 8i:
[EMAIL PROTECTED]> @explain8
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)
-- 9i:
[EMAIL PROTECTED]> @explain8
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)
-- 8i:
Access path: index (index-only)
INDEX#: 307169 TABLE: DIS_TAB_ALBUM_TITRE
(obj_id=307169 -> DIS_IND_ALBUM_TITRE_1)
CST: 3 IXSEL: 6.2017e-06 TBSEL: 6.2017e-06
...
BEST_CST: 3.00 PATH: 4 Degree: 1
-- 9i:
Access path: index (index-only)
Index: DIS_IND_ALBUM_TITRE_1
TABLE: DIS_TAB_ALBUM_TITRE
RSC_CPU: 0 RSC_IO: 39
IX_SEL: 3.4877e-02 TB_SEL: 3.4877e-02
...
BEST_CST: 39.00 PATH: 4 Degree: 1
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).
