oracle performance tuning效能優化學習系列(四)_補

--查詢度量引數的含義
SQL> select * from v$metricname mt where mt.METRIC_NAME='Database CPU Time Ratio';

GROUP_ID GROUP_NAME                                                        METRIC_ID METRIC_NAME                                                      METRIC_UNIT
---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
         2 System Metrics Long Duration                                           2108 Database CPU Time Ratio                                          % Cpu/DB_Time
         3 System Metrics Short Duration                                          2108 Database CPU Time Ratio                                          % Cpu/DB_Time

SQL>

--上述的度量值>100%,因為是4個cpu，
SQL> select value/4 from v$sysmetric_history uh where uh.METRIC_NAME like '%Database CPU Time Ratio%' and uh.GROUP_ID=3;

   VALUE/4
----------
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
36.1781076
         0

13 rows selected

Virtual Memory Statistics
1,檢查系統是否存在分頁和交換活動；
2,這種問題在中間層計算機的共享伺服器應用更為嚴重；
在這裡會話狀態會持久化存在於多個使用者互動中；而完成狀態的資訊
並非完全釋放

3,v$osstat

disk i/0 statistics
1,最重要的統計資訊即當前的響應時間和磁碟佇列的長度；
2，這些統計可以判斷磁碟是否處理最佳；磁碟是否過載
3，每次磁碟資料塊的讀取的時間為 5-20 毫秒,這和具體的硬體有關
4，如果磁碟響應時間長於正常的範圍，這可能是效能更差或過載
5，如果磁碟佇列長度超過2,表明磁碟有潛在的瓶頸

oracle維護io呼叫的一系列io統計資訊，會根據如下維度自單一多多個
資料塊讀寫操作獲取統計資訊：
1,consumer group,v$iostat_consumer_group;(前提：開啟resource manager)
2,database file,v$iostat_file
3,database function（比如：lgwr and dbwr）,儲存在v$iostat_function;

--示例：
SQL> desc v$iostat_consumer_group;
Name                  Type   Nullable Default Comments
--------------------- ------ -------- ------- --------
CONSUMER_GROUP_ID     NUMBER Y
SMALL_READ_MEGABYTES NUMBER Y
SMALL_WRITE_MEGABYTES NUMBER Y
LARGE_READ_MEGABYTES NUMBER Y
LARGE_WRITE_MEGABYTES NUMBER Y
SMALL_READ_REQS       NUMBER Y
SMALL_WRITE_REQS      NUMBER Y
LARGE_READ_REQS       NUMBER Y
LARGE_WRITE_REQS      NUMBER Y
NUMBER_OF_WAITS       NUMBER Y
WAIT_TIME             NUMBER Y

SQL> desc v$iostat_file;
Name                    Type         Nullable Default Comments
----------------------- ------------ -------- ------- --------
FILE_NO                 NUMBER       Y
FILETYPE_ID             NUMBER       Y
FILETYPE_NAME           VARCHAR2(28) Y
SMALL_READ_MEGABYTES    NUMBER       Y
SMALL_WRITE_MEGABYTES   NUMBER       Y
LARGE_READ_MEGABYTES    NUMBER       Y
LARGE_WRITE_MEGABYTES   NUMBER       Y
SMALL_READ_REQS         NUMBER       Y
SMALL_WRITE_REQS        NUMBER       Y
SMALL_SYNC_READ_REQS    NUMBER       Y
LARGE_READ_REQS         NUMBER       Y
LARGE_WRITE_REQS        NUMBER       Y
SMALL_READ_SERVICETIME NUMBER       Y
SMALL_WRITE_SERVICETIME NUMBER       Y
SMALL_SYNC_READ_LATENCY NUMBER       Y
LARGE_READ_SERVICETIME NUMBER       Y
LARGE_WRITE_SERVICETIME NUMBER       Y
ASYNCH_IO               VARCHAR2(9) Y
ACCESS_METHOD           VARCHAR2(11) Y
RETRIES_ON_ERROR        NUMBER       Y

