1、 調(diào)整概述 調(diào)整Oracle的內(nèi)存使用 調(diào)整Oracle的磁盤利用 調(diào)整數(shù)據(jù)庫的應(yīng)用數(shù)據(jù)庫性能調(diào)整 數(shù)據(jù)庫配置和I/O問題 有效利用Oracle數(shù)據(jù)塊 調(diào)整排序操作 調(diào)整回滾段調(diào)整Oracle的磁盤利用不同類型Oracle文件的I/O統(tǒng)計ProcessCKPTDBWnLGWRARCnSERVEROracle File I/OData Files Log Archive Control Write Write Write Write Read Write Read/write Read表空間的利用僅為系統(tǒng)數(shù)據(jù)字典對象保留SYSTEM表空間創(chuàng)建本地管理的表空間，避免空間管理的問題發(fā)生將表和索引放在

2、不同的表空間中存放創(chuàng)建單獨的回滾表空間在各自的表空間中存儲非常大的數(shù)據(jù)庫對象建立一個或多個臨時表空間在不同的設(shè)備上分布文件數(shù)據(jù)文件與重做日志文件分開存放表數(shù)據(jù)劃分減少磁盤的I/O評價裸設(shè)備的使用Oracle文件的劃分操作系統(tǒng)劃分:使用操作系統(tǒng)劃分軟件或RAID 確定合適的劃分大小手工劃分:使用CREATE TABLE或ALTER TABLE ALLOCATE命令并行查詢值得利用調(diào)整全表掃描操作了解全表掃描的需求指定初始化參數(shù)DB_FILE_MULTIBLOCK_READ_COUNT： 確定服務(wù)器進(jìn)程一次讀取數(shù)據(jù)塊的個數(shù) 對cost-based優(yōu)化器的執(zhí)行計劃有影響使用V$SESSION_LON

3、GOPS視圖來監(jiān)測長時間運行的全表掃描操作檢查I/O統(tǒng)計的診斷工具V$FILESTATV$DATAFILEData files report.txtOracle I/O 利用利用System I/O 利用利用Performancetools I/O統(tǒng)計 SQL Rem I/O should be spread evenly across drives. A big difference between phys_reads and phys_blks_rd implies table scans are going on.SQL select table_space, file_name, p

4、hys_reads reads, phys_blks_rd 2 blks_read, phys_rd_time read_time, phys_writes writes, phys_blks_wr 3 blks_wrt, phys_wrt_tim write_time 4 from stats$files order by table_space, file_name;TABLE_SPACE FILE_NAME READS BLKS_ READ_ WRITES BLKS_ WRITE_ READ TIME WRT TIME- - - - - - - -RBS /DISK2/rbs01.dbf

5、 26 26 50 257 257 411SCOTT_DATA /DISK4/scott_dat.dbf 65012 416752 38420 564 564 8860SCOTT_INDEX /DISK4/scott_ind.dbf 8 8 0 8 8 0SYSTEM /DISK1/sys01.dbf 806 1538 1985 116 116 1721TEMP /DISK1/temp01.dbf 168 666 483 675 675 0USER_DATA /DISK3/user01.dbf 8 8 0 8 8 06 rows selected.Redo Log 組和成員MemberMemb

6、erMemberMember LGWR聯(lián)機重做日志文件的配置可以進(jìn)行如下配置： 適當(dāng)設(shè)置重做日志文件的大小，減少沖突 設(shè)置足夠的重做日志組，防止等待 重做日志文件單獨存放，并最好是快速設(shè)備 可以查詢動態(tài)性能視圖V$LOGFILE和V$LOG歸檔日志文件的配置允許LGWR進(jìn)程寫重做日志到與ARCn進(jìn)程正在讀的不同磁盤上共享歸檔作業(yè)：改變歸檔速度：LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_n, (LOG_ARCHIVE_DUPLEX_DEST, LOG_ARCHIVE_MIN_SUCCEED_DEST)ALTER SYSTEM ARCHIVE LOG

