Re: Slow exports Oracle 8.1.7.0 & 4 and " alter system flush shared_pool "

Well, the negative effect of flushing shared pool is that all cached library objects and query plans CBO was working so hard to produce are now gone and server will waste time reloading/reparsing them. Since only the most frequently used queries and objects are usually left in the shared pool after the system runs for some time, by flushing it you force server to spend a lot of additional time to come back to balanced and effective SGA load - it will be shuffling objects and plans in and out until it finally leaves only the most frequently used ones cached in memory.

To find out why export takes so much time you need to inspect v$session_event and see where it wastes its time, then act accordingly.

Corrections and additions are welcome.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Ton" <76434.1353_at_compuserve.com> wrote in message
news:4477604.0209170008.78b701fb_at_posting.google.com...


> I have send this problem earlier but want to come back on it.

>

> We have installed several Oracle databases on different location and

> hardware. IBM / Compaq

> I red a lot about export problems performance problems on the net but

> not mine.

>

> What is the case: Normally the exports performed well dumps of 700 MB

> in 4 minutes but frequently the exports slows down and the same

> quantity of data takes 2 hours. After a few days the export performes

> suddenly normal again (4 minutes)

>

> I have these slow exports frequently and they also disappear as they

> come.

> Yesterday I had again a extremely slow export and I flushed the SGA

> during the export.  " alter system flush shared_pool"  The export

> startsto perform again on full speed immediatly !!

>

> Is there a negative aspect using the " alter system flush shared_pool

> " command before the export. Are there any realy negative consequences

> using the command?