Re: CTAS running long

2nd attempt - short version.
A couple of points - how confident are you that after you've done the CTAS the exchange will work? Has ANYTHIHG happened in the past that means Oracle will be unable to exchange (e.g. you've dropped a column). Before doing a massive CTAS try a basic "create table as select ,,, where rownum <= 10" to get a very small clone and check that exchange works.

Why are you doing it this way and not doing an online move with filtering? Is this following a pattern that I suggested to minimise downtime when it had to be done on 11g? Have you tested an inline move (or even an "offline" move) with 19c.as Mladen suggested?

Looking at your stats
a) operation 6 is doing a tablescan (with smart scan and offload to identify rows you want, and the returned rows include the lob locator for rows where the lob exceeds 4,000 bytes but include the lob when the lob is small enough (i.e. in-row). That's why you get 6GB for data for 10,000 read requests. (I don't know why it isn't closer to 6,000 reads, but there will be some bitmap blocks and some undo blocks read, possibly - that's why it's good to look at Instance (or session/px_session stats) while doing this type of thing).

b) operation 3 is writing the data - but as it does so it reads the LOBs for the rows where it has only the LOB locators. You've got a basicfile lob so the large LOBs are read one block as a time using the lobindex, but the default is that the copy will be a securefile lob which can be written in large chunks. This is why you get 40M read requests to read 308GB but only 545K writes to write 318GB - Oracle is writing each LOB in chunks of up to 1MB at a time. You need to do some testing to make sure that when you exchange a securefile partition with a basicfile table all the bits work properly and you don't get side effects.

c) It might have been helpful to see the section on parallel work done.

d) I don't understand why the global stats show "offload returned bytes" = 632GB. Possibly it's an accounting error and Oracle has summed the PX slave returned bytes to the coordinator then summed that with the PX slave bytes to get this x2 result.

More to follow later today - "what can you do to go faster"

Regards
Jonathan Lewis

