已经几乎不需要修正应用了! 划时代的SQL调优方法

 

http://www.askmaclean.com/archives/sqltuning-sql-profile.html

 

  • 以前的SQL调优
  • SQL Profile是什么
  • 调优执行顺序
  • 经常被问到的问题
  • 总结

 

<一般而言SQL的调优流程>

  1. 诊断/指定瓶颈
  2. 执行合适的调优
  3. 检查效果  
  4. 根据需要进一步调优

Automatic Database Diagnostic Monitor(ADDM

ADDM是监视/诊断数据库性能的功能

发现内存不足以及I/O问题、性能较差的SQL、Real Application Clusters(RAC相关的问题等等各种问题,给数据库管理者提供建议。

那时,会为数据库管理者提供解决问题所需要进行的操作提出建议,数据库管理者就可以高效解决问题。

ADDM与其他建议相同都可以手动启动来诊断数据库,但一般而言是定期性地自动启动,监视数据库是否有性能问题。

ADDM自动启动的时机是在取得AWRsnapshot时,通过取得了snapshotMMON进程来自动启动

手动启动ADDM的情况下,可以诊断过去任一时间点的数据库。

ADDM是从两个snapshot中取得数据库的负荷信息,进行诊断。

自动启动的情况下,使用最新取得的snapshot以及1个之前的snapshot,手动启动的情况下,用户可以指定任意两个snapshot

要使用这个功能的话,需要将STATISTICS_LEVEL 初始化参数设定为TYPICAL(默认)或者ALL

 

SQL较慢的时候,首先要寻找原因

 

以前的SQL调优

<一般而言SQL的调优流程>

  • 诊断/指定瓶颈
  • STATSPACK:
    与DB整体的统计一起收集SQL统计
  • EXPLAIN PLAN:
    在每个SQL中表示执行计划
  • SQL TRACE与TKPROF:
    收集以session等单位来执行的SQL执行的统计信息,并且

总结结果报告。

  • SQL*Plus的AUTOTRACE機能:
    在每个SQL中表示执行计划以及性能统计

 

2.执行合适的调优

  • 优化的选择<到Oracle 9i 为止>
  • RBO ( Rule-based optimizer )<到Oracle 9i 为止>
  • 重新检查物理設計(Index相关)
  • SQL的改写           etc..
  • CBO ( cost-based optimizer )
  • 取得合适的对象的统计信息
  • 使用优化提示   不知所措时一定要使用这个功能
  • 检查Partitioning导入
  • 并行处理的检验   etc..

3.检查效果  =》 根据需要进一步调优

 

 

优化提示

  • 通过使用提示、可以指示优化使用特定的访问路径

      <例>

  • 指定使用合适的index
  • 指定合适的表结合方法与结合顺序
  • 指定CBO的目标(重视吞吐量 or面向 OLTP 等) etc.
  • 在使用RULE提示以外的情况下,就会自动执行CBP的最优化
  • 提示的使用例子( /*+ 与*/ 之间指定提示、直接嵌入到SQL中 )
  • 使其使用附着与sales表的customer_id列的cust_id_indx这个index。

SELECT  /*+ INDEX(sales cust_id_indx) */  sales_date , sales_amount

FROM sales WHERE customer_id=‘ABE’;

 

SELECT  /*+ USE_HASH(s c) LEADING(c s) */  *

FROM sales s , customers c

WHERE s.customer_id=c.customer_id AND s.sales_amount > 1000;

 

以前的SQL调优的缺点

HINT提示的缺点

  • 每个SQL都需要自定义
  • 要求高度的知识存量与技巧
  • 性能恶化时,需要修正应用

某些SQL语句可以高速嵌入提示,但这就不得不对应用进行修正。

package应用中无法使用提示

 

SQL Profile是什么

每个SQL中取得的固有的辅助性的统计信息

SQL Profile直到被删除或者被重新制作为止,都在数据库中保持原样

优化需要用到SQL Profile以及现存的优化统计两方面才能制成执行计划

Oracle Database 10g 开始,追加 SQL调优建议,从这里开始生成

 

简而言之 

制成服从建议的SQL profile的话,执行计划也会最优化。 =》 原因是 数据库有如提示一样可以改善执行计划的统计信息!=》

如果使用SQL profile的话、、就可以不修正应用,但又能实现与提示同样的效果!!

 

SQL profile的制成与使用

实际应用SQL profile之后,会说明直到Active session降低为止所需要等待的时间

SQL profile是指SQLTuning Advisor,收集的辅助统计信息。

通过接受被提示的SQL profile,就可以不加工SQL就控制执行计划。

<追加>

SQL profile是在10g中得到强化的功能之一,特征是不全面变更应用就可以进行调优。

 

SQL profile的制成与使用

SQL调优建议

  • 在高负荷背景下诊断有问题的SQL语句以及执行计划
  • 诊断结果是?
  • 寻找SQL语句的问题点,SQL语句的修正方法
  • 建议制成必要的索引
  • 制成SQL profile

一般而言,SQL调优建议等一系列的自动调优功能是从GUI的管理工具

Oracle Enterprise Manager 10g开始执行的。

 

sql_tuning_z1

SQL profile经常出现的问题

Q1. 制作一个SQL profile的话、能够加速多个SQL吗?

SQL_PROFILE1

Q2. 执行SQL调优建议时,可以明确指定制成SQLprofile吗?

A2. 不可以。

SQL调优建议是在分析SQL的时候,为了改善性能,做出最合适的建议的功能。在手动调优中,比起使用提示进行调优,制成index的效果更好的情况下,请选择制成index。与此相同在只要制成index就可以明显改善性能的情况下,不会建议制成SQL profile。

Q3. 可以确认SQL profile的内容吗?

A3. 无法确认SQL profile的内容。(SQL profile的列表可以通过搜索DBA_SQL_PROFILES视图来进行确认。)

Q4. SQL profile是随着数据量的增加而动态更新吗?

A4.因为SQLprofile是静态的信息,所以不会自动更新。 因此,使用时,                因为数据量的变化,SQL profile就会变旧,就可能使得性能恶化。

(已经产生恶化的情况下,可以再次制成SQL profile)

Q5. 我们已经明白了SQL profile的效果有多好了,那要制成性能更好的执     行计划的话,为什么不使用默认的优化呢?

A5. 制成SQL profile比一般的CBO需要花费的时间更多。如果,默认中,对于所有SQL,都检验SQL profile的话,就可能影响到整体的性能,所以现实中不采用那种架构。

 

Q6. 能将开发环境下制成的SQL profile移动到正式环境中使用吗?

A6. 如果是Oracle Database 10g R2的话就可以。

<顺序>

・在开发环境中、

  1. 执行 dbms_sqltune.create_stgtab_sqlprof,然后制成暂时储存SQL profile Staging表。
  2. 执行dbms_sqltune.pack_stgtab_sqlprof,将SQL profile储存在Staging表中。
  3. 使用 DataPump以及Export,在dump文件中取出Staging表。

・在正式环境中

4.使用在开发环境中制成的dump文件使用Data Pump进行import。

5. 执行dbms_sqltune.unpack_stgtab_sqlprof,从Staging表表中取出SQL profile,反应在正式环境中 Oracle Database 10g R1中无法将SQL profile移动到其他数据库中         (比如,制成index时,在开发环境下,可以将通过建议制成的项目用Export / Import 等移动到正式环境中,SQL profile不需要在正式环境中用别的途径来制成)

 

Q7. 有确认是否使用了 SQL profile的方法吗?

A7. 确认有两种方法。

①灵活使用SQL*Plus的Autotrace功能

1. 设定Autotrace为ON

SQL> set autotrace on

2. 每次执行SQL语句时,以下的note就都会被表示出来,使用了SQL profile的情况下,

是表示出SQLprofile的名字

Note

—–

– SQL profile SYS_SQLPROF_014564deb351c000 used for this statement

②灵活使用Explain Plan

1. 对于SQL执行Explain Plan

(例)

SQL> EXPLAIN PLAN FOR SELECT * FROM emp;

2. 执行下述SQL的话,就会得到与①同样的结果

SQL> select plan_table_output from table(dbms_xplan.display());

要使用SQL调优建议需要什么!?

  • 仅限Oracle Database 10g Enterprise Edition或以上版本
  • 需要购买以下两个Option

Database Diagnostics Pack

  • 可以使用的功能
  • AWR (Automatic Workload Repository)
  • ADDM (Automatic Database Diagnostic Monitor)
  • 性能监视(数据库以及主机)
  • 项目通知:
    通知method、规定、以及日程
  • 项目历史以及Metric历史
    (数据库以及主机)

Database Tuning Pack

  • 可以使用的功能
  • SQL Access Advisor
  • SQL Tuning Advisor
  • SQL Tuning Sets
  • 重新编辑对象

 

总结

  • SQL profile非常有效
  • 克服了一直以来提示调优的弱点
  • 不需要修正应用就可以完成类似于提示的调优
  • 总结了US oracle的咨询的调优与窍门
  • 即使没有技能与经验也能完成高度的SQL调优

灵活使用Oracle Database 10g Enterprise Edition+ Option 非常重要!

灵活使用SQL调优建议

由建议开始制成SQL profile

 

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪ICP备14014813号

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569