A working example of bad SQL statement causes too much CPU usage

1. Performance Issue Analysis

 

The "task list" query has a huge CPU cost, 20 concurrent execution (user access) can make the CPU (8 cores) usage ratio up to about 80%.

 

​

 

The reasons that lead to high CPU cost include...

 

• Use the complex views instead of the underlying tables. The consequence is that a lot of unnecessary table (data) access (i.e. I/O cost, LIO is CPU operation) and table joins ( i.e. CPU cost) are involved.
• Use the uncessary sort operation which is CPU intensive. (e.g. distinct, group by, etc)
• Use the wrong table joins operation. (e.g. using correlated scalary sub-query, semi-join, etc)

2. Oracle parameter configuration changes:

Can view via the following SQL statement

select name, value, display_value  from v$parameter where name in ('sga_target', 'sga_max_size', 'pga_aggregate_target', 'session_cached_cursors');​

             

​Parameter Name	​Original Value	Adjusted Value​
​SGA_MAX_SIZE	​4G	​8G
SGA_TARGET​	​4G	​6G
​PGA_AGGREGATE_TARGET	​1G	​3G
SESSION_CACHED_CURSOR​	​50	​150
​	​	​
​	​	​
​	​	​

     

SQL>alter system set sga_max_size=8G scope=spfile;

SQL>alter system set sga_target=6G scope=spfile;

SQL>alter system set pga_aggregate_target=3G;

SQL>alter system set session_cached_cursors=150 scope=spfile;

Comment: The effect is not significant at all as these parameter changes can ease the I/O cost, not CPU cost.

3. Table storage parameter change

Seen from the AWR report, the table cd_users is really hot, and it's better to "pin" it in the buffer cache. Can be done by executing the following statment...

alter table ro_load_test.cd_users storage(buffer_pool keep);​

Comment: The effect is not significant neither.

 

3. SQL Statement Change

Before Tuned...

​

​​

WITH user_permission AS

( SELECT grantee_id,

        vgpa.priv_id,

        action_type,

        vgpa.object_name,

        CASE vgpa.object_name WHEN 'N/A' THEN NULL ELSE vleg.long_name END long_name

  FROM ( SELECT v_grantee_priv.grantee_id,

                v_priv_all.priv_id,

                v_priv_all.action_type_id,

                v_priv_all.object_name

        FROM

        ( SELECT ug.grantee_id,

                 pp.priv_id

          FROM grantee_priv gp,

               (SELECT group_grantee_id,

                       grantee_id

                FROM ( SELECT DISTINCT gm.group_grantee_id,

                              CONNECT_BY_ROOT gm.grantee_id AS grantee_id

                       FROM grantee_member gm,

                            grantees g

                      WHERE gm.group_grantee_id = g.grantee_id

                      CONNECT BY PRIOR gm.group_grantee_id = gm.grantee_id

                      UNION ALL

                      SELECT grantee_id,

                             grantee_id

                      FROM grantees

                      WHERE grantee_id > 0

                     )

                WHERE grantee_id > 0

                AND grantee_id IN (SELECT user_id FROM cd_users)

                UNION ALL

                SELECT 0,

                      user_id

                FROM cd_users

                WHERE user_id != 1

               ) ug,

               ( SELECT role_priv_id,

                        priv_id

                 FROM ( SELECT DISTINCT prp.role_priv_id,

                               CONNECT_BY_ROOT prp.priv_id AS priv_id

                        FROM priv_role_priv prp,

                             priv_role pr

                        WHERE prp.role_priv_id = pr.priv_id

                        CONNECT BY PRIOR prp.role_priv_id = prp.priv_id

                       )

                UNION ALL

                SELECT priv_id,

                       priv_id

                FROM priv

                WHERE grant_access = 'Y'

               ) pp

        WHERE gp.grantee_id = ug.group_grantee_id

        AND gp.priv_id = pp.role_priv_id

        GROUP BY ug.grantee_id, pp.priv_id

        ) v_grantee_priv,

        ( SELECT p.priv_id,

                 pop.object_name,

                 pop.action_type_id

          FROM priv p,

               priv_other_app pop

          WHERE p.priv_id = pop.priv_id

          AND p.grant_access = 'Y'

         ) v_priv_all

  WHERE v_grantee_priv.priv_id = v_priv_all.priv_id

  ) vgpa,

  v_access_external vae,

  v_lo_entity_group vleg

 WHERE vae.action_type_id = vgpa.action_type_id

 AND UPPER (vae.application) = 'RISKORIGINS'

 AND UPPER (vae.object_type) = 'WORKFLOW ROLE'

 AND vleg.group_name(+) = vgpa.object_name

 AND grantee_id IN (SELECT user_id FROM cd_users cu WHERE UPPER (cu.user_name) IN ('RO_USER'))

)