On Sat, 7 May 2022 at 18:37, Lok P <loknath.73_at_gmail.com> 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.
>
> Btw we saw while performing CTAS for other tables with even more data we
> didn't see such an issue. And then one thing we noticed is, there exists
> two CLOB columns in this main table(table1 here) which were not there in
> other tables. And the CLOB columns were defined as below. So my question is
> , is this high "log file switch (checkpoint incomplete)" wait event
> happening because of the presence of these clob columns and how can we make
> this interim table creation faster in such a case?
>
> And we have our database in force logging mode and we have data guard
> configuration in place. If we create the interim table as nologging will it
> help here or cause any issue as we have our main non partitioned table is
> created as 'logging' mode?
>
> LOB (CLOB1) STORE AS (
>
> TABLESPACE TBS2
> ENABLE STORAGE IN ROW
> CHUNK 8192
> RETENTION
> NOCACHE
> NOLOGGING
> STORAGE (
> INITIAL 64K
> NEXT 1M
> MINEXTENTS 1
> MAXEXTENTS UNLIMITED
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> ))
> TABLESPACE TBS2
> PCTUSED 0
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> NEXT 1M
> MAXSIZE UNLIMITED
> MINEXTENTS 1
> MAXEXTENTS UNLIMITED
> PCTINCREASE 0
> BUFFER_POOL DEFAULT
> )
> NOLOGGING
> NOCOMPRESS
> NOCACHE
> MONITORING;
>
>
> select group#, thread#, archived, status, BYTES/1024/1024 MB from v$log
>
> GROUP# THREAD# ARCHIVED STATUS MB
>
> 1 1 NO CURRENT 500
>
> 2 1 YES ACTIVE 500
>
> 3 1 YES ACTIVE 500
>
> 4 2 YES INACTIVE 500
>
> 5 2 NO CURRENT 500
>
> 6 2 YES ACTIVE 500
>
>
> CREATE TABLE TABLE_BKP (c1, c2, c3, c4.. c12) parallel 32 partition by
> range (C3) (partition p0 values less than (maxvalue) tablespace TBS1) as (
> select /*+ parallel(t, 32) */ * from TABLE1 t where cre_ts>Sysdate-700)
>
> Global Information
> ------------------------------
> Status : EXECUTING
> Instance ID : 1
> SQL ID : 3rfch2jrrciou
> SQL Execution ID : 16777216
> Execution Started : 05/07/2022 01:47:51
> First Refresh Time : 05/07/2022 01:47:52
> Last Refresh Time : 05/07/2022 02:24:12
> Duration : 2198s
> Module/Action : SQL*Plus/-
>
> Global Stats
>
> =======================================================================================================================================================================
> | Elapsed | Cpu | IO | Application | Concurrency | Cluster |
> Other | Buffer | Read | Read | Write | Write | Uncompressed | Offload
> | Offload |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
> Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Bytes | Elig
> Bytes | Returned Bytes |
>
> =======================================================================================================================================================================
> | 70278 | 5941 | 42197 | 0.00 | 10 | 2.32 |
> 22128 | 378M | 40M | 314GB | 544K | 314GB | 6GB | 6GB
> | 632GB |
>
> =======================================================================================================================================================================
>
> SQL Plan Monitoring Details (Plan Hash Value=2463526707)
> ============================================================
> ============================================================
> ============================================================
> ============================================================
> | Id | Operation | Name |
> Rows | Cost | Time | Start | Execs | Rows | Read | Read |
> Write | Write | Mem | Activity | Activity Detail
> | Progress |
> | | | |
> (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
> Reqs | Bytes | | (%) | (# samples)
> | |
> ============================================================
> ============================================================
> ============================================================
> ============================================================
> | 0 | CREATE TABLE STATEMENT | |
> | | | | 33 | | | |
> | | . | |
> | |
> | 1 | PX COORDINATOR | |
> | | | | 33 | | | |
> | | . | |
> | |
> | 2 | PX SEND QC (RANDOM) | :TQ10000 |
> 44M | 58295 | | | 32 | | | |
> | | . | |
> | |
> | -> 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | TABLE_BKP |
> | | 2201 | +3 | 32 | 0 | 40M | 308GB | 545K
> | 314GB | 65MB | 99.98 | gc cr grant 2-way (4)
> | |
> | | | |
> | | | | | | | |
> | | | | buffer busy waits (1)
> | |
> | | | |
> | | | | | | | |
> | | | | log buffer space (16)
> | |
> | | | |
> | | | | | | | |
> | | | | log file switch (checkpoint incomplete)
> (21981) | |
> | | | |
> | | | | | | | |
> | | | | log file switch completion (30)
> | |
> | | | |
> | | | | | | | |
> | | | | Cpu (4405)
> | |
> | | | |
> | | | | | | | |
> | | | | enq: TX - contention (13)
> | |
> | | | |
> | | | | | | | |
> | | | | ASM IO for non-blocking poll (331)
> | |
> | | | |
> | | | | | | | |
> | | | | cell single block physical read (656)
> | |
> | | | |
> | | | | | | | |
> | | | | direct path read (42447)
> | |
> | | | |
> | | | | | | | |
> | | | | direct path write (2)
> | |
> | -> 4 | OPTIMIZER STATISTICS GATHERING | |
> 44M | 58295 | 2201 | +3 | 32 | 3M | | |
> | | 5MB | 0.01 | Cpu (4)
> | |
> | -> 5 | PX BLOCK ITERATOR | |
> 44M | 58295 | 2201 | +3 | 32 | 3M | | |
> | | . | |
> | |
> | -> 6 | TABLE ACCESS STORAGE FULL | TABLE1 |
> 44M | 58295 | 2201 | +3 | 62 | 3M | 10068 | 6GB |
> | | 225MB | 0.01 | Cpu (5)
> | 100% |
> | | | |
> | | | | | | | |
> | | | | cell smart table scan (2)
> | |
> ============================================================
> ============================================================
> ============================================================
> ============================================================
>

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