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

Home -> Community -> Usenet -> c.d.o.tools -> Re: procedures and plans

Re: procedures and plans

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 12 Oct 2000 23:00:31 +0200
Message-ID: <971391863.26662.1.pluto.d4ee154e@news.demon.nl>

Answers embedded
Hth,

Sybrand Bakker, Oracle DBA

<ericwade_at_my-deja.com> wrote in message news:8s4ik7$g1j$1_at_nnrp1.deja.com...
> I'm trying to figure out how/when procedures figure out what indexes to
> use. What happens in the following scenario:
>
> 1) I have a table (tbla) with an index (idxa) ; I also have another
> index on the same table which is fairly similar (idxb); I analyzed the
> table after the indexes are created
> 2) I create and compile a stored procedure which when I look at the
> plan goes against table tbla using index idxa
> 3) I then drop index idxa
>
> what happens the next time the proc is executed ? I assume since the
> proc is compiled, that it does not usually create a new plan.

This is a false assumption. Plans are created upon execution. 8.1.6 and beyond have some options to use predefined plans. I haven't used them yet however.

Does it
> automatically recompile ? Does it try to use idxb ?
> We had a problem like the above and the procedure did not have a new
> recompile date or become mark invalid....but it also did not use
> indxb....looked like it was doing a table scan. Does any of the above
> change depending if the procedure has a variable in the where clause
> versus being hard coded ? While we are on the subject, how does the
> cost based optimizer determine which index to use at compile time if
> the where clause has a variable which could change at run time anyhow ?

If the statement uses a bind variable, and you don't have histograms, the optimizer looks at the overall cardinality of the index as in dba_indexes. If the value is hardcoded it does try to determine the number of keys. In 8.1.6 and beyond the optimizer will 'look in' bind variables.

>
> fairly verbose question but thanks for any help.
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Oct 12 2000 - 16:00:31 CDT

Original text of this message

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