Ask Maclean

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

 

https://www.askmac.cn/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自动启动的时机是在取得AWR的snapshot时，通过取得了snapshot的MMON进程来自动启动

手动启动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

 

Posted

in

by

mac

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *