| Not sure what is causing the slow performance [message #382484] |
Thu, 22 January 2009 10:30  |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Hi all,
I’m troubleshooting a performance issue for a process running from the application. The process is expected to finish in 2 or 3 seconds but it is taking about 40 sec now. I monitored the session on Toad when the process ran and I also generated the trace file. Since there is software code and sql’s in the process code, I’m not able to paste the code here. But from what I monitored, a delete statement took about 30 seconds to run and in the trace file also, I see this delete statement has many executions and altogether the delete statement is taking about 30-35 seconds. Here is the sql for the delete:
delete from llk_item where (llk_key =:v1)
{
delete from llk where (llk_key=:v1)
llk_item is child table to llk. It is coded as a loop to delete the record (v1) in llk_item table and then delete the same record in llk table. Depending on the input to the process, there could be any number of deletes that can occur. We tested this session with 50 deletes and it took 40 seconds. There are 1350 records in llk table and no records in llk_item table.
Both the tables have been analyzed recently and the explain plan shows both the indexes have been used..
As a work around, I rebuilt the index for llk.llk_key and llk_item.llk_key. Ran the process again, it took 25 sec now. Is there anything else that I could possibly look for to improve the performance on this?? Please give me your thoughts.
Thank you
|
|
|
|
| Re: Not sure what is causing the slow performance [message #382485 is a reply to message #382484] |
Thu, 22 January 2009 10:45  |
ahudspith
Messages: 26
Registered: January 2009
Location: Avoiding the tax man.
|
Junior Member |
|
|
Just random thoughts:
You are manually doing the delete (in a PL/SQL block ... for ... loop) rather than using a trigger. Is there a good reason for this? You are preventing the optimizer from making choices such as hash join by doing it this way...
Move the index to a 32K block size. I know that this is something people always say - but it should help if the index lookup is a significant portion of the explain plan.
Are there any obvious storage pitfalls in the database such as row chaining in these tables?
Id start there and work upwards...
Adam
|
|
|
|
| Re: Not sure what is causing the slow performance [message #382487 is a reply to message #382484] |
Thu, 22 January 2009 11:05 |
sant_new
Messages: 165
Registered: June 2008
|
Senior Member |
|
|
Adam, thanks for your thoughts.
The process has been coded in such a way to delete the records in a loop.. Its been running like this for a while now..
Could you please tell me how I can find out the block size for the current index?? I'll try to increase the block size and check if it helps.
Thanks again.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
