"Dbms pclxutil"의 두 판 사이의 차이
DB CAFE
(→DBMS_PCLXUTIL) |
(→DBMS_PCLXUTIL) |
||
31번째 줄: | 31번째 줄: | ||
where index_name ='T_IDX'; | where index_name ='T_IDX'; | ||
While this command is running from session#1 | 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'); | 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 #1802 created for partition P1 with 4 slaves | ||
INFO: Job #1803 created for partition P2 with 4 slaves | INFO: Job #1803 created for partition P2 with 4 slaves |
2023년 4월 7일 (금) 17:45 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
DBMS_PCLXUTIL[편집]
record_voice_over
- 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