Dbms pclxutil - DB CAFE

DBMS_PCLXUTIL[편집]

dbms_pclxutil 프로시저는 큰 파티션된 테이블이 있고 이러한 테이블에 로컬 인덱스를 작성해야 하는 경우에 사용됩니다.
build_part_index 절차를 통해 사용자는 병렬 처리를 사용하여 이러한 인덱스를 만들 수 있습니다.
dbms_pclxutil는 병렬 힌트의 대안입니다.

number of session_ids on dbms_pclxutil
January 11, 2016 - 2:34 pm UTC
Reviewer: Rajeshwaran Jeyabal
Team,

Help me to understand the number of parallel session spanned on dbms_pclxutil package.

drop table t purge;
create table t 
partition by hash(object_id)
( partition p1 ,
  partition p2,
  partition p3,
  partition p4 ) 
parallel 4  
as
select * from big_table;
alter table t noparallel;
create index t_idx on t(owner,object_type,object_name) local unusable;
column partition_name format a10
select partition_name,partition_position,status
from user_ind_partitions
where index_name  ='T_IDX';
While this command is running from session#1

-------------------- [ dbms_pclxutil.build_part_index ] 수행 
rajesh@ORA10G> exec dbms_pclxutil.build_part_index(3,4,'T','T_IDX');

INFO: Job #1802 created for partition P1 with 4 slaves
INFO: Job #1803 created for partition P2 with 4 slaves
INFO: Job #1804 created for partition P3 with 4 slaves
INFO: Job #1805 created for partition P4 with 4 slaves

PL/SQL procedure successfully completed.

rajesh@ORA10G>
Monitoring this from session#2
rajesh@ORA10G> select * from dba_jobs_running order by sid;

       SID        JOB   FAILURES LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC   INSTANCE
---------- ---------- ---------- ----------- -------- ----------- -------- ----------
       108       1803                                 11-JAN-2016 19:45:48          0
       138       1804                                 11-JAN-2016 19:45:48          0
       148       1802                                 11-JAN-2016 19:45:48          0

3 rows selected.

rajesh@ORA10G>
rajesh@ORA10G> 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_sesstat
  6  where qcsid in (select sid from dba_jobs_running)
  7  group by grouping sets( (qcsid),() )
  8  order by qcsid;

     QCSID       CNT0       CNT1       CNT2       CNT3
---------- ---------- ---------- ---------- ----------
       108       3123          9          2          4
       138       3123          9          2          4
       148       3123          9          2          4
                 9369         27          2          4

4 rows selected.

rajesh@ORA10G>

a) requested explicitly to go with 3 jobs per batch so job_id are 108,138,148.
b) 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 in total.

I am able to understand this, but why does the count(*) shows 3123 for each job? i am unable to get that, please help me to understand.

PS: I am the only user connected this database, no other active user/processing running though