EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans

victorymoshui發表於2012-11-25
AISQL


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 later
Information 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

2. Unreal plans due to Disabled Bind Peeking:
  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


3. Unreal plans due to Data Type Conversion:
  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


4. V$SQL_PLAN, DBMS_XPLAN.DISPLAY_CURSOR:


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;


-- For 10g 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 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章