Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Implement Parallel Processing on DB Warehouse

RE: Implement Parallel Processing on DB Warehouse

From: Jesse, Rich <>
Date: Wed, 11 Feb 2004 09:57:55 -0600
Message-ID: <>

OK, I'm *way* off the thread here...

I'm not sure if "congestion" is the same, but we had definitely experienced "fragmentation" due to literals in SQL and unpinned objects. The problem (<9i, not sure about 9i) is that once the shared pool gets heavily fragmented it's very difficult to clean without bouncing the instance.

Trying to convince devs to use bind variables has been a losing battle with the advent of CS=F. We didn't start out using binds because we didn't know exactly how evil literals are. Once I knew it was too late...

And I hadn't even considered our BCHR...Eeeep! Only 96.24%! (been so long since I've calc'd it that I had to lookup the query)


Rich Jesse                        System/Database Administrator      QuadTech, Sussex, WI USA

-----Original Message-----
From: Mladen Gogala [] Sent: Wednesday, February 11, 2004 9:32 AM To:
Subject: Re: Implement Parallel Processing on DB Warehouse

On 02/11/2004 10:15:07 AM, "Jesse, Rich" wrote:
> Based on what? I have a job to flush our ERP daily partially in part
> by
> advice found on Steve Adams' site As our
> nightly
> jobs will have aged out most every OLTP statement from the shared
> pool, I
> fail to see how a timely daily flush of the shared pool is harmful to
> our
> instance, which BTW is on HP/UX 11.0 (9iR2 migrate in
> progress).

Well, flushing will remove all your procedures and dictionary definitions from shared pool, and thus decrease caching which is the primary purpose of the shared pool. If your intent is to change mode of operation, then it may not be harmful. Flushing the shared pool before parallel execution is definitely not needed. If, on the other hand, you expect to reuse procedures the following day, then nightly flushes are going to make it much harder. I am not entirely at ease with flushing the shared pool just to keep it squeaky clean. The reason that Steve has recommended it was to alleviate library cache congestion. Donald didn't specify the version, so I assumed it's 9i, which doesn't have that many problems with the library cache. Not to mention, it exerts a bad influence on BCHR.....

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Wed Feb 11 2004 - 09:57:55 CST

Original text of this message