1
hoctinhoc
Guest
Lên kế hoạch kiểm tra Fragmentation và Rebuild Index Table trong database
Lên kế hoạch kiểm tra Fragmentation và Rebuild Index Table trong database
1. Kiểm Tra Fragmentation
//blogs.technet.com/b/sql_serve...-t-fix-it.aspx
//basitaalishan.com/2012/06/18/...rver-database/
//sqldbpool.com/2012/03/24/how-...in-sql-server/
2. Rebuild or Reorganize Index Table
//sqldbpool.com/2012/03/24/how-...ntenance-plan/
Rebbuild all table
//www.foliotek.com/devblog/sql-...ex-rebuilding/
--
Chạy sript sau để xác định xem Table nào cần Rebuild Index
DECLARE @page_count_minimum smallint DECLARE @fragmentation_minimum float
SET @page_count_minimum = 50 SET @fragmentation_minimum = 30
SELECT sys.objects.name AS Table_Name, sys.indexes.name AS Index_Name, avg_fragmentation_in_percent AS frag, page_count AS page_count, sys.dm_db_index_physical_stats.object_id AS objectid, sys.dm_db_index_physical_stats.index_id AS indexid, partition_number AS partitionnum FROM sys.dm_db_index_physical_stats [DB_ID[], NULL, NULL , NULL, 'LIMITED'] inner join sys.objects on sys.objects.object_id = sys.dm_db_index_physical_stats.object_id inner join sys.indexes on sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id and sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id WHERE avg_fragmentation_in_percent > @fragmentation_minimum AND sys.dm_db_index_physical_stats.index_id > 0 AND page_count > @page_count_minimum ORDER BY page_count DESC
SQL Server – Check Index Fragmentation on ALL Indexes in a Database
Mã:
SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats [DB_ID[], NULL, NULL, NULL, NULL] AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID[] ORDER BY indexstats.avg_fragmentation_in_percent desc
- 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;
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.tab1i1 REBUILD PARTITION partition data20210101 REBUILD parallel 8 nologging online;
ALTER INDEX binhtv.tab1i1 REBUILD PARTITION partition data20210102 REBUILD parallel 8 nologging online;
ALTER INDEX binhtv.tab1i1 REBUILD PARTITION partition data20210103 REBUILD parallel 8 nologging online;
....
ALTER INDEX binhtv.tab1i1 REBUILD noparallel;
Hy vọng hữu ích cho bạn.
@ Trần Văn Bình - Founder of "Oracle DBA Việt Nam"
OracleTutorial
OracleDBA
OracleDatabaseAdministration
học oracle database
oca
ocp
oce
ocm