리오그 대상 테이블을 선정할 때 실제 Table에서 사용하고 있는 blocks를 계산하는 쿼리를 사용중인데요,
그냥 사용하기보다는 정리를 통해서 이해하고 사용하기 위해서 이번 글을 포스팅합니다.
1.DBMS_ROWID와 DBA_SEGMENTS로 확인
아래는 실제 사용하고 있는 block을 계산하는 쿼리입니다.
SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)||DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)) "USED"
FROM [테이블 명];
그리고 DBA_SEGMENTS 뷰를 조회하여 현재 테이블이 점유하고 있는 Block수도 구할 수 있습니다.
SELECT OWNER,SEGMENT_NAME, BLOCKS, bytes/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME IN (
[테이블 명]
)
실제 사용하고 있는 Block을 계산할때 사용하는 DBMS_ROWID 패키지에 대해서 알아보도록 하겠습니다.
ROWID를 생성하고 PL/SQL 프로그램 및 SQL문에서 ROWID에 대한 정보를 얻을 수 있습니다.
아래는 DBMS_ROWID 패키지에서 사용할 수 있는 subprogram입니다.
extended ROWID = Oracle 8.x 혹은 그 이상에서 사용하는 ROWID
restriced ROWID = Oracle 7.x까지 사용했던 ROWID
|
Subprogram |
Description |
|
ROWID의 블록번호를 반환 |
|
|
ROWID를 생성,(테스트를 위해서만 사용) |
|
|
ROWID의 유형과 구성정보를 반환 |
|
|
extended ROWID의 object 수를 반환 |
|
|
ROWID의 file 수를 반환 |
|
|
row 수를 반환 |
|
|
지정된 테이블의 row에 대한 ROWID에 연결된 절대값 파일 수를 반환 |
|
| restricted 포맷의 ROWID를 extended 포맷으로 변환 | |
|
extended 포맷의 ROWID를 restricted 포맷으로 변환 |
|
|
Returns the ROWID type: 0 is restricted, 1 is extended |
|
|
ROWID_TO_EXTENDED 함수를 사용하여 ROWID가 extended 될 수 있는지 여부를 확인 |
출처 : docs.oracle.com/database/121/ARPLS/d_rowid.htm#ARPLS67762
위의 패키지 설명을 토대로 함수를 사용해서 결과를 뽑아 본것입니다.
/* 오라클은 block 단위로 데이터를 읽고 씁니다. 따라서 각 rowid와 함수를 조합해서 사용시 같은 블록에 어떤 row값들이 있는지 확인할 수 있는 것입니다. 이것을 토대로 위의 실제 사용하고 있는 block수 SQL을 보면, fno(파일 수)와 bno(블록 수)를 distinct로 조회하여 실제 데이터들이 가지고 있는 block의 수를 구할 수 있는 것입니다. fno까지 붙여서 확인하는 이유는 데이터파일이 여러개 일 경우 블록 수는 동일(예를들어 363)인데 FNO가 다를수 있기 때문에 같이 붙여서 조회를 해야 정확한 블록 수를 구할 수 있습니다 */
2.DBMS_SPACE 사용하여 확인
Reorg 대상 테이블을 확인할 수 있는 방법이 하나 더 있습니다.
DBMS_SPACE 패키지를 사용하여 테이블의 사용량을 분석하는 것입니다.
SELECT * FROM TABLE(dbms_space.asa_recommendations())
where segment_name='테이블 명';
아래는 실제로 테이블명을 넣어서 패키지를 수행한 내용입니다.
컬럼 중 c1,c2,c3는 reorg 추천방법입니다.
SQL> SELECT * FROM TABLE(dbms_space.asa_recommendations())
where segment_name='MVALLPYRO';
TABLESPACE_NAME SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME ALLOCATED_S USED_SPACE RECLAIMABLE CHAIN_ROWEX RECOMMENDATIONS C1 C2 C3 TASK_ID MESG_ID
------------------------------ ------------------------------ ------------------------------ ------------------ ------------------------------ ----------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
DATATBS MPDB MVALLPYRO TABLE 1457520640 326289848 1131230792 0 Enable row movement of the table MPDB.MVALLPYRO and perform shrink, estimated savings is 1131230792 bytes. alter table "MPDB"."MVALLPYRO" shrink space alter table "MPDB"."MVALLPYRO" shrink space COMPACT alter table "MPDB"."MVALLPYRO" enable row movement 136931 36
NEWDATATBS NEWMPDB MVALLPYRO TABLE 1031798784 1019253045 12545739 0 Enable row movement of the table NEWMPDB.MVALLPYRO and perform shrink, estimated savings is 12545739 bytes. alter table "NEWMPDB"."MVALLPYRO" shrink space alter table "NEWMPDB"."MVALLPYRO" shrink space COMPACT alter table "NEWMPDB"."MVALLPYRO" enable row movement 136931 36
2 rows selected.
아래는 DBMS_SPACE의 subprogram입니다.
|
Subprogram |
Description |
|
시스템에 의해 자동으로 실행되거나 사용자가 수동으로 호출한 세그먼트 어드바이저의 권장사항을 반환 |
|
|
기존 테이블에 인덱스를 만드는 비용을 결정 |
|
|
다양한 속성이 주어진 테이블의 크기를 결정 |
|
|
테이블,인덱스,클러스터 같은 오브젝트의 사용가능한 블록에 대한 정보를 반환 |
|
|
데이터파일이 삭제 가능한지 확인 |
|
|
오브젝트와 관련된 세그먼트 목록을 반환 |
|
|
각 행이 특정 시점에서 오브젝트의 공간 사용량을 설명하는 함수 |
|
|
자동 세그먼트 공간관리(ASSM)에서 사용가능한 블록에 대한 정보를 반환 |
|
|
테이블,인덱스,클러스터 같은 오브젝트의 사용되지 않는 블록에 대한 정보를 반환 |
출처 : docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS056
저는 주로 첫번째방법으로 확인하여 블록크기를 구하고 reorg여부를 판단하고 있습니다.
'Oracle > 운영' 카테고리의 다른 글
| [RAC] 12.2 CRS Process 강제로 kill 하고 복구방법 (0) | 2021.02.08 |
|---|---|
| dba_tables 과 dba_segments의 차이 (0) | 2021.02.05 |
| 권한으로 다른유저의 Package body 확인하기 (2) | 2021.02.03 |
| DBA_SCHEDULER_JOB_LOG (0) | 2021.01.29 |
| [일일점검] 쿼리 및 Shell 파일 - Linux& Unix (0) | 2021.01.27 |
댓글