|
|
因为以前写过一篇Index Enhancements in 10G(在我的blog上)的,
所以这个就算第二篇了.
参考的文档:
Oracle10G Performance Tuning Guide
Oracle10G New Features
1. 简介
一直以来，如何有效的诊断和监控高负载的SQL对于DBA
来说不是件容易的事情.但是从Oracle 10G 开始, ADDM的
引入使得由数据库自身参与监控成为可能.尽管通过ADDM
发现出的高负载的SQL可能只会占全部SQL的很小一部分
比例,但是如何对这些语句进行调优依然需要经验的积累.
2. 手工调优SQL的挑战
对SQL语句手工调优需要很多的经验和技巧,例如:
. 对SQL语句本身进行优化以便获得更优的执行计划;
. 合理地调整数据读取方式(例如通过索引)以便能更快地
访问数据;
. 合理的设计SQL实现方式以实现最优的架构(例如:使用
静态SQL还是动态SQL)
当然,手工调优又是一件非常耗时的工作,因为:
1. 每条SQL可能都是唯一的,这意味着你需要分别优化不同
的SQL;
2. 系统可能是很复杂的系统,SQL代码非常非常多;
3. SQL调整可能是一件永远看不到终点的任务,因为系统中的
SQL workload可能经常在变化;
4. 一条原来运行效率很高的SQL, 因为数据的改变或者存取
方式的改变 (例如删除了索引或者索引已经失效)可能会
导致性能的降低.
...
3. 自动SQL调优
SQL Tuning Advisor (SQL调优顾问)是Oracle 10G中引入的新
特性之一.设计它的目的就是为了代替传统的手工SQL调整
(DBA和开发人员在工作中往往要花费很多时间和精力在
SQL调整上).
SQL调优顾问处理的对象包括那些响应时间很慢或者是
占用CPU/DISK很高的SQL.
SQ调优顾问收集这些SQL,并且给出自己的建议,它包括下
面的部分:
. 怎样调整SQL的执行计划;
. 做出这条建议的理论原理;
. 优化后效率的提升幅度;
. 直接给出推荐使用的命令
用户可以有选择性地接受这些建议，然后去调优SQL.
随着SQL调优顾问的引入,你现在就可以让Oracle优化器来
自动的为你调整SQL.
4. 概述: SQL调优顾问
首先，我们应该知道, SQl调优顾问只是调优过程的一个
驱动程序. 它只是以一种内部的，特殊的方式调用CBO来
分析以下存在的对象:
4.1 缺失或者失效的统计信息:
我们都知道，如果想让CBO有效的工作，就必须提供最新
的统计信息. 如果一个SQL语句中引用的对象的统计信息不
存在或者已经陈旧过时,它们就会被捕捉到, 并且作为建议的
一部分;
4.2 SQL Profile(计划调整分析):
CBO为一些特定的SQL收集一些后台的信息作为SQL profile的
组成部分,它包括:
. SQL语句的历史运行信息
. 实际运行代价和估计运行代价的比较
. 谓词选择
实际上,CBO会在后台运行一条SQL语句中的部分子句,以产生
一个复杂的SQL profile,这样就使CBO能够根据这些附加的信息
来为一个复杂的SQL产生更优的执行计划.
SQL Profile被存放在数据字典中,因此不需要应用程序的干预.
4.3 SQL访问路径的分析:
有些情况下, CBO 会推荐使用一个新的索引，这个索引能够
提供更快的执行路径, SQL Access Advisor 可以完成这个工作.
4.4 重新构建SQL
CBO 会鉴定出哪些SQL语句的执行计划非常糟糕，并且给出
相关的建议去重新调整. 推荐使用的SQL语句可能与原有的
SQL语句非常相似, 因此DBA或者开发人员可以根据具体的
应用来灵活的选择.
5. 怎样检测缺失或者陈旧的统计信息
使用从9i开始引入的DBMS_STATS包,例如:
DBMS_STATS.GATHER_TABLE_STATS(
ownname=>'SCOTT',tabname=>'CUSTOMERS',
estimate_percent=>dbms_stats.auto_sample_size);
精确的统计信息是CBO产生最优化执行计划的前提条件.从
Oracle 10G开始引入了Automaic Statistics Gathering
(初始化参数STATISTICS_TYPE设成BASIC或者ALL),如果
没有激活这个特性, 统计信息可能就会丢失或者陈旧.
在统计检测模式下,Oracle优化器会执行一些额外的检查以
便验证需要使用的统计信息,并且会适时的提醒去产生需要
的统计信息.
6. More information about SQL profile
通常情况下,CBO会根据当前的统计信息为一条SQL语句产生
一个最优的执行计划。随着数据的改变或者存取方式的改变，
这个计划已经不是最优的执行计划了,由于没有可用的
SQL Profile, CBO会一直认为当前的统计信息还是很精确的,
从而做出了错误的选择. 而如果使用了SQL Profile, CBO会花
费一些时间去根据这个SQL语句去搜集一些附加的信息,从
而得到更精确的统计信息和产生更优的执行计划，这个过
程可能会比较耗时,因为优化器可能要做下面的尝试:
. 执行原有SQL的一部分和使用动态抽样技术来收集更准确
的统计信息;
. 进行更深层次的谓词选择: 例如分析列和列之间的相互关系，
使用不同的JOIN 方式，以及使用更复杂的谓词(加入函数
等等)
. 必要的时候，数据统计信息可能在统计检测模式下就已经
产生了,这样就会避免统计信息失效或者陈旧对CBO带来的
不良影响.
基于这些统计信息, 优化器就会根据SQL Profile来进行自我
调整.
7. More information about SQL Analysis
简而言之,SQL Analysis的作用就是帮助DBA和开发人员捕捉
那些低效率的SQL, 并且提供自己的修改建议.
例如, NOT EXISTS 和 NOT IN 的构造器很相似,但是不完全
一样.因此，需要你自己去决定应该使用哪一个.也正是基于
这样的考虑, CBO 不会自动替你重写整个查询语句而只是
提供建议:
SQl Analysis能够检测以下类型的SQL:
1. 在查询中使用 NOT IN 而不是使用 NOT EXISTS
(说明: Oracle 10G中,如果相关表的相关列上有索引,CBO会为
使用了 NOT IN的语句选择最优的查询计划,而不是简单的
使用全表扫描)
2. 如果索引列上的数据类型不匹配，会引入更复杂的谓词
来预防笛卡儿集的产生
8. 怎样去使用SQL Tuning Advisor
SQL Tuning Advisor 检测的SQL来源于很多部分:
. ADDM 检测到的高负荷的SQL
. 当前共享池中和游标池中的SQL
. AWR快照中反映的SQL: 缺省情况下,AWR会保持近一个星
期的快照信息, 因此用户可以使用近七天中任何一个时间
段的历史信息来选择需要调整的SQL语句.
. 用户自定义的workload: 用户可以创建自定义的workload,
例如根据自己的需要来选择SQL语句,这些语句可能不是
高负荷的，可能也不在当前的游标池中
. 对于多个SQL语句,用户可以创建SQL 优化集
(SQL Tuning Set),一个STS 可以存储多个SQL语句以及它们
的执行信息:
执行上下文: 包括绑定变量和具体方案的解析信息
执行的统计信息: 执行的次数，平均执行的时间
9. 在EM 10G 中使用SQL Advisor Analyzer
STEP 1: 从下面的页面找到有问题的SQL
. ADDM report page
. Top SQL page
. SQL Tuning Set (STS) page
STEP 2: 查看 SQL Tuning Advisor 提供的建议
STEP 3: 根据这些建议去实施
对于普通用户，需要以下权限:
ADVISOR
CREATE JOB
EXECUTE ON SYS.DBMS_SQLTUNE
如果需要创建STS,还需要ADMINISTER_TUNING_SET的权限
10. 调用DBMS_SQLTUNE包来使用SQL Advisor Analyzer |
|
IP卡
狗仔卡
发表于 2005-3-12 18:42
收藏
提升卡
置顶卡
变色卡
显身卡
发表于 2005-7-23 07:49