7、ALL TO 診斷工具 V$ARCHIVE_DESTV$ARCHIVED_LOGV$ARCHIVE_PROCESSESArchived logsLOG_ARCHIVE_DEST_STATE_n檢查點 檢查點導(dǎo)致： DBWn進(jìn)程執(zhí)行I/O CKPT進(jìn)程更新數(shù)據(jù)文件頭和控制文件檢查點頻繁發(fā)生： 減少實例恢復(fù)的時間 降低運行時間的性能原則適當(dāng)設(shè)置聯(lián)機重做日志文件的大小，減少檢查點的發(fā)生次數(shù)增加聯(lián)機重做日志組來提高LGWR進(jìn)程開始重寫的時間與檢查點控制有關(guān)的初始化參數(shù)： FAST_START_IO_TARGET LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOU

8、T DB_BLOCK_MAX_DIRTY_TARGET調(diào)整DBWn的I/O影響DBWn進(jìn)程更經(jīng)常地寫臟緩沖區(qū)的參數(shù)是DB_BLOCK_MAX_DIRTY_TARGET如果臟緩沖區(qū)的個數(shù)低于計算的低限，DBWn進(jìn)程不寫檢查點緩沖區(qū)如果臟緩沖區(qū)的個數(shù)介于計算的低限和高限之間，DBWn進(jìn)程從檢查點隊列中寫，直至檢查點緩沖區(qū)的個數(shù)低于計算的低限如果臟緩沖區(qū)的個數(shù)高于計算的高限，DBWn進(jìn)程則寫檢查點緩沖區(qū)缺省值為(2*32)-1Tablespace數(shù)據(jù)庫存儲的層次結(jié)構(gòu)SegmentsExtentsExtentsExtents分配一個范圍TablespaceSegmentsExtentsExtentsF

9、ullextentsFreespaceIncrementalextent避免動態(tài)分配SQL SELECT owner, table_name, blocks, empty_blocks 2 FROM dba_tables 3 WHERE empty_blocks / (blocks+empty_blocks) ALTER TABLE hr.emp ALLOCATE EXTENT;Table altered.避免動態(tài)分配建立本地管理的表空間:CREATE TABLESPACE user_data_1DATAFILE oracle8/oradata/db1/lm_1.dbfSIZE 100MEXT

10、ENT MANAGEMENT LOCAL UNIFORM SIZE 2M;大范圍的優(yōu)缺點優(yōu)點： 較少地動態(tài)擴展 帶來一些性能方面的好處 可以克服操作系統(tǒng)關(guān)于文件大小的限制缺點： 空閑空間可能減少 未使用的空間 數(shù)據(jù)庫配置和I/O問題 有效利用Oracle數(shù)據(jù)塊 調(diào)整排序操作 調(diào)整回滾段調(diào)整Oracle的磁盤利用數(shù)據(jù)塊的大小TablespaceSegmentsExtentsExtentsExtentsDB_BLOCK_SIZE當(dāng)數(shù)據(jù)庫創(chuàng)建時設(shè)置是數(shù)據(jù)文件讀寫的最小I/O單位缺省為2KB或4KB，允許到64KB不容易改變應(yīng)當(dāng)是操作系統(tǒng)塊大小的倍數(shù)操作系統(tǒng)I/O大小大于或等于DB_BLOCK_SIZ

11、E小數(shù)據(jù)塊的優(yōu)缺點優(yōu)點： 減少數(shù)據(jù)塊的沖突 適合于小的行 適合于隨機訪問缺點： 相對來說，開銷較大 每個數(shù)據(jù)塊容納較少的行 可能會導(dǎo)致讀取更多的索引塊大數(shù)據(jù)塊的優(yōu)缺點優(yōu)點： 較小的開銷 適合于非常大的行 適合于順序訪問 利用索引讀取數(shù)據(jù)會有更好的性能缺點： 數(shù)據(jù)塊沖突增加 占用buffer cache更多的空間PCTFREE和PCTUSED1234原則PCTFREE 缺省為10 如果沒有UPDATE操作，可以設(shè)置為0 PCTFREE = 100 x upd / (upd + ins)PCTUSED 缺省為40 如果有刪除的行可以設(shè)置 PCTUSED = 100 - PCTFREE - 100

12、x rows x (ins + upd) / blocksize Migration和Chaining檢測遷移和鏈接使用使用ANALYZE命令檢測遷移的和鏈接的行命令檢測遷移的和鏈接的行:Statistic Total Per transaction . - - - .table fetch continued row 495 .02 從從report.txt的輸出的輸出檢測遷移的和鏈接的行檢測遷移的和鏈接的行:SQL ANALYZE TABLE sales.order_hist COMPUTE STATISTICS;Table analyzed.SQL SELECT num_rows, ch

