Flushing the shared Pool

[m_d_marian]

Hi All
I was been told that flushing the shared pool is dangerous because this can put sessions in inconsistent state. A user who had not commit his transaction will get lost his data. Is this true? I mean if in the shared pool are only reutilisable data why should my transaction be there?

[stecal]

What does the log writer do? When you commit, are data blocks with changes immediately written to? What does "flush" mean? Answer those, and you'll have a better idea about the question you are asking.

[m_d_marian]

The Logwriter write the redo log buffer on disk. The flush shared_pool will free data from shared pool. So because log_buffer is not in the shared_pool, there is no danger? True?

[stecal]

http://otn.oracle.com/docs/products/...memor.htm#8999

Also,

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 but does nothing to improve shared pool fragmentation. 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.



[Edited by stecal on 10-21-2002 at 12:47 PM]

[jmodic]

If you were told that flushing the shared pool should be avoided because of wasting all the data allready cached (and hence having impact on performance after the flush), then you have been told the truth.

If you were told that flushing the shared pool should be avoided because "this can put sessions in inconsistent state", then you have been told rubish!

Flushing the shared pool is by all means the process one should avoid executing regulary, but it is by no means dangerous to execute it whene there are reasons for that.