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博客

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

EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans [ID 1268111.1]

Applies to:

Symptoms

Cause

Solution

最新文章