Re: Curious question about flushing the Pool

> Comments? Corrections? Rants?

I'll go for rant...

I've had long and painful experience with a few pathological applications. One in particular contained hundreds of sets of common SQL statements - varying only in the literal values. Many were executed hundreds of thousands or even millions of times every day. (e.g. What could have been a few hundred distinct statements with bind variables was instead tens or hundreds of millions of distinct statements in the course of a day.) None of these applications ran on anything later than Oracle 8.1.7.1 - some on 7.3.x. On the vast majority of the 8i systems, there was some bug/quirk that prevented CURSOR_SHARING=FORCE. (For example, one such bug/quirk was that if the value for the first variable in a bind list was a null, the application process puked and either hung or died. I don't now remember the specific color, chunksize, ORA-xxxxx, or bug number though.)

The typical symptom was a gradual degradation of overall performance as the shared pool filled up. When it slowed to a crawl, the only remedy was to flush the shared pool - which resulted temporarily in a much more dramatic performance hit. ("Would you like that adhesive tape pulled off your hairy chest a little at a time or in one quick heart-rending yank?") Then everything ran fine until we came full-circle again on the roller-coaster ride. We created a scheduled job to flush the shared pool and "tuned" the frequency with which it ran! This continued for at least 2 1/2 years (until I left). I hear that it is still the main performance issue with this particular ultra-critical multi-million dollar system (loaded E10K, large EMC Sym, ...).

I showed the outsourcing "consultants" that designed and wrote this atrocity the wait analysis - which they adamantly insisted was NOT caused by their code. ("Tuning is the DBA's responsibility" - and our management bought it). My most convincing argument was routinely dumping a join of v$SQLAREA and v$SQLTEXT sorted by SQL_TEXT into a file. Actually seeing 60,000+ copies of the same statement differing only in literal values, followed by 23,000 of another, etc. eventually convinced management that it might actually be a problem. (This isn't an argument against using the wait interface, but in this case it was unnecessary - the problem was quite obvious).

Just as I fled the company, one of the people who took over this system came up with a simple "solution" (encouraged by Oracle support and several very popular tuning books) - he raised the shared pool size to almost a gigabyte - in spite of my admonitions. Oddly enough, that made it MUCH worse! ;-)

In this case, the flushing "solution" was just barely survival. And, obviously, it did nothing to avoid the wildly excessive parsing.

In other words, "I agree" - flushing the shared pool is curing symptoms, not the disease.

Don Granaman
[cranky old OraSaurus with enough war stories to publish a multi-volume set]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 24, 2002 8:58 PM

[... snipped for brevity ...]
> In the case of flushing the shared pool, it is a valid response to the
> problem of OLTP applications not utilizing "bind-variables" and bollixing
up
> the Shared SQL Area. In this case, using ALTER SYSTEM FLUSH SHARED_POOL
is
> very much analogous to using chemo-therapy to treat cancer. The cure is
> very nearly as debilitating as the disease, but it works.
>
> I've always seen the use of FLUSH SHARED_POOL as the last resort when the
> problem is entirely in the hands of the application, provided the Oracle
> version is 8.1.6 or less. CURSOR_SHARING was introduced in 8.1.6, but it
> didn't work until 8.1.7.3, I understand. To this day, I've not yet
> encountered that type of malicious application in a database of version
> 8.1.7 or above (yet!), so I've not used CURSOR_SHARING yet...
>
> Without the availability of the CURSOR_SHARING=FORCE functionality, the
> Shared SQL Area is simply at the mercy of the application. As I visualize
> it (and I could be very wrong!), there is little contention as long as the
> Shared SQL Area is *filling up*. Once it is *full*, however, is when
> contention starts. Once the Shared SQL Area has filled, it becomes
> necessary for the RDBMS must find an entry to age-out of the cache instead
> of just simply locating the next empty slot. So, frequent usage of the
> FLUSH SHARED_POOL command continually keeps the Shared SQL Area on a
> less-contentious "always filling" basis, rather than the very-contentious
> "gotta-pitch-one-to-make-room-for-another" basis. SQL is not being
re-used,
> but it's not being re-used anyway -- using FLUSH SHARED_POOL has no impact
> on that. At least, that's my simple-minded way of looking at it...
>
> Anyway, if this is the problem they are facing, then a script to
> periodically (i.e. 5 mins? 30 mins? 60 mins?) FLUSH SHARED_POOL may be
the
> only way to survive. However, if there is another alternative, then it
> might be worthwhile to attempt to talk them off the precipice...
>
> Comments? Corrections? Rants?
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, April 24, 2002 5:53 PM
>
>
> > I see a couple of folks who want to
> > know how to flush the pool or are looking
> > for a script to do it automatically.
> >
> > Shouldn't we be asking what is causing
> > the behavior that got us to this quandry
> > in the first place ?
> >
> > Just a stupid question .. I know !
> >
> > Peace !
> >
> > Mike

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Don Granaman
  INET: granaman_at_cox.net

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).