AWR Report compares two points in time

cd /u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin

awrddrpt.sql

WORKLOAD REPOSITORY COMPARE PERIOD REPORT

Report Summary

Snapshot Set	DB Name	DB Id	Instance	Inst num	Release	Cluster	Host	Std Block Size
First (1st)	RMAN	1839312449	rman	1	12.1.0.2.0	NO	ol7-122.localdomain	8192
Second (2nd)	RMAN	1839312449	rman	1	12.1.0.2.0	NO	ol7-122.localdomain	8192

Snapshot Set	Begin Snap Id	Begin Snap Time	End Snap Id	End Snap Time	Avg Active Users	Elapsed Time (min)	DB time (min)
1st	138	28-Sep-17 19:07:47 (Thu)	139	28-Sep-17 20:25:06 (Thu)	0.0	77.3	0.0
2nd	128	26-Sep-17 15:00:25 (Tue)	129	26-Sep-17 16:25:47 (Tue)	0.0	85.4	0.0
%Diff					0.0	10.4	-79.8

Host Configuration Comparison

	1st	2nd	Diff	%Diff
Number of CPUs:	1	1	0	0.0
Number of CPU Cores:	1	1	0	0.0
Number of CPU Sockets:	1	1	0	0.0
Physical Memory:	1504.8M	1504.8M	0M	0.0
Load at Start Snapshot:	0	.16	.16	100.0
Load at End Snapshot:	0	.08	.08	100.0
%User Time:	.35	.09	-.26	-74.3
%System Time:	.12	.06	-.07	-50.0
%Idle Time:	99.52	99.85	.33	0.3
%IO Wait Time:	.01	.01	0	0.0

Cache Sizes

	1st (M)	2nd (M)	Diff (M)	%Diff
Memory Target	352.0	352.0	0.0	0.0
SGA Target	200.0	208.0	8.0	4.0
Buffer Cache	40.0	36.0	-4.0	-10.0
Shared Pool	136.0	148.0	12.0	8.8
Large Pool	8.0	8.0	0.0	0.0
Java Pool	4.0	4.0	0.0	0.0
Streams Pool
PGA Target	140.0	140.0	0.0	0.0
Log Buffer	5.0	5.0	0.0	0.0
In-Memory Area

Load Profile

	1st per sec	2nd per sec	%Diff	1st per txn	2nd per txn	%Diff
DB time:	0.0	0.0	0.0	0.0	0.0	0.0
CPU time:	0.0	0.0	0.0	0.0	0.0	-100.0
Background CPU time:	0.0	0.0	0.0	0.1	0.0	-37.5
Redo size (bytes):	595.8	440.1	-26.1	30,041.8	43,350.9	44.3
Logical read (blocks):	77.3	14.6	-81.0	3,895.3	1,442.8	-63.0
Block changes:	2.3	1.7	-28.2	118.0	165.6	40.3
Physical read (blocks):	0.0	0.2	425.0	1.9	20.6	991.0
Physical write (blocks):	0.2	0.1	-43.5	11.5	12.7	10.5
Read IO requests:	0.0	0.2	400.0	1.9	20.0	959.3
Write IO requests:	0.1	0.1	-36.4	5.7	7.0	23.7
Read IO (MB):	0.0	0.0	0.0	0.0	0.2	1,500.0
Write IO (MB):	0.0	0.0	0.0	0.1	0.1	11.1
IM scan rows:	0.0	0.0	0.0	0.0	0.0	0.0
Session Logical Read IM:
User calls:	0.1	0.0	-57.1	3.4	3.3	-1.5
Parses (SQL):	3.5	1.0	-72.3	174.7	94.3	-46.0
Hard parses (SQL):	1.2	0.1	-87.1	58.5	14.4	-75.3
SQL Work Area (MB):	0.0	0.0	-66.7	1.5	0.7	-66.7
Logons:	0.0	0.0	-33.3	1.5	1.5	-2.0
Executes (SQL):	18.3	3.0	-83.5	924.9	297.3	-67.9
Transactions:	0.0	0.0	-50.0

	1st	2nd	Diff
