Re: Column modification happening in serial

I've remembered this one:

https://timurakhmadeev.wordpress.com/2011/02/16/enabling-constraint-in-parallel/

On Sat, May 7, 2022 at 1:30 AM Rich J <rich242j_at_gmail.com> wrote:

> Hi Pap,
>
> I don't know if it's a bug or just undocumented behavior, but I've seen
> this as far back as 11.2, and possibly 10.1. I drop indexes and the PK for
> loading large tables in test/QA environments, and yeah, validating the
> constraint during it's creation refuses to use parallel. But the separate
> validation does use it. There's probably an old thread or two here about
> that.
>
> Good luck!
> Rich
>
> On Fri, May 6, 2022, 14:02 Pap <oracle.developer35_at_gmail.com> wrote:
>
>> 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>
>>>
>>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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