Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: OUTLINES

Re: OUTLINES

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 16 Jul 2006 08:07:51 +0100
Message-ID: <5audnSfA1ppTeCTZnZ2dnUVZ8tydnZ2d@bt.com>


"Mladen Gogala" <gogala_at_sbcglobal.net> wrote in message news:pan.2006.07.16.02.50.02.885015_at_sbcglobal.net...
> On Fri, 14 Jul 2006 22:39:04 +0100, Jonathan Lewis wrote:
>
>>
>> You have cursor_sharing = similar.
>>
>> When you create the outline, the stored SQL text is literally
>> as supplied. When you run the statement, it is first rewritten
>> to change any constants to things like ":SYS_B1" - so it no
>> longer matches the text stored in the database.
>>
>> You have to work to CAPTURE the outline if you want to
>> use stored outlines with cursor_sharing enabled; or you
>> have to work out / discover the actual text optimized, and
>> "create stored outline" for that text somehow.
>
> I haven't really tried outlines since Oracle8i but a person who I really
> trust and whose opinion I really appreciate stated on the Oracle-l that
> outlines work even with cursor_sharing is set to force. I also recited
> the same thing, I even added materialized views and query_rewrite_enabled,
> but then again, I haven't tested it for a very long period of time. Did
> you test this recently?
>
> --
> http://www.mgogala.com
>

Yes, they work - but you have to ensure that the outline you store matches the SQL that the optimizer will be executing. That's the point of the two paragraphs you have quoted. Yes, tested recently on 9.2.0.6 and 10.2.0.1 (as well as 8.1.7.4)

If you take the query 'select * from t1 where n1 = 1' and do:

    create stored outline for .....
then the text stored and used for matching will be exactly the text you supplied.. But when you run the query with cursor sharing enabled, the actual run-time text optimised will be more like:

    select * from t1 where n1 = ":SYS_B0" and that won't match the text stored.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Sun Jul 16 2006 - 02:07:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US