| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Implement Parallel Processing on DB Warehouse
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
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael McMullen
>Sent: Wednesday, February 11, 2004 7:45 AM
>To: oracle-l_at_freelists.org
>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_at_hotmail.com
>----- Original Message -----
>From: "Jesse, Rich" <Rich.Jesse_at_quadtechworld.com>
>To: <oracle-l_at_freelists.org>
>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_at_quadtechworld.com QuadTech, Sussex, WI USA
>>
>> -----Original Message-----
>> From: Mladen Gogala [mailto:mladen_at_wangtrading.com]
>> Sent: Tuesday, February 10, 2004 3:51 PM
>> To: oracle-l_at_freelists.org
>> 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_at_freelists.org
>> put 'unsubscribe' in the subject line.
>> --
>> Archives are at http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://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_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Feb 11 2004 - 15:59:12 CST
 |
 |