ORACLE在邏輯存儲(chǔ)上分4個(gè)粒度:表空間,段,區(qū)和塊.
- 塊:是粒度最小的存儲(chǔ)單位,現(xiàn)在標(biāo)準(zhǔn)的塊大小是8K,ORACLE每一次I/O操作也是按塊來操作的,也就是說當(dāng)ORACLE從數(shù)據(jù)文件讀數(shù)據(jù)時(shí),是讀取多少個(gè)塊,而不是多少行.
- 區(qū):由一系列相鄰的塊而組成,這也是ORACLE空間分配的基本單位,舉個(gè)例子來說,當(dāng)我們創(chuàng)建一個(gè)表PM_USER時(shí),首先ORACLE會(huì)分配一區(qū)的空間給這個(gè)表,隨著不斷的INSERT數(shù)據(jù)到PM_USER,原來的這個(gè)區(qū)容不下插入的數(shù)據(jù)時(shí),ORACLE是以區(qū)為單位進(jìn)行擴(kuò)展的,也就是說再分配多少個(gè)區(qū)給PM_USER,而不是多少個(gè)塊.
- 段:是由一系列的區(qū)所組成,一般來說,當(dāng)創(chuàng)建一個(gè)對(duì)象時(shí)(表,索引),就會(huì)分配一個(gè)段給這個(gè)對(duì)象.所以從某種意義上來說,段就是某種特定的數(shù)據(jù).如CREATE TABLE PM_USER,這個(gè)段就是數(shù)據(jù)段,而CREATE INDEX ON PM_USER(NAME),ORACLE同樣會(huì)分配一個(gè)段給這個(gè)索引,但這是一個(gè)索引段了.查詢段的信息可以通過數(shù)據(jù)字典: SELECT * FROM USER_SEGMENTS來獲得,
- 表空間:包含段,區(qū)及塊.表空間的數(shù)據(jù)物理上儲(chǔ)存在其所在的數(shù)據(jù)文件中.一個(gè)數(shù)據(jù)庫至少要有一個(gè)表空間.
確定碎片程度
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)計(jì)信息時(shí)間
select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME';
- 整理表，不影響DML操作
SQL> alter table TABLE_NAME enable ROW MOVEMENT;--啟動(dòng)行移動(dòng)功能
SQL> alter table TABLE_NAME shrink space compact; --只整理碎片 不回收空間
SQL> alter table TABLE_NAME disable ROW MOVEMENT;--關(guān)閉行移動(dòng)
如果要同時(shí)壓縮表的索引,可以發(fā)布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
- 重置高水位，此時(shí)不能有DML操作
SQL> alter table TABLE_NAME enable ROW MOVEMENT;--啟動(dòng)行移動(dòng)功能
SQL> alter table TABLE_NAME shrink space; --整理碎片并回收空間，并調(diào)整水位線。業(yè)務(wù)少時(shí)執(zhí)行
SQL> alter table TABLE_NAME disable ROW MOVEMENT;--關(guān)閉行移動(dòng)
- shrink 的優(yōu)勢(shì)：
不需要重建索引。
可以在線操作
釋放表的高水位通常有如下幾種辦法：
- 對(duì)表進(jìn)行MOVE操作：ALTER TABLE TABLE_NAME MOVE;。若表上存在索引，則記得重建索引。
- 對(duì)表進(jìn)行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;，注意，在執(zhí)行該指令之前必須開啟行移動(dòng)：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;。該方法的優(yōu)點(diǎn)是：在碎片整理結(jié)束后，表上相關(guān)的索引仍然有效，缺點(diǎn)是會(huì)產(chǎn)生大量的UNDO和REDO。
- 復(fù)制要保留的數(shù)據(jù)到臨時(shí)表T，DROP原表，然后RENAME臨時(shí)表T為原表。
- exp/imp或expdp/impdp重構(gòu)表。
- 若表中沒有數(shù)據(jù)則直接使用TRUNCATE來釋放高水位。
如何找出系統(tǒng)中哪些表擁有高水位呢？
- 比較表的行數(shù)和表的大小關(guān)系。如果行數(shù)為0，而表的當(dāng)前占用大小減去初始化時(shí)的大?。↖NITIAL_EXTENT）后依然很大，那么說明該表有高水位。
- 行數(shù)和塊數(shù)的比率，即查看一個(gè)塊可以存儲(chǔ)多少行數(shù)據(jù)。如果一個(gè)塊存儲(chǔ)的行數(shù)少于5行甚至更少，那么說明有高水位。
注意：
這兩種方法都不是十分準(zhǔn)確，需要再對(duì)查詢結(jié)果進(jìn)行篩選。需要注意的是，在查詢表的高水位時(shí)，首先需要分析表，以得到最準(zhǔn)確的統(tǒng)計(jì)信息。
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%' ;
