Re: Shared Pool Flushed Frequently

Isn't there a new facility in Oracle 8i (release 2?) that converts hard-coded values to bind variables dynamically? It was designed to address issues like this. If upgrading is an option, you may want to look into this. I have also heard of someone that wrote a client-side program to convert to bind variables dynamically by sitting between the actual client program and Sqlnet.

Marc Perkowitz
MTP Systems Consulting, Ltd.

In a message dated 7/14/2000 3:06:19 PM Central Daylight Time, Jack.Applewhite_at_sbti.com writes:

<< A Telecom client has a number of applications and batch jobs pounding a large
 OLTP database. They found several months ago that they had to flush the Shared
 Pool (350MB) every 15 to 30 minutes. Otherwise they got the ORA-4031 error  (unable to allocate xxxx bytes of shared memory).  

 We found that they had Cursor_Space_For_Time set to TRUE. We recommended that
 they set it to FALSE and stop flushing, since that hurt reuse of already-parsed
 SQL. They eased off gradually, but when they backed off to flushing every  couple of hours, performance of their major batch jobs went way down. They tell
 us it's absolutely predictable, if they don't flush the Shared Pool, the  Operations folks will call pretty quickly, complaining of decreased  performance. Immediately after the Shared Pool is flushed Operations can see a
 huge jump in performance.  

 Yesterday I discovered what I suspect is the reason. Examination of v$sqlarea  showed a rapid accumulation of SQL statements not using bind variables - at a  rate of up to one hundred per second. It turns out that all their client  applications use ODBC to communicate with the Oracle  database. I guess ODBC can't support client-side cursors or other SQL  statements with bind variables.  

 I'm speculating that, at that rate, the Shared Pool fills up fairly quickly with
 unreusable statements and then the process of hashing new SQL statements,  searching for matches, and aging out older statements to make room for the new
 ones can't keep up with the rate of new SQL statements  coming in. That would explain the slowdown in the batch applications - they're
 pounding the Shared Pool with new SQL statements faster than it can age out old
 ones to make room.  

 There are no error messages returned to the applications or appearing in the  Alert Log. Oracle8 8.0.5 on HPUX.  

 Is my hunch right? Any other possibilities that I'm overlooking? Given that  they can't immediately rewrite their apps to not use ODBC, might it improve  their situation to actually *decrease* the size of the Shared Pool? That would
 decrease the number of hashed SQL statements that have to be searched before  Oracle determines that there isn't a match, hence speed up its aging out older
 unreusable statements to make room for the new unreusable ones. What d'ya Received on Fri Jul 14 2000 - 21:53:38 CDT