INDEX Rebuild시 parallel옵션 사용에 따른 degree확인 테스트(partitioned index 포함)

문의가 온 내용을 찾아보고 확인해보며 테스트하여 정리한 내용입니다.

 

제가 우선 알고 있었던 내용으로는, 테이블이나 인덱스나 상관없이 PARALLEL 작업을 할때

ALTER TABLE [테이블명] PARALLEL 4 , ALTER INDEX [인덱스명] PARALLEL 4 같은 명령어를 사용했다면, 다시 원복을 해줘야 한다고 알고 있었습니다. 그렇지 않으면 계속 해당 오브젝트에는 PARALLEL이 적용되어 있어 PARALLEL이 필요하지 않을 때도 PARALLEL로 수행될 수 있기 때문입니다.

실제로도 이 말이 맞습니다.

 

이번에 테스트를 한 내용은 명시적으로 PARALLEL 명령어를 주는 경우가 아닌, INDEX REBUILD 시에  PARALLEL 옵션을 주고 다시 noparallel로 원복을 해야하는지에 대한 테스트입니다.

 

결론부터 이야기하면 파티션인덱스의 rebuild 작업은 rebuild 작업에만 parallel이 영향을 미친다고 보면 될 것 같습니다.

일반인덱스의 경우 rebuild 작업 후에 degree에 영향이 있습니다.

 

우선 일반 인덱스에 대한 테스트입니다.

 

Non Partitioned Index

 

1.rebuild 수행전 degree 확인

select index_name,table_name,degree from dba_indexes
where table_name='ORD_ITEM_RANDOM'

INDEX_NAME 		TABLE_NAME		DEGREE
-----------------------------------------
IX_ORD_ITEM_RANDOM_X09		ORD_ITEM_RANDOM		1

 

2.rebuild 명령어 수행

alter index IX_ORD_ITEM_RANDOM_X09 rebuild parallel 8;

 

3.rebuild 수행 후 degree 확인

16개로 parallel 프로세스가 작동 중인것을 확인 할 수 있습니다.

실제 parallel 프로세스 수 확인은 세션을 하나 더 열어서 rebuild 작업이 수행 중일때 확인해야 합니다.

select index_name,table_name,degree from dba_indexes
where table_name='ORD_ITEM_RANDOM'

INDEX_NAME 		TABLE_NAME		DEGREE
-----------------------------------------
IX_ORD_ITEM_RANDOM_X09		ORD_ITEM_RANDOM		8




실제 parallel 프로세스 수 확인

SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';

STATISTIC             VALUE      CON_ID       
------------------------- ------------ 
Servers Busy           16            0

 

4.degree 다시 1로 변경

alter index IX_ORD_ITEM_RANDOM_X09  parallel 1;

 

일반 인덱스의 경우 rebuild 시 parallel 옵션을 사용하면 index의 degree가 변경되어 적용됩니다.

(rebuild 작업자체가 move -> re create 작업 수행과 비슷한 매커니즘이라고 볼 수 있을 것 같습니다, 따라서 parallel로 적용이 된다고 보여집니다.)

 

 

PARTITIONED INDEX

 

https://docs.oracle.com/database/121/VLDBG/GUID-E3F353CB-9748-44D4-B7B1-4BBAAF618D9D.htm

Using ALTER INDEX to Rebuild a Partition

 

위의 URL을 확인하면 PARTITION INDEX에서도 Rebuild 시 parallel 옵션을 사용할 수 있습니다.

 

 

1.rebuild 전

SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';

STATISTIC             VALUE      CON_ID       
------------------------- ------------ 
Servers Busy           0            0

select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01'

INDEX_NAME 		TABLE_NAME		DEGREE
-----------------------------------------
ORD_ITEM_RANGE_X01	ORD_ITEM_RANGE	1

2.수행명령어

alter index ORD_ITEM_RANGE_X01 rebuild partition P201201 parallel 8;

 

2.rebuild 후

SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';

STATISTIC             VALUE      CON_ID       
------------------------- ------------ 
Servers Busy           16            0


select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01'

