AWR을 사용하는 간단한 예제
***** 테이블 생성후 1 row 추가한다.
SQL> create table t1(c1 number);
SQL> insert into t1 values(1);
SQL> commit;
***** TEMP.SQL을 작성한다.
begin
for idx in 1..100 loop
update t1 set c1 = 1;
dbms_lock.sleep(0.1);
commit;
for r in (select * from user_objects) loop
null;
end loop;
end loop;
end;
/
***** 첫번째 스냅샷을 생성한다.
col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;
select dbid as db_id from v$database;
DB_ID
----------
3588319577
select instance_number as inst_num from v$instance;
INST_NUM
----------
1
select dbms_workload_repository.create_snapshot as begin_snap from dual;
BEGIN_SNAP
----------
14739
***** 5개의 세션에서 동시에 TEMP.SQL 파일을 실행한다.
***** 5개의 세션에서 작업이 끝난 후 두번째 스냅샷을 생성한다.
col end_snap new_value end_snap;
select dbms_workload_repository.create_snapshot as end_snap from dual;
END_SNAP
----------
14740
***** DBMS_WORKLAD_REPOSITORY.AWR_REPORT_TEXT 함수를 이용해 첫번째 스냅샷(14739)과 두번째 스냅샷(14740)간의 차이에 대한 리포트를 만든다.
select * from table (
dbms_workload_repository.awr_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap)
);
OR
@?/rdbms/admin/awrrpt 스크립트 이용
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ELEVEN 3588319577 ELEVEN 1 28-Feb-11 17:52 11.2.0.1.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
oranewdb Linux x86 64-bit 8 8 2 7.79
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 14739 14-Mar-12 15:22:14 25 1.4
End Snap: 14740 14-Mar-12 15:26:02 27 1.4
Elapsed: 3.79 (mins)
DB Time: 3.38 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 448M 448M Std Block Size: 8K
Shared Pool Size: 784M 784M Log Buffer: 4,848K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.9 0.4 0.09 8.45
DB CPU(s): 0.7 0.3 0.07 6.71
Redo size: 7,700.2 3,505.7
Logical reads: 3,386.3 1,541.6
Block changes: 24.1 11.0
Physical reads: 0.2 0.1
Physical writes: 6.9 3.2
User calls: 0.1 0.1
Parses: 3.2 1.5
Hard parses: 0.3 0.2
W/A MB processed: 2.6 1.2
Logons: 0.0 0.0
Executes: 10.1 4.6
Rollbacks: 0.0 0.0
Transactions: 2.2
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 92.99 Soft Parse %: 89.97
Execute to Parse %: 67.79 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 68.75 % Non-Parse CPU: 99.93
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 78.22 78.42
% SQL with executions>1: 91.16 94.41
% Memory for SQL w/exec>1: 80.79 91.74
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 161 79.4
enq: TX - row lock contention 480 41 86 20.4 Applicatio
Disk file operations I/O 117 0 2 .1 User I/O
db file sequential read 164 0 1 .1 User I/O
log file sync 2 0 15 .0 Commit
Time Model Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Total time in database user-calls (DB Time): 202.7s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 202.6 100.0
DB CPU 160.9 79.4
PL/SQL execution elapsed time 2.5 1.2
parse time elapsed 0.3 .2
hard parse elapsed time 0.3 .1
hard parse (sharing criteria) elapsed time 0.3 .1
PL/SQL compilation elapsed time 0.0 .0
connection management call elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 202.7
background elapsed time 19.7
background cpu time 0.2
-------------------------------------------------------------
Operating System Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Statistic Value End Value
------------------------- ---------------------- ----------------
BUSY_TIME 16,542
IDLE_TIME 165,157
IOWAIT_TIME 2,884
NICE_TIME 0
SYS_TIME 173
USER_TIME 16,359
LOAD 0 1
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,360,480,768
NUM_CPUS 8
NUM_CPU_CORES 8
NUM_CPU_SOCKETS 2
Operating System Statistics - DetailDB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
14-Mar 15:22:14 0.2 N/A N/A N/A N/A N/A
14-Mar 15:26:02 1.4 9.1 9.0 0.1 90.9 1.6
-------------------------------------------------------------
Foreground Wait Class DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
DB CPU 161 79.4
Application 480 0 41 86 20.4
User I/O 288 0 0 1 0.2
Commit 2 0 0 15 0.0
System I/O 867 0 0 0 0.0
Other 294 100 0 0 0.0
Concurrency 8 0 0 0 0.0
Network 19 0 0 0 0.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
enq: TX - row lock content 480 0 41 86 1.0 20.4
Disk file operations I/O 117 0 0 2 0.2 .1
db file sequential read 164 0 0 1 0.3 .1
log file sync 2 0 0 15 0.0 .0
direct path sync 1 0 0 23 0.0 .0
...
-------------------------------------------------------------
Background Wait Events DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Avg
%Time Total Wait wait Waits % bg
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
db file async I/O submit 193 0 12 61 0.4 59.7
log file parallel write 1,054 0 3 3 2.1 15.5
...
Wait Event Histogram DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 6 100.0
LGWR wait for redo copy 4 100.0
SQL*Net message to client 29 100.0
asynch descriptor resize 319 100.0
buffer busy waits 8 100.0
control file parallel writ 114 2.6 87.7 9.6
control file sequential re 1144 100.0
db file async I/O submit 193 2.1 1.6 6.2 90.2
db file sequential read 269 92.2 3.0 4.5 .4
db file single write 114 2.6 76.3 21.1
direct path sync 1 100.0
direct path write 6 100.0
enq: TX - row lock content 480 .6 .6 .2 1.0 .6 96.9
log file parallel write 1054 50.0 .3 12.0 31.8 5.4 .3 .3
log file sync 2 100.0
....
Wait Event Histogram Detail (64 msec to 2 sec)DB/Inst: ELEVEN/ELEVEN Snaps:
Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
control file parallel writ 11 90.4 8.8 .9
db file async I/O submit 174 9.8 48.2 41.5 .5
enq: TX - row lock content 465 3.1 .6 96.3
log file parallel write 3 99.7 .2 .1
-------------------------------------------------------------
Wait Event Histogram Detail (4 sec to 2 min)DB/Inst: ELEVEN/ELEVEN Snaps: 14
No data exists for this section of the report.
-------------------------------------------------------------
....
SQL ordered by Elapsed Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
201.0 5 40.19 99.1 79.3 .0 gx6mfa8zvs1n1
Module: sqlplus@oranewdb (TNS V1-V3)
begin for idx in 1..100 loop update t1 set c1 = 1; dbms_lock.sleep(0.1
); commit; for r in (select * from user_objects) loop null; e
nd loop; end loop; end;
155.1 500 0.31 76.5 99.8 .0 7dazh7hup4yq3
Module: sqlplus@oranewdb (TNS V1-V3)
SELECT * FROM USER_OBJECTS
.....
SQL ordered by CPU Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by User I/O Wait Time DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Gets DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Physical Reads (UnOptimized)DB/Inst: ELEVEN/ELEVEN Snaps: 147
SQL ordered by Executions DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
SQL ordered by Parse Calls DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Instance Activity Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Ordered by statistic name
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
....
buffer is not pinned count 141,402 621.2 282.8
buffer is pinned count 16,230,862 71,302.8 32,461.7
bytes received via SQL*Net from 2,756 12.1 5.5
bytes sent via SQL*Net to client 6,516 28.6 13.0
calls to get snapshot scn: kcmgs 2,851 12.5 5.7
calls to kcmgas 1,358 6.0 2.7
calls to kcmgcs 5,013 22.0 10.0
cell physical IO interconnect by 42,452,480 186,495.3 84,905.0
....
user calls 24 0.1 0.1
session logical reads 770,822 3,386.3 1,541.6
redo size 1,752,824 7,700.2 3,505.7
physical reads 43 0.2 0.1
Instance Activity Stats - Absolute ValuesDB/Inst: ELEVEN/ELEVEN Snaps: 14739
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
opened cursors current 36 38
logons current 25 27
session uga memory max 6.705085227E+11 6.705168584E+11
session pga memory 2.529616695E+11 2.529617689E+11
session pga memory max 5.829489299E+11 5.829629230E+11
session cursor cache count 4,650,564 4,650,601
session uga memory 8.301075209E+14 8.301075220E+14
-------------------------------------------------------------
Instance Activity Stats - Thread ActivityDB/Inst: ELEVEN/ELEVEN Snaps: 14739-
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 0 .00
-------------------------------------------------------------
IOStat by Function summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Function Name Data per sec per sec Data per sec per sec Count Tm(ms)
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Others 19M 6.0 .083467 6M 2.0 .026358 1719 4.2
DBWR 0M 0.0 0M 12M 5.7 .052716 193 60.5
LGWR 0M 0.0 0M 2M 2.3 .008786 527 5.3
Buffer Cache Re 0M 0.2 0M 0M 0.0 0M 37 1.9
Direct Writes 0M 0.0 0M 0M 0.0 0M 6 0.0
TOTAL: 19M 6.2 .083467 20M 10.0 .087860 2482 8.8
-------------------------------------------------------------
IOStat by Filetype summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Small Large
Filetype Name Data per sec per sec Data per sec per sec Read Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Control File 18M 5.0 .079074 6M 1.5 .026358 0.0 N/A
Data File 2M 1.2 .008786 21M 6.2 .092253 0.3 N/A
Log File 0M 0.0 0M 2M 2.3 .008786 N/A N/A
TOTAL: 20M 6.2 .087860 29M 10.0 .127398 0.1 N/A
-------------------------------------------------------------
IOStat by Function/Filetype summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Reads: Reqs Data Writes: Reqs Data Waits: Avg
Data per sec per sec Data per sec per sec Count Tm(ms)
------- ------- ------- ------- ------- ------- ------- -------
Others
19M 6.0 .083467 6M 2.0 .026358 1491 1.0
Others (Control File)
18M 5.0 .079074 5M 1.5 .021965 1144 0.0
Others (Data File)
1M 1.0 .004393 1M 0.5 .004393 347 4.3
DBWR
0M 0.0 0M 12M 5.7 .052716 0 N/A
DBWR (Data File)
0M 0.0 0M 12M 5.7 .052716 0 N/A
LGWR
0M 0.0 0M 2M 2.3 .008786 0 N/A
LGWR (Log File)
0M 0.0 0M 2M 2.3 .008786 0 N/A
Direct Writes
0M 0.0 0M 0M 0.0 0M 0 N/A
Direct Writes (Data File)
0M 0.0 0M 0M 0.0 0M 0 N/A
Buffer Cache Reads
0M 0.2 0M 0M 0.0 0M 37 1.9
Buffer Cache Reads (Data File)
0M 0.2 0M 0M 0.0 0M 37 1.9
TOTAL:
19M 6.2 .083467 20M 10.0 .087860 1528 1.0
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
KIDS2_DATA
0 0 0.0 .0 1,282 6 0 0.0
SYSAUX
41 0 2.0 1.0 6 0 0 0.0
UNDOTBS1
0 0 0.0 .0 13 0 0 0.0
SYSTEM
5 0 2.0 1.0 2 0 8 1.3
-------------------------------------------------------------
File IO Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------- ------- ------------ -------- ---------- -------
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data01.dbf
0 0 N/A N/A 258 1 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data02.dbf
0 0 N/A N/A 316 1 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data03.dbf
0 0 N/A N/A 391 2 0 0.0
KIDS2_DATA /data03/oradata/ELEVEN/kids2_data04.dbf
0 0 N/A N/A 317 1 0 0.0
SYSAUX /data01/oradata/ELEVEN/sysaux01.dbf
41 0 2.0 1.0 6 0 0 0.0
SYSTEM /data01/oradata/ELEVEN/system01.dbf
5 0 2.0 1.0 2 0 8 1.3
UNDOTBS1 /data01/oradata/ELEVEN/undotbs01.dbf
0 0 N/A N/A 13 0 0 0.0
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys %DBtime
P Est (M) Factor (thousands) Factor (thousands) Read Time for Rds
--- -------- ------ ------------ ------ -------------- ------------ -------
D 448 1.0 55 1.0 145,989 1 5.3E+04
D 480 1.1 59 1.0 144,799 1 5.1E+04
D 512 1.1 63 1.0 143,851 1 5.0E+04
D 544 1.2 67 1.0 142,927 1 4.9E+04
D 576 1.3 71 1.0 142,025 1 4.7E+04
D 608 1.4 75 1.0 141,095 1 4.6E+04
D 640 1.4 79 1.0 140,124 1 4.5E+04
Shared Pool Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem Obj
Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Hits (K)
-------- ----- -------- ------------ -------- ------ ------- ------ ------------
544 .7 26 1,127 395,539 .9 42,177 2.8 18,917
624 .8 107 4,205 399,224 .9 38,492 2.6 58,071
704 .9 186 6,908 410,782 1.0 26,934 1.8 59,403
784 1.0 267 9,998 422,764 1.0 14,952 1.0 60,704
864 1.1 348 12,785 432,060 1.0 5,656 .4 61,750
SGA Target Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
SGA Target SGA Size Est DB Est Physical
Size (M) Factor Time (s) Reads
---------- ---------- ------------ ----------------
672 0.5 1,037,899 540,832,598
1,008 0.8 495,523 145,989,472
1,344 1.0 458,436 145,989,472
1,680 1.3 443,904 139,332,352
2,016 1.5 442,207 138,368,822
PGA Aggr Summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ------------------ --------------------------
100.0 596 0
-------------------------------------------------------------
PGA Aggr Target Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
PGA Aggr Auto PGA PGA Mem W/A PGA %PGA W/A %Auto W/A %Man W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- ----------- ---------- --------- -------- ---------- ---------- -------- ------------
B 488 410 39.7 0.0 .0 .0 .0 99,942
E 488 396 57.2 0.0 .0 .0 .0 99,942
------------------------------------------------------------
PGA Aggr Target Histogram DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
2K 4K 105 105 0 0
64K 128K 6 6 0 0
512K 1024K 24 24 0 0
1M 2M 504 504 0 0
4M 8M 2 2 0 0
-------------------------------------------------------------
PGA Memory Advisory DB/Inst: ELEVEN/ELEVEN Snap: 14740
PGA Target Size W/A MB W/A MB Read/ Cache Overallo Estd
Est (MB) Factr Processed Written to Disk Hit % Count Time
---------- ------- ---------------- ---------------- ------ -------- -------
88 0.1 1,189,011.0 563,408.7 68.0 535 1.5E+10
176 0.3 1,189,011.0 470,950.8 72.0 52 1.4E+10
352 0.5 1,189,011.0 260,464.2 82.0 0 1.2E+10
528 0.8 1,189,011.0 111,743.9 91.0 0 1.1E+10
704 1.0 1,189,011.0 70,549.6 94.0 0 1.1E+10
....
Latch Activity DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Name Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
....
cache buffer handles 48 0.0 0 0 N/A
cache buffers chains 1,545,866 0.0 0.0 0 181 0.0
cache buffers lru chain 1,680 0.0 0 2,704 0.0
call allocation 246 0.0 0 0 N/A
cas latch 1 0.0 0 0 N/A
change notification clie 1 0.0 0 0 N/A
channel handle pool latc 5 0.0 0 0 N/A
channel operations paren 1,062 0.0 0 0 N/A
checkpoint queue latch 19,515 0.0 0 461 0.0
....
Segments by Logical Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
SYS SYSTEM OBJ$ TABLE 595,216 77.22
SYS SYSTEM IND$ TABLE 120,800 15.67
SYS SYSTEM I_IND1 INDEX 38,752 5.03
SYS SYSAUX WRH$_SEG_STAT_OBJ_PK INDEX 2,864 .37
SYS SYSTEM T1 TABLE 2,256 .29
-------------------------------------------------------------
Segments by Physical Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Physical Read Requests DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by UnOptimized Reads DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Physical Writes DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Table Scans DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by DB Blocks Changes DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Segments by Row Lock Waits DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
....
Dictionary Cache Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> "Pct Misses" should be very low (< 2% in most cases)
-> "Final Usage" is the number of cache entries being used
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 8 0.0 0 N/A 2 1
dc_global_oids 7 0.0 0 N/A 0 31
dc_histogram_data 514 0.0 0 N/A 0 4,728
dc_histogram_defs 2,020 12.4 0 N/A 0 3,435
dc_objects 656 0.3 0 N/A 0 1,685
dc_rollback_segments 45 0.0 0 N/A 0 23
dc_segments 312 6.1 0 N/A 7 894
dc_tablespaces 1,392 0.0 0 N/A 0 16
dc_users 1,259 0.0 0 N/A 0 158
global database name 155 0.0 0 N/A 0 1
-------------------------------------------------------------
Library Cache Activity DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> "Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 9 0.0 12 0.0 0 0
CLUSTER 2 0.0 1 0.0 0 0
DBLINK 3 0.0 0 N/A 0 0
EDITION 3 0.0 3 0.0 0 0
INDEX 11 0.0 11 0.0 0 0
SCHEMA 60 0.0 0 N/A 0 0
SQL AREA 440 17.0 3,059 6.1 57 0
TABLE/PROCEDURE 490 0.0 1,023 9.8 60 0
-------------------------------------------------------------
Memory Dynamic Components DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Min/Max sizes since instance startup
-> Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
-> ordered by Component
Begin Snap Current Min Max Oper Last Op
Component Size (Mb) Size (Mb) Size (Mb) Size (Mb) Count Typ/Mod
--------------- ----------- ----------- ----------- ----------- ------ -------
....
DEFAULT buffer 448.00 448.00 448.00 560.00 0 SHR/DEF
KEEP buffer cac .00 .00 .00 .00 0 STA/
PGA Target 704.00 704.00 704.00 704.00 0 STA/
RECYCLE buffer .00 .00 .00 .00 0 STA/
SGA Target 1,344.00 1,344.00 1,344.00 1,344.00 0 STA/
-------------------------------------------------------------
SGA Memory Summary DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ ------------------- -------------------
Database Buffers 469,762,048
Fixed Size 2,215,064
Redo Buffers 4,964,352
Variable Size 1,660,945,256
-------------------
sum 2,137,886,720
-------------------------------------------------------------
SGA breakdown difference DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> ordered by Pool, Name
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- -------
java free memory 16.0 16.0 0.00
large PX msg pool 7.8 7.8 0.00
large free memory 8.2 8.2 0.00
shared ASH buffers 15.5 15.5 0.00
....
-------------------------------------------------------------
init.ora Parameters DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> if IP/Public/Source at End snap is different a '*' is displayed
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
_nlj_batching_misses_enabled 0
audit_file_dest /oracle/eleven/admin/ELEVEN/adump
audit_trail DB
compatible 11.2.0.0.0
control_files /data01/oradata/ELEVEN/control01.
db_block_size 8192
db_domain
db_name ELEVEN
....
Buffer Pool Statistics DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- ------------ ------------ ----------- ------ ------ --------
D 55,109 100 770,869 47 1,574 0 0 8
-------------------------------------------------------------
Undo Segment Stats DB/Inst: ELEVEN/ELEVEN Snaps: 14739-14740
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
14-Mar 15:22 436 259 513 3 24 0/0 0/0/0/0/0/0
-------------------------------------------------------------
성능 문제를 파악하는 가장 직관적인 방법은 차이를 보는 것
AWR Diff를 사용하는 간단한 예제
***** 첫번째 스냅샷을 생성한다.
col begin_snap new_value begin_snap;
col db_id new_value db_id;
col inst_num new_value inst_num;
select dbid as db_id from v$database;
DB_ID
----------
3588319577
select instance_number as inst_num from v$instance;
INST_NUM
----------
1
select dbms_workload_repository.create_snapshot as begin_snap2 from dual;
BEGIN_SNAP
----------
14791
***** 10개의 세션에서 동시에 TEMP.SQL 파일을 실행한다.
***** 10개의 세션에서 작업이 끝난 후 두번째 스냅샷을 생성한다.
col end_snap new_value end_snap;
select dbms_workload_repository.create_snapshot as end_snap2 from dual;
END_SNAP
----------
14792
***** DBMS_WORKLAD_REPOSITORY.AWR_DIFF_REPORT_TEXT 함수를 이용해 생성
select * from table (
dbms_workload_repository.awr_diff_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap,
&db_id,
&inst_num,
&begin_snap2,
&end_snap2)
);
Snapshot Set Begin Snap Id Begin Snap Time End Snap Id End Snap Time Avg Active Users Elapsed Time (min) DB time (min)
------------ -------------- ------------------------- ------------ ------------------------- -------------------------- -------------------------- --------------------------
1st 14739 14-Mar-12 15:22:14 (Wed) 14740 14-Mar-12 15:26:02 (Wed) 0.89 3.79 3.38
2nd 14791 16-Mar-12 17:49:29 (Fri) 14792 16-Mar-12 17:52:14 (Fri) 3.66 5.76 13.88
~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~
%Diff: 466.29% 151.97%% 310.89%
1st
---------------------------------------------------------------------------
Event Wait Class Waits Time(s) Avg Time(ms) %DB time
------------------------------ ------------- ------------ ------------ ------
CPU time N/A 160.9 N/A 79.40
enq: TX - row lock contention Application 480 41.3 86.1 20.40
db file async I/O submit System I/O 193 11.8 61.0 5.81
log file parallel write System I/O 1,052 3.1 2.9 1.51
control file parallel write System I/O 114 2.9 25.6 1.44
-db file sequential read User I/O 278 0.1 0.4 0.06
-----------------------------------------------------------
2nd
---------------------------------------------------------------------------
Event Wait Class Waits Time(s) Avg Time(ms) %DB time
------------------------------ ------------- ------------ ------------ -----
enq: TX - row lock contention Application 5,135 509.0 99.1 61.12
CPU time N/A 322.6 N/A 38.74
log file parallel write System I/O 2,034 5.5 2.7 0.66
control file parallel write System I/O 78 1.8 22.6 0.21
db file sequential read User I/O 190 0.2 0.9 0.02
- N/A N/A N/A N/A
---------------------------------------------------------------------------
AWR SQL 리포트 사용 예제
-- 우선 SQL ID를 알아내야 합니다.
col sql_id new_value sql_id
select sql_id, sql_text from v$sqlarea where sql_text like 'SELECT * FROM USER_OBJECTS%';
SQL_ID SQL_TEXT
--------------------------------------- --------------------------------------------------
7dazh7hup4yq3 SELECT * FROM USER_OBJECTS
select * from table (
dbms_workload_repository.awr_sql_report_text (
&db_id,
&inst_num,
&begin_snap,
&end_snap2,
'&sql_id')
);
OR
@?/rdbms/admin/awrsqlrpt 이용
Top SQL에 속하지 않은 SQL문을 "색깔을 칠하여" AWR/AWR SQL 리포트에 포함
exec dbms_workload_repository.add_colored_sql('&sql_id');
exec dbms_workload_repository.remove_colored_sql('&sql_id');