Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cursor_sharing = force? (issue with ref cursors)

Re: cursor_sharing = force? (issue with ref cursors)

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Mon, 1 Apr 2002 19:09:44 +0100
Message-ID: <3ca8cc98_2@mk-nntp-1.news.uk.worldonline.com>


Thank you Thomas, Sybrand and Joe, for the helpful advice.

Paul

"Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
news:3ca4a0fa$1_1_at_mk-nntp-1.news.uk.worldonline.com...

> Database server: 8.1.6 and 8.1.7 EE on hpux 11 (32bit and 64 bit).
> Application Server: MS IIS, MS Transaction Server, VB6, ADO.
> Client: IE5 on NT4 (for the moment).
>
> Our developers are designing applications which will use a web browser
> as the client, and connecting from the web server to the database using
> Visual Basic and ADO. One in particular may be rolled out to up to 3000
> users.
> Of the only a small number will be doing updates; the vast majority will
be
> searching/viewing on the data.
>
> DML will be via a database 'API'; i.e. a PL/SQL package. No privileges
will
> be
> granted on the tables (a model of which I wholeheartedly approve).
>
> So far so good. My dilemma, however is this:
> While inserts, updates and deletes will be done in the 'traditional'
manner
> (i.e. a call to the relevant package procedure, passing parameters, and
SQL
> in
> the package), selects will be done via Ref Cursors (dynamic SQL). The
> developers tell me that it is much simpler to program ADO that way, so I
am
> trying to anticipate possible performance issues and see what might be
done
> given this constraint.
>
> Specifically, my understanding is that this precludes use of bind
variables,
> thus resulting in the overhead of reparsing. On the other hand it does
mean
> that CBO can make better plans, particularly where the data is skewed, but
> I may need to increase the shared pool size to compensate.
>
> Looking at the documentation, it appears that CURSOR_SHARING = FORCE
> may be an appropriate option to look at. On the other hand it may be We're
> not yet at the stage of starting load testing, so in the meantime I'd
> appreciate
> any comments, corrections, ideas, opinions or suggestions on this, or any
> ideas
> or possibilities I may have overlooked.
>
> Thanks in advance.
>
> Paul
>
>
>
Received on Mon Apr 01 2002 - 12:09:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US