Re: Datapump import disable_archive_logging will still generate redo?

Because you said you're running in noarchivelog mode, any archivelog parameters are irrelevant.

How frequently was the log switching at 200MB? How frequently is it switching now?

select to_char(first_time,'YYYY-MM-DD HH24') hour_of_day, count(*) switches_per_hour from v$log_history
group by to_char(first_time,'YYYY-MM-DD HH24') order by 1;

You should be targeting a redo log file size that causes no more than about 4-5 switches per hour. So, use above query to see how many sdwitches per hour you currently have at peak, and the current size, and do the math.

Once you've got the log switches under control, set 'archive_lag_target' to, say, 1800. This will ensure that even at relatively idle times, you'll get at least 2 log switches per hour.

-Mark

On Wed, Sep 5, 2018 at 5:38 AM Ashish Lunawat <ashish.lunawat_at_gmail.com> wrote:

> Hi,
>
> I have a Oracle database version 12.2.0.1 running in noarchivelog mode. I`m
> wondering if datapump import parameter TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
> will reduce the redo generated and thus improve the import timing?
>
> I have an import of a LOB table which takes about 10 hours to import
> about 85GB of data. AWR reports show almost all the time spent on "log file
> switch (checkpoint incomplete)" event. There were 3 redo log groups
> configured with each member size 200MB.
>
> I increased to 6 groups with each member 1.5GB but I am still getting
> these wait events accounting to 94% of the DB time. And now the Avg wait
> for these events has gone up to about 34 seconds from 7-8 secs last time.
>
> How can I get rid of the log file switch (checkpoint incomplete) events?
> Increase DBwriters? FAST_START_MTTR_TARGET?
>
> Thanks
> Regards,
> Ashish
>
>
>

--
http://www.freelists.org/webpage/oracle-l