13、ain_cnt FROM dba_tables 2 WHERE table_name=ORDER_HIST; NUM_ROWS CHAIN_CNT- - 168 102選擇遷移的行SQL ANALYZE TABLE sales.order_hist LIST CHAINED ROWS;Table analyzed.SQL SELECT owner_name, table_name, head_rowid 2 FROM chained_rows 3 WHERE table_name = ORDER_HIST;OWNER_NAME TABLE_NAME HEAD_ROWID - - -SALES

14、ORDER_HIST AAAAluAAHAAAAA1AAASALES ORDER_HIST AAAAluAAHAAAAA1AAB.消除遷移的行1. 運行ANALYZE TABLE . LIST CHAINED ROWS命令2. 將有關(guān)的行從原表復(fù)制到另外的臨時表中3. 刪除原表中的有關(guān)行4. 從另外的臨時表將有關(guān)行插入原表中說明：第4步即可以消除遷移的行，因為遷移行的產(chǎn)生有由于UPDATE語句造成的，而非INSERT語句高水位標(biāo)志記錄在段頭塊中在創(chuàng)建時，設(shè)置在段的開始處在插入行時，以5個數(shù)據(jù)塊為單位增長由TRUNCATE命令復(fù)位，而不是DELETE命令表的統(tǒng)計SQL ANALYZE TABLE

15、 hr.emp COMPUTE STATISTICS;Table analyzed.SQL SELECT num_rows, blocks, empty_blocks as empty, 2 avg_space, chain_cnt, avg_row_len 3 FROM dba_tables 4 WHERE owner = HR 5 AND table_name = EMP;NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN- - - - - - 13214 615 35 1753 0 184DBMS_SPACE包declare own

16、erVARCHAR2(30);name VARCHAR2(30);seg_typeVARCHAR2(30);tblockNUMBER;.BEGIN dbms_space.unused_space (&owner,&table_name,TABLE, tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock); dbms_output.put_line(.END;/索引重組織在經(jīng)常變動的表上建立的索引可能會引起性能問題空的索引塊可以回到free list中即使一個數(shù)據(jù)塊只包含一個項目，它也必須維護(hù)可以對索引執(zhí)行重新建立的操作監(jiān)測索引SQL

17、 ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;Index analyzed.SQL SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 2 AS index_usage 3 FROM index_stats;INDEX_USAGE- 24SQL ALTER INDEX acct_no_idx REBUILD;Index altered. 數(shù)據(jù)庫配置和I/O問題 有效利用Oracle數(shù)據(jù)塊 調(diào)整排序操作 調(diào)整回滾段調(diào)整Oracle的磁盤利用需要排序的有關(guān)操作創(chuàng)建索引包括索引維護(hù)的并行插入操作ORDER BY或GROU

18、P BY子句DISTINCT值選擇UNION、INTERSECT或MINUS操作排序合并的連接ANALYZE命令的執(zhí)行排序處理排序空間需求大于排序空間需求大于SORT_AREA_SIZE:Serverprocess Sort run 1 Sort run 2TEMPORARY tablespace Sort run 2 Temporary segmentMULTIBLOCK排序區(qū)和參數(shù)排序空間： Stack space User session data Cursor state Stack space PGA 專用服務(wù)器連接在專用服務(wù)器連接在PGA 多線索服務(wù)器連接在共享池多線索服務(wù)器連接在

19、共享池 User session data Cursor state PGA UGA UGA Sort area Sort areaSORT_AREA_SIZE = 64000SORT_AREA_RETAINED_SIZE = 64000排序處理和臨時空間臨時的表空間是執(zhí)行CREATE TABLESPACE . DATAFILE . TEMPORARY命令創(chuàng)建的temp03.dbf 2Mtemp02.dbf 2Mtemp01.dbf 2MPermanent ObjectsOne single sort segment 由第一次排序操作創(chuàng)建 在需要時進(jìn)行擴展 由多個范圍構(gòu)成，不同的排序操作都可以

20、使用 在SGA的排序范圍池(SEP)中說明臨時空間段 無論何時，盡量避免排序操作 盡可能使排序操作在內(nèi)存中完成，減少分頁及交換 減少空間分配的調(diào)用：適當(dāng)?shù)胤峙渑R時空間調(diào)整排序操作避免排序操作無論何時，都應(yīng)當(dāng)盡量避免排序操作: 創(chuàng)建索引時使用NOSORT選項 使用UNION ALL，而不是UNION 在表的連接時，使用索引進(jìn)行訪問 為在ORDER BY子句中引用的列創(chuàng)建索引 為分析選擇列 對于大的對象分析時，使用ESTIMATE，而不是COMPUTE診斷工具V$SYSSTATSort area (UGA)V$SORT_SEGMENTV$SORT_USAGEreport.txtSORT_AREA_

