[20200117]ashtop指令碼使用簡介.txt

[20200117]ashtop指令碼使用簡介.txt

--//ashtop.sql是 Tanel Poder編寫的指令碼，用於定位有問題的sql語句。生產系統大部分效能問題都可以歸為sql語句問題。
--//實際上看看指令碼就可以找到如何使用。
-- Usage:
--     @ashtop <grouping_cols> <filters> <fromtime> <totime>
--
-- Example:
--     @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
--
-- Other:
--     This script uses only the in-memory V$ACTIVE_SESSION_HISTORY, use
--     @dashtop.sql for accessiong the DBA_HIST_ACTIVE_SESS_HISTORY archive
--
--------------------------------------------------------------------------------

--//還有1個dashtop.sql指令碼檢視DBA_HIST_ACTIVE_SESS_HISTORY，使用方法相似。

> @ashtop username,sql_id session_type='FOREGROUND' trunc(sysdate)+8/24 sysdate
    Total
  Seconds     AAS %This   USERNAME             SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------- -------------------
      559      .0    6% | PPPPPP_HHH                         2020-01-16 08:00:54 2020-01-16 17:45:03
      448      .0    5% | HZMCASSET            6mnrdrgdys4uc 2020-01-16 08:00:04 2020-01-16 17:44:31
      428      .0    5% | PPPPPP_HHH           9mb3vstumytc8 2020-01-16 08:03:45 2020-01-16 17:41:44
      348      .0    4% | PPPPPP_HHH           98t0yhuj0fd66 2020-01-16 08:04:00 2020-01-16 17:43:40
      329      .0    4% | PPPPPP_HHH           bs988hajsxfut 2020-01-16 08:04:29 2020-01-16 17:43:28
      317      .0    3% | LIS                  gyaz8r00nspdj 2020-01-16 08:46:00 2020-01-16 15:37:31
      307      .0    3% | PPPPPP_HHH           g7ytdh9mxt1s0 2020-01-16 08:04:46 2020-01-16 17:41:28
      236      .0    3% | HZMCASSET                          2020-01-16 08:03:40 2020-01-16 17:44:41
      164      .0    2% | PPPPPP_HHH           ajumbh0a21c5q 2020-01-16 08:00:50 2020-01-16 17:37:21
      157      .0    2% | PPPPPP_HHH           61nak06qw9h0t 2020-01-16 09:01:56 2020-01-16 17:08:03
      147      .0    2% | SYS                                2020-01-16 08:01:30 2020-01-16 17:44:42
      128      .0    1% | PPPPPP_HHH           32u3jw8n6f8r6 2020-01-16 08:41:56 2020-01-16 16:31:06
      126      .0    1% | HZMCASSET            9dj4166ys0z0w 2020-01-16 08:01:01 2020-01-16 17:39:24
      123      .0    1% | PPPPPP_HHH           f8733rs2f3bng 2020-01-16 08:11:48 2020-01-16 17:39:59
      117      .0    1% | DRB                  6kkfgfjybkpfk 2020-01-16 08:06:29 2020-01-16 17:43:21
      107      .0    1% | PPPPPP_HHH           5wnky979drmys 2020-01-16 08:05:38 2020-01-16 16:55:40
       97      .0    1% | PPPPPP_HHH           2dapgszyhwhks 2020-01-16 08:04:55 2020-01-16 17:23:34
       91      .0    1% | PPPPPP_HHH           b21wnbjv2jj22 2020-01-16 08:06:10 2020-01-16 17:43:22
       88      .0    1% | PORTAL_EMR                         2020-01-16 08:19:39 2020-01-16 17:36:22
       86      .0    1% | PPPPPP_HHH           9s6xvf5bcg13s 2020-01-16 08:08:50 2020-01-16 17:19:32
20 rows selected.

--//檢視5分鐘前的情況，執行如下：
> @ashtop username,sql_id 1=1 sysdate-15/1440 sysdate
    Total
  Seconds     AAS %This   USERNAME             SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- -------------------- ------------- ------------------- -------------------
       80      .1   18% | SYS                                2020-01-17 08:30:59 2020-01-17 08:45:37
       25      .0    6% | PPPPPP_HHH           9mb3vstumytc8 2020-01-17 08:31:07 2020-01-17 08:45:14
       24      .0    5% | PPPPPP_HHH                         2020-01-17 08:31:35 2020-01-17 08:45:19
       17      .0    4% | PPPPPP_HHH           1xkmbmbnmcata 2020-01-17 08:43:05 2020-01-17 08:43:21
       13      .0    3% | PPPPPP_HHH           g7ytdh9mxt1s0 2020-01-17 08:31:22 2020-01-17 08:44:28
       11      .0    2% | HZMCASSET            6mnrdrgdys4uc 2020-01-17 08:32:47 2020-01-17 08:44:54
       11      .0    2% | PPPPPP_HHH           bs988hajsxfut 2020-01-17 08:32:15 2020-01-17 08:45:39
        7      .0    2% | HZMCASSET                          2020-01-17 08:31:34 2020-01-17 08:45:19
        6      .0    1% | PPPPPP_HHH           98t0yhuj0fd66 2020-01-17 08:33:21 2020-01-17 08:42:34
        5      .0    1% | DBO                                2020-01-17 08:31:10 2020-01-17 08:39:10
        5      .0    1% | PPPPPP_HHH           20kkbx3yfx6qq 2020-01-17 08:37:05 2020-01-17 08:44:09
        5      .0    1% | PPPPPP_HHH           495q1j4nq2p5g 2020-01-17 08:31:47 2020-01-17 08:42:19
        5      .0    1% | PPPPPP_HHH           5wnky979drmys 2020-01-17 08:31:13 2020-01-17 08:37:01
        5      .0    1% | PPPPPP_HHH           bjagu7tgqhqkg 2020-01-17 08:30:57 2020-01-17 08:41:25
        5      .0    1% | PPPPPP_HHH           c8s65f1cuhcb1 2020-01-17 08:33:43 2020-01-17 08:43:57
        4      .0    1% | DBO                  284xbhpcdj6qa 2020-01-17 08:33:50 2020-01-17 08:36:12
        4      .0    1% | DRB                  gvk2t9xhaxc7q 2020-01-17 08:35:58 2020-01-17 08:45:29
        4      .0    1% | PPPPPP_HHH           6ctjaqsxn4jdh 2020-01-17 08:31:53 2020-01-17 08:45:20
        4      .0    1% | PPPPPP_HHH           gy1k038mqcy0f 2020-01-17 08:34:09 2020-01-17 08:40:20
        4      .0    1% | SYS                  49wbcqj3a0183 2020-01-17 08:43:01 2020-01-17 08:44:18
20 rows selected.