数据库性能调整

1、 调整概述 调整Oracle的内存使用 调整Oracle的磁盘利用 调整数据库的应用数据库性能调整 应用需求和SQL调整 监控锁冲突调整数据库的应用数据库管理的角色应用调整是调整中最重要的部分数据库管理员并不直接参与应用调整数据库管理员必须熟悉不好的SQL语句对数据库性能的所产生的重要影响诊断工具概述EXPLAIN PLANSQL跟踪和TKPROFSQL*Plus的AUTOTRACEOracle SQL的ANALYZE命令Explain Plan使用explain plan时，可以不使用跟踪使用explain plan：1. 使用utlxplan.sql脚本创建PLAN_TABLE表2. 运行

2、EXPLAIN PLAN SQL命令3. 查询PLAN_TABLE表，显示SQL语句的执行计划SQL $ORACLE_HOME/rdbms/admin/utlxplanSQL跟踪和TKPROF1. 设置初始化参数2. 调用SQL跟踪3. 运行应用4. 关闭SQL跟踪5. 使用TKPROF格式化跟踪文件6. 解释输出SQL跟踪的使能及使不能实例级: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统计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创建PLAN_TABLE表运行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 优化

5、器模式Rule-based: 使用一个分级系统 语法和数据字典驱动的Cost-based: 选择代价最小的路径 统计驱动的优化器模式的设置实例级:optimizer_mode = choose|rule|first_rows|all_rows会话级:alter session set optimizer_mode = choose|rule|first_rows|all_rows语句级:使用提示管理统计使用ANALYZE命令可以收集或删除统计使用DBMS_STATS包: GATHER_TABLE_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATH

6、ER_DATABASE_STATS表统计表的行数表的数据块个数和空的数据块数可用的平均空闲空间链接或迁移的行数行的平均长度最后的ANALYZE日期和样本大小数据字典视图: DBA_TABLES索引统计索引层次 (高度)叶块和不同键的个数每个键的叶块平均个数每个键的数据块平均个数索引条目的个数聚集因子数据字典视图: DBA_INDEXES列统计不同值的个数最小值，最大值最后的ANALYZE日期和样本大小数据字典视图: USER_TAB_COL_STATISTICS直方图以更详细的方式描述一个特殊列的数据分布为不均衡分布数据做更好的选择性评估使用ANALYZE TABLE . FOR COLUMN

7、S 命令创建直方图 数据字典视图: DBA_HISTOGRAMSOracle Corporation Confidential1数据库之间统计复制Copyto user tableExportImportCopy usertable to DD432Oracle Corporation Confidential举例: 统计复制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 */);优化器计划的稳定性允许应用强制使用一个所希望的SQL访问路径通过数据库的改变维护一致性的执行计划使用由提示构成的存储纲要来实现计划同等SQL语句的文本必须匹配计划的维护，通过: 新的Oracle版本 新的对象统计 初始化参数改变 数据库重组织 模式改

9、变创建存储纲要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参数设置成 TRUE或一个目录名CREATE

10、_STORED_OUTLINES和USE_STORED_OUTLINES都可以在实例级或会话级设置SQL alter session 2 set USE_STORED_OUTLINES = train;SQL select . from .;维护存储纲要使用OUTLN_PKG包可以: 删除纲要或纲要的目录 重命名目录使用ALTER OUTLINE命令可以: 重命名一个纲要 重建一个纲要 改变一个纲要的目录纲要存储在OUTLN模式中 数据访问方法为了提高性能，可以使用以下数据访问方法: 索引 (B-树, 位图, 颠倒键) 索引组织表 聚集 直方图 物化视图B-树索引Index entry hea

