[20220822]奇怪的ashtop輸出.txt

lfree發表於2022-10-10

[20220822]奇怪的ashtop輸出.txt

--//晚上看了TPT的一些blog,連結：
http://blog.tanelpoder.com/2013/09/11/advanced-oracle-troubleshooting-guide-part-11-complex-wait-chain-signature-analysis-with-ash_wait_chains-sql

--//他執行的dashtop指令碼輸出很特殊，多了許多欄位。比如CPU User I/O Application Concurrency之類的資訊，如下：
SQL> @ash/dashtop session_state,event 1=1 "TIMESTAMP'2013-09-09 21:00:00'" "TIMESTAMP'2013-09-09 22:00:00'"
%This  SESSION EVENT                         TotalSeconds   CPU User I/O Application Concurrency Commit Configuration Cluster Idle Network System I/O Scheduler Administrative Queueing Other MIN(SAMPLE_TIME)          MAX(SAMPLE_TIME)
------ ------- ----------------------------- ------------ ----- -------- ----------- ----------- ------ ------------- ------- ---- ------- ---------- --------- -------------- -------- ----- ------------------------- -------------------------
  68%  ON CPU                                       25610 25610        0           0           0      0             0       0    0       0          0         0              0        0     0 09-SEP-13 09.00.01.468 PM 09-SEP-13 09.59.58.059 PM
  14%  WAITING SQL*Net more data from client         5380     0        0           0           0      0             0       0    0    5380          0         0              0        0     0 09-SEP-13 09.00.01.468 PM 09-SEP-13 09.59.58.059 PM
   6%  WAITING enq: HW - contention                  2260     0        0           0           0      0          2260       0    0       0          0         0              0        0     0 09-SEP-13 09.04.41.893 PM 09-SEP-13 09.56.07.626 PM
   3%  WAITING log file parallel write               1090     0        0           0           0      0             0       0    0       0       1090         0              0        0     0 09-SEP-13 09.00.11.478 PM 09-SEP-13 09.59.58.059 PM
   2%  WAITING db file parallel write                 730     0        0           0           0      0             0       0    0       0        730         0              0        0     0 09-SEP-13 09.01.11.568 PM 09-SEP-13 09.59.48.049 PM
   2%  WAITING enq: TX - contention                   600     0        0           0           0      0             0       0    0       0          0         0              0        0   600 09-SEP-13 09.04.41.893 PM 09-SEP-13 09.48.16.695 PM
   1%  WAITING buffer busy waits                      560     0        0           0         560      0             0       0    0       0          0         0              0        0     0 09-SEP-13 09.10.02.492 PM 09-SEP-13 09.56.07.626 PM
   1%  WAITING log file switch completion             420     0        0           0           0      0           420       0    0       0          0         0              0        0     0 09-SEP-13 09.47.16.562 PM 09-SEP-13 09.47.16.562 PM
   1%  WAITING latch: redo allocation                 330     0        0           0           0      0             0       0    0       0          0         0              0        0   330 09-SEP-13 09.04.41.893 PM 09-SEP-13 09.53.27.307 PM
...

--//我仔細看tpt的ash/dashtop指令碼，發現實際上該內容是註解的：
--      , 10 * SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
--      , 10 * SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
--      , 10 * SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
--      , 10 * SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"
--      , 10 * SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
--      , 10 * SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"
--      , 10 * SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"
--      , 10 * SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"
--      , 10 * SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"
--      , 10 * SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
--      , 10 * SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
--      , 10 * SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"

--//實際上還缺少了如下內容：
--      , 10 * SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
--      , 10 * SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"

--//連結：http://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2
--//也出現類似的情況：

