oracle8i數(shù)據(jù)庫(kù)管理培訓(xùn) - uml軟件工程組織

1、調(diào)整概述調(diào)整Oracle的內(nèi)存使用調(diào)整Oracle的磁盤(pán)利用調(diào)整數(shù)據(jù)庫(kù)的應(yīng)用,,數(shù)據(jù)庫(kù)性能調(diào)整,數(shù)據(jù)庫(kù)配置和I/O問(wèn)題有效利用Oracle數(shù)據(jù)塊調(diào)整排序操作調(diào)整回滾段,,調(diào)整Oracle的磁盤(pán)利用,不同類型Oracle文件的I/O統(tǒng)計(jì),ProcessCKPTDBWnLGWRARCnSERVER,Oracle File I/OData Files Log Archive Control

2、 Write Write Write Write Read Write Read/write Read,,,,,,,,,,表空間的利用,僅為系統(tǒng)數(shù)據(jù)字典對(duì)象保留SYSTEM表空間創(chuàng)建本地管理的表空間，避免空間管

3、理的問(wèn)題發(fā)生將表和索引放在不同的表空間中存放創(chuàng)建單獨(dú)的回滾表空間在各自的表空間中存儲(chǔ)非常大的數(shù)據(jù)庫(kù)對(duì)象建立一個(gè)或多個(gè)臨時(shí)表空間,在不同的設(shè)備上分布文件,數(shù)據(jù)文件與重做日志文件分開(kāi)存放表數(shù)據(jù)劃分減少磁盤(pán)的I/O評(píng)價(jià)裸設(shè)備的使用,Oracle文件的劃分,操作系統(tǒng)劃分:使用操作系統(tǒng)劃分軟件或RAID 確定合適的劃分大小手工劃分:使用CREATE TABLE或ALTER TABLE ALLOCATE命令并行查詢值得利用,

4、調(diào)整全表掃描操作,了解全表掃描的需求指定初始化參數(shù)DB_FILE_MULTIBLOCK_READ_COUNT：確定服務(wù)器進(jìn)程一次讀取數(shù)據(jù)塊的個(gè)數(shù)對(duì)cost-based優(yōu)化器的執(zhí)行計(jì)劃有影響使用V$SESSION_LONGOPS視圖來(lái)監(jiān)測(cè)長(zhǎng)時(shí)間運(yùn)行的全表掃描操作,檢查I/O統(tǒng)計(jì)的診斷工具,,V$FILESTAT,,V$DATAFILE,,,report.txt,,Oracle I/O 利用,System I/O 利用,Perfor

5、mancetools,I/O統(tǒng)計(jì),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, phys_reads reads, phy

6、s_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

7、 READS BLKS_ READ_ WRITES BLKS_ WRITE_ READ TIME WRT TIME------------- ----------------------- ------ --------- --------- ------ -------- ------RBS /DISK2/rbs0

8、1.dbf 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

9、 8 8 0SYSTEM /DISK1/sys01.dbf 806 1538 1985 116 116 1721TEMP /DISK1/temp01.dbf 168 666 483 675 675 0USER_DATA /DISK3/user

10、01.dbf 8 8 0 8 8 06 rows selected.,Redo Log 組和成員,,Group 2,,,Group 3,,Group 1,,,,,Member,Member,,,,,,,,,,,Disk 1,Disk 2,LGWR,聯(lián)機(jī)重做日志文件的配置,可以進(jìn)行如下配置：適當(dāng)設(shè)置重做日志文件的大小，減少?zèng)_突設(shè)置足夠的重做日志組

11、，防止等待重做日志文件單獨(dú)存放，并最好是快速設(shè)備可以查詢動(dòng)態(tài)性能視圖V$LOGFILE和V$LOG,歸檔日志文件的配置,允許LGWR進(jìn)程寫(xiě)重做日志到與ARCn進(jìn)程正在讀的不同磁盤(pán)上共享歸檔作業(yè)：改變歸檔速度：LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_n, (LOG_ARCHIVE_DUPLEX_DEST, LOG_ARCHIVE_MIN_SUCCEED_DEST),ALT

12、ER SYSTEM ARCHIVE LOG ALL TO ,診斷工具,,,V$ARCHIVE_DESTV$ARCHIVED_LOGV$ARCHIVE_PROCESSES,,LOG_ARCHIVE_DEST_STATE_n,檢查點(diǎn),檢查點(diǎn)導(dǎo)致：DBWn進(jìn)程執(zhí)行I/OCKPT進(jìn)程更新數(shù)據(jù)文件頭和控制文件檢查點(diǎn)頻繁發(fā)生：減少實(shí)例恢復(fù)的時(shí)間降低運(yùn)行時(shí)間的性能,原則,適當(dāng)設(shè)置聯(lián)機(jī)重做日志文件的大小，減少檢查點(diǎn)的發(fā)生次數(shù)增加聯(lián)機(jī)重

