how to find query with full scan for last hour ashtop.sql tanel poder (c)

every time it tooks about 15 minutes to find out how to find queries with full scan… time to save it to my blog
[code language=»sql»]
@http://blog.tanelpoder.com/files/scripts/ash/ashtop.sql sql_id,u.username,event "sql_plan_operation=’TABLE ACCESS’ and sql_plan_options=’FULL’" sysdate-1/24 sysdate
[/code]

[code language=»sql»]
Total | | | | | | | | Distinct
Seconds | AAS | %This | SQL_ID | USERNAME | EVENT | FIRST_SEEN | LAST_SEEN | Execs Seen
——— | ——- | ——- | ————- | ——————— | —————————————- | ——————— | ——————— | ———-
124 | .0 | 27% | | 4ztz048yfq32s | DBSNMP | direct path read | 2017-11-27 11:17:15 | 2017-11-27 11:48:41 | 2
58 | .0 | 13% | | 27mg3w92ah9fx | PATROL | <NULL> | 2017-11-27 11:18:28 | 2017-11-27 11:52:26 | 33
48 | .0 | 11% | | 4ztz048yfq32s | DBSNMP | <NULL> | 2017-11-27 11:17:29 | 2017-11-27 11:48:35 | 2
28 | .0 | 6% | | dwz7dgfp7k41u | XXX | <NULL> | 2017-11-27 10:53:20 | 2017-11-27 11:52:38 | 28
27 | .0 | 6% | | 8576v2udda8xd | PATROL | <NULL> | 2017-11-27 11:18:28 | 2017-11-27 11:51:50 | 27
17 | .0 | 4% | | cv8umnmuc8kc8 | XXX | <NULL> | 2017-11-27 10:53:53 | 2017-11-27 11:50:26 | 17
16 | .0 | 4% | | 297hq9h6h9mgt | PATROL | <NULL> | 2017-11-27 10:56:51 | 2017-11-27 11:45:34 | 16
15 | .0 | 3% | | 5ypmtb01t0bpf | NSI | <NULL> | 2017-11-27 10:53:26 | 2017-11-27 11:49:35 | 15
13 | .0 | 3% | | 68q8jd8rj7fjp | XXXXLOGS | <NULL> | 2017-11-27 11:06:25 | 2017-11-27 11:07:14 | 13
10 | .0 | 2% | | 9g6pyx7qz035v | XXXXAUDIT | <NULL> | 2017-11-27 10:59:31 | 2017-11-27 11:45:06 | 10
9 | .0 | 2% | | 9q00wxqqzqjdg | SYS | db file scattered read | 2017-11-27 11:08:02 | 2017-11-27 11:08:16 | 1
7 | .0 | 2% | | 98uu7x2kgw9f7 | SYS | db file scattered read | 2017-11-27 11:08:18 | 2017-11-27 11:08:30 | 1
4 | .0 | 1% | | 98uu7x2kgw9f7 | SYS | <NULL> | 2017-11-27 11:08:20 | 2017-11-27 11:08:28 | 1
4 | .0 | 1% | | 9q00wxqqzqjdg | SYS | gc cr multi block request | 2017-11-27 11:08:01 | 2017-11-27 11:08:15 | 1
4 | .0 | 1% | | 9q00wxqqzqjdg | SYS | <NULL> | 2017-11-27 11:08:03 | 2017-11-27 11:08:17 | 1

[/code]

Оставьте комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.