RE: explain plan question

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Wed, 11 Jul 2001 08:26:49 -0700
Message-ID: <F001.00347371.20010711080616@fatcity.com>

Depends, if your using bind variables in production, then you can expect to have a very static choice of explain plans.

Although bind variables are great for promoting more efficient use of the shared pool, you do hinder the optimizer's ability to use selectivity and histograms to detirmine if an index is worth using or not. It is a catch 22, some cases having histograms and selectivity available to the optimizer out weighs the advantages of using bind variables and providing better memory management and quicker parse times. Some times it does not.

But using literals in your statements, your in effect changing what would happen under production with bind variables. Oracle will not chose many different explain plans based on the literal value when in an environment with bind variables.

If the literals perform WAY better, than opt towards histograms and selectivity. If they are only small portion better or worse, then opt towards better memory management and faster parse times.

-----Original Message-----
Sent: Wednesday, July 11, 2001 10:06 AM
To: Multiple recipients of list ORACLE-L

Hi,

I'm trying to tune a query which has several input parameters. I used the bind variables in their place to create an explain plan. Then I put some real data in their place and created another explain plan. The two plans were different. Which plan should I be using in my tuning? The one with the bind vars or the one with the real data?

TIA Gene

Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Gene Gurevich
INET: g_u_r_e_v_i_c_h_at_yahoo.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: Christopher Spence
INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------