SELECT /*+result_cache*/

       task.task_id,

       task.task_name,

       task.description,

       task.couterparty_id,

       task.entity_code,

       task.short_name,

       task.long_name,

       task.assignee,

       task.process_id,

       task.process_code,

       task.process_name,

       task.process_deployment_name,

       CASE WHEN EXISTS

          ( SELECT 1 FROM lo_bpm_audit la WHERE task.task_id = la.next_task_id AND la.is_reject = 1) THEN 'T'

          ELSE 'F'

      END AS is_redo,

      createtime,

      task.entity_group_id,

      p.groupid_,

      p.userid_

FROM

(SELECT DISTINCT le.ID couterparty_id,

        le.entity_code,

        le.short_name,

        le.long_name,

        le.entity_group_id,

        t.dbid_ task_id,

        t.activity_name_ task_name,

        TO_CHAR (t.descr_) description,

        t.assignee_ assignee,

        t.create_ createtime,

        dpkey.stringval_ AS process_code,

        dpid.objname_ AS process_name,

        dpid.stringval_ AS process_deployment_name,

        jhp.id_ AS process_id

FROM jbpm4_variable v,

     jbpm4_task t,

     jbpm4_participation participant,

     jbpm4_hist_procinst jhp,

     jbpm4_deployprop dpid,

     jbpm4_deployprop dpkey,

     lo_entity le

WHERE t.execution_ = v.execution_

AND t.procinst_ = jhp.dbid_

AND v.key_ = 'counterparty_id'

AND dpid.key_ = 'pdid'

AND dpid.stringval_ = jhp.procdefid_

AND dpkey.key_ = 'pdkey'

AND dpkey.deployment_ = dpid.deployment_

AND le.ID = v.string_value_

AND t.assignee_ IS NULL

AND participant.task_ = t.dbid_

AND ( UPPER (participant.userid_) IN ('RO_USER')

    OR (EXISTS ( SELECT priv_id

                 FROM user_permission UP

                 WHERE UPPER (UP.action_type) = UPPER (participant.groupid_)

                 AND ( UP.object_name = 'N/A'

                       OR EXISTS ( SELECT vleg_.GROUP_ID

                                   FROM v_lo_entity_group vleg_

                                   WHERE vleg_.long_name LIKE UP.long_name || '%'

                                   AND vleg_.GROUP_ID = le.entity_group_id)

                      )

                )

        )

     )

) task

LEFT OUTER JOIN jbpm4_participation p

ON p.task_ = task.task_id

AND p.type_ = 'candidate'​

 

After Tuned...

 

WITH v_jbpm4_deployprop AS

 

(

 

SELECT /*+result_cache*/

 

    MAX(CASE key_ WHEN 'pdkey' THEN stringval_ ELSE NULL END) AS process_code,

 

    MAX(CASE key_ WHEN 'pdid' THEN objname_ ELSE NULL END) AS process_name,

 

    MAX(CASE key_ WHEN 'pdid' THEN stringval_ ELSE NULL END) AS process_deployment_name

 

FROM jbpm4_deployprop

 

WHERE key_ IN ('pdid', 'pdkey')

 

GROUP BY deployment_

 

),

 

v_entity_task AS

 

