Oracle效能最佳化之提升block的效率(轉)
建立本地管理的表空間;
合理設定segment的大小;
監控將要擴充套件的segment:
SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < .1;
2、high water mark
記錄在segment header block中,在segment建立的時候設定在segment的起始位置,當記錄被插入的時候以5個block的增量增加,truncate可以重設high water mark的位置,但delete不能。
在full table scan中,oracle會讀取high water mark以下的所有的資料塊,所以high water mark以上的塊也許會浪費儲存空間,但不會降低效能。
可以透過下列方法收回表中high water mark以上的塊:
Alter table_name deallocate unused;
對於high water mark以下的塊:
使用import/export工具:export資料;drop或truncate表;import資料。或者利用alter table tanle_name move命令去移動表的儲存位置(此時需要重建索引)。
3、表統計
用analyize命令生成表統計,然後到dba_table查詢相關資訊。
ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;
SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner ='NDLS' AND table_name='T_WH_SHIPPING_BILL';
4、block size
透過下列方法可以最小化block的訪問次數:
使用更大的block size;緊密壓縮行;阻止行映象。後兩者存在衝突,越多的行被壓縮在一個block裡,越容易產生映象。
Block size 在資料庫建立的時候設定,不能被輕易改變,是讀取資料檔案時最小的IO單元,大小範圍是2K-64K,應該設定成OS塊的整數倍,小於或等於OS IO時能讀取的儲存區域。
較小的block size的優點:極少block競爭;有利於較小的行和隨機訪問。缺點是存在相當高的成本,每個block的行數更少,可能需要讀取更多的index塊。
Block size的選擇影響系統的效能,在一個OLTP環境中,較小的block size更合適,而在DSS環境中,適宜選擇較大的block size。
5、PCTFREE、PCTUSED
1)PCTFREE、PCTUSED使你能控制一個segment裡所有資料塊裡free space的使用。
PCTFREE:一個資料塊保留的用於塊裡已有記錄的可能更新的自由空間佔block size的最小比例。
PCTUSED:在新記錄被插入block裡之前這個block可以用於儲存行資料和其他資訊的空間所佔的最小比率。
2)這兩個引數的使用
如果建立表的時候指定pctfree=20%,oracle會在這個表的data segment的每個block都保留20%的空間用於已有記錄的更新。Block的已使用空間上升到整個block size的80%時,這個block將移出free list;在提交了delete、update之後,oracle server處理這條語句並檢查對應block的已使用空間是否低於PCTUSED,如果是,則這個block放進free list。
3)PCTFREE、PCTUSED的設定
• PCTFREE
– Default 10
– Zero if no UPDATE activity
– PCTFREE = 100 × upd / (average row length)
• PCTUSED
– Default 40
– Set if rows deleted
– PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize
其中,upd : the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length;
average row length:在執行了analyize命令之後,這個值可以從dba_tables中的avg_row_len列中獲得。
rows : the number of rows to be deleted before free list maintenance occurs。
4)Delete、update可以增加block的自由空間,但是釋放出來的空間有可能是不連續的,oracle在下列情況下會對碎片進行整理:一個block有足夠的自由空間容納row piece,但是由於每個碎片都較小以至這個row piece不能存放在一個連續的section中。
6、Migration和Chaining
1)如果一行的資料太大以至一個單獨的block容納不下,會產生兩種現象:
A、Chaining:行資料太大以至一個空block容納不下,oracle會將這一行的資料存放在一個或多個block 組成的block chain中,insert、update都可能導致這個問題,在某些情況下row chaining是不能避免的。
B、Migration:一次update操作可能導致行資料增大,以至它所在的block容納不下,oracle server會去尋找一個有足夠自由空間容納整行資料的block,如果這樣的block存在,oracle server把整行移到新的block,在原位置儲存一個指向新存放位置的映象行,映象行的rowid和原來的rowid一致。
Chaining、Migration的弊端:insert、update的效能降低,索引查詢增加了IO次數。
2)檢測migration和chaining:
Analyize table table_name compute statistics;
Select num_rows,chain_cnt from dba_tables where table_name=’...’;
查詢映象行:
Analyize table table_name list chained rows;
Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
產生Migration的原因可能是由於PCTFREE設定的太低以至沒有保留足夠的空間用於更新。
可以透過增加PCTFREE的值避免行映象產生。
3)消除映象行的步驟:
執行analyize table ... list chained rows;
複製映象行到另一個表tmp;
從源表中刪除這些行;
從tmp中將這些行插回到源表中。
指令碼:
/* Get the name of the table with migrated rows */
accept table_name prompt ’Enter the name of the table with migrated rows: ’
/* Clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* List the chained & migrated rows */
analyze table &table_name list chained rows;
/* Copy the chained/migrated rows to another table */
create table migrated_rows as
select orig.* from &table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper(’&table_name’);
/* Delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in ( select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
insert into &table_name select * from migrated_rows;
spool off
使用這個指令碼時,必須將涉及到的外來鍵約束去掉。
7、索引重組
在一個不穩定的表上建索引會影響效能,一個索引block只有完全空時才能進入free list,即使一個索引block裡只含有一個條目,它也必須被維護,因此索引需要進行階段性的重建。
1)檢查索引是否需要重組
A、收集一個index的使用統計
ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
B、檢視收集的統計資料
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;
C、如果浪費超過20%則索引需要重建
ALTER INDEX acct_no_idx REBUILD;
D、或者對索引進行整理
Alter index acct_no_idx coalesce;
2)標記未使用的索引
A、 開始監測索引的使用
Alter index hr.emp_name_ix monitoring usage;
B、 停止監測索引的使用
Alter index hr.emp_name_ix nomonitoring usage;
C、 查詢索引的使用情況
Select index_name,used from v$object_usage;
刪除未使用過的索引,可以降低DML操作的成本,從而提升系統效能。
為了儘可能經濟的利用block,應對存在較多空block、映象行的表進行重建,對建立不穩定表上的索引應有規律的進行重建,並儘可能建立本地管理的表空間。
[@more@]來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12180666/viewspace-1021786/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle效能最佳化之SQL最佳化(轉)OracleSQL
- Oracle效能最佳化之LockContention(轉)Oracle
- Oracle效能最佳化之應用最佳化(轉)Oracle
- Oracle效能提升策略 (轉)Oracle
- Oracle效能最佳化之Rollback(undo)Segment最佳化(轉)Oracle
- RMAN備份效率之-oracle block internal(block 內部結構分解)OracleBloC
- 【MySQL】MySQL效能最佳化之Block Nested-Loop Join(BNL)MySqlBloCOOP
- Oracle效能最佳化之診斷latch競爭(轉)Oracle
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- Oracle效能最佳化 之 共享池Oracle
- 效能基準DevOps之如何提升指令碼執行效率dev指令碼
- Oracle效能最佳化之最佳化排序操作(轉)Oracle排序
- Oracle效能最佳化 之 庫快取Oracle快取
- Oracle效能最佳化之虛擬索引Oracle索引
- ORACLE SQL效能最佳化系列 (十) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (十一) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (一) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (二) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (三) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (四) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (九) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (五) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (八) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (六) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (七) (轉)OracleSQL
- ORACLE SQL效能最佳化系列 (十二) (轉)OracleSQL
- 開發效率提升之工具篇
- Oracle效能最佳化經驗分享之系統引數設定(轉)Oracle
- oracle 效能最佳化Oracle
- Analyze table對Oracle效能的提升Oracle
- Oracle 效能最佳化之核心的shmall 和shmmax 引數OracleHMM
- 使用智慧最佳化器提高Oracle的效能極限 (轉)Oracle
- Oracle資料庫效能最佳化技術(轉)Oracle資料庫
- 轉換思維提升效率的學習方法
- C#正規表示式效能最佳化:[0-9] vs. d,輕鬆提升匹配效率C#
- Magnet:最佳化你的Mac視窗布局,提升工作效率Mac
- App Cleaner & Uninstaller:全面最佳化Mac,提升執行效率APPMac
- 【效能最佳化】ORACLE資料庫效能最佳化概述Oracle資料庫