13、做日志組來(lái)提高LGWR進(jìn)程開(kāi)始重寫(xiě)的時(shí)間與檢查點(diǎn)控制有關(guān)的初始化參數(shù)：FAST_START_IO_TARGETLOG_CHECKPOINT_INTERVALLOG_CHECKPOINT_TIMEOUTDB_BLOCK_MAX_DIRTY_TARGET,調(diào)整DBWn的I/O,影響DBWn進(jìn)程更經(jīng)常地寫(xiě)臟緩沖區(qū)的參數(shù)是DB_BLOCK_MAX_DIRTY_TARGET如果臟緩沖區(qū)的個(gè)數(shù)低于計(jì)算的低限，DBWn進(jìn)程不寫(xiě)檢查點(diǎn)緩沖區(qū)

14、如果臟緩沖區(qū)的個(gè)數(shù)介于計(jì)算的低限和高限之間，DBWn進(jìn)程從檢查點(diǎn)隊(duì)列中寫(xiě)，直至檢查點(diǎn)緩沖區(qū)的個(gè)數(shù)低于計(jì)算的低限如果臟緩沖區(qū)的個(gè)數(shù)高于計(jì)算的高限，DBWn進(jìn)程則寫(xiě)檢查點(diǎn)緩沖區(qū)缺省值為(2*32)-1,數(shù)據(jù)庫(kù)存儲(chǔ)的層次結(jié)構(gòu),,Segments,,,,,,,,,,,,,,,,,,,,,,,,,Blocks,,,分配一個(gè)范圍,避免動(dòng)態(tài)地分配范圍: 創(chuàng)建本地管理的表空間 適當(dāng)設(shè)置段的大小 監(jiān)測(cè)段的存儲(chǔ)，并準(zhǔn)備擴(kuò)展,避免動(dòng)態(tài)分配,SQL

15、> SELECT owner, table_name, blocks, empty_blocks 2 FROM dba_tables 3 WHERE empty_blocks / (blocks+empty_blocks) < .1;OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS------ ---------- ---------- ------------

16、HR EMP 1450 50HR REGION 460 40,顯示空閑數(shù)據(jù)塊小于10%的段:,SQL> ALTER TABLE hr.emp ALLOCATE EXTENT;Table altered.,避免動(dòng)態(tài)分配:,避免動(dòng)態(tài)分配,建立本地管理的表空間:,CREATE TABLESPACE user_data_1DATA

17、FILE ‘oracle8/oradata/db1/lm_1.dbf’SIZE 100MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;,大范圍的優(yōu)缺點(diǎn),優(yōu)點(diǎn)：較少地動(dòng)態(tài)擴(kuò)展帶來(lái)一些性能方面的好處可以克服操作系統(tǒng)關(guān)于文件大小的限制缺點(diǎn)：空閑空間可能減少未使用的空間,數(shù)據(jù)庫(kù)配置和I/O問(wèn)題有效利用Oracle數(shù)據(jù)塊調(diào)整排序操作調(diào)整回滾段,,調(diào)整Oracle的磁盤(pán)利用,,,,數(shù)據(jù)塊的

18、大小,,Segments,,,,,,,,,,,,,,,,,,,,,,,Blocks,,減少數(shù)據(jù)塊的訪問(wèn): 使用大的數(shù)據(jù)塊大小 行的存儲(chǔ)緊密打包 防止行的遷移,DB_BLOCK_SIZE,當(dāng)數(shù)據(jù)庫(kù)創(chuàng)建時(shí)設(shè)置是數(shù)據(jù)文件讀寫(xiě)的最小I/O單位缺省為2KB或4KB，允許到64KB不容易改變應(yīng)當(dāng)是操作系統(tǒng)塊大小的倍數(shù)操作系統(tǒng)I/O大小大于或等于DB_BLOCK_SIZE,小數(shù)據(jù)塊的優(yōu)缺點(diǎn),優(yōu)點(diǎn)：減少數(shù)據(jù)塊的沖突適合于小的行適合

