> 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" <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]>
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
