oracle的AWR报告样式供参考学习

最新推荐文章于 2022-07-06 16:03:24 发布

嶶風

最新推荐文章于 2022-07-06 16:03:24 发布

阅读量4.3k

点赞数

分类专栏： Oracle管理文章标签： oracle管理

本文链接：https://blog.csdn.net/S630730701/article/details/56480129

版权

oracle的AWR报告样式供参考学习

WORKLOAD REPOSITORY report for(example for learning)

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
ORCL	14498*****	orcl	1	13-Sep-16 15:09	11.2.0.4.0	NO

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
rhel6712971	Linux x86 64-bit	4	4	2	31.68

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	3874	22-Feb-17 01:00:49	109	5.2
End Snap:	3882	22-Feb-17 09:00:06	108	5.3
Elapsed:		479.29 (mins)
DB Time:		0.32 (mins)

Report Summary

Load Profile

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	0.0	0.0	0.00	0.00
DB CPU(s):	0.0	0.0	0.00	0.01
Redo size (bytes):	2,477.5	23,996.5
Logical read (blocks):	32.4	314.3
Block changes:	16.4	158.9
Physical read (blocks):	0.2	2.4
Physical write (blocks):	0.5	5.2
Read IO requests:	0.2	2.4
Write IO requests:	0.3	2.7
Read IO (MB):	0.0	0.0
Write IO (MB):	0.0	0.0
User calls:	0.2	2.0
Parses (SQL):	2.1	19.8
Hard parses (SQL):	0.0	0.1
SQL Work Area (MB):	0.0	0.1
Logons:	0.1	0.6
Executes (SQL):	3.3	31.8
Rollbacks:	0.0	0.0
Transactions:	0.1

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:	100.00	Redo NoWait %:	100.00
Buffer Hit %:	99.25	In-memory Sort %:	100.00
Library Hit %:	99.36	Soft Parse %:	99.35
Execute to Parse %:	37.59	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	84.70	% Non-Parse CPU:	94.85

Top 10 Foreground Events by Total Wait Time

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
DB CPU		30.1		159.0
log file sync	738	.5	1	2.7	Commit
db file sequential read	6,818	.1	0	.3	User I/O
direct path sync	1	0	13	.1	User I/O
Disk file operations I/O	208	0	0	.1	User I/O
SQL*Net message to client	384	0	0	.0	Network
control file sequential read	120	0	0	.0	System I/O
latch free	1	0	0	.0	Other
latch: shared pool	11	0	0	.0	Concurrency
direct path write temp	1	0	0	.0	User I/O

Wait Classes by Total Wait Time

Wait Class	Waits	Total Wait Time (sec)	Avg Wait (ms)	% DB time
DB CPU		30		159.0
System I/O	79,886	22	0	116.1
Concurrency	1,144	14	12	74.1
Commit	756	1	1	2.8
Other	1,773	0	0	1.2
User I/O	7,839	0	0	.5
Configuration	2	0	7	.1
Application	41	0	0	.0
Network	1,152	0	0	.0

Host CPU

CPUs	Cores	Sockets	Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
4	4	2	0.00	0.00	0.2	0.1	0.0	99.7

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
0.2	76.2	0.0

IO Profile

	Read+Write Per Second	Read per Second	Write Per Second
Total Requests:	3.4	2.2	1.2
Database Requests:	0.5	0.2	0.3
Optimized Requests:	0.0	0.0	0.0
Redo Requests:	0.2	0.0	0.2
Total (MB):	0.1	0.0	0.0
Database (MB):	0.0	0.0	0.0
Optimized Total (MB):	0.0	0.0	0.0
Redo (MB):	0.0	0.0	0.0
Database (blocks):	0.8	0.2	0.5
Via Buffer Cache (blocks):	0.8	0.2	0.5
Direct (blocks):	0.0	0.0	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	32,440.9	32,440.9
SGA use (MB):	2,048.0	2,048.0
PGA use (MB):	263.7	262.4
% Host Mem used for SGA+PGA:	7.13	7.12

Cache Sizes

	Begin	End
Buffer Cache:	752M	752M	Std Block Size:	8K
Shared Pool Size:	985M	984M	Log Buffer:	16,140K

Shared Pool Statistics

	Begin	End
Memory Usage %:	85.52	85.56
% SQL with executions>1:	94.56	88.22
% Memory for SQL w/exec>1:	93.33	78.58

Main Report

Report Summary
Wait Events Statistics
SQL Statistics
Instance Activity Statistics
IO Stats
Buffer Pool Statistics
Advisory Statistics
Wait Statistics
Undo Statistics
Latch Statistics
Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Streams Statistics
Resource Limit Statistics
Shared Server Statistics
init.ora Parameters

Wait Events Statistics

