Re: Implement Parallel Processing on DB Warehouse
- From: Mladen Gogala <mgogala@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Feb 2004 19:05:06 -0500
John, I have no experience with Oracle applications whatsoever.
I trust you, however, that flushing shared pool helps.
On 02/11/2004 04:59:12 PM, John Kanagaraj wrote:
> Maybe we should clarify when flushing is 'harmful' and when it is
> not... On
> an Oracle Apps database which is _heavily_ dependent on the use of
> Packaged
> code (which can be pinned), and uses Bind variables (mostly anyway),
> I
> have
> pinned large and much-used packages (as well as Sequences, Triggers
> and even
> cursors). I have flushed the Shared Pool on this heavy OLTP database
> and
> have not seen a rise in any of the latching stats or Cpu usage which
> indicates this was harmful. The Free chunks coalesced and the largest
> free
> chunk became quiet large. If you are not heavily dependent on
> Packages
> or
> other clearly 'pinnable' objects, you *might* suffer a bit. Steve
> Adams has
> 'nice flush' script (name may be incorrect) in which he pins and
> _then_
> flushes. Btw, you _can_ pin cursors using the Addrs + Hash value and
> the 'C'
> type to DBMS_SHARED_POOL.KEEP....
>
> John Kanagaraj <><
> DB Soft Inc
> Phone: 408-970-7002 (W)
>
> Listen to great, commercial-free christian music 24x7x365 at
> http://www.klove.com
>
> ** The opinions and facts contained in this message are entirely mine
> and do
> not reflect those of my employer or customers **
>
> >-----Original Message-----
> >From: oracle-l-bounce@xxxxxxxxxxxxx
> >[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael McMullen
> >Sent: Wednesday, February 11, 2004 7:45 AM
> >To: oracle-l@xxxxxxxxxxxxx
> >Subject: Re: Implement Parallel Processing on DB Warehouse
> >
> >
> >Steve Adam's site says
> >"In a well managed shared pool, flushing should not be needed."
> >
> >So is your shared pool too big or are people not using bind
> >variables? So
> >all your users take a hit in the morning as they hard parse
> >all the new sql
> >statements. But maybe you pin everything.
> >
> >I agree flushing the shared pool is a very poor bandade for shared
> pool
> >problems.
> >
> >Mike
> >ganstadba@xxxxxxxxxxx
> >----- Original Message -----
> >From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
> >To: <oracle-l@xxxxxxxxxxxxx>
> >Sent: Wednesday, February 11, 2004 10:15 AM
> >Subject: RE: Implement Parallel Processing on DB Warehouse
> >
> >
> >> Based on what? I have a job to flush our ERP daily
> >partially in part by
> >> advice found on Steve Adams' site http://www.ixora.com.au
> >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 8.1.7.4.0 on HP/UX 11.0 (9iR2 migrate
> >in progress).
> >>
> >> Rich
> >>
> >> Rich Jesse System/Database Administrator
> >> rich.jesse@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA
> >>
> >> -----Original Message-----
> >> From: Mladen Gogala [mailto:mladen@xxxxxxxxxxxxxxx]
> >> Sent: Tuesday, February 10, 2004 3:51 PM
> >> To: oracle-l@xxxxxxxxxxxxx
> >> Subject: Re: Implement Parallel Processing on DB Warehouse
> >>
> >>
> >> On 02/10/2004 04:17:39 PM, "Freeman, Donald" wrote:
> >> > SET timing ON
> >> >
> >> > ALTER SYSTEM FLUSH SHARED_POOL;
> >>
> >> Flushing is harmful.
> >> ----------------------------------------------------------------
> >> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >> ----------------------------------------------------------------
> >> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> >> put 'unsubscribe' in the subject line.
> >> --
> >> Archives are at //www.freelists.org/archives/oracle-l/
> >> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> >> -----------------------------------------------------------------
> >>
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at //www.freelists.org/archives/oracle-l/
> >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
> >
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
