個人常用的獲取執行計劃的方法
1.select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced')); --只有執行計劃
2.set autotrace traceonly exp --只有執行計劃
1與2的區別在於當SQL有繫結變數時，1更準確(畢竟sql先執行再獲取的執行計劃)，2可能不準確，其他情況下兩者獲取的執行計劃基本一樣

執行計劃指標和統計資訊指標值的總結：
1.sql執行多次不改變arraysize的情況下，每次的consistent gets不會變
2.sql執行一次改變一次arraysize的情況下，每次的consistent gets會變，但是v$sql_plan中記錄的CPU_COST並沒有改變，說明v$sql_plan.CPU_COST是CHILD_NUMBER對應的SQL在第一次執行時生成的
3.執行計劃中的Cost (%CPU)等於v$sql_plan.COST而非v$sql_plan.CPU_COST
4.只是一個select(非select for update)時，db block gets是0
5.邏輯讀（consistent gets+db block gets）和每批次處理的資料行的大小是有一定關係的。每批次處理的資料行越大，則邏輯讀越小。所以減少邏輯讀的一個方法就是增加arraysize。
6.執行計劃中的Cost (%CPU)只和按物理相關，所以修改arraysize是沒有辦法減少物理讀的，也就是Cost (%CPU)不變

統計資訊各個指標的解釋可以在官方文件關於Statistics Descriptions中找到
recursive calls:Number of recursive calls generated at both the user and system level
db block gets:Number of times a CURRENT block was requested(DML產生的邏輯讀).
consistent gets:Number of times a consistent read was requested for a block(select產生的邏輯讀).
physical reads:Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache（物理讀）.
redo size:Total amount of redo generated in bytes
bytes sent via SQL*Net to client:Total number of bytes sent to the client from the foreground processes(可以理解為sql的查詢結果的位元組數)
bytes received via SQL*Net from client:Total number of bytes received from the client over Oracle Net Services
SQL*Net roundtrips to/from client:Total number of Oracle Net Services messages sent to and received from the client(客戶端和資料庫伺服器之間的互動次數,與arraysize(行預取數量)和rows processed相關,等於rows processed/arraysize)
sorts (memory):Number of sort operations that were performed completely in memory and did not require any disk writes
sorts (disk):Number of sort operations that required at least one disk write
rows processed:Total number of rows that the parsed SQL statement returns(可以理解為sql的查詢結果的行數)

看執行計劃的方法：
1、從上往下，第一個沒有子節點的步驟先執行
2、對於兄弟節點，運用第一點,即靠上的節點先執行。
3、所有兄弟節點執行完以後，執行父節點。

執行順序的原則是：由上至下找到第一個並列的兩列開始，從上至下，從右向左
由上至下：在執行計劃中一般含有多個節點，相同級別(或並列)的節點，靠上的優先執行，靠下的後執行
從右向左：在某個節點下還存在多個子節點，先從最靠右的子節點開始執行。

執行計劃順序為3、2、5、4、1、0

執行計劃順序為3、5、4、2、6、1、0

執行計劃順序為4、3、6、5、2、8、7、1、0

執行計劃順序為3、5、6、4、1、0

四種獲取執行計劃的方法
1.使用explain plan for和DBMS_XPLAN.DISPLAY
SQL> explain plan for select * from te123;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
或
SQL> SELECT id,operation,options,object_name,position from plan_table

2.使用autotrace
set autotrace on
1、執行sql
2、顯示sql的結果集
3、顯示執行計劃
4、顯示sql執行後的統計資訊
-------------------------
set autotrace traceonly
or
set autotrace traceonly exp stat
1、執行sql
2、顯示執行計劃
3、顯示sql執行後的統計資訊
-------------------------
set autotrace traceonly exp | explain
1、顯示執行計劃(select不會執行sql即V$SQL.EXECUTIONS不會增加，但是insert、update、delete會執行SQL的即V$SQL.EXECUTIONS會增加)
-------------------------
set autotrace traceonly stat | statistics
1、執行sql
2、顯示sql執行後的統計資訊

