RE: CTAS running long
Date: Thu, 12 May 2022 10:23:46 -0400
Message-ID: <031e01d8660b$e4bb1950$ae314bf0$_at_rsiz.com>
Usually copying in with some columns “blank” (aka null) initially and then populating the values is ill-advised.
In the case of LOBs (especially out of line storage lobs), I *suspect* (without testing) that if you create your interim slenderized (deletion by leaving behind) table without populating the LOBs and then pull just the LOBs into their final destination as securefile LOBs this will be more efficient.
I would at least test that approach for a medium or large partition of “keepers.”
You may also want to set up scan jobs to cache a chunk of LOBs from the original (perhaps at parallel by job matching the number of partition destinations one-for-one), and NOT running any partition destination in parallel (use multiple independent destinations up the ouch point).
Zero of this is in conflict with what JL has written, it is just an askew “you might want to try this.”
good luck (which tends to be tilted in your favor by testing)…
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, May 12, 2022 5:52 AM
To: Oracle L
Subject: Re: CTAS running long
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
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
Global Stats
SQL Plan Monitoring Details (Plan Hash Value=2463526707)
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
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/-
| 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 |
=======================================================================================================================================================================
| 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
Received on Thu May 12 2022 - 16:23:46 CEST