快速得到SQL帶A-Time時間的執行計劃的小技巧

快速得到SQL帶A-Time時間的執行計劃的小技巧

使用spool把結果輸出到文件，然后直接去文件查看執行計劃即可

得到帶時間的執行計劃的方法有兩種：

1、在會話設置參數statistics_level=all

alter session set statistics_level=all;
執行SQL
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

2、在語句中使用hint（/*+ gather_plan_statistics */），然后查出這個SQL的SQL_ID，然后再去查詢執行計劃

執行SQL
select /*+ gather_plan_statistics */ count(*) from test;
查詢SQL的SQL_ID
select sql_id,sql_text from v$sql where sql_text like '%/*+ gather_plan_statistics */%';
查看執行計劃
select * from table(dbms_xplan.display_cursor('sql_id',null,'allstats'));

以上的兩種方式都可以得到帶時間的執行計劃。

但是用這樣的方式去查看執行計劃，有一個弊端：必須等到SQL語句的全部的記錄在屏幕輸出，然后才可以查看執行計劃。

為了不需要等待SQL執行完，然后才可以查看執行計劃；以及不需要在屏幕輸出SQL的執行結果刷屏，

可以采用把SQL的結果以及執行計劃輸出到文件，然后去文件查看執行計劃。

！！！注意：！！！

使用spool把結果輸出到文件，是把查詢結果輸出到文件，包含真實的數據，因此需要慎重考慮，

是否可以采取這樣的方式進行查詢。

測試：

需要查看一條SQL的帶時間的執行計劃

方法1、

設置會話參數
alter session set statistics_level=all;
執行SQL
select /*+ index(t1 idx_t1_c1) */* from t1 where c1 <= 90000;
需要等待執行完SQL，然后才可以查詢
查看執行計劃
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

方法2、

執行帶hint的SQL
select /*+ gather_plan_statistics index(t1 idx_t1_c1) */* from t1 where c1 <= 90000;
需要等待執行完SQL
查詢該SQL對應的SQL_ID
select sql_id,sql_text from v$sql where sql_text like '%/*+ gather_plan_statistics */%';
查看執行計劃（根據這條SQL的SQL_ID查詢）
select * from table(dbms_xplan.display_cursor('sql_id',null,'allstats'));

方法3、

先編寫腳本

vi test.sql
set line 200 pages 999
set timing on
set termout off
spool /home/oracle/test.txt
alter session set statistics_level=all;
select /*+ index(t1 idx_t1_c1) */* from t1 where c1 <= 90000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
spool off
-----------
參數解釋：
set line 200 pages 999  設置輸出格式
set timing on設置時間
set termout off設置不讓腳本的內容在屏幕輸出
spool /home/oracle/test.txt 打開spool；并且設置輸出文件的位置以及名稱
spool off關閉spool，否則會一直把sqlplus里的內容寫道test.txt文件中
登錄到sqlplus
sqlplus / as sysdba
(或者執行該SQL的用戶)
執行該腳本
@/home/oracle/test.txt
查看文件中的執行計劃
90000 rows selected.
Elapsed: 00:00:25.90
PLAN_TABLE_OUTPUT                                                                                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  caumajpb0jz24, child number 1                                                                                                                                                  
-------------------------------------                                                                                                                                                  
select /*+ index(t1 idx_t1_c1) */* from t1 where c1 <= 90000                                                                                                                           
Plan hash value: 4200789634                                                                                                                                                            
------------------------------------------------------------------------------------------------------------                                                                           
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                                                           
------------------------------------------------------------------------------------------------------------                                                                           
|   0 | SELECT STATEMENT            |           |      1 |        |  90000 |00:00:14.07 |     456K|    315K|                                                                           
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |      1 |  90001 |  90000 |00:00:14.07 |     456K|    315K|                                                                           
|*  2 |   INDEX RANGE SCAN          | IDX_T1_C1 |      1 |  90001 |  90000 |00:00:00.08 |    6187 |      0 |                                                                           
------------------------------------------------------------------------------------------------------------                                                                           
Predicate Information (identified by operation id):                                                                                                                                    
---------------------------------------------------                                                                                                                                    
   2 - access("C1"<=90000)                                                                                                                                                             
19 rows selected.
Elapsed: 00:00:00.08

等執行完畢去查看該文件即可，在文件最低端可以看到執行計劃，省去了中間刷屏的時間，

但是產生了一個帶有數據的文件，當記錄很多時，最好不要使用，

此方法只作為小技巧，需要看具體情況去判斷是否使用該方法。