Re: flush shared_pool?

Hi,

   This is what I found on Oracle Site(Metalink) Flushing the SHARED POOL
On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced. After the flush there is likely to be an interim spike in performance as the act of flushing may remove sharable SQL from the shared pool. The command to flush the shared pool is:

        ALTER SYSTEM FLUSH SHARED_POOL; Contrary to reports elsewhere items kept in the shared pool using DBMS_SHARED_POOL.KEEP will NOT be flushed by this command. Any items (objects or SQL) actually pinned by sessions at the time of the flush will also be left in place.
NB: Flushing the shared pool will flush any cached sequences potentially leaving gaps in the sequence range. DBMS_SHARED_POOL.KEEP ('sequence_name','Q') can be used to KEEP sequences preventing such gaps.

Thanks and Regards,
Ashish
In article <90qtgn$in5$1_at_nnrp1.deja.com>,   letokai_at_my-deja.com wrote:
> So when I execute this command it is supposed to low the performance
 of
> a query.
> I mean if I run the same query 5 times i will have better performance
> because of the cache mechanism. If i execute the alter command and
 then
> i run the query again, is it going to run like it was the first time
 or
> not.
> With the timing on, it seems it has no effects.
> Moreover in the trace I can see Oracle is doing what seems to be
 system
> queries after the alter command.
> So...
>
> I don't really understand what's going on.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 08 2000 - 09:14:56 CST