Sometimes you simple need to know whether a statement is fast or not, or why it is slow. (Sadly, it seems like virtually nobody wants to know why a statement is fast, but that’s another story…)
Do not use EXPLAIN PLAN in such cases, it does not (can not) deal bind variables right. I’ve got a simple script in my toolbox to create a real execution plan using bind variables, but avoid displaying all the lines the SQL returns. Just have a look, and use it if it’s suitable for your needs. Please keep in mind, that you need to set the binds the way I do, because the cost based optimizer handles bind statements entirely different (bind peeking, (adaptive) cursor sharing …)
This script was designed to run in Oracle SQL Developer very well, but it seems that version 2.1 has trouble with the LAST cursor, due to some changed output.
set timing on;
set serveroutput off;
----------
--_cursor_plan_unparse_enabled=TRUE is the database default.
-- Du to several bugs in the current RDBMs, it might be set
-- otherwise on your box. Change it to TRUE to make valuable
-- predicate information visible.
ALTER SESSION SET "_cursor_plan_unparse_enabled"=TRUE;
----------
VAR a number
VAR c char
EXECUTE :a := 9000
EXECUTE :c := 'j. random hacker'
set termout off;
--------------------------------------------------
--------------------------------------------------
SELECT /*+ gather_plan_statistics */ *
from EMP
where NAME = :b
and SALARY < :a
;
--------------------------------------------------
--------------------------------------------------
set termout on;
select * from table(
dbms_xplan.display_cursor(null,null,'COST IOSTATS LAST')
);
For cases in which you have to see an execution plan for a running statement that’s in the cursor cache (fast-changing situation, vast amount of binds, temp tables, anything that prevents you from running the statement manually), consider doing it this way:
SELECT * FROM table(
DBMS_XPLAN.DISPLAY_CURSOR(
'&&SQL_ID',&&CHILD_NUMBER,'COST,IOSTATS,LAST'
)
);
The &&SQL_ID is the SQL_ID form V$SQL, and &&CHILD_NUMBER is the CHILD_NUMBER from all V$SQL_* views in the system. The child number is especially useful for anything related to adaptive cursor sharing in 11g+.
Regards
Martin