In a DBA’s life this is very common to see high
CPU usage of a server. If this is happening most of the time and occurrence is
common, you may think of increasing the number of CPUs of the system. This CPU
busy rate may be observed in CPU
usage percentage or CPU load average matrics. But if this is not a very
commonly occurring issue of your system and someday you suddenly see high CPU
usage, you may immediately want to kick out the sessions that are consuming
high CPU to avoid a system hang and reboot (or node eviction in case of RAC).
This
article to find out high CPU
consuming sessions. But, you may not want to immediately kill the session with
high CPU usage, and could do some investigation before killing them. In the
following, I am explaining a real incident that I faced when one node of my 3
nodes RAC started consuming 100% CPU.
Firstly, I generated ADDM
report to find out the top SQLs with high CPU usage. High CPU consuming SQLs
usually don’t show any other wait events in the ADDM report, therefore, you can
easily find out the top SQLs with high CPU usage. You may also want look into
the section “SQL Ordered by CPU Time” of AWR report to find out top SQLs by CPU
consumption.
Once you have found out top SQLs, next is to use
following query to figure out how many sessions are currently executing this
SQL.
|
SQL>
select sid,serial#,machine,sql_child_number from v$session where
sql_id='bqp2ps6zxqxvx';
SID
SERIAL# MACHINE
SQL_CHILD_NUMBER
----------
---------- ------------------------ -------------------------------------
12 48689 MYAPPSERVER01 4
1108 49681 MYAPPSERVER01 4
1111 55531 SGPVSLORSAP02 4
2054 25217 MYAPPSERVER01 4
2213 8679 MYAPPSERVER01 4
4252 25507 MYAPPSERVER01 4
4557 695 MYAPPSERVER01 4
5500 11553 MYAPPSERVER01 4
6289 36331 MYAPPSERVER01 4
6448 26305 MYAPPSERVER01 4
6911 14317 MYAPPSERVER01 4
|
Finding All Execution plans of the SQL
Above query returned
sessions currently executing this SQL and the cursor child number in use. I used
following query to find out all execution plans of this SQL currently in shared
pool.
|
SQL>
select
plan_hash_value,child_number,last_load_time,executions,cpu_time,(cpu_time/1000000)/executions
CPU_SEC_PER_EXEC from v$sql where sql_id='bqp2ps6zxqxvx';
PLAN_HASH_VALUE CHILD_NUMBER LAST_LOAD_TIME
EXECUTIONS CPU_TIME CPU_SEC_PER_EXEC
---------------
------------ ---------------------------------------------------------
---------- ---------- -----------------------------
394908553 0 2018-05-04/17:47:16 1 1981212 1.981212
1998046819 1 2018-05-04/17:50:15 1 185906391 185.906391
394908553 2 2018-05-04/18:05:08 1 5054432 5.054432
1998046819 3 2018-05-04/18:30:25 1 21886940 21.88694
2900290368 4 2018-05-02/16:54:46 100 1.5451E+11 1545.09896
|
Finding all current execution plans of the SQL in use by sessions
If you take a look at the
output of the query above, the values under SQL_CHILD_NUMBER column show that
child cursor number 4 of this SQL is the one using an SQL plan that is consuming
higher CPU per execution as compared to the other child cursors. This is also
the child number of this SQL being used by all sessions currently, and this is
also the worst plan as you can see CPU_SEC_PER_EXEC (CPU used by each
execution) column.
By consulting with your
application team and/or end users, you may go for killing these sessions that
are using the worst execution plan and hogging the CPU.
To avoid this form happening in future, you may
want to create and SQL Profile to help optimizer pick the best plan, or create
an SQL
Baseline for this SQL to select only the best execution plan for this
SQL.
As a temporary solution, you might want to invalidate
the current cursor of this SQL so that next time a better plan might by loaded
by the optimizer. Here
is how we do it.
No comments:
Post a Comment