Explain Plan with BIND Variables

From: Stephan Schaefer <stephan.schaefer_at_camline.com>
Date: 2000/03/22
Message-ID: <38D8B0E0.4B0EBDC0@camline.com>#1/1

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 Wed Mar 22 2000 - 00:00:00 CST