1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
SELECT USERNAME,
PROGRAM,
MACHINE,
SERVICE_NAME,
GETS,
DISK,
CPU,
ELAPSED,
SQL_TEXT
FROM
(
SELECT a.USERNAME, a.SQL_ID, a.PROGRAM, a.MACHINE, a.SERVICE_NAME,
a.GETS, a.DISK, a.CPU, a.ELAPSED,LTRIM(sq.sql_text) AS SQL_TEXT
FROM ( SELECT du.username AS USERNAME,
s.sql_id AS SQL_ID,
s.module AS PROGRAM,
sh.machine AS MACHINE,
sn.service_name AS SERVICE_NAME,
ROUND ( SUM (s.buffer_gets_delta) / 1000000 / SUM (s.executions_delta),2) AS GETS,
ROUND ( SUM (s.disk_reads_delta)/ 1000/ SUM (s.executions_delta),2 ) AS DISK,
ROUND ( SUM (s.cpu_time_delta) / 1000000 / SUM (s.executions_delta), 2 ) AS CPU,
ROUND ( SUM (s.elapsed_time_delta) / 1000000 / SUM (s.executions_delta), 2 ) AS ELAPSED
FROM dba_hist_sqlstat s,
dba_hist_snapshot snp,
dba_hist_active_sess_history sh,
dba_hist_service_name sn,
dba_users du
WHERE s.snap_id = snp.snap_id
AND s.sql_id = sh.sql_id
AND sh.service_hash = sn.service_name_hash
AND sh.user_id=du.user_id
AND snp.snap_id >= nl_begin_snap
AND snp.snap_id <= nl_end_snap
AND s.parsing_schema_name NOT IN ('SYS', 'SYSTEM')
AND sh.program NOT LIKE '%(P0%'
AND sh.machine NOT LIKE 'xxxxxx%'
AND sh.machine <> 'xxxxx'
AND sn.service_name IN
('xxxxxx')
GROUP BY du.username, s.sql_id, s.module, sh.machine, sn.service_name
HAVING SUM (s.executions_delta) > 0 AND SUM (s.elapsed_time_delta) > 0
) a, dba_hist_sqltext sq
WHERE a.sql_id=sq.sql_id
ORDER BY ELAPSED DESC )
WHERE ROWNUM<11; |
