Re: Column modification happening in serial

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 6 May 2022 13:40:16 +0200
Message-ID: <CAJu8R6gkdD_gxvkOqKZWpYa7444rYC2=3kAdYdyO-AZCdTXLNg_at_mail.gmail.com>

Hello Pap

Have you tried something like this?

SQL> _at_desc t1_part

Name Null? Type

-------- ---------------------------- PKEY VARCHAR2(11) N1 NUMBER N_50 NOT NULL NUMBER N_800 NUMBER N2 NUMBER

SQL> alter table t1_part modify n_800 not null enable novalidate;

Table altered.

SQL> select constraint_name, search_condition from user_constraints where table_name ='T1_PART' and validated = 'NOT VALIDATED';

CONSTRAINT_NAME SEARCH_CONDITION

-------------------- -------------------
SYS_C008342          "N_800" IS NOT NULL

SQL> alter session force parallel ddl;

Session altered.

SQL> alter table t1_part parallel 4;

Table altered.

SQL> alter table t1_part modify constraint SYS_C008342 validate;

Table altered.

SQL> alter table t1_part noparallel;

Table altered.

SQL> alter session disable parallel ddl;

Session altered.

You can then search for the recursive sql executed by Oracle to validate the constraints like what follows:

SQL> select sql_id from gv$sql_monitor where sql_text like '%"N_800" IS NOT NULL%'; SQL_ID

4xz1qn2wvj2fq

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => '4xz1qn2wvj2fq',
  type         => 'TEXT',
  report_level => 'ALL') AS report

FROM dual;

SQL Monitoring Report

SQL Text

select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "C##MHOURI"."T1_PART" A where not ( "N_800" IS NOT NULL)

Global Information

 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  C##MHOURI (59:48630)
 SQL ID              :  4xz1qn2wvj2fq
 SQL Execution ID    :  16777216
 Execution Started   :  05/06/2022 11:32:05
 First Refresh Time  :  05/06/2022 11:32:05
 Last Refresh Time   :  05/06/2022 11:32:05
 Duration            :  .087084s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus_at_localhost.localdomain (TNS V1-V3)
 Fetch Calls         :  1

Global Stats

| 0.27 | 0.20 | 0.01 | 0.06 | 1 | 6443 | 128 | 39MB |

Parallel Execution Details (DOP=4 , Servers Allocated=4)

| PX Coordinator | QC | | 0.02 | 0.01 | |

0.01 |    167 |      |     . |             |


| p000           | Set 1 |       1 |    0.07 |    0.04 |     0.00 |


0.02 |   1328 |   27 |   8MB |             |


| p001           | Set 1 |       2 |    0.05 |    0.04 |     0.00 |


0.00 |   1527 |   31 |   9MB |             |


| p002           | Set 1 |       3 |    0.05 |    0.04 |     0.00 |


0.00 |   1463 |   34 |   9MB |             |


| p003           | Set 1 |       4 |    0.09 |    0.06 |     0.00 |


0.03 |   1958 |   36 |  12MB |             |
====================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=4020278909)

| 0 | SELECT STATEMENT | | | | |

   |     5 |


|  1 |   PX COORDINATOR       |          |         |      |           |


   |     5 |


|  2 |    PX SEND QC (RANDOM) | :TQ10000 |       1 |  609 |           |


   |     4 |


|  3 |     PX BLOCK ITERATOR  |          |       1 |  609 |           |


   |     4 |


|  4 |      TABLE ACCESS FULL | T1_PART  |       1 |  609 |           |

| 68 |

Best regards
Mohamed Houri

Le ven. 6 mai 2022 à 12:54, Pap <oracle.developer35_at_gmail.com> a écrit :

> This is version 19c of oracle 19.11 to be specific. We have table with ~25
> billion rows and is ~2.4T in size and is range partitioned with 500
> partitions. We have a column we want to convert it to not null + validate
> and post that these four column will comprise the primary key. But making
> this columns NOT NULL is running forever. And the bad part is we are not
> seeing this happening in parallel even the table is having degree
> parallel-32 and also we see the wait events its showing 'cell multiblock
> physical read' allover but not "cell smartscan". So this alter query is
> running in serial fashion but not running in parallel.
>
> I tried to see few blogs as below but seeing below two are contradicting ,
> the first doc stating ALTER ATBLE MODIFY can run in parallel by just
> stating the parallel degree for table but its somehow now working as i am
> testing. And the second blog showing 'the ddl statement that can be
> parallelized' but 'alter table modify' statement is not there. So wanted to
> understand what is the fastest way to achieve this?
>
>
> alter table TAB1 modify(COL1 not null,COL2 not null,COL3 not null,COL4 not
> null ) parallel 32;
>
>
>
> Do Not Use Parallel Query In ALTER TABLE MODIFY (column Not Null) (Doc ID
> 2264469.1)
>
>
>
>
> https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-365F1095-6434-44E2-AE16-F2777EDC82F1
>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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

Received on Fri May 06 2022 - 13:40:16 CEST

This message: [ Message body ]
Next message: Beckstrom, Jeffrey: "Unable to add database to EM"
Previous message: Pap: "Column modification happening in serial"
In reply to: Pap: "Column modification happening in serial"
Next in thread: Pap: "Re: Column modification happening in serial"
Reply: Pap: "Re: Column modification happening in serial"
Reply: Lok P: "CTAS running long"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message