lesson6-2editor67db2參數(shù)優(yōu)化操作系統(tǒng)內(nèi)核

1、置初始STMM自置初始STMM自動內(nèi)存管查看已設(shè)db2set-查看已設(shè)db2set-查看所db2set-表變修表變db2setDB2_PARALLEL_IO參數(shù)定義容DB2_PARALLEL_IO參數(shù)定義容器的容器的并行度定義了每個容器對應(yīng)幾個物理磁當prefetch定義為automatic時生db2set db2set DB2_PARALLEL_IO=*:5,2:8表空間2的容器的并行度為其他表空間容器的并行度為對于DB2_PARALLEL_IO=*:5配置，表空間的prefetch計算方法下表變量與并發(fā)性相正常模式下，DB2先下表變量與并發(fā)性相正常模式下，DB2先加鎖，再檢查數(shù)設(shè)置該參數(shù)后

2、，先檢查數(shù)據(jù)在CS與級別下，將忽略的未提交數(shù)在CS與級別下，將忽略刪除的未提交數(shù)設(shè)置上述參數(shù)，需對應(yīng)用程試置初始STMM自動置初始STMM自動內(nèi)存管Sistics緩沖Sistics緩沖PackageCatalogUtilityApplication 日鎖優(yōu)化異步預(yù)Sement接收并執(zhí)行c送的SQL接收并執(zhí)行c送的SQL語句nt未啟用連接集中接對應(yīng)一啟用連接集中 多個連接共用一 個 個DBMMAX_COORDAGENTS DBMMAX_COORDAGENTS DB 一個實例只有一個數(shù)據(jù)庫，建議與MAX_COORDAGENTS 多個數(shù)據(jù)庫：MAXAPPLS之和不能小于MAX_COORDAGENTS

3、db2getsnapshotfordb on umdb2getsnapshotfordb on umagentsted參數(shù)MAX_COORDAGENTS ， 應(yīng)用程序日 如果出現(xiàn)SQL1040N設(shè)置max_coordagents理資 -2: 自動調(diào)整大-1: 采用db cfg-2: 自動調(diào)整大-1: 采用db cfg中的BUFFPAGE其他值：固定大select substr(bpname,1,20), npages from 讀命中的次數(shù)/讀總次 或： 讀命中的次數(shù)/讀總次 或：1-()通過SYSIBMADM.BP_HITRATIO視圖直接查通過SYSIBMADM.SNAPBP視圖或SNAP

4、SHOT OOL命令計db2pddble-DB_NAME TOTAL_RATIODATA_RATIOINDEX_RATIODB_NAME TOTAL_RATIODATA_RATIOINDEX_RATIOsubstr(DB_NAME,1,8) as DB_NAME, substr(BP_NAME,1,20) as BP_NAME, TOTAL_HIT_RATIO_PERCENTasTOTAL_RATIO, DATA_HIT_RATIO_PERCENT as DATA_RATIO, INDEX_HIT_RATIO_PERCENTasINDEX_RATIO, XDA_HIT_RATIO_PERCEN