(

 

SELECT  /*+result_cache*/

 

        le.id couterparty_id,

 

        le.entity_code,

 

        le.short_name,

 

        le.long_name,

 

        le.ENTITY_GROUP_ID,

 

        t.dbid_ task_id,

 

        t.activity_name_ task_name,

 

        to_char(t.descr_) description,

 

        t.assignee_ assignee,

 

        t.create_ createtime,

 

        t.PROCINST_,

 

        dep.process_code,

 

        dep.process_name,

 

        dep.process_deployment_name,

 

        jhp.id_ AS process_id,

 

        participant.groupid_,

 

        participant.userid_

 

FROM      jbpm4_task         t,

 

        lo_entity         le,

 

        jbpm4_variable     v,

 

        v_jbpm4_deployprop dep,

 

        jbpm4_hist_procinst jhp,

 

        jbpm4_participation participant

 

WHERE     v.key_ = 'counterparty_id'

 

AND     t.execution_ = v.execution_ 

 

AND        le.id = v.string_value_

 

AND        t.ASSIGNEE_ IS NULL

 

AND     dep.process_deployment_name = jhp.procdefid_

 

AND     t.procinst_ = jhp.dbid_

 

AND     participant.task_ = t.dbid_

 

),

 

v_lo_entity_grps AS

 

(

 

 SELECT /*+result_cache*/

 

        gg.group_id,

 

        gg.group_name,

 

        fgm.full_group_names,

 

        DECODE(fgm.full_group_names, NULL, gg.group_name, fgm.full_group_names || ', ' || gg.group_name) long_name

 

  FROM     lo_entity_group gg,

 

       (SELECT  DISTINCT gm.group_id,

 

                SUBSTR(csv_list(' ' ||g.group_name)

 

                      over (PARTITION BY gm.group_id ORDER BY gm.distance DESC

 

                            ROWS BETWEEN unbounded preceding AND unbounded following),

 

                      2) full_group_names

 

        FROM

 

          (

 

            SELECT  parent_group_id,

 

                    group_id,

 

                    MIN(distance) distance

 

            FROM

 

            (    SELECT     l.parent_group_id,

 

                        connect_by_root l.group_id AS group_id,

 

                        LEVEL distance

 

                FROM     lo_entity_group_link l

 

                CONNECT BY PRIOR l.parent_group_id = l.group_id

 

            )

 

            GROUP BY     parent_group_id,

 

                        group_id

 

          )gm,

 

          lo_entity_group g

 

        WHERE gm.parent_group_id = g.group_id

 

        ) fgm

 

WHERE     gg.group_id = fgm.group_id(+)

 

),

 

user_permission AS

 

( SELECT vgpa.grantee_id,

 

         vgpa.priv_id,

 

         vgpa.action_type,

 

         vgpa.object_name,

 

         CASE vgpa.object_name WHEN 'N/A' THEN NULL ELSE vleg.long_name END long_name

 

  FROM ( SELECT v_grantee_priv.grantee_id,

 

                v_priv_all.priv_id,

 

                v_priv_all.action_type,

 

                v_priv_all.object_name

 

        FROM

 

        ( SELECT ug.grantee_id,

 

                 pp.priv_id

 

          FROM grantee_priv gp,

 

               (SELECT group_grantee_id,

 

                       grantee_id

 

                FROM ( SELECT DISTINCT gm.group_grantee_id,

 

                              CONNECT_BY_ROOT gm.grantee_id AS grantee_id

 

                       FROM grantee_member gm

 

                       CONNECT BY PRIOR gm.group_grantee_id = gm.grantee_id

 

                       UNION ALL

 

                       SELECT grantee_id,

 

                               grantee_id

 

                       FROM   grantees

 

                       WHERE  grantee_id > 0

 

                      )

 

                WHERE  grantee_id  IN

 

                       (

 

                        SELECT  user_id

 

                        FROM    cd_users

 

                        WHERE   user_name IN ('RO_USER')

 

                       )

 

               ) ug,

 

               ( SELECT role_priv_id,

 

                        priv_id

 

                 FROM ( SELECT DISTINCT prp.role_priv_id,

 

                               CONNECT_BY_ROOT prp.priv_id AS priv_id

 

                        FROM priv_role_priv prp

 

                        CONNECT BY PRIOR prp.role_priv_id = prp.priv_id

 

                      )

 

               ) pp

 

        WHERE gp.grantee_id = ug.group_grantee_id

 

        AND gp.priv_id = pp.role_priv_id

 

        GROUP BY ug.grantee_id, pp.priv_id

 

        ) v_grantee_priv,

 

        ( SELECT poa.priv_id,

 

                 poa.object_name,

 

                 aota.action_type   

 

          FROM     priv_other_app poa,

 

                access_obj_type aot,

 

                access_obj_type_action aota,

 

                access_app aa

 

          WHERE poa.action_type_id = aota.action_type_id

 

          AND   aota.object_type_id = aot.object_type_id

 

          AND   aot.object_type = 'Workflow Role'

 

          AND   aot.application_id = aa.application_id

 

          AND   aa.application = 'RiskOrigins'    

 

         ) v_priv_all

 

  WHERE v_grantee_priv.priv_id = v_priv_all.priv_id

 

  ) vgpa,

 

  v_lo_entity_grps vleg

 

 WHERE vleg.group_name(+) = vgpa.object_name

 

)

 

