SQL> create global temporary table ses_snapshot(
2 seq number,
3 type varchar2 (5) ,
4 name varchar2 (64) ,
5 value number
6 ) on commit preserve rows;
테이블이 생성되었습니다.
SQL>
SQL>
SQL> -- 1st snapshot
SQL> -- v$sesstat
SQL> insert into ses_snapshot
2 select 1,
3 'STAT',
4 n.name,
5 s.value
6 from v$sesstat s ,
7 v$statname n
8 where s.statistic# = n.statistic#
9 and s.sid = userenv('sid');
347 개의 행이 만들어졌습니다.
SQL>
SQL> -- v$session_event
SQL> insert into ses_snapshot
2 select 1,
3 'EVENT',
4 e.event,
5 e.time_waited
6 from v$session_event e
7 where e.sid = userenv ('sid');
3 개의 행이 만들어졌습니다.
SQL>
SQL> -- 2. Workload Process
SQL> select count(*) from user_objects ;
COUNT(*)
----------
126
SQL>
SQL> -- 2nd snapshot
SQL> -- v$sesstat
SQL> insert into ses_snapshot
2 select 2,
3 'STAT',
4 n.name,
5 s.value
6 from v$sesstat s ,
7 v$statname n
8 where s.statistic# = n.statistic#
9 and s.sid = userenv('sid');
347 개의 행이 만들어졌습니다.
SQL>
SQL> -- v$session_event
SQL> insert into ses_snapshot
2 select 2,
3 'EVENT',
4 e.event,
5 e.time_waited
6 from v$session_event e
7 where e.sid = userenv ('sid');
3 개의 행이 만들어졌습니다.
SQL>
SQL> -- report
SQL> col name format a50
SQL> col value1 format 999,999,999
SQL> col value2 format 999,999,999
SQL> col diff format 999,999,999
SQL>
SQL> select s2.type,
2 s2.name,
3 (s2.value - nvl(s1.value, 0)) as diff,
4 s1.value as value1 ,
5 s2.value as value2
6 from ses_snapshot s1 ,
7 ses_snapshot s2
8 where s1.seq(+) = 1 -- 1st snapshot
9 and s2.seq = 2 -- 2nd snapshot
10 and s1.name(+) = s2.name
11 and s1.type(+) = s2.type
12 and abs(s2.value - nvl(s1.value, 0)) > 0
13 order by decode(s1.type , 'STAT', 1
14 , 'EVENT', 2)
15 , abs(s2.value - s1.value) desc;
TYPE NAME DIFF VALUE1 VALUE2
----- -------------------------------------------------- ------------ ------------ ------------
STAT table scan rows gotten 47,557 58 47,615
STAT redo size 1,520 8,076 9,596
STAT undo change vector size 1,056 2,236 3,292
STAT bytes received via SQL*Net from client 884 3,282 4,166
STAT bytes sent via SQL*Net to client 740 3,649 4,389
STAT session logical reads 645 122 767
STAT consistent gets from cache 622 72 694
STAT consistent gets 622 72 694
STAT no work - consistent read gets 592 11 603
STAT table scan blocks gotten 566 3 569
STAT buffer is pinned count 158 38 196
STAT buffer is not pinned count 51 13 64
STAT db block changes 27 62 89
STAT table fetch by rowid 25 16 41
STAT db block gets 23 50 73
STAT recursive calls 23 350 373
STAT db block gets from cache 23 50 73
STAT consistent gets - examination 21 20 41
STAT consistent changes 21 1 22
STAT index fetch by key 19 6 25
STAT calls to get snapshot scn: kcmgss 11 38 49
STAT user calls 10 25 35
STAT enqueue requests 7 12 19
STAT SQL*Net roundtrips to/from client 7 18 25
STAT execute count 6 29 35
STAT enqueue releases 6 10 16
STAT parse count (total) 6 23 29
STAT opened cursors cumulative 6 32 38
STAT DB time 5 1 6
STAT CPU used by this session 5 1 6
STAT redo entries 5 34 39
STAT CPU used when call started 5 1 6
STAT free buffer requested 3 1 4
STAT session cursor cache hits 3 7 10
STAT parse count (hard) 2 3 5
STAT calls to kcmgcs 2 1 3
STAT session cursor cache count 2 18 20
STAT shared hash latch upgrades - no wait 1 29 30
STAT cleanout - number of ktugct calls 1 0 1
STAT table scans (short tables) 1 4 5
STAT index scans kdiixs1 1 29 30
STAT active txn count during cleanout 1 0 1
EVENT SQL*Net message from client 4 5,779 5,783
43 개의 행이 선택되었습니다.