Home » RDBMS Server » Performance Tuning » Tune a cursor
icon5.gif  Tune a cursor [message #574641] Mon, 14 January 2013 10:19 Go to next message
cherryreddi
Messages: 1
Registered: January 2013
Location: MI
Junior Member
Below is an interview question. Can someone answer this?

If a loop in a cursor is taking long time to execute.. How will you tune it???
Re: Tune a cursor [message #574642 is a reply to message #574641] Mon, 14 January 2013 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

http://www.lmgtfy.com/?q=oracle+tune+SQL
Re: Tune a cursor [message #574645 is a reply to message #574641] Mon, 14 January 2013 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the cursor loop.

Regards
Michel
Re: Tune a cursor [message #574668 is a reply to message #574641] Mon, 14 January 2013 14:46 Go to previous message
xtender
Messages: 34
Registered: January 2011
Location: Russia
Member

cherryreddi wrote on Tue, 15 January 2013 01:19
Below is an interview question. Can someone answer this?

If a loop in a cursor is taking long time to execute.. How will you tune it???

I think it's incomplete question, because it depends on where most time spends.
Therefore, you first need to know where the bottleneck is.
If it is on fetch and context switching than you need to use "bulk fetch" with suitable limit. btw, cursor loop already would use bulk fetch on 10g and above if cursor is implicit.
If it is on pl/sql inside a loop, then you can use parallelizing.
If it is on sql, you need to tune execution plan.

PS. I'm not sure that you mean a cursor loop and not nested loops in a execution plan
Previous Topic: AWR Baseline in RAC
Next Topic: does 11g not do a soft parse when we set cursor_cached_curosr?
Goto Forum:
  


Current Time: Fri Mar 29 04:40:54 CDT 2024