11、derKey column lengthKey column valueROWIDRootBranchLeafIndex entry位图索引TableIndexBlock 10Block 11Block 12File 3位图索引用于低基数的列适合多个谓词使用最少的存储空间适合只读的系统适合非常大的表创建和维护位图索引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. . .创建颠倒键索引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;索引组织表索引组织表与常规表的比较更快地基于键值访问表数据减少存储的需求次要索引和逻辑ROWID主要限制: 必须有一个主键 不能使用唯一性约束 不能被聚集创建索引组织表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聚集类型适合使用何种类型聚集的情况规范规范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查询的实例化可以用来查询重写刷新类型: 完全或快速 强制或从不刷新模式: 手工 自动 (同步或异步) 物化视图: 手工刷新刷新指定的物化视图:基于一个或多个基表的物化视图:应当刷新的全部物化视图:DBMS_MVIEW.REFRESH(SF_SALES, parallelism = 10);DBMS_MVIEW.REFRESH_DEPENDENT(SALES);DBM

21、S_MVIEW.REFRESH_ALL_MVIEWS;查询重写为了使用物化视图，而不是基表，一个查询必须重写查询重写是透明的，不需要对物化视图有任何特殊权限物化视图的查询重写可以使能或使不能查询重写初始化参数QUERY_REWRITE_ENABLED必须设置成TRUEQUERY REWRITE权限可以允许用户使能物化视图DBMS_OLAP包具有使用物化视图的选项物化视图和查询重写: 举例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查询重写的使能和控制初始化参数： OPTIMIZER_MODE QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY动态的和会话级参数： 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，主要是插入和更新数据量持续地、大量地增长由多用户并发访问调整目标: 可用性 速度 并发性 可恢复性OLTP系统OLTP需求明确的空间

25、分配索引: 不能太多 (B-树比位图更适合) 对于有顺序的数据列可使用颠倒键 定期重建在联结查询中使用聚集: 对增长的表建立Index聚集 对稳定的表建立Hash聚集OLTP需求短小的事务不需要大的回滚段；多个回滚段可以防止冲突发生需要一个较大的MINEXTENTS值SQL create rollback segment rbs01 2 storage (initial 100k next 100k 3 minextents 20 maxextents 121 4 optimal 400k ) 5 tablespace rbs;OLTP 应用问题使用数据库的约束，而不是应用 代码保证代码共享使

26、用捆绑变量，而不是字面值，可以更理想地共享SQL查询大批量数据使用大量的全表扫描调整目标: 快速的响应时间 准确性 可用性并行查询是专门为DSS环境设计的DSS系统DataDataDSS需求存储分配:仔细设置db_block_size和 db_file_multiblock_read_count保证范围大小是这个参数的倍数定期运行ANALYZEDSS需求评价对索引的需求: 如有可能，使用位图索引 对于PK的范围查询，可以使用索引组织表 为不均匀分布的索引列生成直方图聚集: 考虑hash聚集来提高访问性能DSS应用问题分析时间不太重要执行计划必须优化 使用并行查询特点 精心调整，如果可能，使用提

27、示 对实际数据量进行测试 考虑在查询逻辑中使用PL/SQL函数捆绑变量是有问题的OLTP和DSS的组合 依赖几种配置的混合系统多用途的应用DataData混合系统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混合系统的参数内存使用: SHARED_POOL_SIZE LARGE_POOL_SIZE DB_BLOCK_BUFFERS SORT_AREA_SIZE并行查询: 为DS

29、S重新配置参数混合系统的配置联机回滚段: 白天较多的小回滚段 晚上较少的大回滚段多线索服务器 (MTS): 高峰时间使用，DSS不使用 应用需求和SQL调整 监控锁冲突调整数据库的应用锁机制自动管理数据并发的高层次 DML事务的行级锁 查询不需要锁数据一致性的可变层次排它锁模式和共享锁模式锁保持到提交或回滚发生两种类型的锁DML或数据锁: 表级锁 行级锁DDL或字典锁(TM)(TX)DML锁一个DML事务至少获得两个锁: 一个共享的表级锁 一个排它的行级锁锁(ENQUEUE)机制记录: 等待锁的用户 请求的锁模式 用户请求锁的次序表级锁模式自动获得:行排它 (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)数据块中的DML锁Row 6Block HeaderLock bytesRow 112TX slot 1