Re: Shared Pool Flushed Frequently

Jack,

Cursors can be passed between Oracle and ODBC. I was just discussing this with someone here last week. He's the local expert on it, so I don't know the particulars of how it is done.

It's probably a matter of the developers being better educated on ODBC.

Jared

On Fri, 14 Jul 2000, Jack C. Applewhite wrote:

> 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
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Tue Jul 18 2000 - 12:21:54 CDT