My DB is slow today but the same time yesterday it is not ... what to do ?

How to compare two awr snapshot periods in a single file?

Sometimes User may say, my DB was good yesterday the same period but today it is very slow.

At these times, we may face a scenario where we need to compare DB performance of specific time period of two different dates.

There are 2 awr related sql scripts which may come handy in this case.

· awrddrpt.sql

· awrddrpi.sql

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

To generate an AWR Compare Periods report:

1. At the SQL prompt, enter:

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

2. Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot Ids in the first time period.

Enter value for num_days: 2

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Specify a beginning and ending snapshot ID for the first time period:

Enter value for begin_snap: 102

Enter value for end_snap: 103

In this example, the snapshot with a snapshot ID of 102 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 103 is selected as the ending snapshot for the first time period.

5. Specify the number of days for which you want to list snapshot Ids in the second time period.

Enter value for num_days2: 1

A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

6. Specify a beginning and ending snapshot ID for the second time period:

Enter value for begin_snap2: 126

Enter value for end_snap2: 127

In this example, the snapshot with a snapshot ID of 126 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 127 is selected as the ending snapshot for the second time period.

7. Enter a report name, or accept the default report name:

Enter value for report_name: 

Using the report name awrdiff_1_102_1_126.txt

In this example, the default name is accepted and an AWR report named awrdiff_1_102_126 is generated.

Sample comparison report :

Load Profile

	1st Per Sec	2nd Per Sec	%Diff	1st Per Txn	2nd Per Txn	%Diff
Redo size:	123,857.13	121,099.81	-2.23	2,880.38	2,991.06	3.84
Logical reads:	106,735.14	102,118.46	-4.33	2,482.20	2,522.23	1.61
Block changes:	661.82	653.86	-1.20	15.39	15.39	0.00
Physical reads:	8.13	18.04	121.89	0.19	0.45	136.84
Physical writes:	18.60	20.45	9.95	0.43	0.51	18.60
User calls:	17,570.41	15,607.24	-11.17	408.61	385.48	-5.66
Parses:	420.86	394.35	-6.30	9.79	9.74	-0.51
Hard parses:	2.09	2.60	24.40	0.05	0.06	20.00
Sorts:	162.41	156.78	-3.47	3.78	3.87	2.38
Logons:	0.68	0.97	42.65	0.02	0.02	0.00
Executes:	473.63	438.40	-7.44	11.01	10.83	-1.63
Transactions:	43.00	40.49	-5.84

	1st	2nd	Diff
% Blocks changed per Read:	0.62	0.64	0.02
Recursive Call %:	0.67	1.12	0.45
Rollback per transaction %:	0.48	0.50	0.02
Rows per Sort:	29.53	48.07	18.54
Avg Length of Calls (sec):	0.00	0.00	0.00

Top 5 Timed Events

1st					2nd
Event	Waits	Time(s)	Percent Total DB Time	Wait Class	Event	Waits	Time(s)	Percent Total DB Time	Wait Class
CPU time		4,195.0	87.32		CPU time		3,690.9	88.48
log file sync	160,150	212.3	4.42	Commit	log file sync	148,704	197.6	4.74	Commit
gc cr block 2-way	317,347	124.8	2.60	Cluster	gc cr block 2-way	283,227	109.8	2.63	Cluster
db file sequential read	29,094	112.7	2.34	User I/O	db file sequential read	20,843	98.1	2.35	User I/O
*enq: TX - row lock contention	3,188	109.6	2.28	Application	*gc current block 2-way	233,458	87.8	2.11	Cluster
-gc current block 2-way	226,309	87.0	1.81	Cluster	-enq: TX - row lock contention	2,354	39.7	.95	Application