EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans [ID 1268111.1] |
Applies to:
Oracle Server - Enterprise Edition - Version 8.0.3.0 and laterInformation in this document applies to any platform.
Symptoms
"Explain Plan" and SQL*PLUS AUTOTRACE may not generate an actual plan for a query with bind variables.
Cause
1. Bind Peeking (9.2 and later):
A real execution peeks bind values during the hard parse. "Explain Plan" and SQL*PLUS AUTOTRACE do NOT peek bind values when they generate plans.
2. Data Type Conversion:
For example, the type of a character column is converted when it is compared with a numeric value. In this case, the index on the column cannot be used. But, "Explain Plan" and SQL*PLUS AUTOTRACE do NOT consider data type conversion.
Solution
Use SQL TRACE (10046 trace) to get correct plans.
If you know sql_id of the query, you can get correct plans using V$SQL_PLAN or DBMS_XPLAN.DISPLAY_CURSOR.
EXAMPLE:
1. Setup a testcase:
drop table test; create table test (a number(5), b varchar2(5)); declare cnt number(5) := 1; begin loop insert into test values(1,1); if cnt=10000 then exit; end if; cnt:=cnt+1; end loop; insert into test values (2,2); end; / create index test_ix_a on test(a); create index test_ix_b on test(b); exec dbms_stats.gather_table_stats(user, 'test', estimate_percent=>100, method_opt =>'for all columns size 100'); select a, count(*) from test group by a; A COUNT(*) ---------- ---------- 1 10000 2 1 select b, count(*) from test group by b; B COUNT(*) ----- ---------- 1 10000 2 1 |
alter session set optimizer_mode=all_rows; var v1 number; exec :v1 := 2; explain plan for select * from test where a = :v1; SQL> @?/rdbms/admin/utlxpls -------------------------- SELECT STATEMENT TABLE ACCESS FULL TEST -------------------------- alter session set sql_trace=true; var v2 number; exec :v2 := 2; select * from test where a = :v2; alter session set sql_trace=false; Rows Row Source Operation ------- --------------------------------- 1 TABLE ACCESS BY INDEX ROWID TEST 1 INDEX RANGE SCAN TEST_IX_A |
alter session set optimizer_mode=first_rows; var v1 number; exec :v1 := 2; explain plan for select * from test where b = :v1; SQL> @?/rdbms/admin/utlxpls ----------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID TEST INDEX RANGE SCAN TEST_IX_B ----------------------------------- alter session set sql_trace=true; var v2 number; exec :v2 := 2; select * from test where b = :v2; alter session set sql_trace=false; Rows Row Source Operation ------- ------------------------- 1 TABLE ACCESS FULL TEST |
If you know sql_id or the set of (hash_value, address) of the shared cursor, you can get the correct plan using V$SQL_PLAN or DBMS_XPLAN.DISPLAY_CURSOR. Make sure the calling user has privileges on fixed following views: V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN.
-- For 9i and later
col operation for a20 col object_name for a20 col options for a20 col optimizer for a12 col child_number a3 SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost FROM V$SQL_PLAN WHERE hash_value = &hash_value AND address = '&address' ORDER BY child_number, id; |
col operation for a20 col object_name for a20 col options for a20 col optimizer for a12 col child_number a3 SELECT child_number, id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost FROM V$SQL_PLAN WHERE sql_id = '&sql_id' ORDER BY child_number, id; select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL')); |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9390331/viewspace-749894/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9390331/viewspace-749894/