Re: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen

Steve Dirschel has just sent me an example where he found multiple child cursors with

    cursor_sharing = similar,
and

    predicate involving inequality.

Sure enough - Oracle 9i generates a new child cursor for each new set of inputs
e.g.

    SELECT * from YFS_ORDER_HEADER WHERE ORDER_HEADER_KEY > '20040101'
    SELECT * from YFS_ORDER_HEADER WHERE ORDER_HEADER_KEY > '20040102'
    SELECT * from YFS_ORDER_HEADER WHERE ORDER_HEADER_KEY > '20040103'

Moreover, my test case (which once 'proved' that the only important factor was a histogram on a column in the predicate) generates new child cursors on every version of 9i I can find. So I was wrong.

Thanks to Steve, apologies to everyone else.

Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated March 9th 2005

• Original Message ----- From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, March 11, 2005 2:48 AM To: oracle-l_at_freelists.org Subject: Re: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen

I have never seen what Oracle calls an "unsafe" statement (i.e. <, >, between, those types of conditions in the where clause) be reparsed unless the histogram condition was met - have you ?

Even though the claim is lurking somewhere in an official Oracle document (was it an OW white-paper), I suspect it is complete rubbish. (at least up to 9i - it occurs to me that I haven't repeated that set of tests on 10g yet).

Regards

Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l