Home » RDBMS Server » Performance Tuning » Not sure what is causing the slow performance (10.2.0.1, Windows 2003)
Not sure what is causing the slow performance [message #382484] Thu, 22 January 2009 10:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.

Re: Not sure what is causing the slow performance [message #382818 is a reply to message #382484] Sun, 25 January 2009 05:41 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF of your process.
Re: Not sure what is causing the slow performance [message #382850 is a reply to message #382818] Sun, 25 January 2009 19:46 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could try collecting the llk_keys in an array and perform a FORALL DELETE. That will reduce executions.

Ross Leishman
Previous Topic: How to interpret the tkprof output
Next Topic: How to tune
Goto Forum:
  


Current Time: Sun Dec 04 06:34:24 CST 2016

Total time taken to generate the page: 0.13990 seconds