19、于隨機(jī)訪問(wèn)缺點(diǎn)：相對(duì)來(lái)說(shuō)，開(kāi)銷較大每個(gè)數(shù)據(jù)塊容納較少的行可能會(huì)導(dǎo)致讀取更多的索引塊,大數(shù)據(jù)塊的優(yōu)缺點(diǎn),優(yōu)點(diǎn)：較小的開(kāi)銷適合于非常大的行適合于順序訪問(wèn)利用索引讀取數(shù)據(jù)會(huì)有更好的性能缺點(diǎn)：數(shù)據(jù)塊沖突增加占用buffer cache更多的空間,PCTFREE和PCTUSED,Inserts,,,,,,,,,,,,,,,,,,,,,,,,,Inserts,,,Inserts,,,,Inserts,,,1,2,3,4,,,,

20、原則,PCTFREE缺省為10如果沒(méi)有UPDATE操作，可以設(shè)置為0PCTFREE = 100 x upd / (upd + ins)PCTUSED缺省為40如果有刪除的行可以設(shè)置PCTUSED = 100 - PCTFREE - 100 x rows x (ins + upd) / blocksize,Migration和Chaining,Index,Table,,

21、,檢測(cè)遷移和鏈接,使用ANALYZE命令檢測(cè)遷移的和鏈接的行:,Statistic Total Per transaction ... ------------------------- ----- --------------- ...table fetch continued row 495 .02,從report.txt的輸出檢測(cè)遷移的和鏈接的行:,SQL>

22、ANALYZE TABLE sales.order_hist COMPUTE STATISTICS;Table analyzed.,SQL> SELECT num_rows, chain_cnt FROM dba_tables 2 WHERE table_name=‘ORDER_HIST’; NUM_ROWS CHAIN_CNT--------- --------- 168 102,選擇遷移的行

23、,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_N

24、AME HEAD_ROWID ---------- ---------- ------------------SALES ORDER_HIST AAAAluAAHAAAAA1AAASALES ORDER_HIST AAAAluAAHAAAAA1AAB...,消除遷移的行,1. 運(yùn)行ANALYZE TABLE ... LIST CHAINED ROWS命令2. 將有關(guān)的行從原表

25、復(fù)制到另外的臨時(shí)表中3. 刪除原表中的有關(guān)行4. 從另外的臨時(shí)表將有關(guān)行插入原表中說(shuō)明：第4步即可以消除遷移的行，因?yàn)檫w移行的產(chǎn)生有由于UPDATE語(yǔ)句造成的，而非INSERT語(yǔ)句,高水位標(biāo)志,Empty blocks(rows deleted),,High- watermark,,Empty blocks (never used),,Extent 1,Extent 2,,,,,Segment header block,

26、,,,,記錄在段頭塊中在創(chuàng)建時(shí)，設(shè)置在段的開(kāi)始處在插入行時(shí)，以5個(gè)數(shù)據(jù)塊為單位增長(zhǎng)由TRUNCATE命令復(fù)位，而不是DELETE命令,表的統(tǒng)計(jì),SQL> ANALYZE TABLE hr.emp COMPUTE STATISTICS;Table analyzed.SQL> SELECT num_rows, blocks, empty_blocks as empty, 2 avg_space

27、, 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-------- ------ ----- --------- ---------

28、----------- 13214 615 35 1753 0 184,查詢來(lái)自ANALYZE命令的關(guān)于表的統(tǒng)計(jì)信息:,DBMS_SPACE包,declare ownerVARCHAR2(30);name VARCHAR2(30);seg_typeVARCHAR2(30);tblockNUMBER;...BEGIN dbms_s