% Blocks changed per Read:	3.0	11.5	8.4
Recursive Call %:	99.9	99.7	-0.2
Rollback per transaction %:	0.0	0.0	0.0
Rows per Sort:	9.9	9.1	-0.7
Avg DB time per Call (sec):	0.0	0.0	-0.0

Top Timed Events

• Events with a "-" did not make the Top list in this set of snapshots, but are displayed for comparison purposes

1st	2nd
Event	Wait Class	Waits	Time(s)	Avg Time(ms)	%DB time	Event	Wait Class	Waits	Time(s)	Avg Time(ms)	%DB time
oracle thread bootstrap	Other	129	3.52	27.26	270.15	control file parallel write	System I/O	428	1.13	2.65	430.15
control file parallel write	System I/O	856	2.20	2.57	169.31	oracle thread bootstrap	Other	70	1.02	14.61	388.31
os thread creation	Other	129	1.60	12.40	122.94	os thread creation	Other	70	0.50	7.20	191.33
CPU time			1.20		91.86	db file async I/O submit	System I/O	223	0.47	2.09	176.81
db file async I/O submit	System I/O	354	1.12	3.17	86.16	log file parallel write	System I/O	75	0.44	5.82	165.90
log file parallel write	System I/O	130	0.75	5.81	57.98	CPU time			0.25		93.68
db file sequential read	User I/O	149	0.08	0.52	5.98	log file sync	Commit	11	0.07	6.40	26.74
cursor: pin S wait on X	Concurrency	9	0.06	6.51	4.50	db file sequential read	User I/O	1,172	0.03	0.03	11.35
LGWR wait for redo copy	Other	5	0.04	8.71	3.35	control file sequential read	System I/O	3,089	0.02	0.01	6.61
control file sequential read	System I/O	6,441	0.04	0.01	3.07	ADR block file read	Other	16	0.01	0.45	2.75
-log file sync	Commit	17	0.03	1.57	2.05	-cursor: pin S wait on X	Concurrency	1	0.00	2.14	0.81
-ADR block file read	Other	16	0.01	0.66	0.82	-LGWR wait for redo copy	Other	1	0.00	1.98	0.75

Report Details

• Report Summary
• Time Model Statistics
• Operating System Statistics
• Wait Events
• Service Statistics
• SQL Statistics
• Instance Activity Statistics
• IO Stats
• Advisory Statistics
• Wait Stats
• Undo Statistics
• Latch Statistics
• Segment Statistics
• Dictionary Cache Statistics
• Library Cache Statistics
• Memory Statistics
• Replication Statistics (GoldenGate, XStream)
• Streams Statistics
• Shared Server Statistics

Supplemental Information

• init.ora Parameters
• SQL Statements

Back to Top

---

Time Model Statistics

• Ordered by absolute value of 'Diff' column of '% of DB time', descending (DB time statistic first, background statistics last)

	% of DB time	% of Total CPU time	Time (seconds)	Time per Trans (seconds)
Statistic Name	1st	2nd	Diff	1st	2nd	Diff	1st	2nd	%Diff	1st	2nd	%Diff
parse time elapsed	77.98	42.83	-35.15				1.01	0.11	-89.11	0.01	0.00	-100.00
hard parse elapsed time	71.52	39.72	-31.80				0.93	0.10	-89.25	0.01	0.00	-100.00
sql execute elapsed time	63.19	38.17	-25.02				0.82	0.10	-87.80	0.01	0.00	-100.00
hard parse (sharing criteria) elapsed time	4.69	1.77	-2.92				0.06	0.00	-100.00	0.00	0.00	0.00
PL/SQL compilation elapsed time	9.77	12.05	2.28				0.13	0.03	-76.92	0.00	0.00	0.00
DB CPU	91.86	93.68	1.82	14.16	9.09	5.07	1.20	0.25	-79.17	0.01	0.00	-100.00
repeated bind elapsed time	0.46	0.17	-0.29				0.01	0.00	-100.00	0.00	0.00	0.00
sequence load elapsed time	1.74	1.54	-0.20				0.02	0.00	-100.00	0.00	0.00	0.00
PL/SQL execution elapsed time	1.75	1.57	-0.18				0.02	0.00	-100.00	0.00	0.00	0.00
background elapsed time							27.54	11.95	-56.61	0.30	0.23	-23.33
background cpu time				85.84	90.91	-5.07	7.25	2.47	-65.93	0.08	0.05	-37.50
DB time							1.30	0.26	-80.00	0.01	0.01	0.00
total CPU time	648.89	1,030.77	381.88				8.45	2.71	-67.87	0.09	0.05	-43.15

