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 개의 행이 선택되었습니다.