3.使用dbms_xplan.display_cursor
select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced'));
官方文件對display_cursor這個函式的說明裡面沒有advanced這個引數值，只有BASIC、TYPICAL、ALL這幾個，不過實踐中發現advanced這個引數值顯示的內容比這幾個引數值顯示的都多

4.使用v$sql_plan
v$sql_plan也可以來查詢某個物件的SQL執行計劃
SQL>select id,operation,options,object_name,object_owner from v$sql_plan where object_name='TABLE_NAME'

如下實驗證明增加arraysize可以減少邏輯讀（其中Elapsed時間到達一定階段就不再減少了），但是物理讀不會減少即Cost (%CPU)不變
SQL> set timing on
SQL> set arraysize 15
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:11.22
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 512K| 41M| 1785 (1)| 00:00:22 |
| 1 | TABLE ACCESS FULL| T1 | 512K| 41M| 1785 (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
93297 consistent gets
12933 physical reads
0 redo size
96936855 bytes sent via SQL*Net to client
751345 bytes received via SQL*Net from client
68268 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024000 rows processed

SQL> set arraysize 1500
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:07.43
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 512K| 41M| 1785 (1)| 00:00:22 |
| 1 | TABLE ACCESS FULL| T1 | 512K| 41M| 1785 (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26518 consistent gets
12933 physical reads
0 redo size
88150935 bytes sent via SQL*Net to client
7921 bytes received via SQL*Net from client
684 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024000 rows processed

SQL> set arraysize 5000
SQL> select * from t1;
1024000 rows selected.
Elapsed: 00:00:07.65
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 512K| 41M| 1785 (1)| 00:00:22 |
| 1 | TABLE ACCESS FULL| T1 | 512K| 41M| 1785 (1)| 00:00:22 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26043 consistent gets
12933 physical reads
0 redo size
88088795 bytes sent via SQL*Net to client
2663 bytes received via SQL*Net from client
206 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1024000 rows processed
SQL>

如下證明執行計劃中的Cost (%CPU)等於v$sql_plan.COST
SQL> set autotrace traceonly
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1600 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RECORDLIST | 10 | 1600 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_RECORDLIST_MAINID | 5778 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
69335 consistent gets
13843 physical reads
0 redo size
20229455 bytes sent via SQL*Net to client
308282 bytes received via SQL*Net from client
27980 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
419680 rows processed
SQL>

SQL> select sql_id,OPERATION,OPTIONS,OBJECT_NAME,OPTIMIZER,ID,COST,CARDINALITY,BYTES,CPU_COST,IO_COST from v$sql_plan where sql_id='cndu66r2wpa63' and CHILD_NUMBER=0;
SQL_ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER ID COST CARDINALITY BYTES CPU_COST IO_COST
--------------- ---------------- --------------- -------------------- ---------- --- ---- ----------- ------- -------- -------
cndu66r2wpa63 SELECT STATEMENT FIRST_ROWS 0 5
cndu66r2wpa63 TABLE ACCESS BY INDEX ROWID RECORDLIST 1 5 10 1600 42307 5
cndu66r2wpa63 INDEX RANGE SCAN IX_RECORDLIST_MAINID 2 4 5778 30486 4

如下，sql執行多次不改變arraysize的情況下，每次的consistent gets不會變
SQL> set timing on
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Elapsed: 00:00:02.30
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1600 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RECORDLIST | 10 | 1600 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_RECORDLIST_MAINID | 5778 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69335 consistent gets
0 physical reads
0 redo size
20229455 bytes sent via SQL*Net to client
308282 bytes received via SQL*Net from client
27980 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
419680 rows processed

SQL>
SQL>
SQL>
SQL> select IMEID1,IMEID2,IMEID3,IMEID4,PALLET,SPARE4 from recordlist where MAINID<100000000;
419680 rows selected.
Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
Plan hash value: 477921739
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1600 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RECORDLIST | 10 | 1600 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_RECORDLIST_MAINID | 5778 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MAINID"<100000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69335 consistent gets
0 physical reads
0 redo size
20229455 bytes sent via SQL*Net to client
308282 bytes received via SQL*Net from client
27980 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
419680 rows processed

SQL>

執行計劃__獲取方法、檢視執行順序、統計資訊詳解

相關文章