Re: Doing battle with the CBO in 9i (9.2.0.6)
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2007 22:02:45 +0100
It's the necessary plan
Hacking the predicates to make things clearer:
where
:1 = 'XXX'
or (:2 != 'XXX' and FPGI.PC_ID = :3)
If you enter 'XXX' as the value for :1, then Oracle MUST
return every row in the table. If you give it an index hint,
and there is an index with a non-null column, Oracle HAS
to do a full scan to get every row.
The optimizer can only generate one static plan that has to
work for all possible sets of inputs.
What you really want to see is a use_concat plan - where Oracle
turns your query into UNION ALL of two non-overlapping
data sets.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Cc: "Consul, Mamta" <Mamta.Consul@xxxxxxxxxxxxxxx>; "Nair, Krishna"
<Krishna.Nair@xxxxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2007 9:54 PM
Subject: 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:
--
//www.freelists.org/webpage/oracle-l
Other related posts:
