Re: CTAS running long

Thank You Jonathan. Somehow the parallel activity section was not going through in the email body , so i tried to add it in the below location.

https://gist.github.com/oraclelearner/c69bf6131a3738815aa075cb007eae30

And also yes the partition exchange method we opted as its been a standard for non partition table purge since 11.2.0.4 time. As Mladen and you also highlighted we are definitely going to use 'Move table with filter' driven in parallel with index rebuild post option for the non partition table purge going forward.

We have encountered errors/bugs with the partition exchange method in the past where a table column 'fast added'/dropped/hidden has been there in the past on the main table. But it was working fine on dev when we tested with a clob column but will double check on this and will definitely try to opt for table move with filter option as purging strategy going forward.

But to say we were removing ~80% of rows and there was a good side of the partition exchange method was that the full table was sitting as a backup table for a few days(For ~2 weeks) just in case business needed any of that historical data(which was a requirement for us here). And in this table move case we may have to take an export of that table before doing the table move operation as that data will be purged in place.

As I got it from the details , at a minimum we will try to run the CTAS by doing the CLOB column as CACHE in the main table + increasing the redo logs to ~10GB i.e 20 groups at least.

Thank you So much.

On Thu, May 12, 2022 at 3:22 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> A thought about why using move with filter - the lob segment would end up
> as a basicfile lob segment after the move.
> So you'd do just about the same amount of work (more, possibly because of
> the necessary maintenace of the lobindex).
>
> 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