SQL>-- 예제4) 수행
SQL>
SQL>-- 2. Workload Process : 이전 스냅샵과 다른 작업수행
SQL> select count(*) from all_objects ;
COUNT(*)
----------
45944
SQL>
SQL> -- 3rd snapshot
SQL> -- v$sesstat
SQL> insert into ses_snapshot
2 select 3,
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 3,
3 'EVENT',
4 e.event,
5 e.time_waited
6 from v$session_event e
7 where e.sid = userenv ('sid');
3 개의 행이 만들어졌습니다.
SQL> -- report
SQL> col name format a40
SQL> col diff1 format 999,999,999
SQL> col diff2 format 999,999,999
SQL>
SQL> select s3.type,
2 s3.name,
3 (nvl(s2.value, 0) - nvl(s1.value, 0)) as diff1,
4 (nvl(s3.value, 0) - nvl(s2.value, 0)) as diff2
5 from ses_snapshot s1 ,
6 ses_snapshot s2 ,
7 ses_snapshot s3
8 where s1.seq(+) = 1 -- 1st snapshot
9 and s2.seq(+) = 2 -- 2nd snapshot
10 and s3.seq = 3 -- 3rd snapshot
11 and s1.name(+)= s2.name
12 and s1.type(+)= s2.type
13 and s2.name(+)= s3.name
14 and s2.type(+)= s3.type
15 and abs(s3.value - nvl(s1.value, 0)) > 0
16 order by decode(s1.type, 'STAT', 1
17 , 'EVENT', 2)
18 , abs(s2.value - s1.value) desc;
TYPE NAME DIFF1 DIFF2
----- ---------------------------------------- ------------ ------------
STAT table scan rows gotten 47,557 50,257
STAT redo size 1,476 1,380
STAT undo change vector size 1,056 1,020
STAT bytes received via SQL*Net from client 884 1,563
STAT bytes sent via SQL*Net to client 740 3,140
STAT session logical reads 639 73,476
STAT consistent gets from cache 616 73,459
STAT consistent gets 616 73,459
STAT no work - consistent read gets 586 31,274
STAT table scan blocks gotten 566 1,203
STAT buffer is pinned count 158 48,790
STAT buffer is not pinned count 39 57,270
STAT db block changes 27 22
STAT db block gets from cache 23 17
STAT db block gets 23 17
STAT consistent gets - examination 21 15,232
STAT consistent changes 21 17
STAT table fetch by rowid 19 15,177
STAT index fetch by key 19 3,201
STAT user calls 10 16
STAT calls to get snapshot scn: kcmgss 8 10,577
STAT SQL*Net roundtrips to/from client 7 12
STAT enqueue requests 5 3
STAT redo entries 5 5
STAT DB time 4 54
STAT enqueue releases 4 3
STAT CPU used when call started 3 53
STAT CPU used by this session 3 53
STAT free buffer requested 3 2
STAT opened cursors cumulative 3 1,163
STAT parse count (total) 3 1,160
STAT execute count 3 2,357
STAT session cursor cache count 2 3
STAT calls to kcmgcs 2 2
STAT cursor authentications 2 3
STAT cleanout - number of ktugct calls 1 0
STAT active txn count during cleanout 1 0
STAT shared hash latch upgrades - no wait 1 26,795
STAT index scans kdiixs1 1 26,795
STAT table scans (short tables) 1 52
STAT workarea executions - optimal 0 3
STAT session cursor cache hits 0 1,151
STAT rows fetched via callback 0 12,027
STAT cluster key scan block gets 0 24
STAT cluster key scans 0 24
STAT table fetch continued row 0 49
STAT session pga memory max 0 1,507,328
STAT session pga memory 0 393,216
STAT session uga memory max 0 1,380,400
STAT session uga memory 0 392,784
STAT recursive cpu usage 0 29
STAT recursive calls 0 4,669
STAT opened cursors current 0 6
STAT sorts (memory) 0 1
STAT sorts (rows) 0 41
EVENT SQL*Net message from client 3 15
56 개의 행이 선택되었습니다.