1 / 75

数据库性能调整

数据库性能调整. 调整概述 调整 Oracle 的内存使用 调整 Oracle 的磁盘利用 调整 数据库的应用. 调整数据库的应用. 应用需求和 SQL 调整 监控锁冲突. 数据库管理的角色. 应用调整是调整中最重要的部分 数据库管理员并不直接参与应用调整 数据库管理员必须熟悉不好的 SQL 语句对数据库性能的所产生的重要影响. 诊断工具概述. EXPLAIN PLAN SQL 跟踪和 TKPROF SQL*Plus 的 AUTOTRACE Oracle SQL 的 ANALYZE 命令. Explain Plan.

hamilton-noble
Download Presentation

数据库性能调整

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 数据库性能调整 • 调整概述 • 调整Oracle的内存使用 • 调整Oracle的磁盘利用 • 调整数据库的应用

  2. 调整数据库的应用 • 应用需求和SQL调整 • 监控锁冲突

  3. 数据库管理的角色 • 应用调整是调整中最重要的部分 • 数据库管理员并不直接参与应用调整 • 数据库管理员必须熟悉不好的SQL语句对数据库性能的所产生的重要影响

  4. 诊断工具概述 • EXPLAIN PLAN • SQL跟踪和TKPROF • SQL*Plus的AUTOTRACE • Oracle SQL的ANALYZE命令

  5. Explain Plan • 使用explain plan时，可以不使用跟踪 • 使用explain plan： 1. 使用utlxplan.sql脚本创建PLAN_TABLE表 2. 运行EXPLAIN PLAN SQL命令 3. 查询PLAN_TABLE表，显示SQL语句的执行计划 SQL> @$ORACLE_HOME/rdbms/admin/utlxplan

  6. SQL跟踪和TKPROF 1. 设置初始化参数 2. 调用SQL跟踪 3. 运行应用 4. 关闭SQL跟踪 5. 使用TKPROF格式化跟踪文件 6. 解释输出

  7. SQL跟踪的使能及使不能 • 实例级:SQL_TRACE = {TRUE|FALSE} • 会话级: SQL> alter session set SQL_TRACE = {true|false};SQL> execute DBMS_SESSION.SET_SQL_TRACE 2 ({true|false});SQL> execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION 2 (session_id, serial_id, {true|false});

  8. 使用TKPROF格式化跟踪文件 $ tkprof tracefile.trc output.txt [options] tracefile.trc output.txt USER_DUMP_DEST

  9. TKPROF统计 • Count: Number of execution calls • CPU: CPU seconds used • Elapsed: Total elapsed time • Disk: Physical reads • Query: Logical reads for consistent read • Current: Logical reads in current mode • Rows: Rows processed

  10. SQL*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 ]

  11. 优化器模式 • Rule-based: • 使用一个分级系统 • 语法和数据字典驱动的 • Cost-based: • 选择代价最小的路径 • 统计驱动的

  12. 优化器模式的设置 • 实例级: optimizer_mode = {choose|rule|first_rows|all_rows} • 会话级: alter session set optimizer_mode = {choose|rule|first_rows|all_rows} • 语句级: 使用提示

  13. 管理统计 • 使用ANALYZE命令可以收集或删除统计 • 使用DBMS_STATS包: • GATHER_TABLE_STATS • GATHER_INDEX_STATS • GATHER_SCHEMA_STATS • GATHER_DATABASE_STATS

  14. 表统计 • 表的行数 • 表的数据块个数和空的数据块数 • 可用的平均空闲空间 • 链接或迁移的行数 • 行的平均长度 • 最后的ANALYZE日期和样本大小 • 数据字典视图: DBA_TABLES

  15. 索引统计 • 索引层次 (高度) • 叶块和不同键的个数 • 每个键的叶块平均个数 • 每个键的数据块平均个数 • 索引条目的个数 • 聚集因子 • 数据字典视图: DBA_INDEXES

  16. 列统计 • 不同值的个数 • 最小值，最大值 • 最后的ANALYZE日期和样本大小 • 数据字典视图: USER_TAB_COL_STATISTICS

  17. 直方图 • 以更详细的方式描述一个特殊列的数据分布 • 为不均衡分布数据做更好的选择性评估 • 使用ANALYZE TABLE ... FOR COLUMNS …命令创建直方图 • 数据字典视图: DBA_HISTOGRAMS

  18. 数据库之间统计复制 Data dictionary User-defined statistics table Export Import Copy user table to DD Copy to user table 3 2 4 1 User-defined statistics table Data dictionary Oracle Corporation Confidential

  19. 举例: 统计复制 DBMS_STATS.CREATE_STAT_TABLE ('TRAIN' /* schema name */ ,'STATS' /* statistics table name */ ,'USERS' /* tablespace */ ); DBMS_STATS.EXPORT_TABLE_STATS (‘TRAIN’ /* schema name */ ,‘COURSES’ /* table name */ ,NULL /* no partitions */ ,‘STATS’ /* statistics table name */ ,‘CRS990601’/* id for statistics */ ,TRUE /* index statistics */ ); Oracle Corporation Confidential

  20. 优化器计划的稳定性 • 允许应用强制使用一个所希望的SQL访问路径 • 通过数据库的改变维护一致性的执行计划 • 使用由提示构成的存储纲要来实现

  21. 计划同等 • SQL语句的文本必须匹配 • 计划的维护，通过: • 新的Oracle版本 • 新的对象统计 • 初始化参数改变 • 数据库重组织 • 模式改变

  22. 创建存储纲要 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;

  23. 使用存储纲要 • 将USE_STORED_OUTLINES参数设置成 TRUE或一个目录名 • CREATE_STORED_OUTLINES和USE_STORED_OUTLINES都可以在实例级或会话级设置 SQL> alter session 2 set USE_STORED_OUTLINES = train; SQL> select ... from ...;

  24. 维护存储纲要 • 使用OUTLN_PKG包可以: • 删除纲要或纲要的目录 • 重命名目录 • 使用ALTER OUTLINE命令可以: • 重命名一个纲要 • 重建一个纲要 • 改变一个纲要的目录 • 纲要存储在OUTLN模式中

  25. 数据访问方法 • 为了提高性能，可以使用以下数据访问方法: • 索引 (B-树, 位图, 颠倒键) • 索引组织表 • 聚集 • 直方图 • 物化视图

  26. B-树索引 Index entry Root Branch Index entry header Key column length Leaf Key column value ROWID

  27. Start ROWID End ROWID Key Bitmap <Blue, 10.0.3, 12.8.3, 1000100100010010100> <Green, 10.0.3, 12.8.3, 0001010000100100000> <Red, 10.0.3, 12.8.3, 0100000011000001001> <Yellow, 10.0.3, 12.8.3, 0010001000001000010> 位图索引 File 3 Table Block 10 Block 11 Block 12 Index

  28. 位图索引 • 用于低基数的列 • 适合多个谓词 • 使用最少的存储空间 • 适合只读的系统 • 适合非常大的表

  29. 创建和维护位图索引 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 indx01;

  30. B-树索引与位图索引的比较 B-Tree indexes Bitmap indexes Suitable for high-cardinality Suitable for low-cardinality columns columns Updates on keys relatively Updates to key columns very inexpensive expensive Inefficient for queries using Efficient for queries usingOR predicates OR predicates Row-level locking Bitmap segment-level locking More storage Less storageUseful for OLTP Useful for DSS

  31. 颠倒键索引 KEY ROWID ----- ------------------- 1257 0000000F.0002.0001 2877 0000000F.0006.0001 4567 0000000F.0004.0001 6657 0000000F.0003.0001 8967 0000000F.0005.0001 9637 0000000F.0001.0001 9947 0000000F.0000.0001 ... ... EMPNO ENAME JOB ... ----- ----- -------- --- 7499 ALLEN SALESMAN 7369 SMITH CLERK 7521 WARD SALESMAN ... 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER ... ... ... Index on EMPNO EMP Table

  32. 创建颠倒键索引 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(c2); SQL> alter index i2_t1 REBUILD REVERSE;

  33. 索引组织表 常规表的访问 索引组织表的访问 ROWID Non-key columns Key column Row header

  34. 索引组织表与常规表的比较 • 更快地基于键值访问表数据 • 减少存储的需求 • 次要索引和逻辑ROWID • 主要限制: • 必须有一个主键 • 不能使用唯一性约束 • 不能被聚集

  35. 创建索引组织表 SQL> create table sales 2 (office_cd number(3) 3 ,qtr_end date 4 ,revenue number(10,2) 5 ,review varchar2(1000) 6 ,constraint sales_pk 7 PRIMARY KEY (office_cd,qtr_end) 8 ) 9 ORGANIZATION INDEX tablespace indx 10 PCTTHRESHOLD 20 11 INCLUDING revenue 12 OVERFLOW TABLESPACE user_data;

  36. 索引组织表的行溢出 INDX tablespace USER_DATA tablespace Segment = SALES_PK IOT_type = IOT Segment_type = INDEX Index_type = IOT - TOP Segment = SYS_IOT_OVER_n IOT_type = IOT_OVERFLOW Segment_type = TABLE Remaining row part Rows within PCTTHRESHOLD

  37. 索引组织表的字典视图 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_OVERFLOW SQL> select index_name,index_type,tablespace_name,table_name 2 from DBA_INDEXES; INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME ------------- ---------- ---------- ---------- SALES_PK IOT - TOP INDX SALES SQL> 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

  38. ORD_NO PROD QTY ... ----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 ORD_NO ORD_DT CUST_CD ------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45 聚集 非聚集的ORD和ITEM表 非聚集的ORD和ITEM表

  39. 聚集类型 Index cluster Hash cluster Hash function

  40. 适合使用何种类型聚集的情况 规范 Uniform key distribution Evenly distributed key values Rarely updated key Often joined master-detail tables Predictable number of key values Queries using equality predicate on key Index X X X Hash X X X X X

  41. 物化视图 • 一个SQL查询的实例化 • 可以用来查询重写 • 刷新类型: • 完全或快速 • 强制或从不 • 刷新模式: • 手工 • 自动 (同步或异步)

  42. 物化视图: 手工刷新 • 刷新指定的物化视图: • 基于一个或多个基表的物化视图: • 应当刷新的全部物化视图: DBMS_MVIEW.REFRESH(‘SF_SALES’, parallelism => 10); DBMS_MVIEW.REFRESH_DEPENDENT(‘SALES’); DBMS_MVIEW.REFRESH_ALL_MVIEWS;

  43. 查询重写 • 为了使用物化视图，而不是基表，一个查询必须重写 • 查询重写是透明的，不需要对物化视图有任何特殊权限 • 物化视图的查询重写可以使能或使不能

  44. 查询重写 • 初始化参数QUERY_REWRITE_ENABLED必须设置成TRUE • QUERY REWRITE权限可以允许用户使能物化视图 • DBMS_OLAP包具有使用物化视图的选项

  45. 物化视图和查询重写: 举例 SQL> create MATERIALIZED VIEW sales_summary 2 tablespace sales_ts 3 parallel (degree 4) 4 BUILD IMMEDIATE REFRESH FAST 5 ENABLE QUERY REWRITE 6 AS 7 select s.zip, p.product_type 8 , sum(s.amount) 9 from sales s, product p 10 where s.product_id = p.product_id 11 group by s.zip, p.product_type;

  46. 物化视图和查询重写: 举例 SQL> select s.zip, p.product_type, sum(s.amount) 2 from sales s, product p 3 where s.product_id = p.product_id 4 group by s.zip, p.product_type; OPERATION NAME ---------------------- ----------------- SELECT STATEMENT TABLE ACCESS FULL SALES_SUMMARY

  47. 查询重写的使能和控制 • 初始化参数： • OPTIMIZER_MODE • QUERY_REWRITE_ENABLED • QUERY_REWRITE_INTEGRITY • 动态的和会话级参数： • QUERY_REWRITE_ENABLED • QUERY_REWRITE_INTEGRITY • 新的提示： • REWRITE • NOREWRITE

  48. 查询重写使不能 : 举例 SQL> select /*+ NOREWRITE */ 2 s.zip, p.product_type, sum(s.amount) 3 from sales s, product p 4 where s.product_id = p.product_id 5 group by s.zip, p.product_type; OPERATION NAME ----------------------------- ----------- SELECT STATEMENT SORT GROUP BY HASH JOIN TABLE ACCESS FULL SALES . . .

  49. OLTP系统 • 高吞吐量High，主要是插入和更新 • 数据量持续地、大量地增长 • 由多用户并发访问 • 调整目标: • 可用性 • 速度 • 并发性 • 可恢复性

  50. OLTP需求 • 明确的空间分配 • 索引: • 不能太多 (B-树比位图更适合) • 对于有顺序的数据列可使用颠倒键 • 定期重建 • 在联结查询中使用聚集: • 对增长的表建立Index聚集 • 对稳定的表建立Hash聚集

More Related