| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: selectivity of predicates with LIKE - diff between 8i and 9i
By any chance are you using CURSOR_SHARING parameter in your 8i version?
I think the default selectivity of 5% is used while costing the like
operator and with the binds (and with an underscore parameter which
I think defaults TRUE) it is treated as equality .
If not you can set the underscore parameter _like_with_bind_as_equality to get the index costing.
KG
Best Regards,
K Gopalakrishnan
-----Original Message-----
Boris Dali
Sent: Wednesday, June 11, 2003 10:21 AM
To: Multiple recipients of list ORACLE-L
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.
-- 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 - 12:11:31 CDT
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: K Gopalakrishnan 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).
 |
 |