Dbms pclxutil - DB CAFE

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 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