RE: Doing battle with the CBO in 9i (9.2.0.6)

Is there a way to force a USE_CONCAT to do the union all? I tried giving it a USE_CONCAT, but, it had no effect. The 9i manual mentions that it's costed, and will only do the transformation if the cost is cheaper?

-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
www.proquest.com
www.csa.com

ProQuest...Start here.

-----Original Message-----

From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Tuesday, July 10, 2007 5:03 PM
To: Bobak, Mark; oracle-l
Subject: Re: Doing battle with the CBO in 9i (9.2.0.6)

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_at_il.proquest.com> To: "oracle-l" <oracle-l_at_freelists.org> Cc: "Consul, Mamta" <Mamta.Consul_at_il.proquest.com>; "Nair, Krishna" <Krishna.Nair_at_il.proquest.com> 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:

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 10 2007 - 16:35:44 CDT