Re: Explain Plan with BIND Variables

The only answer to the first question looks like 'trace the session'.

The answer to the second question: Problem here is when you supply a literal, the optimizer will check the frequency of this particular value. If you have a bind variable, obviously, it doesn't know the frequency because the value of the bind variable is unknown at parse time.

Regards,

Sybrand Bakker, Oracle DBA

Stephan Schaefer <stephan.schaefer_at_camline.com> wrote in message news:38D8B0E0.4B0EBDC0_at_camline.com...
> Two questions about bind variables and execution plans
>
> Question 1
> =================================================================
> How can I see the execution plan for the following statement:
>
> select * from my_table where column1 = :my_value ;
>
> Within SQL*Plus I cannot enter
> explain plan set statement_id = 'my_id' for
> select * from my_table where column1 = :my_value ;
>
> And my precompiler tells me:
> "PCC-S-02206, Host variables are not permitted within a DDL statement"
>
> HP-UX 10.20, oracle 7.3.3.6
>
> Question 2
> =================================================================
>
> oK: The explain plan should be identical with the statement
> "select * from my_table where column1 = 123"
>
> Usually it is. But we have one table (1.5 GB large) and one statement
> where the optimizer decides to take different indexes using binds or
> not!!!!
>
> In this case running the readable statement within SQL*Plus takes 5
> seconds, and the analogue statement with bind variables takes 30
> Minutes...
>
> Is this normal? Has anyone seen a analogue behavior?
>
> Stephan Schaefer
>
> mailto:stesch_at_camline.com
>
>
>
>
>
>
>
Received on Thu Mar 23 2000 - 00:00:00 CST