Top CPU consuming oracle sessions

Use the shell script from here to obtain the details of the Oracle sessions using high CPU. 

Usage:

Log in as oracle user and navigate to the folder where the script has been copied into. Do not forget to grant execute permissions to the script prior to invoking it.

$ ./top_oracle_sessions.sh [cpu_threshold] 

e.g.1 $ ./top_oracle_sessions.sh 90 

     This will gather information if overall CPU usage is above 90%

e.g.1 $ ./top_oracle_sessions.sh  

     This will gather information if overall CPU usage is above 50%. This is the default setting.

Notes:

Use the attached script to obtain the details of the Oracle sessions using high CPU. The script works by follows

• Takes 2 snapshots using Linux "top" command with a interval of 2 seconds between snaps
• Uses the data from the second snapshot to determine if the overall CPU usage is above a set threshold. The threshold is a configurable valu passed in via the command line when the script is invoked. Default threshold is 50%.
• If at the time of execution, the overall CPU usage is below the threshold, then terminates.
• From the top results, picks up top 10 process
• Of the top 10 process, checks if any process belongs to database by looking for "oracle$ORACLE_SID"
• Continue only if database process are in the top 10.
• For each database process selected from above step, creates a list of OS Process Ids.
• Query the database and obtain the following information for each of the OS process id by querying gv$session, gv$process and gv$sql
  • INSTANCE_ID
  • SID
  • SERIAL#
  • OSPID
  • LOGON_TIME
  • USERNAME
  • STATUS
  • PROGRAM
  • MACHINE
  • SQL_ID
  • SQL_CHILD_NUMBER
• Obtain the unique set of SQL_ID and SQL_CHILD_NUMBER
• For each unique combination of SQL_ID and SQL_CHILD_NUMBER, generate EXPLAIN_PLAN with "ADVANCED" option.
• Using the ADVANCED keyword, not only prints the sql text, execution plan, etc but also provides the values used in bind variables.
• The results for analysis are stored in a single file called "oracle_top_session_details_[ddmmyyyy_hhmiss].out"

Email the output file "oracle_top_session_details_[ddmmyyyy_hhmiss].out" to the relevant development team to analyze. Using the data from file and the inputs from development team, one may then kill the sessions using the "alter system kill session 'sid,serial#' immediate;" command.

Decoding/Understanding "oracle_top_session_details_[ddmmyyyy_hhmiss].out".

This file consists of 4 sections 

1. Top 10 process:  This part contains output for the top command for the top 10 processes
2. Checking oracle$ORACLE_SID in the top 10: This part contains process from the 10 that belong to oracle alone. Rest are ignored.
3. Get Session Details : This part contains the Session Details
4. Explain plan for unique SQL_ID: This part contains the explain plan for individual unique queries.

A copy of the shell can be found here "Top CPU consuming Oracle Sessions"

prn_library.sh that is referenced can be found here