29、pace.unused_space ('&owner','&table_name','TABLE', tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock); dbms_output.put_line(...END;/,索引重組織,在經(jīng)常變動(dòng)的表上建立的索引可能會(huì)引起性能問(wèn)題空的索引塊可以回到free

30、 list中即使一個(gè)數(shù)據(jù)塊只包含一個(gè)項(xiàng)目，它也必須維護(hù)可以對(duì)索引執(zhí)行重新建立的操作,監(jiān)測(cè)索引,,SQL> 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_st

31、ats;INDEX_USAGE----------- 24SQL> ALTER INDEX acct_no_idx REBUILD;Index altered.,數(shù)據(jù)庫(kù)配置和I/O問(wèn)題有效利用Oracle數(shù)據(jù)塊調(diào)整排序操作調(diào)整回滾段,,調(diào)整Oracle的磁盤(pán)利用,,,需要排序的有關(guān)操作,創(chuàng)建索引包括索引維護(hù)的并行插入操作ORDER BY或GROUP BY子句DISTINCT值選擇UNION

32、、INTERSECT或MINUS操作排序合并的連接ANALYZE命令的執(zhí)行,,排序處理,SORT_MULTIBLOCK_READ_COUNT可以強(qiáng)制在排序的合并階段一次讀入大量排序run數(shù)據(jù)到內(nèi)存,,排序區(qū)和參數(shù),排序空間：,,,,Shared pool,Stackspace,User session data,Cursor state,,,Stackspace,PGA,專用服務(wù)器連接在PGA,多線索服務(wù)器連接在共享池,,Use

33、r session data,Cursor state,PGA,,UGA,,UGA,,Sort area,,Sort area,,SORT_AREA_SIZE = 64000,,,SORT_AREA_RETAINED_SIZE = 64000,,,排序處理和臨時(shí)空間,臨時(shí)的表空間是執(zhí)行CREATE TABLESPACE ... DATAFILE ... TEMPORARY命令創(chuàng)建的,,,臨時(shí)表空間,,,,temp03.dbf 2M,

34、,,Permanent Objects,,,,One single sort segment,,,,由第一次排序操作創(chuàng)建在需要時(shí)進(jìn)行擴(kuò)展由多個(gè)范圍構(gòu)成，不同的排序操作都可以使用在SGA的排序范圍池(SEP)中說(shuō)明,,臨時(shí)空間段,無(wú)論何時(shí)，盡量避免排序操作盡可能使排序操作在內(nèi)存中完成，減少分頁(yè)及交換減少空間分配的調(diào)用：適當(dāng)?shù)胤峙渑R時(shí)空間,,調(diào)整排序操作,,,避免排序操作,無(wú)論何時(shí)，都應(yīng)當(dāng)盡量避免排序操作:創(chuàng)建索引時(shí)使用N

35、OSORT選項(xiàng)使用UNION ALL，而不是UNION在表的連接時(shí)，使用索引進(jìn)行訪問(wèn)為在ORDER BY子句中引用的列創(chuàng)建索引為分析選擇列對(duì)于大的對(duì)象分析時(shí)，使用ESTIMATE，而不是COMPUTE,診斷工具,V$SYSSTAT,Sort area (UGA),V$SORT_SEGMENT,,V$SORT_USAGE,,report.txt,SORT_AREA_SIZESORT_AREA_RETAINED_SIZESOR

36、T_MULTIBLOCK_READ_COUNT,Serverprocess,TEMPORARYtablespace,,,,(sorts(disk)),(sorts(memory)),PCTINCREASEINITIALNEXT,,,診斷和原則,SQL> select disk.value "Disk", mem.value "Mem", 2 (disk.value

37、/mem.value)*100 "Ratio" 3 from v$sysstat mem, v$sysstat disk 4 where mem.name = 'sorts (memory)' 5 and disk.name = 'sorts (disk)'; Disk Mem Ratio--------- ------

38、--- --------- 23 206 11.165049,磁盤(pán)排序與內(nèi)存排序的比率應(yīng)當(dāng)小于5%如果比率大于5%時(shí)，可以提高SORT_AREA_SIZE參數(shù)的值,,,監(jiān)測(cè)臨時(shí)表空間,,SQL> select tablespace_name, current_users, total_extents, 2 used_extents, extent_hits, max_

39、used_blocks, 3 max_sort_blocks 4 from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS --------------- ------------- ------------- ----

40、-------- ----------- --------------- --------------- TEMP 2 4 3 20 200 200,缺省的存儲(chǔ)參數(shù)適用于排序段排序段不限制范圍數(shù),數(shù)據(jù)庫(kù)配置和I/O問(wèn)題有效利用Oracle數(shù)據(jù)塊調(diào)整排序操作調(diào)整回滾段,,

41、調(diào)整Oracle的磁盤(pán)利用,回滾段: 使用,Transaction rollback,Transaction recovery,Rollback segment,Read consistency,,,,,,Control files,Redo logfiles,,,,,,,回滾段的活動(dòng),,,Active extent,Inactive extent,,,,,4,3,1,2,,,,>update>update>i

42、nsert>insert>update,T1,T2,,>update>update>insert>insert>update,T1,T2,T3,,,,T4,,,回滾段頭的活動(dòng),,,,,4,3,1,2,,,,>update>update>insert>insert>update,T1,T2,,>update>update>in

43、sert>insert>update,T3,T4,T5,,,,T6,,回滾段頭包含每個(gè)事務(wù)的條目每個(gè)事務(wù)必須有更新的訪問(wèn),,回滾段的增長(zhǎng),,,Active extent,Inactive extent,,,,,,,,,,1,2,4,3,1,2,3,4,5,,,New extent,,,,由于缺少回滾空間，導(dǎo)致事務(wù)處理失敗快照太老的錯(cuò)誤發(fā)生，可能是由于以下原因：所需要的塊中有關(guān)內(nèi)容已經(jīng)被重新使用，即塊中的SCN比查詢

