RE: bind vars change explain plan

Mike:

According to the docs, the first time an query is parsed, Oracle "peaks" at the bind variable and bases the explain plan on those values. The values of the bind variables are never looked at again

Kevin

-----Original Message-----
Sent: Tuesday, July 23, 2002 6:58 AM
To: Multiple recipients of list ORACLE-L

Hi,

"Beginning with Oracle9i, the optimizer will consider bind variable values when choosing execution plans."

Does anyone know how Oracle manages to do this? The 9i Database Performance Tuning Guide infers that the treatment of bind variables has not changed but maybe we attribute this to "manual lag". Presumably it must perform some kind of parse to rejig the execution plan despite the use of bind variables. Somewhere between hard and soft perhaps. A "fairly solid" parse.
Anyone know the mechanics of this? Has anyone seen this new functionality in practice?

Cheers,
Mike Hately

-----Original Message-----
Sent: 23 July 2002 09:58
To: Multiple recipients of list ORACLE-L

>From the O'Reilly "Oracle SQL Tuning Pocket Reference"

"There is one situation in which bind variables are not such a great choice. If you have column data in a table having a disproportionate number of rows with certain values, and a very small number of rows with other values, you should be using histograms. Bind variables cannot use histogram information."

"Using bind variables will prevent the optimizer from doing this, [using histograms] because the optimizer is unaware of the value that will be in the bind variable at the time it decides on the execution plan"

"Beginning with Oracle9i, the optimizer will consider bind variable values when choosing execution plans."

HTH
Kev.  

This email and any attached to it are confidential and intended only for the individual or
entity to which it is addressed. If you are not the intended recipient, please let us know
by telephoning or emailing the sender. You should also delete the email and any attachment
from your systems and should not copy the email or any attachment or disclose their content
to any other person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately Mike
  INET: Mike.Hately_at_churchill.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).