RE: Shared Pool Flushed Frequently

Hi Jack,

You could be right that decreasing the shared pool size might help, but your reasoning is not. Access to library cache object (including SQL statements) is hash based and not at all dependent on how many objects are in the library cache. Finding free memory in the shared pool is entirely a different matter and is very much dependent on how fragmented the shared pool has become. If flushing helps, then it is highly likely that the shared pool is too big, but considering that they have got ORA-4031 errors in the past, I would not be too rash with a reduction. There is plenty of material on this sort of stuff on my companies web site, including some in this month's newsletter.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	Jack C. Applewhite [SMTP:Jack.Applewhite_at_sbti.com]
Sent:	Saturday, July 15, 2000 6:51 AM
To:	Multiple recipients of list ORACLE-L
Subject:	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.
Austin, Texas
1.512.502.3337


--
Author: Jack C. Applewhite
  INET: Jack.Applewhite_at_sbti.com

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