5、T as XDA_RATIOFROM db2inst2beijing $ db2 get snapshot for buffoolsontest ool Snapshotool=Database=DatabaseInput database aliasSnapshot=ooldatalogical=ooldataphysical=oolindexlogical=oolindexphysical=對象控制信息對象控制信息(tablesindexes,tablespa ,DBHEAP相關(guān)的性能指DBHEAP Current sizeDBHEAP相關(guān)的性能指DBHEAP Current size 當

6、前使用的大DBHEAP High water mark 歷史最大值(高水位DBHEAP Configured size 配置的大應(yīng)確保POOL_WATERMARK不超過1record(s)substr(DB_NAME,1,10)asDB_NAME, PCKCACHESZ (Package Package是SQL/XQuery編譯后的PCKCACHESZ (Package Package是SQL/XQuery編譯后的可執(zhí)行方靜態(tài)SQL是預(yù)編譯的，其在系統(tǒng)表動態(tài)SQL在執(zhí)行的時候才進行編，Oracle中的Package對 相同SQL大小由PCKCACHESZ性Package的計Package Ca

7、che其他參考Package的計Package Cache其他參考性能指 PKG_CACHE_NUM_OVERFLOWS溢出次數(shù)：當以存放當前正在執(zhí)行的SQL方案時，會將內(nèi)存中內(nèi)容溢出到磁確中率接近100%，并且盡量避+6.80000000000000E-1record(s)1-(float(PKG_CACHE_INSERTS)/PKG_CACHE_LOOKUPS) as from CATALOGCACHE_SZ(Catalog每個數(shù)據(jù)庫會分配一個Catalog Catalog Cache用來緩存系統(tǒng)編CATALOGCACHE_SZ(Catalog每個數(shù)據(jù)庫會分配一個Catalog Catal

8、og Cache用來緩存系統(tǒng)編目Catalog Cache能下列操作的性綁定package、編譯數(shù)據(jù)庫權(quán)限檢DB CFG參數(shù)CATALOGCACHE_SZ定義了內(nèi)存上中率接近100%，并且盡量避免溢確- - - - - -+6.50000000000000E-1record(s)1-(float(CAT_CACHE_INSERTS)/CAT_CACHE_LOOKUPS) as from UTIL_HEAP_SZ (Utility UTIL_HEAP_SZ參UTIL_HEAP_SZ (Utility UTIL_HEAP_SZ參數(shù)控制Utility Heap內(nèi)存上下列參數(shù)會使用Utility Ut

9、ility Heap在工具運行結(jié)束后，內(nèi)存就會回收，不會常駐內(nèi)存 Utility Heap相關(guān)的性能指標Utility Heap Current size 當前使用的大UtilityHeapHighwatermark歷史最大值(高水位) Utility Heap Configured size 配置的大小Utility HeapUtility Heap-1 record(s) substr(DB_NAME,1,10)asDB_NAME, applheapsz (Application 每個連接擁有自己的ApplicationHeap，存放連接相關(guān)applheapsz (Application

10、每個連接擁有自己的ApplicationHeap，存放連接相關(guān) 確保多數(shù)應(yīng)用程序的ApplicationHeap High water mark小于Configured size當應(yīng)用程序接收到SQL0954CApplicationHeap$db2getsnapshotfordbon.Node=Memory Pool Type=ApplicationHeap Secondary ID= 320Currentsize=Highwatermark=Configured size =ement 當接收到SQL0101Nement 當接收到SQL0101NTheementistoolongorS_HE

11、AP_SZisticsSSisticsHeap用來在執(zhí)行S_HEAP_SZisticsSSisticsHeap用來在執(zhí)行 存內(nèi)Sistics Heap當出現(xiàn)下列錯誤或警告時，說明 SQL2312NSistics Heap當出現(xiàn)下列錯誤或警告時，說明 SQL2312Nisticsheapsizeistoosmallcarryouttheoperation.Suggestedheap sizenum SQL2313W All available he heap has been used.isticswere collectedn1 rowsout ofa totalof n2 SORTHEAP

12、SORTHEAP 當排序數(shù)據(jù)量較大而SORTHEAP不夠用時，將產(chǎn)生 制通過SNAPSHOTforDB通過SNAPSHOTforDB排序溢出率=SortoverflowsTotal$ db2 get snapshot for db on sle|grep-isort Total Private Sort heap allocated= 0Total Shared Sort heap = Shared Sort heap high water mark=29 t threshold sorts (shared memory)= 0Total= Total sort time = Sort = A

13、ctive = LOGBUFSZLOGFILSIZLOGBUFSZLOGFILSIZ MIT日志使用率高水位$ db2 get snapshot for db on sle|grep-iNumberlogbuffer日志使用率高水位$ db2 get snapshot for db on sle|grep-iNumberlogbufferfull Number log buffer full= 0$ db2getsnapshotfordbon le|grep-i LogLog space available to the database (Bytes)= 17334246 Log space

14、 used by the database (Bytes)= 3045754umsecondarylogspaceused=umtotallogspaceused= $ db2 get snapshot for db on sle|grep-ilock Locks held currently= 0Lock = Time database waited on locks = Lock list memory in use = Deadlocks = Lock = Exclusive lock = Agents currently waiting on = Lock = 將 將通過SNAPSHO

15、TforDB通過SNAPSHOTforDBooldatawritesAsynchronouspooldatapagewritesoolindexwritesAsynchronouspoolindexpagewritesooldatawrites接近Asynchronouspooldatapageoolindexwrites接近Asynchronouspoolindexpage$ db2 get snapshot for db on sle|grep -i write ooldata= Asynchronouspooldatapage= ool index writes=83 Asynchron

16、ouspoolindexpage= 預(yù)取- I/O 預(yù)取- I/O 4I/O)5I/O6. 置初始STMM自動置初始STMM自動內(nèi)存管參數(shù)取值范含參數(shù)取值范含使用的內(nèi)存占實例的比simple, mixed, 工作負載類11000每個事務(wù)的SQL數(shù)1200 每分鐘事務(wù)performance, recovery, 優(yōu)先性能或優(yōu)先恢yes, 數(shù)據(jù)是否稠本地連接數(shù)連接數(shù)RR,RS,CS,級yes, 緩沖池是否可db2 AUTOCONFIGURE USING MEM_PERCENT 60 WORKLOAD_TYPE MIXED NUM_STMTS db2 AUTOCONFIGURE USING MEM_PERCENT 60 WORKLOAD_TYPE MIXED NUM_STMTS 50