Oracle Performance and Engineering

Another piece of SQL in the "Top 100" SQL list -

SELECT   wss.token,
        MIN (wss.workflow_step_security_id) workflow_step_security_id
   FROM kwfl_workflow_step_security wss
  WHERE wss.workflow_step_id IN (
           SELECT ws.workflow_step_id
             FROM kwfl_workflow_steps ws
            WHERE ws.workflow_id = :b2
               OR ws.workflow_id IN (
                     SELECT workflow_id
                       FROM kwfl_workflow_instances wi
                      WHERE wi.top_instance_source_id = :b10
                        AND wi.top_instance_source_type_code =DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL)))
    AND wss.token IS NOT NULL
GROUP BY wss.token,
        DECODE (UPPER (SUBSTR (wss.token, 1, 3)),:b1, wss.workflow_step_id,0)                           

Here's the statistics from V$SQLAREA for this -

CPU_TIME EXECUTIONS BUFFER_GETS
18,635,370,000 479,770.00 809,709,593

i.e., for about 500,000 executions - it took about 5 hrs. 15 mins doing 1700 LIO per exec!

The explain plan shows -

----------------------------------------------------------------------------------------------
| Id  | Operation                     |  Name                        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |   214 |  5136 |    37 |
|   1 |  SORT GROUP BY                |                              |   214 |  5136 |    37 |
|   2 |   FILTER                      |                              |       |       |       |
|   3 |    HASH JOIN                  |                              |   214 |  5136 |    25 |
|   4 |     TABLE ACCESS FULL         | KWFL_WORKFLOW_STEPS          |   207 |  2070 |    13 |
|   5 |     TABLE ACCESS FULL         | KWFL_WORKFLOW_STEP_SECURITY  |   652 |  9128 |    11 |
|   6 |    TABLE ACCESS BY INDEX ROWID| KWFL_WORKFLOW_INSTANCES      |     1 |    12 |     4 |
|   7 |     INDEX RANGE SCAN          | KWFL_WORKFLOW_INSTANCES_N6   |     1 |       |     3 |
----------------------------------------------------------------------------------------------

Glancing at the SQL with "blink" problem solving mode - I see the "OR".

ws.workflow_id = :b2
OR ws.workflow_id IN (
                     SELECT workflow_id
                       FROM kwfl_workflow_instances wi
                      WHERE wi.top_instance_source_id = :b10
                        AND wi.top_instance_source_type_code =DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL)))

Which can be rewritten as -

                               WHERE ws.workflow_id IN (
                                   SELECT workflow_id
                                   FROM kwfl_workflow_instances wi
                                   WHERE wi.top_instance_source_id = :b10
                                   AND wi.top_instance_source_type_code =
                                          DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL))
                                   UNION
                                   SELECT TO_NUMBER(:b2) FROM dual )

I tried running the SQL with either of the OR-ed parts and it returned faster. So I rewrote the SQL to -

explain plan for SELECT   wss.token,
MIN (wss.workflow_step_security_id) workflow_step_security_id
FROM kwfl_workflow_step_security wss
WHERE
wss.workflow_step_id IN (SELECT ws.workflow_step_id
                              FROM kwfl_workflow_steps ws
                              WHERE ws.workflow_id IN (
                                   SELECT workflow_id
                                   FROM kwfl_workflow_instances wi
                                   WHERE wi.top_instance_source_id = :b10
                                   AND wi.top_instance_source_type_code =
                                          DECODE (:b9,:b8, :b7,:b6, :b5,:b4, :b3,NULL))
                                   UNION
                                   SELECT TO_NUMBER(:b2) FROM dual )
AND wss.token IS NOT NULL
GROUP BY wss.token,DECODE (UPPER (SUBSTR (wss.token, 1, 3)),:b1, wss.workflow_step_id,0) ;

Notice, how I put the other value in a UNION so we do not have to use OR in the outside loop. Now, workflow_id can be equated just once with this inner-view (which, knowing the data model, should be sufficiently faster).

The explain plan of un-OR-ed SQL -

---------------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name                        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |     2 |    54 |    61 |
|   1 |  SORT GROUP BY                     |                              |     2 |    54 |    61 |
|   2 |   HASH JOIN                        |                              |     2 |    54 |    49 |
|   3 |    VIEW                            | VW_NSO_1                     |    13 |   169 |    37 |
|   4 |     SORT UNIQUE                    |                              |    13 |   264 |    37 |
|   5 |      UNION-ALL                     |                              |       |       |       |
|   6 |       TABLE ACCESS BY INDEX ROWID  | KWFL_WORKFLOW_STEPS          |    19 |   190 |     7 |
|   7 |        NESTED LOOPS                |                              |    12 |   264 |    11 |
|   8 |         TABLE ACCESS BY INDEX ROWID| KWFL_WORKFLOW_INSTANCES      |     1 |    12 |     4 |
|   9 |          INDEX RANGE SCAN          | KWFL_WORKFLOW_INSTANCES_N6   |     1 |       |     3 |
|  10 |         INDEX RANGE SCAN           | KWFL_WORKFLOW_STEPS_N1       |    19 |       |     1 |
|  11 |       TABLE ACCESS FULL            | DUAL                         |     1 |       |     2 |
|  12 |    TABLE ACCESS FULL               | KWFL_WORKFLOW_STEP_SECURITY  |   652 |  9128 |    11 |
---------------------------------------------------------------------------------------------------

Though it's difficult to tell from explain plan -- but the "bytes" has decreased at the expense of "cost" (weird! But a plan could indeed be the better one with an increased cost!).

The LIO for this was tested to be 1/3rd as the original one. This is just a case of "doing the work in another way and found improvement", not necessarily one way is better than the other.