昨天看别人调优,我跟对方讲使用SET Autotrace看执行计划可能不真实的.
自己做一个简单的例子来说明:
1.建立测试环境:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t pctfree 99 as select rownum id ,'test' name from dual connect by levelcreate index i_t_id on t(id);
exec dbms_stats.gather_table_stats(user, 'T', method_opt=>'for all columns size 1 ',no_invalidate => false);
variable n number;
exec :n := 10;
select * from t where id>=:n;
2.测试:
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 71 (100)|
|* 1 | TABLE ACCESS FULL| T | 992 | 71 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 10
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">=:N)
--因为n=10,很小,oracle 的绑定变量peek发现选择全表扫描更加合理.
SCOTT@test> set autotrace traceonly
SCOTT@test> select * from t where id>=:n;
991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 450 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 450 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=TO_NUMBER(:N))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
250 consistent gets
0 physical reads
0 redo size
10185 bytes sent via SQL*Net to client
563 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
991 rows processed
3.突然想起以前遇到的问题,set autotrace traceonly explain问题,这个还可能导致执行计划的改变.
http://blog.itpub.net/267265/viewspace-716004/
SCOTT@test> alter system flush shared_pool;
System altered.
SCOTT@test> select * from v$sql where sql_id='0ck6kww1pfuw8';
no rows selected
SCOTT@test> set autotrace traceonly explain
SCOTT@test> select * from t where id>=:n;
Execution Plan
---------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 450 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 450 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 9 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=TO_NUMBER(:N))
SCOTT@test> set autotrace off
SCOTT@test> select sql_id,child_number,sql_text from v$sql where sql_id='0ck6kww1pfuw8';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------
0ck6kww1pfuw8 0 select * from t where id>=:n
--可以发现已经生成了执行计划.
SCOTT@test> select * from t where id>=:n;
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0ck6kww1pfuw8, child number 0
-------------------------------------
select * from t where id>=:n
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 5 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 50 | 5 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 9 | 2 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=:N)
--可以发现执行计划改变.
总结:
--可以发现看到的执行计划选择索引,而实际上根本不是这么回事.实际上如果你使用 explain plan for...,在使用
--select * from table(dbms_xplan.display());查询也是一样的问题.
--总之10g以后看执行计划最好使用dbms_xplan.display_cursor来看.或者不使用参数看执行计划,也许这样会准一点.