數據庫中如何獲取方法、查看執行順序、統計信息

小編給大家分享一下數據庫中如何獲取方法、查看執行順序、統計信息，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

個人常用的獲取執行計劃的方法
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>

以上是“數據庫中如何獲取方法、查看執行順序、統計信息”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！