Friday, February 10, 2023

Finding High CPU Consuming SQLs with Multiple Plans

 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';
--------------- ------------ --------------------------------------------------------- ---------- ---------- -----------------------------
      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

Popular Posts - All Times