RE: Cursor Sharing| Soft Parsing

From: Robert Monical <tech_at_restek.com>
Date: Thu, 25 Jul 2002 23:38:18 -0800
Message-ID: <F001.004A3098.20020725233818@fatcity.com>

I finally got around to doing a test on this to see if explicit cursor management saved clock time on a lightly loaded system (like mine).

Test structure is essentially

Implicit cursor
for outer in 1..some_number loop

         open the_cursor ;
         fetch the_cursor into v_the_cursor ;
         close the_cursor ;

end loop;

Explicit cursor

open the_cursor ;
parse the_cursor ;
for outer in 1..some_number loop

         bind the_cursor ;
         fetch the_cursor ;
         get the variables;

end loop;

The explicit cursor management takes slightly longer with one database user. Tried it against the same database in 7.3.4 and 9.2 (Same Win2K server).

This may be a degenerate test because the cursor has 6 bind variables and returns a number, but it is my innermost cursor. I implemented the cursor in its own package. The cursor was

   cursor curr_rs

      (pUtmId in number,
       pPkgId in number,
       pUsId in number,
       pToday in Date,
       pNightStay in Number,
       pPrioirty in Number
      ) is
   select
      Unit_Rate_ID

   from Rate_Selection
   where PUTM_ID = pUtmId

     and PKG_GRP_ID = pPkgId
     and UNIT_SPECIFIC_ID  = pUsId
     and rate_start_date <= pToday
     and RATE_END_DATE   >= pToday
     and nvl(MAX_NIGHT,99) >= pNightStay
     and nvl(MIN_NIGHT,0) <= pNightStay

I guess I need to get smart enough to look for Library cache latch contention to decide if I want to apply this technique in my < 50 user application.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Robert Monical
  INET: tech_at_restek.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jul 26 2002 - 02:38:18 CDT