Is there something special about the table or index? I tried to create a mock table with your statistics and the 9i optimizer came up with the same cardinality and cost estimates as 8i. Can you post (or e-mail me) more of the 10053 trace?
In 9i the optimizer used a FF of 3.4877e-02 (see IXSEL or TBSEL in the 10053 trace) giving the high cardinality estimate : 241286 * 3.4877e-02 = 8414.85, rounded to 8415.
And the explanation of the cost of 39 is as follows:
It is an index-only access path, so the cost is LVLS + FF * #LB = 2 + 8414.85 * 1035 = 2 + 36.0977, rounded to 39.
At 09:21 AM 6/11/2003 -0800, you wrote:
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).
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
