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

Paul Osborn wrote:
>
> 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.

Actually, I'm pretty sure it saves no time in the parse. An explicit cursor only fetches data when you tell it to. An implicit cursor will fetch *AT LEAST* twice, no matter what. If you do "select user into foo from dual", then two fetch operations will take place, even though you only intend to get one row. This is due to the ANSI standards (and maybe Oracle practice anyway) forcing the engine to check for multiple row returns on implicit cursors. As far as I know, this is where the speed increase comes from. Even if you qualify the statement with "where rownum=1" I'm almost certain that the second fetch still happens. The only way to avoid it, AFAIK, is to use an explicit cursor.

> >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.

It will if you use a ton of them. Received on Sat Jun 15 1996 - 00:00:00 CEST