Re: CTAS running long

Logging on a force log cached lob is based on archivelog. ( https://blogs.sap.com/2013/03/19/oracle-the-importance-of-a-right-performance-test-environment-and-why-lobs-dont-generate-the-expected-redo-content/ )

Now say that 5 times fast.

That said, same applies as you have dataguard so you will be archiving logs.

Thanks,
Andrew

On Sat, 7 May 2022 at 19:25, Noveljic Nenad <nenad.noveljic_at_vontobel.com> wrote:

> Does it get better if you change the lob definition to cache instead of
> nocache?
>
> NoLogging setting for lob shouldn‘t matter because the logging is forced
> on the db level.
>
>
>
> *Von: *oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> im
> namen von: Lok P <loknath.73_at_gmail.com>
> *Datum *Samstag, 07. Mai 2022, 7:37 PM
> *An: *Oracle L <oracle-l_at_freelists.org>
> *Betreff: *CTAS running long
>
> **** E-Mail from outside Vontobel:* Do not click on links or open
> attachments unless you know the content is safe. ***
>
> 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)
> | |
> ============================================================
> ============================================================
> ============================================================
> ============================================================
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the Vontobel Group
> are subject to electronic storage and review by Vontobel Group. Unless
> stated to the contrary and without prejudice to any contractual agreements
> between you and Vontobel Group which shall prevail in any case,
> e-mail-communication is for informational purposes only and is not intended
> as an offer or solicitation for the purchase or sale of any financial
> instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com.
>

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