44、開(kāi)始時(shí)的SCN更新回滾段頭中的事務(wù)插槽已經(jīng)被重新使用回滾段中的撤消數(shù)據(jù)在提交之后，已經(jīng)被覆蓋,,調(diào)整回滾段,調(diào)整回滾段,事務(wù)不應(yīng)當(dāng)?shù)却貪L段的訪問(wèn)在正常運(yùn)行期間不應(yīng)當(dāng)擴(kuò)展回滾段用戶和使用持續(xù)應(yīng)當(dāng)盡量少使用回滾事務(wù)不應(yīng)當(dāng)用完回滾空間在需要時(shí)用戶能夠看到讀一致性的映象,,,,調(diào)整回滾段的診斷工具,,,,,4,3,1,2,,T3T4T5T6,,,,,,Data,Header,SGA,V$ROLLSTATV$ROLLNAME

45、,,V$TRANSACTIONV$SESSION,,report.txt,,V$SYSTEM_EVENTV$ WAITSTATV$SYSSTAT,,回滾段頭沖突的診斷,所有等待的合計(jì)與獲得的合計(jì)之比應(yīng)當(dāng)小于1%否則，應(yīng)當(dāng)創(chuàng)建更多的回滾段,SQL> select sum(waits)* 100 /sum(gets) "Ratio", 2 sum(waits) "Waits&q

46、uot;, sum(gets) "Gets" 3 from v$rollstat; Ratio Waits Gets--------- --------- ---------0.296736 5 1685,回滾段沖突的診斷,SQL> select value from v$sysstat 2 where name = 'consiste

47、nt gets'; VALUE---------- 71563,任何類別的等待數(shù)都應(yīng)當(dāng)小于請(qǐng)求數(shù)的1%否則，應(yīng)當(dāng)創(chuàng)建更多的回滾段,原則: 回滾段的個(gè)數(shù),,,,Large rollback,Small RBS,,,,,,Small RBS,Small RBS,Small RBS,Small RBS,Small RBS,SQL>SET TRANSACTION USE ROLLBACK SEGMENT

48、large_rbs;,OLTP: One RBS for four transactionsBatch: One rollback segment for each concurrent job,原則: 回滾段的大小,Rollback segment 2,Rollback segment 1 =,,,,,,...,INITIAL = NEXT= 2n,20,,,,,,...,INITIAL = NEXT= 2n,20,OPTI

49、MAL = x,OPTIMAL = y,,,,,,,,,,,,,,,,,,0.00,0.10,0.20,0.30,0.40,0.50,0,10,20,30,40,擴(kuò)展的可能性,范圍的個(gè)數(shù),事務(wù)回滾數(shù)據(jù)的大小,刪除操作對(duì)回滾的開(kāi)銷非常大插入操作使用較少的回滾空間更新操作使用的回滾空間與修改的列數(shù)有關(guān)索引維護(hù)增加回滾的使用,SQL>SELECT s.username, t.used_ublk, t.start_time 2

50、 FROM v$transaction t, v$session s3WHERE t.addr = s.taddr;USERNAMEUSED_UBLK START_TIME-------------- ---------- --------------------SCOTT2 11/16/95 10:26:39,事務(wù)回滾數(shù)據(jù)的大小,SQL> select usn,writes from v$rol

51、lstat; USN WRITES--------- ---------- 1 4738SQL> @upd,SQL> select usn,writes from v$rollstat; USN WRITES--------- --------- 1 1102686,語(yǔ)句執(zhí)行之前回滾段的字節(jié)個(gè)數(shù):,語(yǔ)句執(zhí)行之后:,在應(yīng)用設(shè)計(jì)時(shí)，應(yīng)當(dāng)允

52、許用戶定時(shí)提交開(kāi)發(fā)人員不應(yīng)當(dāng)在程序中設(shè)置過(guò)長(zhǎng)的事務(wù),,使用較少的回滾,Import設(shè)置COMMIT = Y使用BUFFER來(lái)設(shè)置行集合的大小Export設(shè)置CONSISTENT=NSQL*Loader設(shè)置帶有ROWS的COMMIT間隔,,使用較少的回滾,由于缺少回滾空間，導(dǎo)致事務(wù)處理失敗快照太老的錯(cuò)誤發(fā)生，可能是由于以下原因：所需要的塊中有關(guān)內(nèi)容已經(jīng)被重新使用，即塊中的SCN比查詢開(kāi)始時(shí)的SCN更新回滾段頭中的事務(wù)插