SQL> @ash/ashtop session_state,event sql_id='3rtbs9vqukc71' "timestamp'2013-10-05 01:00:00'" "timestamp'2013-10-05 03:00:00'"
%This  SESSION EVENT                                 TotalSeconds   CPU User I/O Application Concurrency Commit Configuration Cluster Idle Network System I/O Scheduler Administrative   Queueing      Other MIN(SAMPLE_TIME)          MAX(SAMPLE_TIME)
------ ------- ------------------------------------- ------------ ----- -------- ----------- ----------- ------ ------------- ------- ---- ------- ---------- --------- -------------- ---------- ---------- ------------------------- -------------------------
  57%  WAITING buffer busy waits                            71962     0        0           0       71962      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.09.923 AM 05-OCT-13 02.45.54.106 AM
  35%  ON CPU                                               43735 43735        0           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.34.55.903 AM 05-OCT-13 02.47.28.232 AM
   6%  WAITING direct path write                             6959     0     6959           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.07.923 AM 05-OCT-13 02.47.21.232 AM
   1%  WAITING external table read                           1756     0     1756           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.02.913 AM 05-OCT-13 02.47.15.222 AM
   0%  WAITING local write wait                               350     0      350           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 02.02.40.034 AM 05-OCT-13 02.46.59.202 AM
   0%  WAITING control file parallel write                    231     0        0           0           0      0             0       0    0       0        231         0              0          0          0 05-OCT-13 01.35.22.953 AM 05-OCT-13 02.47.15.222 AM
   0%  WAITING cell smart file creation                       228     0      228           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.09.923 AM 05-OCT-13 02.47.26.232 AM
   0%  WAITING DFS lock handle                                194     0        0           0           0      0             0       0    0       0          0         0              0          0        194 05-OCT-13 01.35.15.933 AM 05-OCT-13 02.47.14.222 AM
   0%  WAITING cell single block physical read                146     0      146           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.12.933 AM 05-OCT-13 02.47.09.212 AM
   0%  WAITING control file sequential read                    63     0        0           0           0      0             0       0    0       0         63         0              0          0          0 05-OCT-13 01.35.17.953 AM 05-OCT-13 02.46.56.192 AM
   0%  WAITING change tracking file synchronous read           57     0        0           0           0      0             0       0    0       0          0         0              0          0         57 05-OCT-13 01.35.26.963 AM 05-OCT-13 02.40.32.677 AM
   0%  WAITING db file single write                            48     0       48           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.38.21.317 AM 05-OCT-13 02.41.55.794 AM
   0%  WAITING gc current grant 2-way                          19     0        0           0           0      0             0      19    0       0          0         0              0          0          0 05-OCT-13 01.35.06.923 AM 05-OCT-13 02.45.46.096 AM
   0%  WAITING kfk: async disk IO                              13     0        0           0           0      0             0       0    0       0         13         0              0          0          0 05-OCT-13 01.42.34.791 AM 05-OCT-13 02.38.19.485 AM
   0%  WAITING resmgr:cpu quantum                               9     0        0           0           0      0             0       0    0       0          0         9              0          0          0 05-OCT-13 01.36.09.085 AM 05-OCT-13 01.59.08.635 AM
   0%  WAITING enq: CR - block range reuse ckpt                 7     0        0           0           0      0             0       0    0       0          0         0              0          0          7 05-OCT-13 02.12.42.069 AM 05-OCT-13 02.40.46.687 AM
   0%  WAITING latch: redo allocation                           3     0        0           0           0      0             0       0    0       0          0         0              0          0          3 05-OCT-13 02.10.01.807 AM 05-OCT-13 02.10.01.807 AM
   0%  WAITING Disk file operations I/O                         2     0        2           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.41.13.639 AM 05-OCT-13 01.43.50.951 AM
   0%  WAITING enq: XL - fault extent map                       2     0        0           0           0      0             0       0    0       0          0         0              0          0          2 05-OCT-13 01.35.34.983 AM 05-OCT-13 01.35.34.983 AM
   0%  WAITING external table open                              2     0        2           0           0      0             0       0    0       0          0         0              0          0          0 05-OCT-13 01.35.02.913 AM 05-OCT-13 01.35.02.913 AM

--//我仔細看tpt的ash/ashtop指令碼，就沒有寫類似的以上註解的內容。
--//我仔細檢索ash裡面下的指令碼，發現又一個gashtop.sql指令碼記錄如下內容：
....
      , SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
      , SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"
      , SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
      , SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
      , SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
      , SUM(CASE WHEN wait_class ='Configuration'  THEN 1 ELSE 0 END) "Configuration"
      , SUM(CASE WHEN wait_class ='Cluster'        THEN 1 ELSE 0 END) "Cluster"
      , SUM(CASE WHEN wait_class ='Idle'           THEN 1 ELSE 0 END) "Idle"
      , SUM(CASE WHEN wait_class ='Network'        THEN 1 ELSE 0 END) "Network"
      , SUM(CASE WHEN wait_class ='System I/O'     THEN 1 ELSE 0 END) "System I/O"
      , SUM(CASE WHEN wait_class ='Scheduler'      THEN 1 ELSE 0 END) "Scheduler"
      , SUM(CASE WHEN wait_class ='Administrative' THEN 1 ELSE 0 END) "Administrative"
      , SUM(CASE WHEN wait_class ='Queueing'       THEN 1 ELSE 0 END) "Queueing"
      , SUM(CASE WHEN wait_class ='Other'          THEN 1 ELSE 0 END) "Other"

--//我估計作者執行時需要這些資訊就取消註解，不需要就設定註解。
--//我根據自己的需要另外改寫一個，僅僅需要定義_tpt_comment變數需要設定'',註解時定義'--'就可以實現，另外寫一篇blog說明問
--//題。


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

相關文章