728x90
Oracle에서 "index REBUILD"는 기존 인덱스를 다시 생성하는 작업입니다. Oracle Database에서 인덱스는 테이블의 데이터를 빠르게 조회할 수 있도록 하는 구조인데, 인덱스가 지속적으로 업데이트되거나 삭제, 삽입 작업이 많아지면 조각화(Fragmentation)가 발생할 수 있습니다. 이러한 조각화는 인덱스 성능을 저하시킬 수 있으므로, 인덱스를 재구성하는 과정인 REBUILD를 통해 인덱스를 최적화할 수 있습니다.
주요 특징과 효과
- 조각화 해결: 조각화된 인덱스를 재구성하여 성능을 향상시킵니다.
- 디스크 공간 최적화: 불필요한 공간을 정리하여 디스크 사용 효율을 높입니다.
- 인덱스 구조 재구성: 새로운 블록을 할당하고 데이터를 재정렬하여 접근 속도를 개선합니다.
사용 방법
ALTER INDEX "INDEX_NAME" REBUILD;
ALTER INDEX "INDEX_NAME" REBUILD ONLINE; --> DML 허용
ALTER INDEX your_index_name REBUILD ONLINE COMPUTE STATISTICS; --> DML허용, 통계수집
index_name 자리에 재구성하고자 하는 인덱스 이름을 넣으면 됩니다. 이 명령은 인덱스를 새롭게 생성하는 것과 비슷하지만, 기존의 인덱스와 동일한 설정을 유지하며 빠르게 재구성합니다.
옵션
- REBUILD ONLINE: 온라인으로 작업을 수행하여 인덱스를 사용하는 동안에도 테이블에 액세스할 수 있도록 합니다.
- TABLESPACE 지정: 특정 테이블스페이스에 인덱스를 재구성할 수 있습니다.
인덱스를 REBUILD하는 작업은 시스템 리소스를 사용할 수 있으므로, 시스템에 큰 부하를 주지 않는 시간에 실행하는 것이 좋습니다.
1. Index Offline Rebuild
- Rebuild 가 이뤄지는 동안 DML Blocking
- Rebuild 가 이뤄지는 동안 추가 적인 공간 필요 ( 기존 Index + 신규 Index )
- Sparse가 높고 크기가 큰 Index 에 대한 Rebuild 효과가 가장 우수
* Sparse Index 란 인덱스 전반에 걸친 밀도가 낮은 인덱스를 말한다.
2. Index Online Rebuild
- PK, UK는 제약 조건으로 인해 인덱스 리빌드에서 제외됨.
- Rebuild가 이뤄지는 동안 DML 허용 ( 맨처음과 맨끝에서의 짧은 시간의 Block 은 발생할 수 있음)
- Rebuild 가 이뤄지는 동안 추가 적인 공간 필요 ( 기존 Index + 신규 Index )
- Offline Rebuild에 비해 시간은 오래 걸릴 수 있다. ( Table Full Scan, Journal Table(IOT), Merge 등을 수행 )
3. Rebuild가 필요한, Sparse Index 찾기
select /* ordered */ u.name Owner ,
o.name "Index",
op.subname Partition,
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, o.subname, '') SubPartition,
(1- floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) -
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt , ip.obj#, ip.rowcnt , i.rowcnt )*
(sum(h.avgcln)+10) /((p.value-66- decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans )*24) *
(1-decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)/100)) )/
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)) Density,
floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) -
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt , ip.obj#, ip.rowcnt , i.rowcnt ) *
(sum(h.avgcln) + 10) /((p.value-66- decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans )*24) *
(1-decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)/100))) "Extra.Block"
from sys.ind$ i,
sys.icol$ ic,
(select obj#,
part#,
bo#,
ts#,
rowcnt,
leafcnt,
initrans,
pctfree$,
analyzetime,
flags
from sys.indpart$
union all
select obj#,
part#,
bo#,
defts#,
rowcnt,
leafcnt,
definitrans,
defpctfree,
analyzetime,
flags
from sys.indcompart$ ) ip,
sys.indsubpart$ isp,
(select ts#,
blocksize value
from sys.ts$ ) p,
sys.hist_head$ h,
sys.obj$ o,
sys.user$ u,
sys.obj$ op
where i.obj# = ip.bo#(+)
and ip.obj# = isp.pobj#(+)
and decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) > 1
and i.type# in (1) /* exclude special types */
and i.pctthres$ is null /* exclude IOT secondary indexes */
and decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts#
and ic.obj# = i.obj#
and h.obj# = i.bo#
and h.intcol# = ic.intcol#
and o.obj# = nvl(isp.obj#, nvl(ip.obj#, i.obj#))
and o.owner# != 0
and u.user# = o.owner#
and op.obj# = nvl(ip.obj#, i.obj#)
and u.name not in ('SYS',
'SYSTEM',
'SYSMAN',
'XDB',
'HR',
'ODM',
'OUTLN',
'OE',
'SH',
'PM',
'SYSAUX',
'IX',
'WK_T EST',
'PERFSTAT',
'DBSNMP',
'OLAPSYS',
'QS_CS',
'QS_CB',
'QS_CBADM',
'QS_OS',
'QS_WS',
'QS',
'QS_ADM',
'ODM_MTR' ,
'WKPROXY',
'QS_ES',
'ANONYMOUS',
'WKSYS',
'WMSYS',
'A PEX_ 030200',
'APEX_PUBLIC_USER',
'APPQOSSYS',
'BI',
'CTXSYS',
'DIP',
'EXFSYS',
'FLOWS_FILES' ,
'MDDATA',
'MDSYS',
'MGMT_VIEW',
'ORACLE_OCM',
'ORDDATA',
'ORDPLUGINS',
'ORDS YS',
'OWBSYS',
'OWBSYS_AUDIT',
'SCOTT',
'SI_INFORMTN_SCHEMA',
'SPATIAL_CSW_AD MIN_USR',
'SPATIAL_WFS_ADMIN_USR',
'XS$NULL')
group by u.name,
o.name,
op.subname,
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, o.subname, ''),
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt , ip.obj#, ip.rowcnt , i.rowcnt ),
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt),
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans ),
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$),
p.value
having (1- floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) -
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt , ip.obj#, ip.rowcnt , i.rowcnt ) *
(sum(h.avgcln)+10) /((p.value-66- decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans )*24) *
(1-decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)/100)) ) /
decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) ) <= 0.75
-- OR floor(decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt) -
-- decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt) *(sum(h.avgcln) + 10) /
-- ((p.value-66- decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans )*24) *
-- (1-decode(nvl(isp.obj#, nvl(ip.obj#, i.obj#)), isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)/100)) ) > 1000
order by 6 desc, 5;728x90
반응형
'Oracle > Admin' 카테고리의 다른 글
| [Oracle] ORA-01804 : this index object is being online .. (2) | 2024.11.20 |
|---|---|
| [ORACLE] RMAN BAKCUP (0) | 2024.11.15 |
| [Oracle] ASH, AWR (0) | 2024.11.04 |
| [Oracle] Wait Event 목록 (0) | 2024.10.31 |
| [Oracle] single grid 환경에서의 oracle 재기동 방법 (0) | 2024.09.19 |
