欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息

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

創(chuàng)新互聯(lián)是一家業(yè)務(wù)范圍包括IDC托管業(yè)務(wù),雅安服務(wù)器托管、主機(jī)租用、主機(jī)托管，四川、重慶、廣東電信服務(wù)器租用,中國電信成都樞紐中心，成都網(wǎng)通服務(wù)器托管,成都服務(wù)器租用,業(yè)務(wù)范圍遍及中國大陸、港澳臺以及歐美等多個(gè)國家及地區(qū)的互聯(lián)網(wǎng)數(shù)據(jù)服務(wù)公司。

個(gè)人常用的獲取執(zhí)行計(jì)劃的方法
1.select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced'));  --只有執(zhí)行計(jì)劃
2.set autotrace traceonly exp  --只有執(zhí)行計(jì)劃
1與2的區(qū)別在于當(dāng)SQL有綁定變量時(shí)，1更準(zhǔn)確(畢竟sql先執(zhí)行再獲取的執(zhí)行計(jì)劃)，2可能不準(zhǔn)確，其他情況下兩者獲取的執(zhí)行計(jì)劃基本一樣



執(zhí)行計(jì)劃指標(biāo)和統(tǒng)計(jì)信息指標(biāo)值的總結(jié)：
1.sql執(zhí)行多次不改變arraysize的情況下，每次的consistent gets不會變
2.sql執(zhí)行一次改變一次arraysize的情況下，每次的consistent gets會變，但是v$sql_plan中記錄的CPU_COST并沒有改變，說明v$sql_plan.CPU_COST是CHILD_NUMBER對應(yīng)的SQL在第一次執(zhí)行時(shí)生成的
3.執(zhí)行計(jì)劃中的Cost (%CPU)等于v$sql_plan.COST而非v$sql_plan.CPU_COST
4.只是一個(gè)select(非select for update)時(shí)，db block gets是0
5.邏輯讀（consistent gets+db block gets）和每批次處理的數(shù)據(jù)行的大小是有一定關(guān)系的。每批次處理的數(shù)據(jù)行越大，則邏輯讀越小。所以減少邏輯讀的一個(gè)方法就是增加arraysize。
6.執(zhí)行計(jì)劃中的Cost (%CPU)只和按物理相關(guān)，所以修改arraysize是沒有辦法減少物理讀的，也就是Cost (%CPU)不變




統(tǒng)計(jì)信息各個(gè)指標(biāo)的解釋可以在官方文檔關(guān)于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產(chǎn)生的邏輯讀).
consistent gets:Number of times a consistent read was requested for a block(select產(chǎn)生的邏輯讀).
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的查詢結(jié)果的字節(jié)數(shù))
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(客戶端和數(shù)據(jù)庫服務(wù)器之間的交互次數(shù),與arraysize(行預(yù)取數(shù)量)和rows processed相關(guān),等于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的查詢結(jié)果的行數(shù))



看執(zhí)行計(jì)劃的方法：
1、從上往下，第一個(gè)沒有子節(jié)點(diǎn)的步驟先執(zhí)行
2、對于兄弟節(jié)點(diǎn)，運(yùn)用第一點(diǎn),即靠上的節(jié)點(diǎn)先執(zhí)行。
3、所有兄弟節(jié)點(diǎn)執(zhí)行完以后，執(zhí)行父節(jié)點(diǎn)。

執(zhí)行順序的原則是：由上至下找到第一個(gè)并列的兩列開始，從上至下，從右向左
由上至下：在執(zhí)行計(jì)劃中一般含有多個(gè)節(jié)點(diǎn)，相同級別(或并列)的節(jié)點(diǎn)，靠上的優(yōu)先執(zhí)行，靠下的后執(zhí)行
從右向左：在某個(gè)節(jié)點(diǎn)下還存在多個(gè)子節(jié)點(diǎn)，先從最靠右的子節(jié)點(diǎn)開始執(zhí)行。

數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息
執(zhí)行計(jì)劃順序?yàn)?、2、5、4、1、0

數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息
執(zhí)行計(jì)劃順序?yàn)?、5、4、2、6、1、0

數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息
執(zhí)行計(jì)劃順序?yàn)?、3、6、5、2、8、7、1、0

數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息
執(zhí)行計(jì)劃順序?yàn)?、5、6、4、1、0




四種獲取執(zhí)行計(jì)劃的方法
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、執(zhí)行sql
2、顯示sql的結(jié)果集
3、顯示執(zhí)行計(jì)劃
4、顯示sql執(zhí)行后的統(tǒng)計(jì)信息
-------------------------
set autotrace traceonly
or
set autotrace traceonly exp stat 
1、執(zhí)行sql
2、顯示執(zhí)行計(jì)劃
3、顯示sql執(zhí)行后的統(tǒng)計(jì)信息
-------------------------
set autotrace traceonly exp| explain
1、顯示執(zhí)行計(jì)劃(select不會執(zhí)行sql即V$SQL.EXECUTIONS不會增加，但是insert、update、delete會執(zhí)行SQL的即V$SQL.EXECUTIONS會增加)
-------------------------
set autotrace traceonly stat | statistics
1、執(zhí)行sql
2、顯示sql執(zhí)行后的統(tǒng)計(jì)信息

3.使用dbms_xplan.display_cursor
select * from table(dbms_xplan.display_cursor('v$sql.sql_id',v$sql.CHILD_NUMBER,'advanced'));
官方文檔對display_cursor這個(gè)函數(shù)的說明里面沒有advanced這個(gè)參數(shù)值，只有BASIC、TYPICAL、ALL這幾個(gè)，不過實(shí)踐中發(fā)現(xiàn)advanced這個(gè)參數(shù)值顯示的內(nèi)容比這幾個(gè)參數(shù)值顯示的都多

4.使用v$sql_plan
v$sql_plan也可以來查詢某個(gè)對象的SQL執(zhí)行計(jì)劃
SQL>select id,operation,options,object_name,object_owner from v$sql_plan where object_name='TABLE_NAME'





如下實(shí)驗(yàn)證明增加arraysize可以減少邏輯讀（其中Elapsed時(shí)間到達(dá)一定階段就不再減少了），但是物理讀不會減少即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>







如下證明執(zhí)行計(jì)劃中的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執(zhí)行多次不改變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>

以上是“數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

文章題目：數(shù)據(jù)庫中如何獲取方法、查看執(zhí)行順序、統(tǒng)計(jì)信息
網(wǎng)站鏈接：http://www.chinadenli.net/article16/iigsdg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、云服務(wù)器、全網(wǎng)營銷推廣、網(wǎng)站改版、商城網(wǎng)站、響應(yīng)式網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)