Time Model Statistics
Operating System Statistics
Operating System Statistics - Detail
Foreground Wait Class
Foreground Wait Events
Background Wait Events
Wait Event Histogram
Wait Event Histogram Detail (64 msec to 2 sec)
Wait Event Histogram Detail (4 sec to 2 min)
Wait Event Histogram Detail (4 min to 1 hr)
Service Statistics
Service Wait Class Stats

Time Model Statistics

Total time in database user-calls (DB Time): 18.9s
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
DB CPU	30.11	158.95
sql execute elapsed time	11.41	60.24
PL/SQL execution elapsed time	3.07	16.20
parse time elapsed	1.31	6.90
connection management call elapsed time	0.86	4.54
hard parse (sharing criteria) elapsed time	0.19	0.98
hard parse elapsed time	0.18	0.97
repeated bind elapsed time	0.00	0.01
sequence load elapsed time	0.00	0.00
DB time	18.95
background elapsed time	340.34
background cpu time	218.14

Back to Wait Events Statistics
Back to Top

Operating System Statistics

*TIME statistic values are diffed. All others display actual values. End Value is displayed if different
ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Statistic	Value	End Value
BUSY_TIME	32,572
IDLE_TIME	11,431,930
IOWAIT_TIME	2,483
NICE_TIME	229
SYS_TIME	6,473
USER_TIME	25,801
RSRC_MGR_CPU_WAIT_TIME	0
PHYSICAL_MEMORY_BYTES	34,016,796,672
NUM_CPUS	4
NUM_CPU_CORES	4
NUM_CPU_SOCKETS	2
GLOBAL_RECEIVE_SIZE_MAX	4,194,304
GLOBAL_SEND_SIZE_MAX	1,048,586
TCP_RECEIVE_SIZE_DEFAULT	87,380
TCP_RECEIVE_SIZE_MAX	4,194,304
TCP_RECEIVE_SIZE_MIN	4,096
TCP_SEND_SIZE_DEFAULT	16,384
TCP_SEND_SIZE_MAX	4,194,304
TCP_SEND_SIZE_MIN	4,096

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Snap Time	Load	%busy	%user	%sys	%idle	%iowait
22-Feb 01:00:49	0.00
22-Feb 02:00:51	0.00	0.31	0.25	0.06	99.69	0.02
22-Feb 03:00:53	0.00	0.28	0.22	0.06	99.72	0.02
22-Feb 04:00:55	0.08	0.28	0.22	0.06	99.72	0.02
22-Feb 05:00:57	0.00	0.27	0.22	0.05	99.73	0.02
22-Feb 06:00:59	0.00	0.28	0.22	0.06	99.72	0.02
22-Feb 07:00:02	0.00	0.28	0.22	0.05	99.72	0.02
22-Feb 08:00:04	0.00	0.28	0.22	0.05	99.72	0.02
22-Feb 09:00:06	0.00	0.29	0.23	0.06	99.71	0.03

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

s - second, ms - millisecond - 1000th of a second
ordered by wait time desc, waits desc
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Captured Time accounts for 162.0% of Total DB time 18.95 (s)
Total FG Wait Time: .59 (s) DB CPU time: 30.11 (s)

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	%DB time
DB CPU			30		158.95
Commit	738	0	1	1	2.69
User I/O	7,028	0	0	0	0.40
Network	384	0	0	0	0.00
System I/O	120	0	0	0	0.00
Other	1	0	0	0	0.00
Concurrency	11	0	0	0	0.00
Application	0		0		0.00
Configuration	0		0		0.00

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

s - second, ms - millisecond - 1000th of a second
Only events with Total Wait Time (s) >= .001 are shown
ordered by wait time desc, waits desc (idle events last)
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn	% DB time
log file sync	738	0	1	1	0.25	2.69
db file sequential read	6,818	0	0	0	2.30	0.27
direct path sync	1	0	0	13	0.00	0.07
Disk file operations I/O	208	0	0	0	0.07	0.05
SQL*Net message from client	384	0	115,203	300007	0.13
jobq slave wait	62,178	100	31,138	501	20.94

Back to Wait Events Statistics
Back to Top

Background Wait Events

