潛水珽的異想世界

關於SQL的統計資訊，分為6塊來統計排序：
ordered by buffer gets
ordered by Physical reads
ordered by Executions
ordered by Parse Calls
ordered by Sharable Memory
ordered by Version Count

這一部分，通過Buffer Gets對SQL語句進行排序，即通過它執行了多少個邏輯I/O來排序。頂端的注釋表明一個PL/SQL單元的緩存獲得(Buffer Gets)包括被這個代碼塊執行的所有SQL語句的Buffer Gets。因此將經常在這個列表的頂端看到PL/SQL過程，因為存儲過程執行的單獨的語句的數目被總計出來。

在這裏的Buffer Gets是一個累積值，所以這個值大並不一定意味著這條語句的性能存在問題。通常我們可以通過對比該條語句的Buffer Gets和physical reads值，如果這兩個比較接近，肯定這條語句是存在問題的，我們可以通過執行計畫來分析，為什麼physical reads的值如此之高。另外，我們在這裏也可以關注gets per exec的值，這個值如果太大，表明這條語句可能使用了一個比較差的索引或者使用了不當的表連接。

另外說明一點：大量的邏輯讀往往伴隨著較高的CPU消耗。所以很多時候我們看到的系統CPU將近100％的時候，很多時候就是SQL語句造成的，這時候我們可以分析一下這裏邏輯讀大的SQL。

SQL ordered by Gets for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Buffer Gets Threshold:     10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100
                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     13,367,435          171       78,172.1   68.3   259.36    353.19 3790040751
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN P_DXH_DEALOVERTIMEDXHREC; :mydate
:= next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END
……

          -------------------------------------------------------------
SQL ordered by Reads for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Disk Reads Threshold:      1000

這部分通過物理讀對SQL語句進行排序。這顯示引起大部分對這個系統進行讀取活動的SQL，即物理I/O。當我們的系統如果存在I/O瓶頸時，需要關注這裏I/O操作比較多的語句。

                                                     CPU      Elapsd
Physical Reads  Executions  Reads per Exec %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
          4,187           24          174.5   15.8     0.79     52.99 1895519470
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN p_dxh_tmp_importUserInfo2(500); :
mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END I
F; END;
            538       21,504            0.0    2.0     5.92    241.61 1725988165
Module: Das.exe
begin P_DXH_AddSms(I_CALLERNO=>:V001,I_CALLEENO=>:V002,I_CALLTY
PE=>:V003,I_DXHHFLAG=>:V004,O_RET=>:V005);end;
……
          -------------------------------------------------------------
SQL ordered by Executions for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Executions Threshold:       100

這部分告訴我們在這段時間中執行次數最多的SQL語句。為了隔離某些頻繁執行的查詢，以觀察是否有某些更改邏輯的方法以避免必須如此頻繁的執行這些查詢， 這可能是很有用的。或許一個查詢正在一個迴圈的內部執行，而且它可能在迴圈的外部執行一次，可以設計簡單的演算法更改以減少必須執行這個查詢的次數。即使它 運行的飛快，任何被執行幾百萬次的操作都將開始耗盡大量的時間。

                                                CPU per    Elap per
Executions   Rows Processed   Rows per Exec    Exec (s)   Exec (s)  Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
     102,491               0              0.0       0.00        0.00 1053795750
Module: Das.exe
COMMIT

      48,861          38,275              0.8       0.00        0.00  947217968
Module: Das.exe
SELECT T.AREAID FROM T_DXH_MOBILE S, T_DXH_AREA T WHERE S.MOBILE
SEGMENT = SUBSTR(:B1 ,1,7) AND T.AREACODE = S.AREACODE AND ROWNU
M = 1

          -------------------------------------------------------------

在這一部分，主要顯示PARSE與EXECUTIONS的對比情況。如果PARSE/EXECUTIONS>1，往往說明這個語句可能存在問題：沒 有使用綁定變數，共用池設置太小，cursor_sharing被設置為exact，沒有設置session_cached_cursors等等問題。

SQL ordered by Parse Calls for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Parse Calls Threshold:      1000

                           % Total
Parse Calls  Executions   Parses  Hash Value
------------ ------------ -------- ----------
      61,404       30,650    32.06 3303409220
Module: SvcProcessor.exe
begin P_DXH_UPDATESUBMITSTATUS(:V00001,:V00002,:V00003,:V00004);
end;
       1,661        1,661     0.87  140223014
Module: SvcProcessor.exe
SELECT SERIALNO, PID, SERVICEID, SMSCONTENT, REPORTFLAG, ORGADDR
, DESTADDR, FEEADDR, FEETYPE, FEEUSERTYPE, FEECODE, SPID FROM T_
DXH_OPENDETECT WHERE LOCKFLAG = :B1

          -------------------------------------------------------------

在這一部分，主要是針對shared memory佔用的情況進行排序。

SQL ordered by Sharable Memory for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Sharable Memory Threshold:   1048576

Sharable Mem (b)  Executions  % Total  Hash Value
---------------- ------------ ------- ------------
       1,115,384       15,112     0.2   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))

          -------------------------------------------------------------

在這一部分，主要是針對SQL語句的多版本進行排序。相同的SQL文本，但是不同屬性，比如物件owner不同，會話優化模式不同、類型不同、長度不同和綁定變數不同等等的語句，他們是不能共用的，所以再緩存中會存在多個不同的版本。這當然就造成了資源上的更多的消耗。


SQL ordered by Version Count for DB: ORA92  Instance: ora92  Snaps: 13 -14
-> End Version Count Threshold:        20

Version
   Count  Executions   Hash Value
-------- ------------ ------------
      30       15,112   3531895589
Module: Das.exe
INSERT INTO T_DXH_DXHRECLOG (CALLERNO, CALLEENO, NOTIFYFLAG, SMS
TYPE, AREAID, LOGDATE) VALUES (:B4 , :B3 , 1, :B2 , :B1 , TO_CHA
R(SYSDATE, 'MMDD'))

小結：
對於出現在上面的可疑的sql語句，我們可以查看語句相關的執行計畫，然後分析相關索引等是否合理。
通過語句查看執行計畫的方法:

SELECT id,parent_id,LPAD(' ',4*(LEVEL-1))||operation||' '||options||' '||object_name "Execution plan" ,cost,cardinality,bytes
FROM (
SELECT p.* FROM v$sql_plan p,v$sql s WHERE p.address = s.ADDRESS
AND p.hash_value = s.HASH_VALUE
and p.hash_value = '&hash_value'
)
CONNECT BY PRIOR id = parent_id
START WITH id = 0;

    查看、分析、優化索引等在這裏就不再一一描述了。