Re: CTAS running long

So it looks like, making the lob cached won't help us here in this case.

 But trying to understand how 'cache' option will make the direct path write faster though as it's mentioned in the blog. it doesn't seems to help in our case here though.

On Sun, 8 May 2022, 12:12 am Andy Sayer, <andysayer_at_gmail.com> wrote:

> 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