DBATOOLZ freeze frame session recording utility
|
2040
2004-OCT-01 19:06:21
|
|
Moderator
|
|
|
Registered On: Mar 2006
Total Posts: 267
|
|
=====================================================================
Copyright (c) 2004 DBAToolZ.com All rights reserved.
=====================================================================
MODULE: FREEZE_FRAME
VERSION: 01/OCT/2004 1.1
AUTHOR$: Vitaliy Mogilevskiy ()
PURPOSE: Freeze Frame locks/waits and session details
=====================================================================
Freeze Frame utility
x.cr8.sql - creates db objects
x.run.sql - runs snapshot
x.save.sql - saves snapshot data into real tables
x.text.sql - gets SID's SQL Text
Usage:
1. install db objects sqlplus user/pass @x.cr8.sql
2. from the same sqlplus session run x.run.sql to
gather first series of snapshots (20 snaps / 3 sec interval)
3. to drill into SID's use x.text.sql
4. if data is any value save backup using x.save.sql
EXAMPLE of output:
SQL> @x.run.sql
running series of 20 snapshots 3 seconds interval ...
please wait ...
SQL> exec freeze_frame_dbatoolz(p_snaps=>20,p_int=>3);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> set echo off
SNAP_ID SNAP_TIM SID EVENT SECONDS_IN_WAIT WAIT_TIME
---------- -------- ---------- ---------------------------------------- --------------- ----------
1 18:49:33 50 buffer busy waits 0 0
2 18:49:40 155 buffer busy waits 0 0
3 18:49:46 50 buffer busy waits 0 0
3 18:49:46 155 buffer busy waits 0 0
3 18:49:46 281 db file scattered read 0 0
4 18:49:52 50 buffer busy waits 0 0
5 18:49:57 50 buffer busy waits 0 0
5 18:49:57 155 db file scattered read 0 0
13 18:50:24 81 db file scattered read 0 0
14 18:50:30 81 db file scattered read 0 0
15 18:50:36 81 db file scattered read 0 0
16 18:50:42 81 db file scattered read 0 0
17 18:50:48 81 db file scattered read 0 0
18 18:50:54 207 db file scattered read 0 0
20 18:51:03 155 buffer busy waits 0 0
20 18:51:03 207 buffer busy waits 0 0
20 18:51:03 281 db file sequential read 0 0
21 18:53:51 155 buffer busy waits 2 0
22 18:53:57 155 buffer busy waits 0 0
22 18:53:57 207 buffer busy waits 0 0
22 18:53:57 281 db file scattered read 0 0
23 18:54:03 207 buffer busy waits 0 0
23 18:54:03 281 db file scattered read 0 0
24 18:54:09 81 buffer busy waits 0 0
24 18:54:09 207 db file scattered read 0 0
24 18:54:09 281 buffer busy waits 0 0
25 18:54:14 207 buffer busy waits 0 0
25 18:54:14 281 buffer busy waits 0 0
26 18:54:20 2 db file parallel write 0 0
26 18:54:20 81 buffer busy waits 0 0
26 18:54:20 207 db file sequential read 0 0
26 18:54:20 281 buffer busy waits 0 0
26 18:54:20 283 db file scattered read 0 0
27 18:54:26 81 buffer busy waits 0 0
27 18:54:26 207 buffer busy waits 0 0
27 18:54:26 245 log file sync 0 0
27 18:54:26 281 db file sequential read 0 0
27 18:54:26 283 db file scattered read 0 0
28 18:54:32 81 buffer busy waits 0 0
28 18:54:32 207 buffer busy waits 0 0
28 18:54:32 281 db file sequential read 0 0
28 18:54:32 283 db file scattered read 0 0
29 18:54:38 281 db file sequential read 0 0
29 18:54:38 283 db file scattered read 0 0
30 18:54:44 81 buffer busy waits 0 0
30 18:54:44 207 db file sequential read 0 0
30 18:54:44 281 buffer busy waits 0 0
31 18:54:50 207 db file scattered read 0 0
31 18:54:50 281 buffer busy waits 0 0
31 18:54:50 283 direct path write 0 0
32 18:54:56 81 buffer busy waits 0 0
32 18:54:56 283 direct path write 6 0
33 18:55:02 207 buffer busy waits 0 0
34 18:55:08 81 buffer busy waits 0 0
34 18:55:08 207 buffer busy waits 0 0
34 18:55:08 281 db file scattered read 0 0
35 18:55:13 207 buffer busy waits 0 0
35 18:55:13 281 buffer busy waits 0 0
36 18:55:19 81 buffer busy waits 0 0
36 18:55:19 207 buffer busy waits 0 0
36 18:55:19 281 db file sequential read 0 0
37 18:55:25 81 buffer busy waits 0 0
37 18:55:25 207 db file sequential read 0 0
37 18:55:25 281 buffer busy waits 0 0
38 18:55:31 81 db file sequential read 0 0
38 18:55:31 207 buffer busy waits 0 0
38 18:55:31 281 buffer busy waits 0 0
39 18:55:37 81 buffer busy waits 0 0
39 18:55:37 207 db file scattered read 0 0
39 18:55:37 281 buffer busy waits 0 0
40 18:55:43 81 buffer busy waits 0 0
40 18:55:43 207 db file sequential read 0 0
72 rows selected.
save backup -> @x.save.sql
get snap -> @x.run.sql
sql-text -> @x.text.sql
blocked objs -> select * from all_blocks_tmp
blocked sids -> select * from all_bloked_sids_tmp
SQL> @x.text.sql 32 283
1=snap_id
2=sid
(both are optional)
old 14: decode(nvl('&&1','x'),'x','x',ses.snap_id) = nvl('&&1','x') and
new 14: decode(nvl('32','x'),'x','x',ses.snap_id) = nvl('32','x') and
old 15: decode(nvl('&&2','x'),'x','x',ses.sid) = nvl('&&2','x')
new 15: decode(nvl('283','x'),'x','x',ses.sid) = nvl('283','x')
Snap ID ...... : 32
Snap Time .... : 18:54:56
Sid .......... : 283
Serial ....... : 2119
Username ..... : MON
Machine ...... : xxxxxxxxx.xxx.xxxxxx.com
OSuser ....... : oracle
Process ...... : 5840
Action ....... : SQL*Plus
SQL_TEXT
----------------------------------------------------------------------
SELECT fcr.concurrent_program_id ||','||fu.user_name ||','||DECO
DE(fcp.concurrent_program_name, 'ALECDC',fcp.concurren
t_program_name||'['||fcr.description||']' ,fcp
.concurrent_program_name) ||','||fcpt.user_concurrent_program_na
me ||','||COUNT(*) ||','||TO_CHAR(fcr.requested_start_date,'MM/D
D/RRRR HH24:MI') FROM fnd_concurrent_programs_tl fcpt , fnd_c
oncurrent_programs fcp , fnd_concurrent_requests fcr , fnd
_user fu WHERE fcr.actual_start_date between SYSDATE
-7 AND SYSDATE AND fcr.concurrent_program_id = fcp
t.concurrent_program_id AND fcr.program_application_id = fcpt.
application_id AND fcr.concurrent_program_id = fcp.concurrent_
program_id AND fcr.program_application_id = fcp.application_id
AND fcr.requested_by = fu.user_id /* AVOID REQUESTS SCHEDULED
WITH PAST DATE */ AND fcr.requested_start_date >= fcr.request
_date AND fcpt.language = USERENV('Lang') GROUP BY fcr.concurr
ent_program_id , fu.user_name , DECODE(fcp.concurr
ent_program_name, 'ALECDC',fcp.concurrent_program_name
||'['||fcr.description||']' ,fcp.concurrent_pr
ogram_name) , fcpt.user_concurrent_program_name ,
TO_CHAR(fcr.requested_start_date,'MM/DD/RRRR HH24:MI') HAVING co
unt(*) > 60 ORDER BY fcr.concurrent_program_id
21 rows selected.
SQL>