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

In article <3ca4a0fa$1_1_at_mk-nntp-1.news.uk.worldonline.com>, "Paul says...
>
>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.
>

you're understanding is wrong. ref cursors fully support bind variables -- 100%.

>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
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp