自己经常在优化sql语句是经常使用set autotrace traceonly来优化sql语句.这样的好处是如果输出记录很多,可以不受干扰,直接查看执行计划以及执行计划的统计信息.但是在一些特殊情况要注意也许执行计划是不真实的.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1.建立测试表:
SQL> create table t2 (id number, name varchar2(100)) ;
Table created.
SQL> create index i_t2_id on t2(id);
Index created.
2.测试1:
SQL> insert into t2 select rownum id ,'test2' from dual connect by level 10000 rows created.
SQL> commit ;
Commit complete.
SQL> set autot traceonly ;
SQL> select * from t2 where id=45;
Execution Plan
----------------------------------------------------------
Plan hash value: 3661687773
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 65 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=45)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
596 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autot off
SQL> select * from t2 where id=45;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 03dqfy40b7t8r, child number 0
-------------------------------------
select * from t2 where id=45
Plan hash value: 3661687773
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=45)
Note
-----
- dynamic sampling used for this statement (level=2)
--我没有分析所以,采用的是dynamic sampling.
3.测试2:
SQL> delete from t2;
10000 rows deleted.
SQL> insert into t2 select 45 ,'test2' from dual connect by level 10000 rows created.
SQL> commit ;
SQL> set autot traceonly ;
SQL> select * from t2 where id=45;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 634K| 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 10000 | 634K| 8 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=45)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
183 consistent gets
0 physical reads
2224 redo size
149746 bytes sent via SQL*Net to client
1058 bytes received via SQL*Net from client
51 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
--注意使用set autot traceonly 看到的是全表扫描.
SQL> set autot off
SQL> select * from t2 where id=45;
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 03dqfy40b7t8r, child number 0
-------------------------------------
select * from t2 where id=45
Plan hash value: 3661687773
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=45)
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
26 rows selected.
实际上真正的执行计划是INDEX RANGE SCAN!
当然这个仅仅是特例!!像使用explain plan for和dbms_xplan.display也是一样的问题,自己在工作要注意这些细节.
SQL> explain plan for select * from t2 where id=45;
Explained.
SQL> select * from table(dbms_xplan.display(NULL,NULL,'ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 1513984157
-------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 10000 |
|* 1 | TABLE ACCESS FULL| T2 | 10000 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=45)
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
20 rows selected.