Re: CTAS running long

Considering the log reuse waits you are worried about are due to checkpoints, I suspect setting the log to use a cache will enable Oracle to do things a little smarter. If it’s cached, it doesn’t need direct path writes which will give you collateral improvements with physical IO anyway.

Another easy thing to do would be to increase the size of those log files. If you’re going to be doing this in parallel 32, you are going to be switching logs very frequently if they’re only 500 mb each.

I will have a closer look at the plan when I’m in front of my computer, there’s probably other easy wins.

On Sat, 7 May 2022 at 21:24, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 5/7/22 13:37, Lok P wrote:
>
> Resending as bounced back...
>
> Hi , We are trying to purge data from a non partition table, as part of
> which we are creating an interim table which would be partitioned and that
> will hold the required data and then we will exchange that partition with
> the main table to purge data from the main table.
>
> The issue is that the create interim partitioned table statement which is
> supposed to pick and purge ~60million records from the non partition
> table(TABLE1) is running long. The base/main table (Say TABLE1) is ~91GB in
> size and holds 71million rows and avg_row_len is showing as 1178. The sql
> monitor we captured is as below. It shows almost all time spent on "log
> file switch (checkpoint incomplete)". The interim table creation is running
> in Parallel-32.
>
> Have you considered filtered table move, in order to purge the rows? Here
> is how it's done:
>
>
> https://asktom.oracle.com/pls/apex/f?p=100:11:::::P11_QUESTION_ID:9541030000346124574
>
> Alter table <table> move including rows where <filter condition> is
> typically faster than a plain delete or even sophisticated bulk PL/SQL
> procedures.
>
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217https://dbwhisperer.wordpress.com
>
> -- http://www.freelists.org/webpage/oracle-l

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