Đúng là chúng ta không nên đặt scheduled rebuild định kỳ, nhưng đối với database OLTP, thi thoảng cần phải rebuild index. Còn thời điểm nào thì rebuild, chúng ta sẽ xem xét các yếu tố sau:
- Thứ nhất, mọi người có thể hình dung ra cách lưu trữ theo mô hình cây của index. Các thành phần của cây bao gồm root(gốc), branch(cành) và leaf(lá).
+ Khi tìm kiếm, oracle sẽ đi từ gốc->cành->lá.
+ Nếu cây chỉ có 1 cành là đến lá hoặc từ gốc mà đến lá luôn thì việc tìm kiếm càng nhanh. Do vậy, mới có khái niệm B-Level (hay Height) của index (Height=BLevel+1). Lấy 1 VD, index có height=3, tức là gốc->cành->lá, khi đó để tìm đến lá cần tìm oracle chỉ cần load 3 block (root block, 01 branch block, 01 leaf block). Như vậy, nếu Height càng nhỏ thì tìm kiếm trên index càng nhanh.
- Thứ 2, index block không có PCTUSED, chính xác và có thể coi là PCTUSED=0. Vì vậy nên khả năng có các index block chỉ chứa 1-2 rows mà không cho chúng ta lưu tiếp vào là rất lớn. Những index block này được gọi là deleted leaf. Như vậy, nếu một index mà có số deleted leaf lớn thì chứng tỏ đã có rất nhiều lệnh delete và update (trên index column), điều này có thể dẫn đến cây không còn cân bằng.
Thông thường, những index có Height > 4 (hay BLevel>3) hoặc có số deleted leaf lớn hơn 20% tổng số leaf trong index thì cần được rebuild (theo khuyến cáo của Oracle).
Nhưng để biết index nào cần được rebuild ta làm như sau:
SQL> Analyze index binhtv.tab1_i1 validate structure;
(SOS: Với DB lớn cẩn thận chạy câu này sẽ bị treo DB 😲😲😲 do đó khi chạy cần giám sát liên tục, kill đi nếu cao tải, lock), ta cần monitor bằng câu lệnh:
SELECT /*1.ActiveSession*/ distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE
S.STATUS = 'ACTIVE' AND
S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
and s.username NOT in ('SYSMAN','DBSNMP','GGATE','GOLDENGATE')
--AND username in 'SYS'
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;
Nếu Event lock nhiều, active tăng nguy cơ gây tăng tải DB thì cần làm vào giờ thấp điểm, ban đêm(sau 23h, có thể phải dừng ứng dụng):
-- Select những index có height>4 và tỉ lệ số deleted leaf > 20%
SELECT name,height,(del_lf_rows/lf_rows)*100 as del_ratio
FROM INDEX_STATS
WHERE height > 4
OR (del_lf_rows/lf_rows)*100 > 20;
FROM INDEX_STATS
WHERE height > 4
OR (del_lf_rows/lf_rows)*100 > 20;
Câu lệnh rebuild index:
ALTER INDEX binhtv.tab1_i1 REBUILD parallel 8 nologging ;
ALTER INDEX binhtv.tab1_i1 REBUILD parallel 8 nologging online;
ALTER INDEX binhtv.tab1_i1 REBUILD noparallel;
hoặc nếu index partition (index local) thì rebuild từng partition 1 theo cú pháp:
ALTER INDEX binhtv.tab1_i1 REBUILD PARTITION partition data20210101 REBUILD parallel 8 nologging online;ALTER INDEX binhtv.tab1_i1 REBUILD PARTITION partition data20210102 REBUILD parallel 8 nologging online;ALTER INDEX binhtv.tab1_i1 REBUILD PARTITION partition data20210103 REBUILD parallel 8 nologging online;....ALTER INDEX binhtv.tab1_i1 REBUILD noparallel;
Hy vọng hữu ích cho bạn.
