[20200117]ashtop脚本使用简介.txt
--//ashtop.sql是 Tanel Poder编写的脚本，用于定位有问题的sql语句。生产系统大部分性能问题都可以归为sql语句问题。
--//实际上看看脚本就可以找到如何使用。
-- Usage:
-- @ashtop
--
-- 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.
[20200117]ashtop脚本使用简介.txt
-
lfree
2020-01-17 08:54:12 -
Oracle
- 原创