Home » RDBMS Server » Performance Tuning » Performance Problem with Stored procedure
Performance Problem with Stored procedure [message #65079] Mon, 19 April 2004 02:41
Prabha R
Messages: 21
Registered: October 2001
Junior Member

  I am facing a strange performance problem:

Our script has 4 steps -
a) create tables and indexes,
b) load tables,
c) filter data in X_ODS table,
d) Validate data in X_ODS table and insert exceptions and warnings into EXCEPTION table.

X_ODS table has around 100K records

the steps c & d are done using stored procedures. 

For step d) we have 2 stored procedures - one for exceptions and other for warnings.  We have exception code 'E0001' till 'E00011' and Warning codes 'W0001' till 'W0013'.  Each exception code and warning code will be identified and inserted into EXCEPTION table using sql queries.

Now the problem is if i run the warning stored procedure in step d seperately, it runs in 30 secs.  But if i run from step a) to d) together, then the script hangs at the point 'W0008' warning code. 

The sql for inserting warning code is :

INSERT INTO exception
        (SELECT DISTINCT 'W0008', 'Direct Manager record has an exception', AO.person_number, AO.resource_id
           FROM ods AO
           WHERE AO.transaction_type in ('A', 'C', 'AC')
   AND AO.direct_manager_nbid IS NOT NULL
                        AND AO.resource_id IS NOT NULL
             AND EXISTS (SELECT b.resource_id
      FROM arl_exception b
      WHERE b.resource_id=AO.direct_manager_nbid
     and b.error_code like 'E%'));

When we run the above sql seperately, it takes 10 secs only.

I do not understand, why the script hangs exactly at this point, if we run from step a) to d) at one shot. 

I thought that there could be some the table space or buffer block which has a threshold limit for the number of inserts/updates and may be at this point it crosses the threshold and hence the script hangs. 

But i am not sure how appropriate this guess could be.  Also, even if this is the problem, how to overcome this?

I have given the pctfree = 5 and pctused=60 for EXCEPTION table and pctfree=10 and pctused=40 for ODS table.

Any suggestions will be of great help.

Thanks and Regards,

Previous Topic: pctfree and pctused settings
Next Topic: optimizer_mode,multiple db_block_size
Goto Forum:

Current Time: Wed Sep 30 11:57:42 CDT 2020