Shared Pool Flushed Frequently

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

TIA Jack

--
Jack C. Applewhite
Senior Consultant, OCP Oracle8 DBA
Stonebridge Technologies, Inc.