INDEX_NAME 		TABLE_NAME		DEGREE
-----------------------------------------
ORD_ITEM_RANGE_X01	ORD_ITEM_RANGE	1

 

rebuild 중일때 parallel 프로세스를 확인해보니 사용중이었습니다. 실제로 parallel로 rebuild가 수행되었다는 것을 의미합니다. 하지만 partition index의 경우 그 파티션 인덱스만의 degree는 확인할 수 없었습니다. 글로벌 인덱스(파티션인덱스들의 상위 인덱스)의 degree는 변하지않은 상태였습니다.

 

이 상태에서 딕셔너리뷰만 체크해서 확인하기에는 힘들것으로 판단되어. 실행계획을 이용한 테스트를 추가 진행하였습니다. degree가 1이 아니라면, 의도적으로 쿼리에 인덱스와 파티션을 사용하였을때 parallel로 실행계획이 보일 것이라 생각했기 때문입니다.

 

 

글로벌 인덱스가 parallel이 4일 때 실행계획 확인

 

degree가 4인 경우에는 (힌트에 parallel이 없음) 실행계획이 parallel로 보입니다.

SQL> alter index ORD_ITEM_RANGE_X01 parallel 4

SQL> select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01'

INDEX_NAME 		TABLE_NAME		DEGREE
--------------------------------------
ORD_ITEM_RANGE_X01	ORD_ITEM_RANGE	4

SQL> select /*+ (ORD_ITEM_RANGE ORD_ITEM_RANGE_X01) */* from DEV.ORD_ITEM_RANGE
where ord_dt is not null
and ord_hms < '000000'

*************************[Explain Plan Time: 2022/05/03 09:39:20]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1K Card=236 Bytes=84K)
   1    0   PX COORDINATOR
   2    1     PX SEND (QC (RANDOM)) OF 'SYS.:TQ10000' (Cost=1K Card=236 Bytes=84K) (PARALLEL_TO_SERIAL) (QC (RANDOM))
   3    2       PX PARTITION RANGE (ALL) (Cost=1K Card=236 Bytes=84K) (PARALLEL_COMBINED_WITH_CHILD)
   4    3         TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF 'ORD_ITEM_RANGE' (TABLE) (Cost=1K Card=236 Bytes=84K) (PARALLEL_COMBINED_WITH_PARENT)
   5    4           INDEX (SKIP SCAN) OF 'ORD_ITEM_RANGE_X01' (INDEX) (Cost=780 Card=236) (PARALLEL_COMBINED_WITH_PARENT)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   5 - access("ORD_HMS"<'000000')
   5 - filter("ORD_HMS"<'000000' AND "ORD_DT" IS NOT NULL)
-----------------------------------------------------------

 

글로벌 인덱스가 degree가 1인경우 실행계획 확인

degree가 1일 경우(default)에는 실행계획이 parallel로 보이지 않고 일반적인 실행계획으로 보입니다.

SQL> alter index ORD_ITEM_RANGE_X01 noparallel


select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01'

INDEX_NAME 		TABLE_NAME		DEGREE
---------------------------------------
ORD_ITEM_RANGE_X01	ORD_ITEM_RANGE	1


SQL> select /*+ (ORD_ITEM_RANGE ORD_ITEM_RANGE_X01) */* from DEV.ORD_ITEM_RANGE
where ord_dt is not null
and ord_hms < '000000'

*************************[Explain Plan Time: 2022/05/03 09:40:24]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1K Card=236 Bytes=84K)
   1    0   PARTITION RANGE (ALL) (Cost=1K Card=236 Bytes=84K)
   2    1     TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF 'ORD_ITEM_RANGE' (TABLE) (Cost=1K Card=236 Bytes=84K)
   3    2       INDEX (SKIP SCAN) OF 'ORD_ITEM_RANGE_X01' (INDEX) (Cost=780 Card=236)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   3 - access("ORD_HMS"<'000000')
   3 - filter("ORD_HMS"<'000000' AND "ORD_DT" IS NOT NULL)
-----------------------------------------------------------

 

파티션인덱스를 parallel로 rebuild 하고 실행계획 확인