SQL> select * from v$iostat_file;

   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
         0           0 Other                                           0                     0                    0                     0              12               20                   12               0                0                     16                      93                      16                      0                       0 ASYNC_OFF OS_LIB                       0
         0           1 Control File                                  226                   152                    0                     0           14389             9716                14331               0                0                  39598                  133603                   39086                      0                       0 ASYNC_OFF                              0
         0           3 Log File                                        0                     3                    0                     4               0             1117                    0               0               11                      0                    2074                       0                      0                      46 ASYNC_OFF                              0
         0           4 Archive Log                                     0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0           9 Data File Backup                                0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0          10 Data File Incremental Backup                    0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0          11 Archive Log Backup                              0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0          12 Data File Copy                                  0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0          17 Flashback Log                                   0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         0          18 Data Pump Dump File                             0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_OFF                              0
         1           2 Data File                                      60                     3                   17                     0            6681              329                 8252              69                0                  91130                     983                   89926                    313                       0 ASYNC_ON OS_LIB                       0
         1           6 Temp File                                       0                     0                    0                     0               0                0                    0               0                0                      0                       0                       0                      0                       0 ASYNC_ON OS_LIB                       0
         2           2 Data File                                      17                    13                    3                     1            1556             1279                 1415              14                5                   8912                    5221                    8096                     61                      16 ASYNC_ON OS_LIB                       0
         2           6 Temp File                                       4                    11                    0                     0              30               94                    0               0                0                     63                     391                       0                      0                       0 ASYNC_ON OS_LIB                       0
         3           2 Data File                                       0                     4                    0                     2              47              425                   47               0               12                    639                    1509                     639                      0                      31 ASYNC_ON OS_LIB                       0
         4           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                     92                       0                      92                      0                       0 ASYNC_ON OS_LIB                       0
         5           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                    234                       0                     234                      0                       0 ASYNC_ON OS_LIB                       0
         6           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                    312                       0                     312                      0                       0 ASYNC_ON OS_LIB                       0
         7           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                    125                       0                     125                      0                       0 ASYNC_ON OS_LIB                       0
         8           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                    125                       0                     125                      0                       0 ASYNC_ON OS_LIB                       0

   FILE_NO FILETYPE_ID FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS SMALL_SYNC_READ_REQS LARGE_READ_REQS LARGE_WRITE_REQS SMALL_READ_SERVICETIME SMALL_WRITE_SERVICETIME SMALL_SYNC_READ_LATENCY LARGE_READ_SERVICETIME LARGE_WRITE_SERVICETIME ASYNCH_IO ACCESS_METHOD RETRIES_ON_ERROR
---------- ----------- ---------------------------- -------------------- --------------------- -------------------- --------------------- --------------- ---------------- -------------------- --------------- ---------------- ---------------------- ----------------------- ----------------------- ---------------------- ----------------------- --------- ------------- ----------------
         9           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                    124                       0                     124                      0                       0 ASYNC_ON OS_LIB                       0
        10           2 Data File                                       0                     0                    0                     0               7                2                    7               0                0                    266                       0                     266                      0                       0 ASYNC_ON OS_LIB                       0
        11           2 Data File                                       0                     0                    0                     0               5                0                    5               0                0                     93                       0                      93                      0                       0 ASYNC_ON OS_LIB                       0
        12           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                    141                       0                     141                      0                       0 ASYNC_ON OS_LIB                       0
        13           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     62                       0                      62                      0                       0 ASYNC_ON OS_LIB                       0
        14           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     48                       0                      48                      0                       0 ASYNC_ON OS_LIB                       0
        15           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     61                       0                      61                      0                       0 ASYNC_ON OS_LIB                       0
        16           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     63                       0                      63                      0                       0 ASYNC_ON OS_LIB                       0
        17           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     63                       0                      63                      0                       0 ASYNC_ON OS_LIB                       0
        18           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     62                       0                      62                      0                       0 ASYNC_ON OS_LIB                       0
        19           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     31                       0                      31                      0                       0 ASYNC_ON OS_LIB                       0
        20           2 Data File                                       0                     0                    0                     0               4                0                    4               0                0                     32                       0                      32                      0                       0 ASYNC_ON OS_LIB                       0

32 rows selected

SQL> desc v$iostat_function;
Name                  Type         Nullable Default Comments
--------------------- ------------ -------- ------- --------
FUNCTION_ID           NUMBER       Y
FUNCTION_NAME         VARCHAR2(18) Y
SMALL_READ_MEGABYTES NUMBER       Y
SMALL_WRITE_MEGABYTES NUMBER       Y
LARGE_READ_MEGABYTES NUMBER       Y
LARGE_WRITE_MEGABYTES NUMBER       Y
SMALL_READ_REQS       NUMBER       Y
SMALL_WRITE_REQS      NUMBER       Y
LARGE_READ_REQS       NUMBER       Y
LARGE_WRITE_REQS      NUMBER       Y
NUMBER_OF_WAITS       NUMBER       Y
WAIT_TIME             NUMBER       Y

