Re: SQL TUNING - Does it make a difference, really?

djetz_at_spots.ab.ca (Doris Jetz) wrote:

>I have read in a couple differency oracle tuning books that using
>explicit cursors will save time if used in place of implicit cursors.

My understanding is that an explicit cursor will save time during the initial statement parse, as Oracle will not need as much information from the data dictionary.

>Will this save a significant amount of time over the extent of the
>application?

Maybe. It depends - as you point out below - on what significant means (>5%?) and the application.

>I know it depends on the size of the app, sql statements, etc. but if
>someone has a general idea - like it may increase the performance
>overall by x% that would help.
 

>What do you think?

What I suggest is that you try it (!), see what it does.

1. Take a procedure or trigger with an implicit cursor,
2. Run it several times when no one else is on the system,
3. Time it and savle the results.
4. Change the procedure or trigger to an explicit cursor,
5. [repeat steps 2 & 3 above]
6. Compare the results.

Regards,
Paul

--
posborn_at_menlosoftware.com      Menlo Software
http://www.menlosoftware.com   744 College Ave.
V:415.324.1286                      Menlo Park, CA 94025-5204 USA