1、Quest SoftwareQuest數(shù)據(jù)庫管理解決方案Space Manager w/LiveReorg1About MeRain Li李文京Manager, Professional Services, Quest ChinaPhone 86 010 85185651 x 505Cell 86ax 86 01085151322Years of Experience with Oracle/SAP Database ReorganizationYears of Experience with Oracle Database Administration, Tunin

2、g and Management2培訓(xùn)日程空間管理問題與收益Space Manager w/LiveReorg介紹如何發(fā)現(xiàn)與診斷問題？如何解決問題？3空間管理問題與收益空間管理問題與收益4空間管理問題與收益空間管理的收益在于：數(shù)據(jù)庫優(yōu)化的重要部分保護數(shù)據(jù)庫可用性存儲空間耗盡！提高性能High WatermarkChained RowsFragmented TablesInvalid IndexesI/O Balance回收浪費的空間優(yōu)化I/O5收益：性能提高提高性能有效地減少I/O操作用戶實際反饋，對關(guān)鍵查詢和批處理操作來說，性能有50-100%的提高例如：對于ERP批處理任務(wù)BEFORE f

3、ull tablespace reorg: 12 hours runtimeAFTER full tablespace reorg: 5 1/2 hours runtime6收益：空間回收浪費的空間（Wasted space）Tables IndexesTablespaces回收浪費的空間例如：650G SAP system重組500G回收200G空間容量規(guī)劃 對象和數(shù)據(jù)庫存儲空間的增長速度為多少？明年需要多少存儲空間？7Space Manager w/LiveReorg介紹Space Manager w/LiveReorg介紹8Traditional Reorganization9Live

4、Reorganization10How LiveReorg Works- light-weight collection triggersTEST_TABLECol1Number Primary KeyCol2DateCol3Varchar2Col4Varchar2Transactions on the table during reorg:INSERT into table values (5, 05/05/2005, a, a)UPDATE table set Col4=A1 where PK=1DELETE from table where PK=2 TEST_TABLE1, 05/05

5、/2005, a, a2, 05/06/2005, b, b3, 05/05/2005, c, c4, 05/06/2005, d, d5, 05/05/2005, a, a, Insert1, 05/05/2005, a, A1, Update2, 05/06/2005, a, a, DeleteTEST_TABLE1, 05/05/2005, a, A13, 05/05/2005, c, c4, 05/06/2005, d, d5, 05/05/2005. a, aTEST_TABLE1, 05/05/2005, a, a2, 05/06/2005, b, b3, 05/05/2005,

6、c, c4, 05/06/2005, d, dRowid5, I1, DRowid1, I2, DTEST_TABLE_LR1, 05/05/2005, a, A13, 05/05/2005, c, c4, 05/06/2005, d, d5, 05/05/2005. a, aexample:Light-weight Collection Trigger example:transactionresult11LiveReorg ArchitectureLiveReorg ClientReorgThreadReorgThreadReorgThreadreorg tablereorg indexr

7、eorg indexget reorg job from repositorystore reorg job to repositoryQuest Server Agentmonitor progressLight-weight Triggerrecord incremental changesOracle Database12A LiveReorg JobCreate Table and Data CopyPost-data CopyPrepare to SwitchTable switch13Create Table and Data CopyCreate Table and Data C

8、opyPost-data CopyPrepare to SwitchTable switchCreate copy tableCreate table as select- Insert as select- PL/SQL block- FastCopy14Post-data CopyCreate Table and Data CopyPost-data CopyPrepare to SwitchTable switch- Create indexes- Post changes from The collection triggers- Constraint, privilege and c

9、omment creation 15Prepare to SwitchCreate Table and Data CopyPost-Data CopyPrepare to SwitchTable switchQSA waits for the switch from original table to reorganized copy table 16Table SwitchCreate Table and Data CopyPost-Data CopyPrepare to SwitchTable switchThe copy table takes the place of the orig

10、inal 17InstallationLiveReorg Client InstallationLiveReorg Server ComponentsQSA Installation18Space Manager w/LiveReorg主要功能監(jiān)控和管理空間使用問題的發(fā)現(xiàn)與診斷Exception reportsTablespace Map問題的解決Reorg ManagerReorg XpertChained Row RepairLiveReorg容量規(guī)劃Capacity reports 19Space Manager w/LiveReorg特點圖形方式檢測空間存儲問題查看數(shù)據(jù)庫對象詳細信息交

