[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.