---

Back to Top

Operating System Statistics

• Ordered by 'Statistic Name'

	Value (centi-seconds)	per Second (DB time)	per Second (Elapsed Time)	per Trans
Statistic Name	1st	2nd	1st	2nd	%Diff	1st	2nd	%Diff	1st	2nd	%Diff
BUSY_TIME	1,108	178	851.25	676.03	-20.58	0.24	0.03	-87.50	12.04	3.42	-71.59
FREE_MEMORY_BYTES	-38,912,000	-22,065,152	-29,895,168.69	-83,802,324.34	180.32	-8,388.44	-4,307.90	-48.64	-422,956.52	-424,329.85	0.32
GLOBAL_RECEIVE_SIZE_MAX	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
GLOBAL_SEND_SIZE_MAX	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
IDLE_TIME	228,309	115,115	175,404.40	437,200.91	149.25	49.22	22.47	-54.35	2,481.62	2,213.75	-10.79
INACTIVE_MEMORY_BYTES	2,252,800	-598,016	1,730,772.92	-2,271,234.33	-231.23	485.65	-116.75	-124.04	24,486.96	-11,500.31	-146.97
IOWAIT_TIME	26	11	19.98	41.78	109.11	0.01	0.00	-100.00	0.28	0.21	-25.00
NICE_TIME	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
RSRC_MGR_CPU_WAIT_TIME	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
SWAP_FREE_BYTES	0	16,384	0.00	62,225.60	100.00	0.00	3.20	100.00	0.00	315.08	100.00
SYS_TIME	281	65	215.89	246.87	14.35	0.06	0.01	-83.33	3.05	1.25	-59.02
TCP_RECEIVE_SIZE_DEFAULT	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
TCP_RECEIVE_SIZE_MAX	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
TCP_RECEIVE_SIZE_MIN	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
TCP_SEND_SIZE_DEFAULT	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
TCP_SEND_SIZE_MAX	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
TCP_SEND_SIZE_MIN	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
USER_TIME	811	109	623.07	413.98	-33.56	0.17	0.02	-88.24	8.82	2.10	-76.19
VM_IN_BYTES	0	32,768	0.00	124,451.20	100.00	0.00	6.40	100.00	0.00	630.15	100.00
VM_OUT_BYTES	0	0	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00

---

Back to Top

Wait Events

• Wait Classes
• Wait Events

Back to Top

Wait Classes

• Ordered by absolute value of 'Diff' column of '% of DB time' descending

	% of DB time	# Waits/sec (Elapsed Time)	Total Wait Time (sec)	Avg Wait Time (ms)
Wait Class	1st	2nd	Diff	1st	2nd	%Diff	1st	2nd	%Diff	1st	2nd	%Diff
System I/O	316.52	779.47	462.95	1.68	0.74	-55.95	4.12	2.05	-50.24	0.53	0.54	1.89
Other	399.19	584.24	185.05	0.12	0.06	-50.00	5.20	1.54	-70.38	9.41	5.08	-46.01
Commit	2.05	26.74	24.69	0.00	0.00	0.00	0.03	0.07	133.33	1.57	6.40	307.64
Concurrency	11.21	0.81	-10.39	0.01	0.00	-100.00	0.15	0.00	-100.00	4.56	2.14	-53.07
User I/O	6.40	11.90	5.49	0.07	0.25	257.14	0.08	0.03	-62.50	0.26	0.02	-92.31

---

Back to Wait Events