David:
I don't really know if this will help you, but it
might be worth a try. You could try setting
session_cached_cursors.
Bjørn Engsig's white paper "Efficient use of bind
variables, cursor_sharing and related cursor
parameters" describes this parameter a bit. It can be
found at http://miracleas.dk in the Technical
Information section) ( Guy Harrison's tuning book
also talks about this parameter. )
good luck!
Barb
- Lord David <DLord_at_ironmountain.co.uk> wrote:
> Hi
>
> Does anyone know whether its possible to control the
> size of the pl/sql
> static cursor cache.
>
> I'm running into ORA-01000: maximum number of open
> cursors exceeded errors
> and part of the problem (apart from the usual
> developers not closing
> explicit cursors) is that _all_ static sql
> statements in compiled pl/sql
> units seem to be getting cached. I can't find any
> documentation of this
> feature apart from a few hints in the pl/sql and
> application development
> docs. Here's an example from an 8.1.7 database: -
>
> SQL>create or replace procedure foobar is
> 2 v_result varchar2(30);
> 3 begin
> 4 select user into v_result from dual;
> 5 end;
> 6 /
>
> Procedure created.
>
> SQL>
> SQL>select b.sql_text
> 2 from v$session a, v$open_cursor b
> 3 where a.sid = b.sid
> 4 and a.audsid = userenv('SESSIONID')
> 5 /
>
> SQL_TEXT
>
> SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
> select b.sql_text from v$session a, v$open_cursor b
> where a.
>
> SQL>
> SQL>exec foobar
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL>select b.sql_text
> 2 from v$session a, v$open_cursor b
> 3 where a.sid = b.sid
> 4 and a.audsid = userenv('SESSIONID')
> 5 /
>
> SQL_TEXT
>
> SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
> select b.sql_text from v$session a, v$open_cursor b
> where a.
> SELECT USER FROM DUAL
>
> TIA
> --
> David Lord
> Senior DBA
> Iron Mountain Europe
>
>
> *** *** *** *** *** *** *** *** *** *** *** *** ***
> *** *** *** *** ***
> This e-mail and its attachments are intended
> for the
> author's addressee only and may be
> confidential.
>
> If they have come to you in error you must take
> no
> action based on them, nor must you copy or show
>
> them to anyone; please reply to this e-mail and
>
> highlight the error.
>
> Please note that this e-mail has been created
> in the
> knowledge that Internet e-mail is not a 100%
> secure
> communications medium. We advise that you
> understand and observe this lack of security
> when
> e-mailing us. Steps have been taken to ensure
> this
> e-mail and attachments are free from any virus,
> but
> advise the recipient to ensure they are
> actually virus
> free.
>
> The views, opinions and judgments expressed in
> this
> message are solely those of the author. The
> message
> contents have not been reviewed or approved by
> Iron
> Mountain.
>
> *** *** *** *** *** *** *** *** *** *** *** *** ***
> *** *** *** *** ***
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Lord David
> INET: DLord_at_ironmountain.co.uk
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> 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).
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
INET: barbarabbaker_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Wed Nov 26 2003 - 10:49:25 CST
