ORACLE在邏輯存儲上分4個粒度:表空間,段,區(qū)和塊.
- 塊:是粒度最小的存儲單位,現(xiàn)在標(biāo)準(zhǔn)的塊大小是8K,ORACLE每一次I/O操作也是按塊來操作的,也就是說當(dāng)ORACLE從數(shù)據(jù)文件讀數(shù)據(jù)時,是讀取多少個塊,而不是多少行.
- 區(qū):由一系列相鄰的塊而組成,這也是ORACLE空間分配的基本單位,舉個例子來說,當(dāng)我們創(chuàng)建一個表PM_USER時,首先ORACLE會分配一區(qū)的空間給這個表,隨著不斷的INSERT數(shù)據(jù)到PM_USER,原來的這個區(qū)容不下插入的數(shù)據(jù)時,ORACLE是以區(qū)為單位進(jìn)行擴(kuò)展的,也就是說再分配多少個區(qū)給PM_USER,而不是多少個塊.
- 段:是由一系列的區(qū)所組成,一般來說,當(dāng)創(chuàng)建一個對象時(表,索引),就會分配一個段給這個對象.所以從某種意義上來說,段就是某種特定的數(shù)據(jù).如CREATE TABLE PM_USER,這個段就是數(shù)據(jù)段,而CREATE INDEX ON PM_USER(NAME),ORACLE同樣會分配一個段給這個索引,但這是一個索引段了.查詢段的信息可以通過數(shù)據(jù)字典: SELECT * FROM USER_SEGMENTS來獲得,
- 表空間:包含段,區(qū)及塊.表空間的數(shù)據(jù)物理上儲存在其所在的數(shù)據(jù)文件中.一個數(shù)據(jù)庫至少要有一個表空間.
確定碎片程度
SELECT table_name,
ROUND ( (blocks * 8), 2) "高水位空間 k",
ROUND ( (num_rows * avg_row_len / 1024), 2) "真實(shí)使用空間 k",
ROUND ( (blocks * 10 / 100) * 8, 2) "預(yù)留空間(pctfree) k",
ROUND (
( blocks * 8
- (num_rows * avg_row_len / 1024)
- blocks * 8 * 10 / 100),
2)
"浪費(fèi)空間 k"
FROM user_tables
WHERE temporary = 'N'
ORDER BY 5 DESC;
查看表上次收集統(tǒng)計信息時間
select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME';
- 整理表，不影響DML操作
SQL> alter table TABLE_NAME enable ROW MOVEMENT;--啟動行移動功能
SQL> alter table TABLE_NAME shrink space compact; --只整理碎片 不回收空間
SQL> alter table TABLE_NAME disable ROW MOVEMENT;--關(guān)閉行移動
如果要同時壓縮表的索引,可以發(fā)布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
- 重置高水位喻粹，此時不能有DML操作
SQL> alter table TABLE_NAME enable ROW MOVEMENT;--啟動行移動功能
SQL> alter table TABLE_NAME shrink space; --整理碎片并回收空間，并調(diào)整水位線舌稀。業(yè)務(wù)少時執(zhí)行
SQL> alter table TABLE_NAME disable ROW MOVEMENT;--關(guān)閉行移動
- shrink 的優(yōu)勢：
不需要重建索引。
可以在線操作
釋放表的高水位通常有如下幾種辦法：
- 對表進(jìn)行MOVE操作：ALTER TABLE TABLE_NAME MOVE;卧檐。若表上存在索引杈女，則記得重建索引。
- 對表進(jìn)行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;呈昔，注意，在執(zhí)行該指令之前必須開啟行移動：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;友绝。該方法的優(yōu)點(diǎn)是：在碎片整理結(jié)束后堤尾，表上相關(guān)的索引仍然有效，缺點(diǎn)是會產(chǎn)生大量的UNDO和REDO迁客。
- 復(fù)制要保留的數(shù)據(jù)到臨時表T郭宝，DROP原表，然后RENAME臨時表T為原表掷漱。
- exp/imp或expdp/impdp重構(gòu)表粘室。
- 若表中沒有數(shù)據(jù)則直接使用TRUNCATE來釋放高水位。
如何找出系統(tǒng)中哪些表擁有高水位呢卜范？
- 比較表的行數(shù)和表的大小關(guān)系衔统。如果行數(shù)為0，而表的當(dāng)前占用大小減去初始化時的大泻Ｑ（INITIAL_EXTENT）后依然很大缰冤，那么說明該表有高水位。
- 行數(shù)和塊數(shù)的比率喳魏，即查看一個塊可以存儲多少行數(shù)據(jù)。如果一個塊存儲的行數(shù)少于5行甚至更少怀薛，那么說明有高水位刺彩。
注意：
這兩種方法都不是十分準(zhǔn)確，需要再對查詢結(jié)果進(jìn)行篩選枝恋。需要注意的是创倔，在查詢表的高水位時，首先需要分析表焚碌，以得到最準(zhǔn)確的統(tǒng)計信息畦攘。
SELECT OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
GREATEST(NVL(HWM, 1), 1)),
2),
0) WASTE_PER
FROM (SELECT A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
B.LAST_ANALYZED,
A.BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
0),
0,
1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS *
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
0)) + 2 AVG_USED_BLOCKS,
ROUND(100 *
(NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
2) CHAIN_PER,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
WHERE A.OWNER = B.OWNER
AND SEGMENT_NAME = TABLE_NAME
AND SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME)
WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
GREATEST(NVL(HWM, 1), 1)),
2),
0) > 50
AND OWNER NOT LIKE '%SYS%'
AND BLOCKS > 100
ORDER BY WASTE_PER DESC;
SELECT D.OWNER,
ROUND(D.NUM_ROWS / D.BLOCKS, 2),
D.NUM_ROWS,
D.BLOCKS,
D.TABLE_NAME,
ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER NOT LIKE '%SYS%' ;