ordered by wait time desc, waits desc (idle events last)
Only events with Total Wait Time (s) >= .001 are shown
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn	% bg time
control file parallel write	10,283	0	14	1	3.46	4.16
os thread startup	1,119	0	14	13	0.38	4.12
db file async I/O submit	8,100	0	4	0	2.73	1.17
log file parallel write	5,725	0	3	1	1.93	1.02
control file sequential read	55,650	0	0	0	18.74	0.12
ADR block file read	120	0	0	1	0.04	0.04
enq: CR - block range reuse ckpt	41	0	0	1	0.01	0.01
log file switch completion	2	0	0	7	0.00	0.00
rdbms ipc reply	2	0	0	7	0.00	0.00
log file sync	18	0	0	1	0.01	0.00
ADR block file write	40	0	0	0	0.01	0.00
Disk file operations I/O	555	0	0	0	0.19	0.00
library cache: mutex X	8	0	0	1	0.00	0.00
asynch descriptor resize	1,311	100	0	0	0.44	0.00
reliable message	106	0	0	0	0.04	0.00
db file single write	14	0	0	0	0.00	0.00
enq: RO - fast object reuse	41	0	0	0	0.01	0.00
db file sequential read	243	0	0	0	0.08	0.00
LGWR wait for redo copy	90	0	0	0	0.03	0.00
log file single write	4	0	0	0	0.00	0.00
rdbms ipc message	146,003	96	344,883	2362	49.18
DIAG idle wait	57,385	100	57,437	1001	19.33
Streams AQ: waiting for time management or cleanup tasks	2	50	57,096	28548010	0.00
Space Manager: slave idle wait	8,441	99	41,858	4959	2.84
dispatcher timer	480	100	28,804	60008	0.16
Streams AQ: qmn slave idle wait	1,027	0	28,765	28009	0.35
Streams AQ: qmn coordinator idle wait	2,054	50	28,765	14004	0.69
smon timer	128	74	28,755	224646	0.04
pmon timer	9,578	100	28,755	3002	3.23
shared server idle wait	958	100	28,748	30008	0.32
VKRM Idle	1	0	14,400	14399900	0.00
SQL*Net message from client	960	0	0	0	0.32
class slave wait	163	0	0	0	0.05

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
% of Waits: value of .0 indicates value was <.05%; value of null is truly 0
% of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
Ordered by Event (idle events last)

Event	Total Waits	<1ms	<2ms	<4ms	<8ms	<16ms	<32ms	<=1s	>1s
		% of Waits
ADR block file read	120	75.0	4.2	8.3	12.5
ADR block file write	40	100.0
ADR file lock	48	100.0
Disk file operations I/O	764	100.0
LGWR wait for redo copy	90	100.0
SQL*Net message to client	1152	100.0
asynch descriptor resize	1311	100.0
buffer busy waits	4	100.0
control file parallel write	10.3K	11.7	85.6	2.1	.2	.1	.1	.2
control file sequential read	55.8K	100.0
db file async I/O submit	8100	96.3	3.1	.3	.1	.0	.1	.0
db file sequential read	7061	100.0		.0
db file single write	14	100.0
direct path sync	1					100.0
direct path write temp	1	100.0
enq: CR - block range reuse ckpt	41	65.9	31.7		2.4
enq: RO - fast object reuse	41	100.0
latch free	2	100.0
latch: cache buffers chains	1	100.0
latch: call allocation	13	100.0
latch: shared pool	12	100.0
library cache: mutex X	8	87.5				12.5
log file parallel write	5725	95.8	1.7	1.3	.9	.2	.0	.1
log file sequential read	4	100.0
log file single write	4	100.0
log file switch completion	2				100.0
log file sync	756	97.8	.9	.4	.3	.1	.1	.4
os thread startup	1119					97.1	2.9
rdbms ipc reply	2	50.0				50.0
reliable message	106	100.0
DIAG idle wait	57.4K							100.0
SQL*Net message from client	1344	85.4	.2	.1					14.3
Space Manager: slave idle wait	8438	.0		.0	.0	.0		.5	99.5
Streams AQ: qmn coordinator idle wait	2054	50.0							50.0
Streams AQ: qmn slave idle wait	1027								100.0
Streams AQ: waiting for time management or cleanup tasks	2							50.0	50.0
VKRM Idle	1								100.0
class slave wait	163	100.0
dispatcher timer	480								100.0
jobq slave wait	62.2K	.0					.0	100.0
pmon timer	9578								100.0
rdbms ipc message	146K	1.4	.3	.2	.4	.6	.2	40.1	56.6
shared server idle wait	959								100.0
smon timer	128	1.6		1.6	.8	1.6		14.1	80.5

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
% of Total Waits: total waits for all wait classes, including Idle
% of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
Ordered by Event (only non-idle events are displayed)

Event	Waits 64ms to 2s	<32ms	<64ms	<1/8s	<1/4s
		% of Total Waits
control file parallel write	25	99.8	.2	.1	.0
db file async I/O submit	4	100.0	.0	.0	.0
log file parallel write	4	99.9	.1
log file sync	3	99.6	.4

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

ordered by DB Time

