An excerpt from....
PIRANHAS IN THE POOL,SQL PERFORMANCE
KILLERSInvestigating the effects of literal SQL on Oracle
performance
John BeresniewiczPrecise Software
Solutio
Effect of CURSOR_SHARINGA 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 VariablesComparing 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.
Received on Fri Oct 19 2001 - 15:10:54 CDT
