Re: Explain Plan with BIND Variables

Stephan Schaefer wrote:
>
> 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

That is strange - we regularly run explain plan with the 'abstract' bind variable references ":b1" etc etc with no problems...

This is via SQL Plus and/or Toad

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse