數(shù)據(jù)庫性能調(diào)整

1、 調(diào)整概述 調(diào)整Oracle的內(nèi)存使用 調(diào)整Oracle的磁盤利用 調(diào)整數(shù)據(jù)庫的應(yīng)用數(shù)據(jù)庫性能調(diào)整 應(yīng)用需求和SQL調(diào)整 監(jiān)控鎖沖突調(diào)整數(shù)據(jù)庫的應(yīng)用數(shù)據(jù)庫管理的角色應(yīng)用調(diào)整是調(diào)整中最重要的部分?jǐn)?shù)據(jù)庫管理員并不直接參與應(yīng)用調(diào)整數(shù)據(jù)庫管理員必須熟悉不好的SQL語句對數(shù)據(jù)庫性能的所產(chǎn)生的重要影響診斷工具概述EXPLAIN PLANSQL跟蹤和TKPROFSQL*Plus的AUTOTRACEOracle SQL的ANALYZE命令Explain Plan使用explain plan時，可以不使用跟蹤使用explain plan：1. 使用utlxplan.sql腳本創(chuàng)建PLAN_TABLE表2. 運(yùn)行

2、EXPLAIN PLAN SQL命令3. 查詢PLAN_TABLE表，顯示SQL語句的執(zhí)行計(jì)劃SQL $ORACLE_HOME/rdbms/admin/utlxplanSQL跟蹤和TKPROF1. 設(shè)置初始化參數(shù)2. 調(diào)用SQL跟蹤3. 運(yùn)行應(yīng)用4. 關(guān)閉SQL跟蹤5. 使用TKPROF格式化跟蹤文件6. 解釋輸出SQL跟蹤的使能及使不能實(shí)例級:SQL_TRACE = TRUE|FALSE會話級:SQL alter session set SQL_TRACE = true|false;SQL execute DBMS_SESSION.SET_SQL_TRACE 2 (true|false);S

3、QL execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 2 (session_id, serial_id, true|false);使用TKPROF格式化跟蹤文件$ tkprof tracefile.trc output.txt optionstracefile.trcoutput.txtUSER_DUMP_DESTTKPROF統(tǒng)計(jì)Count:Number of execution callsCPU:CPU seconds usedElapsed: Total elapsed timeDisk:Physical readsQuery:Logical re

4、ads for consistent read Current: Logical reads in current mode Rows:Rows processedSQL*Plus的AUTOTRACE創(chuàng)建PLAN_TABLE表運(yùn)行ORACLE_HOME/sqlplus/admin 目錄中的plustrce.sql腳本 AUTOTRACE語法SQL ORACLE_HOME/sqlplus/admin/plustrce.sqlSQL grant plustrace to scott;set autotrace off | on | traceonly explain | statistics 優(yōu)化

5、器模式Rule-based: 使用一個分級系統(tǒng) 語法和數(shù)據(jù)字典驅(qū)動的Cost-based: 選擇代價最小的路徑 統(tǒng)計(jì)驅(qū)動的優(yōu)化器模式的設(shè)置實(shí)例級:optimizer_mode = choose|rule|first_rows|all_rows會話級:alter session set optimizer_mode = choose|rule|first_rows|all_rows語句級:使用提示管理統(tǒng)計(jì)使用ANALYZE命令可以收集或刪除統(tǒng)計(jì)使用DBMS_STATS包: GATHER_TABLE_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATH

6、ER_DATABASE_STATS表統(tǒng)計(jì)表的行數(shù)表的數(shù)據(jù)塊個數(shù)和空的數(shù)據(jù)塊數(shù)可用的平均空閑空間鏈接或遷移的行數(shù)行的平均長度最后的ANALYZE日期和樣本大小數(shù)據(jù)字典視圖: DBA_TABLES索引統(tǒng)計(jì)索引層次 (高度)葉塊和不同鍵的個數(shù)每個鍵的葉塊平均個數(shù)每個鍵的數(shù)據(jù)塊平均個數(shù)索引條目的個數(shù)聚集因子數(shù)據(jù)字典視圖: DBA_INDEXES列統(tǒng)計(jì)不同值的個數(shù)最小值，最大值最后的ANALYZE日期和樣本大小數(shù)據(jù)字典視圖: USER_TAB_COL_STATISTICS直方圖以更詳細(xì)的方式描述一個特殊列的數(shù)據(jù)分布為不均衡分布數(shù)據(jù)做更好的選擇性評估使用ANALYZE TABLE . FOR COLUMN

7、S 命令創(chuàng)建直方圖 數(shù)據(jù)字典視圖: DBA_HISTOGRAMSOracle Corporation Confidential1數(shù)據(jù)庫之間統(tǒng)計(jì)復(fù)制Copyto user tableExportImportCopy usertable to DD432Oracle Corporation Confidential舉例: 統(tǒng)計(jì)復(fù)制DBMS_STATS.EXPORT_TABLE_STATS(TRAIN /* schema name */,COURSES /* table name */,NULL /* no partitions */,STATS /* statistics table name *

8、/,CRS990601/* id for statistics */,TRUE /* index statistics */);DBMS_STATS.CREATE_STAT_TABLE(TRAIN /* schema name */,STATS /* statistics table name */,USERS /* tablespace */);優(yōu)化器計(jì)劃的穩(wěn)定性允許應(yīng)用強(qiáng)制使用一個所希望的SQL訪問路徑通過數(shù)據(jù)庫的改變維護(hù)一致性的執(zhí)行計(jì)劃使用由提示構(gòu)成的存儲綱要來實(shí)現(xiàn)計(jì)劃同等SQL語句的文本必須匹配計(jì)劃的維護(hù)，通過: 新的Oracle版本 新的對象統(tǒng)計(jì) 初始化參數(shù)改變 數(shù)據(jù)庫重組織 模式改

9、變創(chuàng)建存儲綱要SQL alter session 2 set CREATE_STORED_OUTLINES = train;SQL select . from .;SQL select . from .;SQL create or replace OUTLINE co_cl_join 2 FOR CATEGORY train ON 3 select co.crs_id, . 4 from courses co 5 , classes cl 6 where co.crs_id = cl.crs_id;使用存儲綱要將USE_STORED_OUTLINES參數(shù)設(shè)置成 TRUE或一個目錄名CREATE

10、_STORED_OUTLINES和USE_STORED_OUTLINES都可以在實(shí)例級或會話級設(shè)置SQL alter session 2 set USE_STORED_OUTLINES = train;SQL select . from .;維護(hù)存儲綱要使用OUTLN_PKG包可以: 刪除綱要或綱要的目錄 重命名目錄使用ALTER OUTLINE命令可以: 重命名一個綱要 重建一個綱要 改變一個綱要的目錄綱要存儲在OUTLN模式中 數(shù)據(jù)訪問方法為了提高性能，可以使用以下數(shù)據(jù)訪問方法: 索引 (B-樹, 位圖, 顛倒鍵) 索引組織表 聚集 直方圖 物化視圖B-樹索引Index entry hea

11、derKey column lengthKey column valueROWIDRootBranchLeafIndex entry位圖索引TableIndexBlock 10Block 11Block 12File 3位圖索引用于低基數(shù)的列適合多個謂詞使用最少的存儲空間適合只讀的系統(tǒng)適合非常大的表創(chuàng)建和維護(hù)位圖索引SQL create BITMAP INDEX ord_region_id_idx 2 on ord(region_id) 3 storage (initial 200k next 200k 4 pctincrease 0 maxextents 50) 5 tablespace i

12、ndx01;B-樹索引與位圖索引的比較B-Tree indexesBitmap indexesSuitable for high-cardinality Suitable for low-cardinality columnscolumnsUpdates on keys relativelyUpdates to key columns very inexpensiveexpensiveInefficient for queries usingEfficient for queries usingOR predicatesOR predicatesRow-level lockingBitmap

13、segment-level lockingMore storageLess storageUseful for OLTPUseful for DSSKEY ROWID- -1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001.顛倒鍵索引EMPNO ENAME JOB .- - - -7499 ALLEN SALESMAN7369

14、 SMITH CLERK7521 WARD SALESMAN .7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER. . .創(chuàng)建顛倒鍵索引SQL create unique index i1_t1 ON t1(c1) 2 REVERSE pctfree 30 3 storage(initial 200k next 200k 4 pctincrease 0 maxextents 50) 5 tablespace indx01;SQL create unique index i2_t1 ON t1(c

15、2);SQL alter index i2_t1 REBUILD REVERSE;索引組織表索引組織表與常規(guī)表的比較更快地基于鍵值訪問表數(shù)據(jù)減少存儲的需求次要索引和邏輯ROWID主要限制: 必須有一個主鍵 不能使用唯一性約束 不能被聚集創(chuàng)建索引組織表SQL create table sales 2 (office_cd number(3) 3 ,qtr_enddate 4 ,revenuenumber(10,2) 5 ,review varchar2(1000) 6 ,constraint sales_pk 7 PRIMARY KEY (office_cd,qtr_end) 8 ) 9 ORG

16、ANIZATION INDEX tablespace indx 10 PCTTHRESHOLD 20 11 INCLUDING revenue 12 OVERFLOW TABLESPACE user_data;Segment = SYS_IOT_OVER_nIOT_type = IOT_OVERFLOWSegment_type = TABLESegment = SALES_PKIOT_type = IOTSegment_type = INDEXIndex_type = IOT - TOP索引組織表的行溢出INDX tablespaceUSER_DATA tablespace索引組織表的字典視圖

17、SQL select table_name,tablespace_name,iot_name,iot_type 2 from DBA_TABLES;TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE- - - -SALES IOT SYS_IOT_OVER_2268 USER_DATA SALES IOT_OVERFLOWSQL select index_name,index_type,tablespace_name,table_name 2 from DBA_INDEXES;INDEX_NAME INDEX_TYPE TABLESPACE TABLE_N

18、AME- - - -SALES_PK IOT - TOP INDX SALESSQL select segment_name,tablespace_name,segment_type 2 from DBA_SEGMENTS;SEGMENT_NAME TABLESPACE SEGMENT_TYPE - - -SYS_IOT_OVER_2268 USER_DATA TABLE SALES_PK INDX INDEX聚集Cluster Key(ORD_NO) 101 ORD_DTCUST_CD 05-JAN-97 R01 PROD QTYA4102 20A5675 19 W0824 10 102 O

19、RD_DTCUST_CD 07-JAN-97 N45PROD QTYA2091 11G7830 20 N9587 26ORD_NOPRODQTY.-101A410220102A209111102G7830 20 102N9587 26101A567519101W082410ORD_NOORD_DT CUST_CD- -10105-JAN-97 R0110207-JAN-97 N45聚集類型適合使用何種類型聚集的情況規(guī)范規(guī)范Uniform key distributionEvenly distributed key values Rarely updated key Often joined m

20、aster-detail tablesPredictable number of key valuesQueries using equality predicate on keyHashXXXXXIndexXXX物化視圖一個SQL查詢的實(shí)例化可以用來查詢重寫刷新類型: 完全或快速 強(qiáng)制或從不刷新模式: 手工 自動 (同步或異步) 物化視圖: 手工刷新刷新指定的物化視圖:基于一個或多個基表的物化視圖:應(yīng)當(dāng)刷新的全部物化視圖:DBMS_MVIEW.REFRESH(SF_SALES, parallelism = 10);DBMS_MVIEW.REFRESH_DEPENDENT(SALES);DBM

21、S_MVIEW.REFRESH_ALL_MVIEWS;查詢重寫為了使用物化視圖，而不是基表，一個查詢必須重寫查詢重寫是透明的，不需要對物化視圖有任何特殊權(quán)限物化視圖的查詢重寫可以使能或使不能查詢重寫初始化參數(shù)QUERY_REWRITE_ENABLED必須設(shè)置成TRUEQUERY REWRITE權(quán)限可以允許用戶使能物化視圖DBMS_OLAP包具有使用物化視圖的選項(xiàng)物化視圖和查詢重寫: 舉例SQL create MATERIALIZED VIEW sales_summary 2 tablespace sales_ts 3 parallel (degree 4) 4 BUILD IMMEDIATE

22、REFRESH FAST 5 ENABLE QUERY REWRITE 6 AS 7 select s.zip, duct_type 8 , sum(s.amount) 9 from sales s, product p 10 where duct_id = duct_id 11 group by s.zip, duct_type;物化視圖和查詢重寫: 舉例SQL select s.zip, duct_type, sum(s.amount) 2 from sales s, product p 3 where duct_id = p.p

23、roduct_id 4 group by s.zip, duct_type;OPERATION NAME- -SELECT STATEMENT TABLE ACCESS FULL SALES_SUMMARY查詢重寫的使能和控制初始化參數(shù)： OPTIMIZER_MODE QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY動態(tài)的和會話級參數(shù)： QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY新的提示： REWRITE NOREWRITE查詢重寫使不能 : 舉例SQL select /*+ NOREWRITE

24、 */ 2 s.zip, duct_type, sum(s.amount) 3 from sales s, product p 4 where duct_id = duct_id 5 group by s.zip, duct_type;OPERATION NAME- -SELECT STATEMENT SORT GROUP BY HASH JOIN TABLE ACCESS FULL SALES . . .高吞吐量High，主要是插入和更新數(shù)據(jù)量持續(xù)地、大量地增長由多用戶并發(fā)訪問調(diào)整目標(biāo): 可用性 速度 并發(fā)性 可恢復(fù)性O(shè)LTP系統(tǒng)OLTP需求明確的空間

25、分配索引: 不能太多 (B-樹比位圖更適合) 對于有順序的數(shù)據(jù)列可使用顛倒鍵 定期重建在聯(lián)結(jié)查詢中使用聚集: 對增長的表建立Index聚集 對穩(wěn)定的表建立Hash聚集OLTP需求短小的事務(wù)不需要大的回滾段；多個回滾段可以防止沖突發(fā)生需要一個較大的MINEXTENTS值SQL create rollback segment rbs01 2 storage (initial 100k next 100k 3 minextents 20 maxextents 121 4 optimal 400k ) 5 tablespace rbs;OLTP 應(yīng)用問題使用數(shù)據(jù)庫的約束，而不是應(yīng)用 代碼保證代碼共享使

26、用捆綁變量，而不是字面值，可以更理想地共享SQL查詢大批量數(shù)據(jù)使用大量的全表掃描調(diào)整目標(biāo): 快速的響應(yīng)時間 準(zhǔn)確性 可用性并行查詢是專門為DSS環(huán)境設(shè)計(jì)的DSS系統(tǒng)DataDataDSS需求存儲分配:仔細(xì)設(shè)置db_block_size和 db_file_multiblock_read_count保證范圍大小是這個參數(shù)的倍數(shù)定期運(yùn)行ANALYZEDSS需求評價對索引的需求: 如有可能，使用位圖索引 對于PK的范圍查詢，可以使用索引組織表 為不均勻分布的索引列生成直方圖聚集: 考慮hash聚集來提高訪問性能DSS應(yīng)用問題分析時間不太重要執(zhí)行計(jì)劃必須優(yōu)化 使用并行查詢特點(diǎn) 精心調(diào)整，如果可能，使用提

27、示 對實(shí)際數(shù)據(jù)量進(jìn)行測試 考慮在查詢邏輯中使用PL/SQL函數(shù)捆綁變量是有問題的OLTP和DSS的組合 依賴幾種配置的混合系統(tǒng)多用途的應(yīng)用DataData混合系統(tǒng)OLTPDSSPerforms index searches More full table scansUses B-tree indexesUses bitmap indexesUses reverse key indexes Uses IOT tablesNeeds more, small rollback Fewer, large rollback segmentssegmentsShould not use parallel

28、query Employs parallel query for large operationsPCTFREE according toPCTFREE can be set to 0expected update activityShared code and bind variablesLiteral variables and hintsUses ANALYZE indexesHistograms generation混合系統(tǒng)的參數(shù)內(nèi)存使用: SHARED_POOL_SIZE LARGE_POOL_SIZE DB_BLOCK_BUFFERS SORT_AREA_SIZE并行查詢: 為DS

29、S重新配置參數(shù)混合系統(tǒng)的配置聯(lián)機(jī)回滾段: 白天較多的小回滾段 晚上較少的大回滾段多線索服務(wù)器 (MTS): 高峰時間使用，DSS不使用 應(yīng)用需求和SQL調(diào)整 監(jiān)控鎖沖突調(diào)整數(shù)據(jù)庫的應(yīng)用鎖機(jī)制自動管理數(shù)據(jù)并發(fā)的高層次 DML事務(wù)的行級鎖 查詢不需要鎖數(shù)據(jù)一致性的可變層次排它鎖模式和共享鎖模式鎖保持到提交或回滾發(fā)生兩種類型的鎖DML或數(shù)據(jù)鎖: 表級鎖 行級鎖DDL或字典鎖(TM)(TX)DML鎖一個DML事務(wù)至少獲得兩個鎖: 一個共享的表級鎖 一個排它的行級鎖鎖(ENQUEUE)機(jī)制記錄: 等待鎖的用戶 請求的鎖模式 用戶請求鎖的次序表級鎖模式自動獲得:行排它 (RX): INSERT, UPDATE, DELETE行共享 (RS): SELECT. FOR UPDATE表級鎖模式使用LOCK語句手工獲得:SQL LOCK TABLE table_name IN mode_name MODE;Share(S) 不允許DML 隱含使用引用完整性表級鎖模式使用LOCK語句手工獲得:共享的行排它 (SRX) 不允許DML或共享模式 隱含使用引用完整性排它 (X)數(shù)據(jù)塊中的DML鎖Row 6Block HeaderLock bytesRow 112TX slot 1