Re: Column modification happening in serial

Thank You So much Mohamed. This alternate way of adding the not null constraint works in parallel(mainly the validate step which is more resource consuming is happening in parallel as per need). Thanks again. This helps.

However just curious to know why the direct alter using 'alter table modify(col1 not null)' statement did not work in parallel. Is this a bug in this version? or am I missing something here?

On Fri, May 6, 2022 at 5:10 PM Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> 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
> ===========================================================================
> | Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
> | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
> ===========================================================================
> | 0.27 | 0.20 | 0.01 | 0.06 | 1 | 6443 | 128 | 39MB |
> ===========================================================================
>
> Parallel Execution Details (DOP=4 , Servers Allocated=4)
>
> ====================================================================================================================
> | Name | Type | Server# | Elapsed | Cpu | IO | Other
> | Buffer | Read | Read | Wait Events |
> | | | | Time(s) | Time(s) | Waits(s) |
> Waits(s) | Gets | Reqs | Bytes | (sample #) |
>
> ====================================================================================================================
> | 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)
>
> ==================================================================================================
> | Id | Operation | Name | Rows | Cost | Time |
> Start | Execs | Rows
> | | | | (Estim) | | Active(s) |
> Active | | (Actual)
>
> ==================================================================================================
> | 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