11、互式重組，保證過程安全重組時對應(yīng)用的影響減至最低預(yù)測空間使用狀況提供容量規(guī)劃和數(shù)據(jù)庫增長趨勢分析20Space Manager w/LiveReorg工作步驟統(tǒng)計信息收集檢測問題對象屬性重組預(yù)測表空間失敗容量規(guī)劃21Space Manager w/LiveReorg結(jié)構(gòu)Windows 控制臺通過SQL*Net連接到數(shù)據(jù)庫 數(shù)據(jù)庫服務(wù)器上建立Repository存儲數(shù)據(jù)對象增長統(tǒng)計信息存儲重組腳本Quest Server Agent(Scheduler)收集統(tǒng)計信息運行重組腳本22Space Manager w/LiveReorg發(fā)現(xiàn)與診斷問題23發(fā)現(xiàn)問題：收集統(tǒng)計信息Space Manager

12、需要使用統(tǒng)計信息進行空間管理，包括查看對象的使用情況、報告存儲問題等統(tǒng)計信息被存儲在Repository中 為了方便統(tǒng)計信息收集，可以使用Analysis groups和Instant analysis功能24發(fā)現(xiàn)問題：收集統(tǒng)計信息步驟Manage=New Analysis GroupManage=Open Analysis Group定義組；查看對象；分析選項；調(diào)度選項；Save&Start。25發(fā)現(xiàn)問題：統(tǒng)計信息使用統(tǒng)計信息可用于問題檢測、問題解決和容量規(guī)劃等問題報告（Exception reports） 管理報告（Management reports）容量規(guī)劃報告（Capacity re

13、ports）統(tǒng)計信息報告（Analysis Validation reports）26診斷問題：方法一Tablespace MapTablespace Maps：在某一表空間上點擊右鍵，選擇“Tablespace Map”Reorg Need 模式Object Type 模式27重組術(shù)語Reorg need is a measure of the benefit of reorganizing an object. Benefit can be thought of in terms of the problems to be fixed by reorg and the improvemen

14、t in database performance to be gained from fixing them. The higher an objects reorg need, the greater the benefit of reorganizing it.Reorg need ratiois a measure of the benefit of reorganizing an object relative to the cost of reorganizing it. Cost can be thought of in terms of the amount of databa

15、se I/O required to reorganize an object (this corresponds to the amount of data to be moved and the time it will take to move it). The value for need ratio can be from zero to 100. The higher an objects need ratio, the greater the benefit of reorganizing it relative to cost. 28屬性信息查看對象屬性在Tablespace

16、Map中選擇對象，點擊右鍵，選中屬性快捷方式即可查看表空間屬性選擇表空間，點擊右鍵，選中屬性快捷方式即可29對象屬性信息在對象屬性頁上可以查看對象信息和檢查問題，這些信息包括Properties TabStatistics TabConstraints TabDependencies TabColumns TabGrowth TabReorg Need Tab30診斷問題：方法二Exception Reports使用問題報告（Exception Reports）：可以使用問題報告識別出有問題的對象，例如具有高Reorg need的對象在報告中可以指定檢測的問題，如 Objects that h

17、ave Reached MaxExtents、Non-selective Indexes、 Table/Index Contention例如，需要檢測具有較高 Reorg need ratio的對象就可以運行Objects Needing Reorganization報告 31診斷問題：方法三Capacity Report使用容量規(guī)劃報告：利用容量規(guī)劃報告可以檢測需要重組的對象或表空間，從而防止?jié)撛诘目臻g問題運行Tablespace Failure Prediction報告查看什么時間表空間的剩余空間將用盡運行Trend Analysis報告查看對象空間的增長速度和Extent分配的歷史32S

18、pace Manager w/LiveReorg解決問題33解決問題識別出存儲問題后，就可以進行問題解決，可以使用的功能為Reorg ManagerReorg XpertChained Row Repair34解決問題：重組對數(shù)據(jù)庫對象的重組包括重組或重新構(gòu)造表和索引修復(fù)行鏈接重組表空間定位表和索引到其它表空間上35解決問題：重組工具Reorg Manager交互式的重組方式，可以針對一個或多個數(shù)據(jù)庫對象 Reorg Xpert基于表空間的智能重組工具，不需要人工干預(yù)Chained Row Repair修復(fù)行鏈接36解決問題：重組方法離線重組（Offline Reorg）重組期間，需要停止應(yīng)用和業(yè)務(wù)處理在線重組（Live Reorg）在線重組試用于全天候運營企業(yè)，重組期間業(yè)務(wù)應(yīng)用不受影響被重組的表始終是可用的，只是在原表和新表之間進行切換時存在短暫的延遲37數(shù)據(jù)移動方法使用SQL方式和FastCopy混合方式使用SQL方式移動不包含 LONG或 LONG RAW字段的數(shù)據(jù)，而用