Dbms_pclxutil package for “Intra” and “Inter” partition wise parallelism for local indexes

rajesh@ORA11G> set feedback off

rajesh@ORA11G> drop table t purge;

rajesh@ORA11G> create table t

  2  partition by hash(object_id)

  3  ( partition p1 ,

  4    partition p2,

  5    partition p3,

  6    partition p4 )

  7  parallel 4

  8  as

  9  select * from big_table;

rajesh@ORA11G> alter table t noparallel;

rajesh@ORA11G> create index t_idx on t(owner,object_type,object_name) LOCAL unusable;

rajesh@ORA11G> set feedback on

rajesh@ORA11G> column partition_name format a10

rajesh@ORA11G> select partition_name,partition_position,status

  2  from user_ind_partitions

  3  where index_name  ='T_IDX';

PARTITION_ PARTITION_POSITION STATUS

---------- ------------------ --------

P1                          1 UNUSABLE

P2                          2 UNUSABLE

P3                          3 UNUSABLE

P4                          4 UNUSABLE

4 rows selected.

We have local index defined in Table ‘T’ all in unusable status.

rajesh@ORA11G> show parameter job_queue

NAME                                          TYPE        VALUE

--------------------------------------------- ----------- -----------------

job_queue_processes                           integer     1000

We have job_queue_processes set to 1000 in database.  Here is an option rebuild all the local indexes concurrently in database using dbms_pclxutil.

rajesh@ORA11G> exec dbms_pclxutil.build_part_index(3,4,'T','T_IDX');

INFO: Job #83 created for partition P1 with 4 slaves

INFO: Job #84 created for partition P2 with 4 slaves

INFO: Job #85 created for partition P3 with 4 slaves

INFO: Job #86 created for partition P4 with 4 slaves

PL/SQL procedure successfully completed.

We requested to go with three jobs concurrently, which each job spans four parallel threads to rebuild all unusable local indexes T_IDX on table T.

While that above scripts run in database, we can monitor the concurrent jobs and parallel slaves from different session.

rajesh@ORA11G> select * from dba_jobs_running order by sid;

       SID        JOB   FAILURES LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC   INSTANCE

---------- ---------- ---------- ----------- -------- ----------- -------- ----------

         9         84                                 13-JAN-2016 09:48:35          0

        73         85                                 13-JAN-2016 09:48:35          0

       206         83                                 13-JAN-2016 09:48:35          0

3 rows selected.

rajesh@ORA11G>

rajesh@ORA11G> select qcsid,count(*) as cnt0,

  2     count(distinct sid) cnt1,

  3     count(distinct server_set) cnt2,

  4     count(distinct server#) cnt3

  5  from v$px_session

  6  where qcsid in (select sid from dba_jobs_running)

  7  group by qcsid

  8  order by qcsid;

     QCSID       CNT0       CNT1       CNT2       CNT3

---------- ---------- ---------- ---------- ----------

         9          9          9          2          4

        73          9          9          2          4

       206          9          9          2          4

3 rows selected.

Each job has 2 parallel server sets(operating in producer and consumer model) and each server set has 4 px_servers, so in total each job has 2 PX_server_sets * 4 Px_servers_per_server_set + 1 QC = 9 sid's per job.

Looking into the v$sql the database has internally submitted multiple jobs for each partition.

rajesh@ORA11G> select sql_text

  2  from v$sql

  3  where upper(sql_text) like '%T_IDX%'

  4  and upper(sql_text) like 'DECLARE%' ;

SQL_TEXT

-------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P4" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P3" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P3" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P2" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P2" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P1" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P4" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P1" parallel (degree 4)'); END;

8 rows selected.

rajesh@ORA11G>