Re: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Mar 2005 22:02:43 -0000
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Friday, March 11, 2005 2:48 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
