오서비네 이글루

*** 위 테스트 수행 후 조회해 본 AWI 정보

SQL> select * from v$mutex order by TOTAL_LOCK_TIME_US desc limit 30;

NAME                           TRY_COUNT   LOCK_COUNT  MISS_COUNT  SPIN_VALUE  TOTAL_LOCK_TIME_US   MAX_LOCK_TIME_US
------------------------------ ----------- ----------- ----------- ----------- -------------------- --------------------
LOGFILE_MANAGER_MUTEX                  146         146           0           1            431140625              9984375
DW_MANAGER_MUTEX                     29038       29038          71           1            266593750              2218750
MMT_SERVICE_THREAD_MUTEX              4448        2062        2386           1            237468750            143187500
LOG_FLUSH_THREAD_MUTEX               73285          63       73222           1              3640625               375000
LOG_FILE_GROUP_MUTEX               2231601     2231601           0           1               468750                31250
SYNC_LSN_MUTEX                      147337      147337           1           1               234375                46875
BUFFER_MGR_MUTEX_2                 3629272     3629272           0           1               218750                15625
BUFFER_MGR_MUTEX_1                 3575660     3575660           0           1               187500                78125
BUFFER_MGR_MUTEX_5                 5272550     5272550           1           1               171875                15625
BUFFER_MGR_MUTEX_4                 3491102     3491102           0           1               156250                15625
BUFFER_MGR_MUTEX_0                 4686517     4686517           0           1               156250                15625
BUFFER_MGR_MUTEX_3                 3050838     3050838           0           1               109375                15625
TABLESPACE_MANAGER_MUTEX            312799      312799           2           1                62500                15625
SDNB_ITERATOR_POOL_0                122000      122000           0           1                62500                46875
BUFFER_MGR_MUTEX_6                 2825479     2825479           0           1                46875                15625
TRANS_MUTEX_1                      1174569     1174569           0           1                31250                15625
SMUHASH_OBJECT_MUTEX_0               13422       13422           0           1                15625                15625
SMNB_ITERATOR_POOL_0                 36420       36420           0           1                15625                15625
MEMMGR_MUTEX_Query_PSM_Execute      386791      386791           0           1                15625                15625
MEMMGR_MUTEX_Query_Execute           96680       96680           0           1                    0                    0

SQL> select
       sid,
       event,
       total_waits,
       average_wait,
       time_waited,
       time_waited_micro,
       average_wait
from   v$session_event
order by  total_waits desc limit 10;

SID EVENT                         TOTAL_WAITS AVERAGE_WAIT TIME_WAITED  TIME_WAITED_MICRO AVERAGE_WAIT
--- ----------------------------- ----------- ------------ ------------ ----------------- ------------
  3 db file sequential read             26477            7       199468         199468750            7
  3 db file single write                 4071            0            0                 0            0
  3 free buffer waits                      69            0            0                 0            0
  3 latch free: drdb dw buffer             26          381         9906           9906250          381
  3 latch free: tbs hash                    1            0            0                 0            0
  3 latch: write complete waits             1            0            0                 0            0
  3 latch: read by other session            0            0            0                 0            0

SQL> select     b.name,
           a.singleblkrds,
           a.singleblkrdtim,
           trunc(a.singleblkrdtim/a.singleblkrds,2) average_wait
from       v$filestat a,
           v$datafiles b
where      a.spaceid = b.spaceid and
           a.fileid = b.id and
           a.singleblkrds > 0
order by   average_wait desc;

NAME           SINGLEBLKRDS SINGLEBLKRDTIM AVERAGE_WAIT
-------------- ------------ -------------- ------------
tbs_disk1             41176         299375         7.27
undo001.dbf            7712        17062.5         2.21
system001.dbf            68              0            0

SQL> select
   substr(b.name,1,128) name,
   a.phyrds phy_rds,
   a.phywrts phy_wrts,
   a.phyrds+a.phywrts total_iO,
   trunc(a.phyrds/read_sum*100,2) read_per,
   trunc(a.phywrts/write_sum*100,2) write_per,
   trunc( (a.phyrds+a.phywrts) / (read_sum+write_sum) * 100 , 2)
        total_io_per,
   a.avgiotim avg_io_time
from
   v$filestat a,
   v$datafiles b,
   (select
           sum(phyrds) read_sum,
           sum(phywrts) write_sum
   from v$filestat ) c
where
   a.spaceid = b.spaceid and
   a.fileid = b.id and
   read_sum > 0 and write_sum > 0
order by
   a.phyrds+a.phywrts desc,
   rownum desc;

NAME           PHY_RDS PHY_WRTS TOTAL_IO READ_PER WRITE_PER TOTAL_IO_PER AVG_IO_TIME
-------------- ------- -------- -------- -------- --------- ------------ -----------
tbs_disk1        41227    23736    64963    84.02     41.45         61.1       4.614
system001.dbf       68    28613    28681     0.13     49.97        26.97       0.003
undo001.dbf       7719     4878    12597    15.73      8.51        11.84       1.354
temp001.dbf          0        3        3        0         0            0           0