| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Explain plan accuracy and bind variables (new thread)
Here's the test, shown inline below. The only thing that may slow down
your reproducing it is the use of view_trace.sql, which I believe you
can download from Steve Adams's www.ixora.com.au
<http://www.ixora.com.au/> site. If you can't, it's no big deal; just
execute the final step manually.
If my comments don't match the result of a step in your environment, please let me know.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Upcoming events:
- Performance <http://www.hotsos.com/training/PD101.html> Diagnosis
101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle
- SQL Optimization <http://www.hotsos.com/training/OP101.html> 101:
2/16 Dallas
- Hotsos Symposium 2004 <http://www.hotsos.com/events/symposium/2004> :
March 7-10 Dallas
- Visit www.hotsos.com for schedule details...
REM Test to demonstrate that explain plan doesn't always show the right plan
REM when using bind variables.
REM Cary Millsap
REM 2004/02/06 spool test.txt
REM Create a table and insert one row.
drop table t
/
create table t (
key varchar2(5),
value varchar2(20)
)
/
create index t_n1 on t (
key
)
/
insert into t values ('1', 'xxxxxxxxxxx')
/
select * from t
/
pause Note that the table has one row.
REM Define the bind variables and set their values.
variable c char;
exec begin :c := '1'; end;
/
variable n number;
exec begin :n := 1; end;
/
pause Note that the bind variables have been declared and set with different types.
REM Trace a query that uses the char, and a query that uses the number.
alter session set sql_trace=true
/
select * from t where key = :c
/
select * from t where key = :n
/
alter session set sql_trace=false
/
pause Note that queries have returned the same result sets.
REM Show explain plan output for both statements.
delete from plan_table
/
explain plan set statement_id = 'c' for
select * from t where key = :c
/
select id, parent_id,
lpad(' ', 2*(level-1)) || operation || ' ' || options
|| ' ' || object_name || ' ' ||
decode(id, 0, 'Cost = ' || position) "Query Plan"
from plan_table
start with id = 0 and statement_id = 'c'
connect by prior id = parent_id and statement_id = 'c'
/
explain plan set statement_id = 'n' for
select * from t where key = :n
/
select id, parent_id,
lpad(' ', 2*(level-1)) || operation || ' ' || options
|| ' ' || object_name || ' ' ||
decode(id, 0, 'Cost = ' || position) "Query Plan"
from plan_table
start with id = 0 and statement_id = 'n'
connect by prior id = parent_id and statement_id = 'n'
/
pause Note that EXPLAIN PLAN shows the same execution plan in both cases.
pause However, what will the trace file show?
spool off
REM Now, look at the trace file to see if the STAT sections match.
@view_trace
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Feb 06 2004 - 12:08:12 CST
 |
 |