How to Resolve a Hung AWR Process in Oracle

If you encounter a hung AWR (Automatic Workload Repository) process in Oracle, you may need to identify and terminate the associated session. Here’s a step-by-step guide on how to troubleshoot and resolve the issue:

1. Identify the Session Associated with the AWR Process: First, use the following SQL query to identify the sessions related to the AWR process:

SQL> SELECT sid, serial#, program, logon_time FROM v$session WHERE program LIKE '%M00%';

This will return a list of sessions associated with AWR processes, for example:

SID    SERIAL# PROGRAM                                          LOGON_TIME
---------- ---------- ------------------------------------------------ --------
   2118      13632 oracle@hostname (QM00)                         17.05.20
   2331      23309 oracle@hostname (M000)                         09.06.20
   7861      57138 oracle@hostname (M003)                         14.06.20

In this case, you can see multiple processes such as QM00, M000, and M003.

2. Locate the Process on the OS Level: Once you have identified the session, you can locate the corresponding process in the operating system. First, access the Oracle user’s terminal session. Then, use the following command to list the processes associated with AWR sessions:

[hostname:/path/to/oracle_home][oracle_user]$ps -ef | grep m00

This will show you the processes associated with the AWR session. For example:

ora01 23972     1  0  May 17  ?         0:29 ora_qm00_hostname_o
ora01 28150     1 200  Jun  9  ?        713:56 ora_m000_hostname_o
ora01 26346 24281  0 15:45:53 pts/3     0:00 grep m00
ora01  2279     1  0 14:31:02 ?         0:08 ora_m003_hostname_o

In this example, you can see the processes for qm00, m000, and m003.

3. Kill the Hung AWR Session:

If you identify a hung process that needs to be terminated, you can kill the session at the OS level. For example, use the kill -9 command with the appropriate process ID (PID):

kill -9 26346

This will forcefully terminate the session and should resolve the issue with the hung AWR process.

4. Verify the Process is Terminated:

After killing the process, run the following SQL query again to verify that the session is no longer active:

SQL> SELECT sid, serial#, program, logon_time FROM v$session WHERE program LIKE '%M00%';

If the session was successfully terminated, it should no longer appear in the list.