Re: SQL TUNING - Does it make a difference, really?
Date: 1996/06/14
Message-ID: <834780062.14996.0_at_lantis.demon.co.uk>#1/1
djetz_at_spots.ab.ca (Doris Jetz) wrote:
>I have read in a couple differency oracle tuning books that using
>Will this save a significant amount of time over the extent of the
>explicit cursors will save time if used in place of implicit cursors.
>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.
>Thanks in advance.
>Oracle DBA/Developer
>NGX Canada Inc.
>Calgary, AB
I don't know if you've heard all of thid before, but using explicit cursors has the advantage of only fetching one row whereas implicit curosrs fetches two rows (in order to determine whether the too_many_rows exception has been satisified). In this respect, the number of FETCHes has been reduced by 50%. Ignoring parse time, this should significantly improve the performance for very complex queries. If you have ORACLE 7.x, try taking advantage of the Global Shared Area but writing your common routines in a database package and pinning it in memory. This will vertually eliminate parse time since the code already resides in memory.
As for your actual question, I suppose it is horses for courses - it depends on the complexity of your code and the critical-ness of speed. Personally I'm against all of this because, together with PL/SQL, we are moving away from the declarative nature of SQL. But there you go.
Cheers Received on Fri Jun 14 1996 - 00:00:00 CEST