SQL> select * from v$iostat_function;

FUNCTION_ID FUNCTION_NAME      SMALL_READ_MEGABYTES SMALL_WRITE_MEGABYTES LARGE_READ_MEGABYTES LARGE_WRITE_MEGABYTES SMALL_READ_REQS SMALL_WRITE_REQS LARGE_READ_REQS LARGE_WRITE_REQS NUMBER_OF_WAITS WAIT_TIME
----------- ------------------ -------------------- --------------------- -------------------- --------------------- --------------- ---------------- --------------- ---------------- --------------- ----------
          0 RMAN                                  0                     0                    0                     0               0                0               0                0               0          0
          4 XDB                                   0                     0                    0                     0               0                0               0                0               0          0
          6 Data Pump                             0                     0                    0                     0               0                0               0                0               0          0
          9 Direct Reads                          4                     0                    0                     0              30                0               0                0               0          0
         10 Direct Writes                         0                    11                    0                     0               0              103               0                0               0          0
          8 Buffer Cache Reads                   77                     0                   21                     0            8202                0              83                0            6425      37132
          2 LGWR                                  0                     3                    0                     4               0             1117               0               11               0          0
          3 ARCH                                  0                     0                    0                     0               0                0               0                0               0          0
         13 Others                              227                   152                    0                     0           14541             9787               0                0           19435     101387
          1 DBWR                                  0                    20                    0                     2               0             2007               0               17               0          0
          5 Streams AQ                            1                     0                    0                     0              68                0               0                0              68        421
         11 Smart Scan                            0                     0                    0                     0               0                0               0                0               0          0
          7 Recovery                              0                     0                    0                     0               0                0               0                0               0          0
         12 Archive Manager                       0                     0                    0                     0               0                0               0                0               0          0

14 rows selected

SQL>



Network Statistics
--網路的延遲也是響應時間組成的一部分；
SQL> desc v$iostat_network;
Name           Type         Nullable Default Comments
-------------- ------------ -------- ------- --------
CLIENT         VARCHAR2(32) Y
READS#         NUMBER       Y
WRITES#        NUMBER       Y
KBYTES_READ    NUMBER       Y
KBYTES_WRITTEN NUMBER       Y
READ_LATENCY   NUMBER       Y
WRITE_LATENCY NUMBER       Y

SQL> select * from v$iostat_network;

CLIENT                               READS#    WRITES# KBYTES_READ KBYTES_WRITTEN READ_LATENCY WRITE_LATENCY
-------------------------------- ---------- ---------- ----------- -------------- ------------ -------------
RMAN                                      0          0           0              0            0             0
PLSQL                                     0          0           0              0            0             0

如何理解或使用這些統計資訊：
1，檢視或計算命中率：比如buffer hit,latch hit,soft parse hit ratio;可採用v$sysstat
2,如配置timed_statistitics=true,則採集等待事件的等待時間
3,如果在取樣2個小時等待時間佔用比例很高，則需要進一步分析等待事件，如等待時間佔取樣時間很小；則沒有必要再分析
3，配置引數 statistics_level=typical or all，自動收集基於時間的統計；
如引數值為basic,必須配置timed_statistics=true;
4,statistics_level=basic禁用一些自動化特性，這不受推薦
5,綜合比較各個統計資訊，即評估一個統計指標也要考慮下相關的指標；比如使用者負荷和硬體容量；45分的取樣產生了30分的等待；
並不表明系統出現了效能問題；因為系統有2000多個使用者；主機執行於64節點

6,處理未配置timed_statiscs=false的等待事件；按照等待次數逆向排序；
注意：如某事件的總等待次數很大，未必有問題；比如總的等待時間很短；所以要看等待時間每次等待的時間；
相反；如果某等待事件的總等待次數很小，也可能出現效能問題；它佔用系統總等待時間的大部分比例；
所以如沒有等待時間每次等待時間很難判斷
7，空等待事件：
   標明oracle程式正處於空閒狀態
8，計算性的統計
   比如：比率，多個事務的一些統計值等；交叉與其它實際的統計值參考驗證相當重要；以驗證些統計資訊是否值得分析；
   例：soft parse ratio為50%,可能表明系統出問題了；但可能取樣期間僅僅有一次硬解析和一次軟解析；所以為50%;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-755736/，如需轉載，請註明出處，否則將追究法律責任。

oracle performance tuning效能優化學習系列(四)_補

相關文章