改进数据库性能-SQL查询优化

  首先、这个部分主要是针对oracle11g（管理艺术）相关章节做的笔记，方便自己有一个系统全面的理论指导。

 

调优的方法论：

1. 正确设计应用程序
2. 调优应用程序的SQL代码
3. 优化内存
4. 优化IO
5. 优化争用和其他的问题

 

oracle11g增强了主动调优proactive tuning,被动的性能优化reactive performance tuning

 

优化SQL的查询处理：

首先需要了解SQL处理的过程和优化要点

SQL语句经历了语句分析parsing、优化optimizing、和执行executing阶段，如果是查询语句，数据要能够被检索，那么在SQL处理之前还有一个额外的获取（fetch）阶段

 

语法分析：

主要有语法和语义，分解成关系代数查询，检查过程中参照数据字典（缓存和基表）

 

优化：

使用优化程序CBO来选择最好的访问方法来查询所涉及的表和索引的检索数据，分为查询重写阶段和物理执行计划生成

执行计划生成阶段：

• 查询执行的操作
• 操作执行的次序
• 执行每个操作的算法
• 从磁盘或者内存检索数据的最好方式
• 查询期间从一个操作向另一个操作传送数据的最好方式

生成几个有效的物理查询计划，然后对他们进行成本估算，选择估算值最低的执行计划。

 

执行阶段：select-直接返回查询结果：insert update delete语句记录被修改

 

最重要的就是查询优化处理阶段---它决定了快速检索数据所涉及的所有重要问题。

 

统计数据：

oracle11g一般每天晚上10-早上6点以及周末的全天打开。gather_stats_job作业，可以禁用

gather_stats_job为所有没有优化程序统计数据，对一个对象，如果修改了10%的数据-就统计，可以通过DBA_TAB_MODIFICATIONS视图相关更改信息

DBA_tables—last_analyzed 提示的上次统计的时间，sample_size采样（表大小和采样百分比没有关系），没有变化的数据就不会做统计分析

 

必要的统计数据：

• 表行数
• 每个数据块的行数
• 行的平均长度
• 表的总块数
• 每个索引的级别数
• 每个索引叶块的数目
• 表中每一个列的不同数据值的数目
• 数据分布柱状图
• 索引建的数目
• 基数
• 每个列的最大和最小值

OPTIMIZER_MODE参数

all_rows，这个是默认的，以实现最大吞吐量

first_row_n：实现响应时间

first_row：使用探测法（经验规则）无论是否有统计数据，响应时间最小。

 

优化程序做什么：

1、SQL转换

常用的转换：

• 将IN转换or语句
• 将or转换为union或者union all
• 将非直接相关的嵌套的选择语句转换为更有效的连接（jion）
• 将外联结转换为更有效的内链接（inner jion)
• 将复杂的子查询转换为联结、半联结或者反联结（antijoin）
• 将基于星型模式的数据仓库进行星型转换
• 将between转换为大于等于或者小于等于语句

2、选择访问路径

• 全表扫描
• 通过rowid对表进行访问
• 索引扫描

3、选择联结方法

• 嵌套循环联结 nested-loop join（驱动表每行数据都要读取内部表的所有行）
• 散列联结 hash join
• 分类合并联结 sort-merge join

4、选择联结次序

联结方式消除最大数目的行数据

 

CBO缺点：

• 版本不稳定带来计划不稳定，使用stored outline
• 有时候不是最好的，可以了解应用的人强制hint
• 依赖统计数据

收集统计数据：

DBMS_stats

必须手工收集统计数据：

• 外部表
• 需要收集系统统计数据
• 大型作业之后

可以收集数据库、表、索引、用户模式

gather_database_statistics

gather_schema_statistics

gather_table_statistics

gather_index_statistics

 

estimate_percent--统计的百分比，如果是null就是全部

method_opt执行是否收集直方图

granularity ：all 所有表的子分区、分区

cascade=yes就是所有的索引统计数据

option

  gather为所有的对象搜索统计信息

  gather_auto只对那些ORACLE人为必要的对象收集

  gather_empty为没有收集过的收集统计信息

gather_stale只对那些过期的收集

 

收集系统统计数据

DBMS_STATS.GATHER_SYSTEM_STATS

包括IO 和CPU（无负荷模式和负荷模式）

 

收集数据字典统计数据

DBMS_STATS.gather_database_stats  中gather_sys=yes

DBMS_STATS.gather_dictionary_stats

 

动态采样optimizer_dynamic_sampling在表中不存在统计数据或者信息过旧的时候，让ORACLE即时估算优化程序统计数据。

 

 

高效SQL

• 使用函数索引
• 使用右联结
• 使用case
• 执行高效的子查询
• 使用where代替having
• 最小化表查询

联结方法：

• 避免笛卡尔联结
• 嵌套循环
• 散列联结
• 合并联结

索引：

对什么进行索引

• 对高选择行进行索引
• 对重要的外键
• 对所有的谓词
• 表联结的列

B树索引

位图索引

IOT索引组织表

反向索引

函数索引

组合索引

 

使用绑定变量

避免全表扫描

 

DBA：

分区表

压缩技术

物化视图

使用存储大纲保证稳定性

 

SQL计划管理SPM

SQL baseline

并行执行

 

性能分析工具

explain plan

utlxplan.sql--创建分析表

utlxpls.sql格式化

 

autotrace

1. utlxplan.sql--创建分析表
2. plustrce.sql授权

 

SQLTRACE+tkprof

第三方工具

 

端对端跟踪

DBMS_MONITOR

需要三个属性：

客户端标示符

服务名称

模块名称 服务名称以及活动名称的组合

利用触发器铺货客户端连接到DB的sessionID

 

 

结果高速缓存

客户端高速缓存

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/500314/viewspace-1163643/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/500314/viewspace-1163643/