21、SIZESORT_AREA_RETAINED_SIZESORT_MULTIBLOCK_READ_COUNTServerprocessTEMPORARYtablespace(sorts(disk)(sorts(memory)PCTINCREASEINITIALNEXT診斷和原則SQL select disk.value Disk, mem.value Mem, 2 (disk.value/mem.value)*100 Ratio 3 from v$sysstat mem, v$sysstat disk 4 where = sorts (memory) 5 and disk.na

22、me = sorts (disk); Disk Mem Ratio- - - 23 206 11.165049 磁盤排序與內(nèi)存排序的比率應(yīng)當(dāng)小于5% 如果比率大于5%時，可以提高SORT_AREA_SIZE參數(shù)的值監(jiān)測臨時表空間SQL select tablespace_name, current_users, total_extents, 2 used_extents, extent_hits, max_used_blocks, 3 max_sort_blocks 4 from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_EXTENT

23、S USED_EXTENTS EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS - - - - - - - TEMP 2 4 3 20 200 200 缺省的存儲參數(shù)適用于排序段缺省的存儲參數(shù)適用于排序段 排序段不限制范圍數(shù)排序段不限制范圍數(shù) 數(shù)據(jù)庫配置和I/O問題 有效利用Oracle數(shù)據(jù)塊 調(diào)整排序操作 調(diào)整回滾段調(diào)整Oracle的磁盤利用回滾段: 使用Control files Data files Redo logfiles回滾段的活動4312updateupdateinsertinsertupdateT1T2updateupdateinserti

24、nsertupdateT1T2T3T4回滾段頭的活動4312updateupdateinsertinsertupdateT1T2updateupdateinsertinsertupdateT3T4T5T6回滾段頭包含每個事務(wù)的條目每個事務(wù)必須有更新的訪問回滾段的增長124312345 由于缺少回滾空間，導(dǎo)致事務(wù)處理失敗 快照太老的錯誤發(fā)生，可能是由于以下原因： 所需要的塊中有關(guān)內(nèi)容已經(jīng)被重新使用，即塊中的SCN比查詢開始時的SCN更新 回滾段頭中的事務(wù)插槽已經(jīng)被重新使用 回滾段中的撤消數(shù)據(jù)在提交之后，已經(jīng)被覆蓋調(diào)整回滾段調(diào)整回滾段事務(wù)不應(yīng)當(dāng)?shù)却貪L段的訪問在正常運行期間不應(yīng)當(dāng)擴展回滾段用戶和使

25、用持續(xù)應(yīng)當(dāng)盡量少使用回滾事務(wù)不應(yīng)當(dāng)用完回滾空間在需要時用戶能夠看到讀一致性的映象調(diào)整回滾段的診斷工具4312T3T4T5T6DataHeaderSGAV$ROLLSTATV$ROLLNAMEV$TRANSACTIONV$SESSIONreport.txtV$SYSTEM_EVENTV$ WAITSTATV$SYSSTAT回滾段頭沖突的診斷所有等待的合計與獲得的合計之比應(yīng)當(dāng)小于1%否則，應(yīng)當(dāng)創(chuàng)建更多的回滾段SQL select sum(waits)* 100 /sum(gets) Ratio, 2 sum(waits) Waits, sum(gets) Gets 3 from v$rollstat; Ratio Waits Gets- - -0.296736 5 1685回滾段沖突的診斷 SQL select value from v$sysstat 2 where name = consistent gets; VALUE- 71563任何類別的等待數(shù)都應(yīng)當(dāng)小于請求數(shù)的1%否則，應(yīng)當(dāng)創(chuàng)建更多的回滾段原則: 回滾段的個數(shù)Large rollbackSmall RBSSmall RBSSmall RBSSmall RBSSmall RBSSmall RBSSQL SET TRANSACTION USE