SQL> -- create temporary table
SQL> create global temporary table ses_snapshot(
2 seq number,
3 name varchar2 (64) ,
4 value number
5 ) on commit preserve rows;
테이블이 생성되었습니다.
SQL>
SQL> -- 1st snapshot
SQL> insert into ses_snapshot
2 select 1,
3 n.name,
4 s.value
5 from v$sesstat s ,
6 v$statname n
7 where s.statistic# = n.statistic#
8 and s.sid = userenv ('sid');
347 개의 행이 만들어졌습니다.
SQL> -- 2. Workload Process
SQL> select count(*) from all_objects ;
COUNT(*)
----------
45944
SQL> -- 2nd snapshot
SQL> insert into ses_snapshot
2 select 2,
3 name,
4 value
5 from v$sesstat s ,
6 v$statname n
7 where s.statistic# = n.statistic#
8 and s.sid = userenv('sid');
347 개의 행이 만들어졌습니다.
SQL> -- report
SQL> col name format a40
SQL> col value1 format 999,999,999
SQL> col value2 format 999,999,999
SQL> col diff format 999,999,999
SQL>
SQL> select s1.name,
2 (s2.value - s1.value) as diff,
3 s1.value as value1,
4 s2.value as value2
5 from ses_snapshot s1 ,
6 ses_snapshot s2
7 where s1.seq = 1 -- 1st snapshot
8 and s2.seq = 2 -- 2nd snapshot
9 and s1.name = s2.name
10 and abs(s2.value - s1.value) > 0
11 order by 2 desc;
NAME DIFF VALUE1 VALUE2
---------------------------------------- ------------ ------------ ------------
session pga memory max 1,441,792 777,812 2,219,604
session uga memory max 1,360,440 352,672 1,713,112
session pga memory 1,114,112 777,812 1,891,924
session uga memory 372,824 287,208 660,032
session logical reads 73,463 167 73,630
consistent gets 73,448 123 73,571
consistent gets from cache 73,448 123 73,571
buffer is not pinned count 57,278 38 57,316
table scan rows gotten 48,857 58 48,915
buffer is pinned count 48,791 38 48,829
no work - consistent read gets 31,268 30 31,298
index scans kdiixs1 26,795 35 26,830
shared hash latch upgrades - no wait 26,795 35 26,830
consistent gets - examination 15,233 46 15,279
table fetch by rowid 15,181 22 15,203
rows fetched via callback 12,027 0 12,027
calls to get snapshot scn: kcmgss 10,574 57 10,631
recursive calls 4,684 623 5,307
index fetch by key 3,201 17 3,218
execute count 2,357 48 2,405
redo size 1,248 7,056 8,304
table scan blocks gotten 1,193 3 1,196
opened cursors cumulative 1,163 51 1,214
parse count (total) 1,160 39 1,199
session cursor cache hits 1,153 12 1,165
undo change vector size 928 1,932 2,860
bytes sent via SQL*Net to client 587 3,649 4,236
bytes received via SQL*Net from client 545 3,241 3,786
DB time 56 1 57
CPU used when call started 54 0 54
CPU used by this session 54 0 54
table scans (short tables) 50 4 54
table fetch continued row 49 0 49
recursive cpu usage 30 0 30
cluster key scans 24 11 35
cluster key scan block gets 24 12 36
db block changes 17 54 71
db block gets from cache 15 44 59
db block gets 15 44 59
consistent changes 13 1 14
user calls 7 25 32
enqueue requests 6 19 25
opened cursors current 6 1 7
SQL*Net roundtrips to/from client 5 18 23
enqueue releases 5 17 22
redo entries 3 30 33
free buffer requested 2 2 4
cleanout - number of ktugct calls 1 0 1
parse count (hard) 1 8 9
parse time elapsed 1 0 1
parse time cpu 1 0 1
active txn count during cleanout 1 0 1
session cursor cache count 1 19 20
calls to kcmgcs 1 0 1
54 개의 행이 선택되었습니다.