RE: Bind variables v. cursor

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 19 Oct 2001 23:51:00 -0700
Message-ID: <F001.003B0AB4.20011019235017@fatcity.com>

Hi Greg and list,

I'm not convinced of John's conclusion that 'cursor_sharing' = FORCE outperforms bind variables. Some months ago I tried
to reproduce John's results and was not able to, despite trying several different variations on the tests. Tom Kyte
claims that 'cursor_sharing' is about 10% more expensive. My results were between 10% and 30%.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/              -  For DBAs
@   http://www.secularislam.org/call.htm  -  For Muslims
@   http://www.christianity.net.au/       -  For all

-----Original Message-----
Sent: Saturday, 20 October 2001 7:21
To: Multiple recipients of list ORACLE-L

An excerpt from....

PIRANHAS IN THE POOL,
SQL PERFORMANCE KILLERS
Investigating the effects of literal SQL on Oracle performance

John Beresniewicz
Precise Software Solutio

Effect of CURSOR_SHARING
A primary purpose of the 8.1.6 testing was to assess the impact the new CURSOR_SHARING system parameter and its
potential for helping DBAs manage ill-behaved applications with high parse rates of literal SQL. Comparing Test 1 to
Test 3 and also Test 0 to Test 4 serves this goal. Comparing these Test results, CURSOR_SHARING exhibits advantages similar to those obtained using bind variables in Test
2:
-- Reduced library cache impact.

Negligible shared pool activity.
Reduced CPU demands. In fact, Test 3 produced the best elapsed time of all tests in spite of the fact that all the SQL was literal. Thus, it is clear that CURSOR_SHARING can be used to greatly enhance performance of applications that produce high volume literal SQL and thus is a great advantage for the DBA saddled with such applications. CURSOR_SHARING vs. Bind Variables Comparing Test 2 and Test 3 reveals that CURSOR_SHARING = FORCE showed significantly better performance than bind variables in both elapsed time and reduced library cache latching impact. This surprising result deserves further investigation to produce an adequate explanation. Library cache latch impact was significantly reduced as well as shared pool pins and releases. Parsing CPU time increased some but overall CPU was reduced. Perhaps the additional parsing involved in forced cursor sharing also enables increased sharing of shared pool memory heaps.

--

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

Author: Steve Adams
INET: steve.adams_at_ixora.com.au

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 Sat Oct 20 2001 - 01:51:00 CDT

RE: Bind variables v. cursor_sharing = FORCE