cursor_sharing = force? (issue with ref cursors)

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 Fri Mar 29 2002 - 07:39:17 CST