Dbms pclxutil
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
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