SELECT

 

       task.task_id,

 

       task.task_name,

 

       task.description,

 

       task.couterparty_id,

 

       task.entity_code,

 

       task.short_name,

 

       task.long_name,

 

       task.assignee,

 

       task.process_id,

 

       task.process_code,

 

       task.process_name,

 

       task.process_deployment_name,

 

       CASE WHEN lba.is_reject IS NULL THEN 'F' ELSE 'T' END AS is_redo,

 

       task.createtime,

 

       task.entity_group_id,

 

       p.groupid_,

 

       p.userid_

 

FROM

 

(

 

SELECT     t.couterparty_id,

 

        t.entity_code,

 

        t.short_name,

 

        t.long_name,

 

        t.entity_group_id,

 

        t.task_id,

 

        t.task_name,

 

        t.description,

 

        t.assignee,

 

        t.createtime,

 

        t.process_code,

 

        t.process_name,

 

        t.process_deployment_name,

 

        t.process_id,

 

        t.groupid_,

 

        t.userid_

 

FROM   v_entity_task t

 

WHERE  UPPER (t.userid_) IN ('RO_USER')

 

UNION ALL

 

SELECT

 

        t.couterparty_id,

 

        t.entity_code,

 

        t.short_name,

 

        t.long_name,

 

        t.entity_group_id,

 

        t.task_id,

 

        t.task_name,

 

        t.description,

 

        t.assignee,

 

        t.createtime,

 

        t.process_code,

 

        t.process_name,

 

        t.process_deployment_name,

 

        t.process_id,

 

        t.groupid_,

 

        t.userid_

 

FROM  v_entity_task t,

 

          user_permission up

 

WHERE

 

           UPPER (UP.action_type) = UPPER (t.groupid_)

 

 AND   (

 

                up.object_name = 'N/A'

 

                OR EXISTS

 

                (

 

                                   SELECT NULL

 

                                   FROM v_lo_entity_grps vleg_

 

                                   WHERE vleg_.long_name LIKE UP.long_name || '%'

 

                                   AND vleg_.GROUP_ID = t.ENTITY_GROUP_ID

 

                )

 

           )

 

) task

 

LEFT OUTER JOIN jbpm4_participation p

 

ON p.task_ = task.task_id

 

AND p.type_ = 'candidate'

 

LEFT OUTER JOIN lo_bpm_audit lba

 

ON task.task_id = lba.next_task_id

 AND  LBA.IS_REJECT=1;

 

 

 

​​

 

The major changes include...

• Replace the correlated scalar subquery (lo_bpm_audit) with the left outer join. The data volume of the table lo_bmp_audit is really huge, using the scalar subquery is not a good idea. 
• Repleace the semi-join (exists statment,  between jbpm4_participation and user_permission) with the normal table join ( sperate the task query into two parts using UNION ALL)
• Remove the sort (distinct) operation in the task query. This seems unnessary and even there are duplicated records, the application layer (java code) also remove the duplicated record.

4. The outcome

Seen from the AWR report, the usage of CPU reduced a lot and the response time (execution time) also decreased a lot. 

 

From the load test report, 100 concurrent user access takes about 30% CPU usage.