Oracle Performance and Engineering

So from the earlier instalment on the test case, it seems the PLSQL procedure does the following -

(1) It selects a bunch of records through a cursor.
(2) For each of the records, it determines the ratio of (EXCEPTION_COUNT / TASK_COUNT) -- both are present in cursor-fetched record
(3) If that ratio is > a threshold value (also present in cursor-fetched record) a "summary_condition" is assigned with each record
(4) The appropriate "rule_id" based on it's YELLOW or RED is also assigned to the record
(5) the EXCEPTION_RULE_ID, SUMMARY_CONDITION and other values are then inserted into a table.

The full sequence can easily be done via a single Nested SQL using "CASE...WHEN..THEN..END" conditional clause.

Here's how it will look -

INSERT INTO exceptions
(exception_rule_id,source_id,exception_message)
(select exception_rule_id,
project_id,
summary_condition
from
(select parent_project_id,
project_id,
(case when summary_condition = 'YELLOW' then yellow_rule_id
when summary_condition ='RED' then red_rule_id
end) exception_rule_id,
summary_condition
from
(select project_id,
parent_project_id,
red_rule_id,
yellow_rule_id,
(case when indicator >= red_rule_threshold then 'RED'
when indicator>= yellow_rule_threshold then 'YELLOW'
end) summary_condition
from
-- the original cursor to populate PLSQL
-- to find the EXCEPTION_RULE_ID and SUMMARY_CONDITION.
(select p.project_id, p.parent_project_id, (pd.EXCEPTION_COUNT / pd.TASK_COUNT) indicator_ratio,
pd.red_rule_id, pd.red_rule_threshold,
pd.yellow_rule_id, pd.yellow_rule_threshold
from project p,
project_details pd
where p.master_project_id = v_master_project_id
and p.project_type_code = 'PROJECT'
and p.project_id = pd.project_id
and task_count > 0)
)
);


The time difference was huge! The single SQL (above) replacing the old, complex PLSQL procedure using (unnecessarily!) two PLSQL tables and extra-SQL iterations finished within 1.5 seconds for the same data set that took 80 seconds to execute through the PLSQL!

There are many benefits to try replacing whole PLSQL blocks with SQL code. The biggest benefit is, however, scalable code. The memory requirement as well as the variance of processing time with data size goes down a lot when the whole operation is done thorough a single "set" like SQL operatiuon.

Like I used "CASE..WHEN..ELSE..END" in the above example, "analytic functions" (available from 8i onwards -- *the* greatest new armour for database guys!) could as well be used in nested SQL for the same. Connor McDonald in his great book "Mastering Oracle PL/SQL - Practical Solutions" illustrates one such example in Chapter 1 - "Efficient PLSQL" (pp 32-47 for the edition I have) under "Don't use PL/SQL to do job of SQL" heading.

In future, I plan to give more examples of PL/SQL code that can be replaced by single SQLs.