Small Change and A Huge Gain

Hi,

Elapsed time of one of our data warehouse procedure was 17 minutes on an average. Following is the skeleton procedure:

Create or replace procedure Update_Customers Is
  Cursor Cust_Cur Is
         Select * From Stg_Customers;

  l_Cust_Value Customers.Cust_Value;
Begin
  For Cust_Rec In Cust_Cur Loop
    :
    :
    Select Value Into l_Cust_Value
      From Customers
     Where Cust_Code = Cust_Rec.Cust_Code
       And Cust_Key = Cust_Rec.Cust_Key;
    :
    :
  End Loop;
End Update_Customers;

Upon taking snaps before and after executing the procedure it was evident that most of the time was consumed by the "SELECT...FROM Customers...." statement.

Elapsed Time from AWR report:

Procedure: 1021 Seconds

SQL Statement: 925 Seconds

There is a Composite Index on "Cust_Code" and "Cust_Key" columns. When I ran the same statement in SQL*Plus, it was fetching results very fast using the appropriate index.

STG_CUSTOMERS is a staging table which consists of nearly 250,000 records. Data is daily purged and populated in this table. CUSTOMERS table was probed for 250,000 times in the loop, individual query execution was very fast but repeated executions within the loop were causing the query to consume more time.

I replaced the original cursor by joining CUSTOMERS and STG_CUSTOMERS tables as shown below:

Create or replace procedure Update_Customers Is
  Cursor Cust_Cur Is
         Select * From Stg_Customers A, Customers b
           Where a.cust_code = b.cust_code
              And a.cust_key =  b.cust_key;

  l_Cust_Value Customers.Cust_Value;
Begin
  For Cust_Rec In Cust_Cur Loop
    :
    :
    :
  End Loop;
End Update_Customers;

When this modified procedure was executed, the performance was remarkably improved and the elapsed time dropped to only less than 75 seconds.

Below is the elapsed time of the same procedure before and after modification:

TYPE            DATE           Elapsed(Min)        CPU(Min)
--------------- ----------- --------------- ---------------
Procedure       19-Apr-2008           17.70            1.87
SQL Statement   19-Apr-2008           15.98           13.15
Procedure       20-Apr-2008           17.67            1.80
SQL Statement   20-Apr-2008           16.05           12.95
Procedure       21-Apr-2008           16.93            1.82
SQL Statement   21-Apr-2008           15.35           12.85
Procedure       22-Apr-2008           16.68            1.78
SQL Statement   22-Apr-2008           15.08           12.42
Procedure       23-Apr-2008           16.38            1.78
SQL Statement   23-Apr-2008           14.77           12.43
Procedure       24-Apr-2008            1.15             .92
Procedure       25-Apr-2008            1.13             .92
Procedure       26-Apr-2008            1.20             .93
Procedure       27-Apr-2008            1.23             .93

Regards