| Performance Problem with Stored procedure [message #65079] |
Mon, 19 April 2004 02:41 |
Prabha R
Messages: 21
Registered: October 2001
|
Junior Member |
|
|
Hi,
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,
prabha.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
