I help determining why parallel thread is not consistent with DDL during dbms_redefinition.redef_tab

User_PBRZM

Hi

we are doing database shrink and in the process, we are doing some dbms_redefinition.redef_table, using ALTER SESSION FORCE PARALLEL DDL PARALLEL 8; This is a RAC environment with non partition tables and the version of oracle is 19.13 on exadata. The reorg is not consistent with the degree of parallelism we want to achieve. At one point, the task is using the right parallelism while and one point parallelism is not being used. Please see the full script we are using to achieve this and advise/help if you can how we can better achieve a much consistent parallelism and fast performance .

set echo on

set verify on

set feedback on

set timing on

spool /home/oracle/scripts/shrink/AHOMA_TS/Logs/09_dbms_move_table.log

alter session set nls_date_format='dd-mon-rr hh24:mi:ss';

alter session force parallel dml parallel 8;

alter session force parallel query parallel 8;

ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;

select sysdate from dual;

declare

errmsg varchar2(200);

begin

for rec IN (

select table_name

from dba_tables t where owner = 'TBS_OWNER'

and T.TABLESPACE_NAME = 'TBS_NAME'

order by table_name

) LOOP

begin

dbms_redefinition.redef_table('AHOMAUD',rec.table_name,NULL,'AHOMA_TS2',NULL,NULL,NULL,NULL,NULL);

DBMS_OUTPUT.PUT_LINE('Completed '||rec.table_name);

exception

when others then

errmsg := substr(sqlerrm,1,200);

DBMS_OUTPUT.PUT_LINE('Error moving table '||rec.table_name);

DBMS_OUTPUT.PUT_LINE(errmsg);

end;

END LOOP;

DBMS_OUTPUT.PUT_LINE('******* MOVE COMPLETE ****** ');

end;

/

select sysdate from dual;

Spool off

exit