Re: [SQL TUNING]The bind variables in a query

On Mon, 14 Nov 2005 06:59:06 -0800, jbmorla wrote:

>
> in the shared pool,
>
> and I want to perform an explain plan on that query?

You don't want to do that. It's already done for you. The plan is in V$SQL_PLAN

>
> Obviously depending on the number of rows selected, my SQL tuning
> strategy varies a lot.

Yes. That is why there is so called bind variable peekaboo in version 9i and later. Take a look at the following text from asktom: http://tinyurl.com/c9ez4

>
> I need to know the value in the variable at runtime.

Turn on the event 10046, level 12. Alternatively, use DBMS_MONITOR to do the same thing. If you want to see which bind variables were used during the last run, you can see that in V$SQL_BIND_CAPTURE, V$SQL_BIND_DATA and V$SQL_BIND_METADATA. If you want to see what's happening during the parse phase, turn on the event 10053. That event is described on http://www.centrexcc.com which is sort of AskTom for the CBO related questions.

-- 
http://www.mgogala.com