From sqlgreg@pacbell.net Fri, 19 Oct 2001 13:10:54 -0700 From: Greg Moore Date: Fri, 19 Oct 2001 13:10:54 -0700 Subject: Re: distribution of the sleeps on the library cache latches Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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.