Oracle檢視執行計劃(六)
前面講了五種檢視Oracle目標SQL執行計劃的方法:
Oracle檢視執行計劃(一)
Oracle檢視執行計劃(二)
Oracle檢視執行計劃(三)
Oracle檢視執行計劃(四)
Oracle檢視執行計劃(五)
其中第一種和第二種在目標SQL使用了繫結變數的情況下,獲得的執行計劃有可能是不準的。
建立測試表:
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> select count(*) from test1;
COUNT(*)
----------
92110
建立索引:
SQL> create index idx_test1_id on test1(object_id);
Index created.
進行更新:
SQL> update test1 set object_id=1 where object_id<90000;
86864 rows updated.
SQL> commit;
Commit complete.
收集統計資訊:
SQL> exec dbms_stats.gather_table_stats('SYS','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
建立一個繫結變數並賦值:
SQL> var x number;
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
使用方法一獲取執行計劃:
SQL> explain plan for select count(*) from test1 where object_id = :x;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
14 rows selected.
使用方法二獲取執行計劃:
SQL> set autot traceonly
SQL> select count(*) from test1 where object_id=:x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
514 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 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 count(*) from test1 where object_id=:x;
COUNT(*)
----------
86864
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID drj4fkb9vj73m, child number 0
-------------------------------------
select count(*) from test1 where object_id=:x
Plan hash value: 746261920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST1_ID | 85968 | 335K| 86 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
可以看到方法一,方法二獲取的執行計劃與方法三不一致,說明在使用繫結變數的情況下,最好使用方法三來獲取準確的執行計劃。
Oracle檢視執行計劃(一)
Oracle檢視執行計劃(二)
Oracle檢視執行計劃(三)
Oracle檢視執行計劃(四)
Oracle檢視執行計劃(五)
其中第一種和第二種在目標SQL使用了繫結變數的情況下,獲得的執行計劃有可能是不準的。
建立測試表:
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> select count(*) from test1;
COUNT(*)
----------
92110
建立索引:
SQL> create index idx_test1_id on test1(object_id);
Index created.
進行更新:
SQL> update test1 set object_id=1 where object_id<90000;
86864 rows updated.
SQL> commit;
Commit complete.
收集統計資訊:
SQL> exec dbms_stats.gather_table_stats('SYS','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
建立一個繫結變數並賦值:
SQL> var x number;
SQL> exec :x := 1;
PL/SQL procedure successfully completed.
使用方法一獲取執行計劃:
SQL> explain plan for select count(*) from test1 where object_id = :x;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
14 rows selected.
使用方法二獲取執行計劃:
SQL> set autot traceonly
SQL> select count(*) from test1 where object_id=:x;
Execution Plan
----------------------------------------------------------
Plan hash value: 3237771381
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_TEST1_ID | 18 | 72 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=TO_NUMBER(:X))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
514 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
520 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 count(*) from test1 where object_id=:x;
COUNT(*)
----------
86864
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID drj4fkb9vj73m, child number 0
-------------------------------------
select count(*) from test1 where object_id=:x
Plan hash value: 746261920
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 86 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST1_ID | 85968 | 335K| 86 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "TEST1"@"SEL$1" ("TEST1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :X (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"=:X)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
49 rows selected.
可以看到方法一,方法二獲取的執行計劃與方法三不一致,說明在使用繫結變數的情況下,最好使用方法三來獲取準確的執行計劃。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28536251/viewspace-2139757/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle檢視執行計劃(五)Oracle
- Oracle檢視執行計劃(一)Oracle
- Oracle檢視執行計劃(二)Oracle
- Oracle檢視執行計劃(三)Oracle
- Oracle檢視執行計劃(四)Oracle
- ORACLE執行計劃的檢視Oracle
- oracle如何檢視執行計劃Oracle
- 檢視oracle執行計劃 - 轉Oracle
- Oracle檢視執行計劃的命令Oracle
- oracle檢視執行計劃的方法Oracle
- Oracle 檢視SQL的執行計劃OracleSQL
- Oracle檢視執行計劃常用方法Oracle
- 檢視執行計劃
- ORACLE資料庫檢視執行計劃Oracle資料庫
- 檢視ORACLE的實際執行計劃Oracle
- 檢視執行計劃(一)
- 檢視執行計劃(二)
- 檢視sql執行計劃SQL
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- oracle10g 檢視SQL執行計劃OracleSQL
- Oracle如何檢視真實執行計劃(一)Oracle
- 檢視歷史執行計劃
- 檢視執行計劃的方法
- 檢視 OceanBase 執行計劃
- 檢視Oracle SQL執行計劃方法比較、分析OracleSQL
- oracle dbms_xplan.display_cursor檢視執行計劃Oracle
- SQLPLUS檢視oracle sql執行計劃命令SQLOracle
- 使用10046事件檢視oracle執行計劃事件Oracle
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- Oracle 9i 開啟autotrace on 檢視執行計劃Oracle
- 如何檢視SQL的執行計劃SQL
- DBMS_XPLAN檢視執行計劃
- 檢視SQL的執行計劃方法SQL
- 使用PL/SQL檢視執行計劃SQL
- db2檢視執行計劃DB2
- 檢視sql執行計劃--set autotraceSQL
- TOAD檢視執行計劃表