글로벌 인덱스의 degree는 1이고, 파티션 rebuild를 parallel 8로 수행 후 쿼리를 수행하였습니다. 이전 쿼리와는 다르게 rebuild된 파티션 인덱스를 이용하기 위해서 ORD_YM='201201'를 추가하였습니다.

실행계획 확인 결과 parallel로 수행되지 않고 일반적인 쿼리로 수행되었습니다.

 

SQL> alter index ORD_ITEM_RANGE_X01 rebuild partition P201201 parallel 8;



SQL> select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01';

INDEX_NAME          TABLE_NAME       DEGREE   
------------------- ---------------- ---------
ORD_ITEM_RANGE_X01  ORD_ITEM_RANGE   1        

1 rows selected.


SQL> select /*+ (ORD_ITEM_RANGE ORD_ITEM_RANGE_X01) */* from DEV.ORD_ITEM_RANGE
WHERE ORD_YM = '201201'
and ord_dt is not null
and ord_hms < '000000'


*************************[Explain Plan Time: 2022/05/03 10:51:14]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=10 Bytes=2K)
   1    0   PARTITION RANGE (SINGLE) (Cost=44 Card=10 Bytes=2K)
   2    1     TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF 'ORD_ITEM_RANGE' (TABLE) (Cost=44 Card=10 Bytes=2K)
   3    2       INDEX (SKIP SCAN) OF 'ORD_ITEM_RANGE_X01' (INDEX) (Cost=33 Card=10)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter("ORD_YM"='201201')
   3 - access("ORD_HMS"<'000000')
   3 - filter("ORD_HMS"<'000000' AND "ORD_DT" IS NOT NULL)
-----------------------------------------------------------

 

글로벌 파티션 degree가 1이 아닐때 실행계획 확인

degree가 1이 아닌경우에도 (parallel 가능) 해당 파티션인덱스는 실행계획에 parallel로 보여지지 않습니다.

SQL> alter index ORD_ITEM_RANGE_X01 parallel 4


SQL> select index_name,table_name,degree from dba_indexes
where index_name='ORD_ITEM_RANGE_X01'

INDEX_NAME          TABLE_NAME       DEGREE   
------------------- ---------------- ---------
ORD_ITEM_RANGE_X01	ORD_ITEM_RANGE	4


SQL> select /*+ (ORD_ITEM_RANGE ORD_ITEM_RANGE_X01) */* from DEV.ORD_ITEM_RANGE
WHERE ORD_YM = '201201'
and ord_dt is not null
and ord_hms < '000000'


*************************[Explain Plan Time: 2022/05/03 10:56:53]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=10 Bytes=2K)
   1    0   PARTITION RANGE (SINGLE) (Cost=44 Card=10 Bytes=2K)
   2    1     TABLE ACCESS (BY LOCAL INDEX ROWID BATCHED) OF 'ORD_ITEM_RANGE' (TABLE) (Cost=44 Card=10 Bytes=2K)
   3    2       INDEX (SKIP SCAN) OF 'ORD_ITEM_RANGE_X01' (INDEX) (Cost=33 Card=10)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter("ORD_YM"='201201')
   3 - access("ORD_HMS"<'000000')
   3 - filter("ORD_HMS"<'000000' AND "ORD_DT" IS NOT NULL)
-----------------------------------------------------------

 

 

저와 비슷한 고민을 했던 커뮤니티의 글을 확인하였습니다.

https://community.oracle.com/tech/developers/discussion/4206429/how-to-check-and-disable-degree-of-parallelism-of-an-partitioned-index

 

여기의 답변중 베스트 답변을 보면

 I believe the default degree for a partitioned index is set at the object level and the degree specified on a rebuild operation only effects the index rebuild.  The last I knew the recommendation was to leave/set the default degree for all objects to one and let the CBO calculate the degree of parallelism based on the database parallel settings at query run time.

 

으로 parallel 옵션은 rebuild 작업에만 오직 영향을 미치고, 기본적으로 object level의 degree를 따라간다고 되어 있습니다.

 

공식 답변은 아니지만 저의 테스트 결과와 비슷한 답변이었습니다.