Dbms pclxutil
DB CAFE
thumb_up 추천메뉴 바로가기
- 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