Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: procedures and plans
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