| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Doing battle with the CBO in 9i (9.2.0.6)
Hi,
Having a bit of a problem with the CBO on 9.2.0.6, and I wonder if anyone can offer any advice?
I have a query that looks like this:
SELECT FPGI.FPGI_ID L_FPGI_ID, FPGI.FPGI_UMI_PAGE_NUMBER DSP_FPGI_UMI_PAGE_NUMBER, FPGI.PC_ID DSP_PC_ID FROM FULL_PAGE_IMAGES FPGI WHERE ( ( :1='ENTER-QUERY') OR ( ( NOT :2='ENTER-QUERY') AND ( /* CG$MDTU_VWC_START DOC.FPGI */ (FPGI.PC_ID = :3) /* CG$MDTU_VWC_END DOC.FPGI */ ) ) ); As some of you may recognize, this query is generated by Oracle Forms, as part of an LOV population. (That's where all those parentheses and the 'ENTER-QUERY' business comes in.) So, as this is generated, I have no control over the code, and can't change it.
It's doing a FULL TABLE SCAN on the table, rather than an INDEX RANGE SCAN on the index on the PC_ID column. So, after playing with it a bit, it seems that the thing that's screwing it up is those extra predicates that Forms is adding, regarding the query mode. If I modify the query and remove those extraneous predicates, the query does the right thing (INDEX RANGE SCAN). But, I can't touch the query, cause it's generated.
So, says I, I'll use stored outlines, and no problem, be on my way.....
Well, after struggling to get stored outlines to work (what an awkward interface! I can't wait till 10g and SQL Profiles!), I see that I've in fact, created a larger problem. The "fixed" plan is in place, via stored outline, and my performance is WORSE than the FTS! After scratching my head for a while, I realized that, even with an INDEX hint correctly specified and correctly choosing the index, the reason that the performance is worse, is that the optimizer is choosing to do an INDEX FULL SCAN, followed by a TABLE ACCESS BY ROWID on every row in the table, which is FAR worse than the FTS I started with! The problem is that an INDEX hint specifies (correctly) which index to use, but it does NOT specify what type of scan operation (unique, range, or full) Oracle will do on said index! It seems that in the presence of those aforementioned, extraneous predicates, forces the CBO into doing a full scan rather than range scan! I did a 10053 trace, to try to get some insight, and found that there are no differences in the trace, up to the SINGLE TABLE ACCESS PATH. At that point, they diverge significantly. Here's what I have in each trace file:
Problem query:
SINGLE TABLE ACCESS PATH Column: PC_ID Col#: 2 Table: FULL_PAGE_IMAGES Alias: FPGI
NDV: 2794406 NULLS: 0 DENS: 3.5786e-07 LO: 1 HI: 34817851
NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: FULL_PAGE_IMAGES ORIG CDN: 95463327 ROUNDED CDN: 954635 CMPTD CDN: 954635 Access path: index (no sta/stp keys)
Index: FPGI_INDX_FK01
TABLE: FULL_PAGE_IMAGES RSC_CPU: 0 RSC_IO: 6964505 IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
BEST_CST: 696451.00 PATH: 4 Degree: 1
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[0]: FULL_PAGE_IMAGES[FPGI]#0
Best so far: TABLE#: 0 CST: 696451 CDN: 954635 BYTES: 16228795
prefetching is on for FPGI_INDX_FK01
Final - All Rows Plan:
JOIN ORDER: 0 CST: 696451 CDN: 954635 RSC: 696450 RSP: 696450 BYTES: 16228795 IO-RSC: 696450 IO-RSP: 696450 CPU-RSC: 0 CPU-RSP: 0 Good query (without extraneous predicates):
SINGLE TABLE ACCESS PATH Column: PC_ID Col#: 2 Table: FULL_PAGE_IMAGES Alias: FPGI
NDV: 2794406 NULLS: 0 DENS: 3.5786e-07 LO: 1 HI: 34817851
NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: FULL_PAGE_IMAGES ORIG CDN: 95463327 ROUNDED CDN: 34 CMPTD CDN: 34 Access path: index (equal)
Index: FPGI_INDX_FK01
TABLE: FULL_PAGE_IMAGES RSC_CPU: 0 RSC_IO: 7 IX_SEL: 0.0000e+00 TB_SEL: 3.5786e-07
BEST_CST: 1.00 PATH: 4 Degree: 1
OPTIMIZER STATISTICS AND COMPUTATIONS
Join order[0]: FULL_PAGE_IMAGES[FPGI]#0
Best so far: TABLE#: 0 CST: 1 CDN: 34 BYTES: 578
Final - All Rows Plan:
JOIN ORDER: 0 CST: 1 CDN: 34 RSC: 1 RSP: 1 BYTES: 578 IO-RSC: 1 IO-RSP: 1 CPU-RSC: 0 CPU-RSP: 0 Note the difference in access paths, "index(no sta/stp)", which is the full scan, vs. "index (equal)" which is the range scan.
So, at this point, I'm stuck....anyone else faced something similar? Without the ability to modify the SQL, what are my options? Any ideas, anyone?
AdvThanksance,
-Mark
-- Mark J. Bobak Senior Database Administrator, System & Product Technologies ProQuest 789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346 734.997.4059 or 800.521.0600 x 4059 mark.bobak_at_il.proquest.com <mailto:mark.bobak_at_il.proquest.com> www.proquest.com <http://www.proquest.com> www.csa.com <http://www.csa.com> ProQuest...Start here. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 10 2007 - 15:54:15 CDT
 |
 |