Service Name	DB Time (s)	DB CPU (s)	Physical Reads (K)	Logical Reads (K)
SYS$USERS	19	30	7	385
SYS$BACKGROUND	0	0	0	548
orcl	0	0	0	0
orclXDB	0	0	0	0

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Wait Class info for services in the Service Statistics section.
Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
Time Waited (Wt Time) in seconds

Service Name	User I/O Total Wts	Concurcy Total Wts	Concurcy Wt Time	Network Total Wts
SYS$USERS	7028	11	0	384
SYS$BACKGROUND	811	1132	14	0
orcl	0	0	0	0

Back to Wait Events Statistics
Back to Top

SQL Statistics

SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by User I/O Wait Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Physical Reads (UnOptimized)
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
Complete List of SQL Text

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
%Total - Elapsed Time as a percentage of Total DB time
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Captured SQL account for 99.6% of Total DB Time (s): 19
Captured PL/SQL account for 61.7% of Total DB Time (s): 19

Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text
9.22	479	0.02	48.68	99.59	0.78	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
2.62	958	0.00	13.81	105.73	0.00	aykvshm7zsabd		select size_for_estimate, size...
1.99	958	0.00	10.49	98.54	0.00	772s25v1y0x8k		select shared_pool_size_for_es...
1.85	440	0.00	9.75	99.34	2.88	3am9cfkvx7gq1		CALL MGMT_ADMIN_DATA.EVALUATE_...
1.18	8	0.15	6.21	99.93	0.00	bunssq950snhf		insert into wrh$_sga_target_ad...
1.16	8	0.15	6.13	99.98	0.00	dayq182sk41ks		insert into wrh$_memory_target...
1.15	8	0.14	6.09	99.98	0.00	bm2pwrpcr8ru6		select sga_size s, sga_size_fa...
1.08	27,505	0.00	5.68	100.94	0.00	cm5vu20fhtnq1		select /*+ connect_by_filterin...
0.73	5,748	0.00	3.87	13.11	0.00	5ms6rbzdnq16t		select job, nvl2(last_date, 1,...
0.60	8	0.08	3.19	99.95	0.00	66gs90fyynks7		insert into wrh$_instance_reco...
0.52	958	0.00	2.74	68.68	0.00	fvkynu9xgck8h		select PGA_TARGET_FOR_ESTIMATE...
0.51	3	0.17	2.69	97.25	0.19	bzscyq07w79ab		delete /*+ dynamic_sampling(4)...
0.50	8	0.06	2.65	100.29	0.01	4dy1xm4nxc0gf		insert into wrh$_system_event ...
0.44	479	0.00	2.30	97.43	0.33	7vcwzqf7mgk3b	EM_PING	INSERT INTO MGMT_JOB_EXECUTION...
0.42	8	0.05	2.23	99.82	0.00	84k66tf2s7y1c		insert into wrh$_bg_event_summ...
0.41	8	0.05	2.14	99.80	0.09	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...
0.39	1	0.39	2.07	99.97	0.00	acc988uzvjmmt		delete from WRH$_SYSMETRIC_HIS...
0.38	3	0.13	2.01	100.35	13.65	gjm43un5cy843		SELECT SUM(USED), SUM(TOTAL) F...
0.33	8	0.04	1.74	100.09	0.01	586b2udq6dbng		insert into wrh$_sysstat (snap...
0.33	504	0.00	1.73	96.90	0.00	4jrfrtx4u6zcx		SELECT TASK_TGT.TARGET_GUID TA...
0.28	48	0.01	1.50	100.71	0.00	6mvfay19q3v4n		SELECT COUNT(CLIENT_INFO) FROM...
0.26	47	0.01	1.39	99.37	0.00	dqbhc9r7gz0a5		SELECT DECODE(COUNT(CLIENT_INF...
0.25	1,086	0.00	1.33	97.18	0.51	18naypzfmabd6	EM_PING	INSERT INTO MGMT_SYSTEM_PERFOR...
0.25	528	0.00	1.32	98.59	0.00	cumjq42201t37		select u1.user#, u2.user#, u3....
0.24	8	0.03	1.27	99.85	0.01	7g732rx16j8jc		insert into WRH$_SERVICE_STAT ...
0.24	721	0.00	1.24	78.93	0.00	0k8522rmdzg4k		select privilege# from sysauth...
0.20	528	0.00	1.08	94.92	0.51	aq8yqxyyb40nn		update sys.job$ set this_date=...
0.19	1,713	0.00	1.00	104.07	0.00	5kyb5bvnu3s04		SELECT DISTINCT METRIC_GUID FR...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total - CPU Time as a percentage of Total DB CPU
%CPU - CPU Time as a percentage of Elapsed Time
%IO - User I/O Time as a percentage of Elapsed Time
Captured SQL account for 59.9% of Total CPU Time (s): 30
Captured PL/SQL account for 38.6% of Total CPU Time (s): 30