How to compare AWR report for two time period

You can do it using awrddrpt.sql

During performance troubleshooting we need to compare the AWR report for good time and bad time. Below the step to compare the awr for two time period


Consider the time you are comparing are::::::::::


First pair of snap id's:
25 April 2016 5:00 AM SNAP ID  19102
25 April 2016 6:00 AM SNAP ID  19103


Second pair of snap id's:
29 April 2016 5:00 AM SNAP ID  19198
29 April 2016 6:00 AM SNAP ID  19199


Example:

connect to sqlplus using sysdba


sql> @?/rdbms/admin/awrddrpt.sql


Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19102
First Begin Snapshot Id specified: 19102

Enter value for end_snap: 19103
First End   Snapshot Id specified: 19103




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 98999865678  1 TSTPRD      TSTPRD      ashish.mydomain.com
                                             
|
|
|
|
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 98999865678 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days2: 19198

Listing the last 19198 days of Completed Snapshots



                              19275 02 May 2016 10:00      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 19199
Second Begin Snapshot Id specified: 19199

Enter value for end_snap2: 19199
Second End   Snapshot Id specified: 19199



And then you got the report to compare.

Longest running session in Oracle database

This script helps in identifying the session which is running on Oracle database from long time. In various performance issue scenario, DBA and application developer is keen to know about the session which is taking long time.

set pages 10000
set lines 180
col USERNAME for a25
col SESSION_RUNNING_FROM for a50
SELECT s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,floor((sysdate-s.logon_time)*24) || ' HOURS ' || mod(floor((sysdate-s.logon_time)*24*60),60) || ' MINUTES ' || mod(floor((sysdate-s.logon_time)*24*60*60),60)|| ' SECS ' Session_running_since  FROM v$session s where s.username<>'SYS' order by SESSION_RUNNING_SINCE desc;

SID	SERIAL#	USERNAME	STATUS	SQL_ID	SESSION_RUNNING_SINCE
158	30	ASHISH	ACTIVE	90ya7z9gh6cx1	0 HOURS 48 MINUTES 44 SECS
152	34	ASHISH	INACTIVE		0 HOURS 40 MINUTES 20 SECS

With the above output, it becomes easier to identify the session which is running from long time. Session_running_since column will show the session running in Hours:Minutes:Seconds(HH:MM:SS) format. Also sid and sql_id is available to investigate further.

SQL currently using the most resources

While investigating performance issue, we may need to find out the sql which is most resource intensive. Below is the script, this will produce the list on resource intensive sqls currently running in you database.

Here goes script as;

select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sqlarea.sql_id,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username,sqlarea.sql_id
order by ttl_wait_time desc;

Example,

The output you will be getting as

USER_ID	USERNAME	SQL_TEXT	SQL_ID	TTL_WAIT_TIME
0	SYS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	gm9t6yefb1yu6	2,802,675
331	ASHISH	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	cb7vqpgadycjp	492,902
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	8tyhx4x2x47ga	485,157
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4d3dhvjvuf880	454,847
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	4fq3fkixrsp9d	432,484
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	3t3umn6pod3ds	142,767
322	OTHERS	"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"	rs0tel46zczna	98,390

The above output will provide you the sql which is most resource extensive. Also you get sql_id for your further investigation.

Identify locked objects at oracle

This query helps the DBA and application developer to identify the locked objects. This query gives you the detail for locked object,owner of object and the hostname of the user who has acquired lock on object.

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

Example:

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

OWNER      OBJECT_NAM OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER          MACHINE
---------- ---------- ------------------- ---------- ---------- -------- --------------- ---------------
ASHISH     T1         TABLE                      148         10 INACTIVE oracle          oracle10g.works
                                                                                         tation.com

Above example, shows table t1 is locked. User has occupied lock on oracle10g.workstation.com machine.

Most waiting session in Oracle

While working on performance issues of the database. Many times DBA needs to identify the user session which is waiting the most for the resources.

The below query will identify the most waiting sessions for resources.

select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;


Example:


SQL> select sesion.sid,
  2  sesion.username,
  3  sum(active_session_history.wait_time +
  4  active_session_history.time_waited) ttl_wait_time
  5  from v$active_session_history active_session_history,
  6  v$session sesion
  7  where active_session_history.sample_time between sysdate - 60/2880 and sysdate
  8  and active_session_history.session_id = sesion.sid
  9  group by sesion.sid, sesion.username
 10  order by 3;

       SID USERNAME                       TTL_WAIT_TIME
---------- ------------------------------ -------------
       145                                                                  0
       164     USER1                                             113
       166                                                          66819
       151     TEST                                        4885256


Here we can identify the user session with sid 151 waited for 4885256 seconds.

Good luck.