Oracle AWR, ASH, ADDM 和 Sql Tuning Advisor

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638 QQ号:47079569 邮箱:service@parnassusdata.com

 

 

目的:
这篇文章的目的是说明如何使用AWR诊断和解决数据库上的性能问题。

在10g之前,AWR提供强大的工具帮助DBA识别和解决性能问题,没有麻烦的复杂统计分析和大量的报告。
更好的使用这些新特性,简化信息的可视化,强烈推荐使用EM。

范围

这篇文档适用于DBA。

细节

性能调优的方法:10g之前

在10g之前,调优数据库的过程是复杂和耗时的。通俗的讲,当一个性能问题发生时,DBA能够通过STATSPACK访问性能统计数据,这数据能够检查确定数据库的时间花在哪里。(比较会话的等待事件和执行时间)。根据顶端事件定位,通常需要额外的深入分析来理解问题的根本原因,最终确定问题的根源。

Document 94224.1 FAQ- Statspack Complete Reference
Document  394937.1 Statistics Package (STATSPACK) Guide

Document 149113.1  Installing and Configuring StatsPack Package
Document 149121.1 Gathering a StatsPack snapshot
Document 228913.1 Systemwide Tuning using STATSPACK Reports

http://www.oracle.com/technetwork/database/focus-areas/performance/statspack-opm4-134117.pdf

这是一些为什么用statspack分析性能问题复杂和耗时的原因:

 

  • 一个数据库有上百的等待事件、闩、队列、buffer busy classes、统计信息和度量,一个事务用于报告所花费的时间。某些等待事件出现在不同的操作和不同的状况。需要广泛的知识和经验来定位数据库最可能的等待是什么特定的事件 。
  • 数据库性能历史信息常见的存储方式是通过使用Statspack资源库,是一个基于时间间隔的工具。因此,当DBA分析一个不好的性能问题时会受限制 (假设快照间隔60分钟). 成千上万的用户在一个事务环境工作的同时有一个高概率数据倾斜的性能峰值可能不可见。
  • 一旦可能的问题被确认,用Statspack 资料库收集的初始信息不够确认和解决问题。因此,DBA必须准备一套额外收集工具和脚本,以便下次再次出现这种问题是能够确认和解决。很可能这一步之前,必须重复几次问题确认或进一步的性能分析。

当前基础架构 (AWR, 时间模型, ASH) (Post 10g)

AWR捕获丰富而复杂的性能统计数据,可以使用性能顾问和ADDM性能工具。Oracle建议使用ADDM进行性能分析,而不是每次手动分析新的统计指标。

注意:

为了能够收集大部分的新的统计信息,确保STATISTICS_LEVEL参数是设置为TYPICAL (推荐) 或ALL.
Statistics_level =ALL 专门的生成环境上不应该设置。他可能会导致高CPU利用率和变慢。should not be set,specially on production box. It may cause high cpu utilzation and slowness. 只能在测试环境设置,在支持人员的推荐下或者设置为特殊会话调试具体问题

参见:

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 5 Automatic Performance Statistics
http://docs.oracle.com/cd/E29505_01/server.1111/e16638/autostat.htm

Document 250655.1 How to use the Automatic Database Diagnostic Monitor

 

使用AWR和相关的特性,需要Oracle Diagnostics Pack许可证,提供自动性能诊断和高级系统监控功能。

参见:

Oracle® Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Diagnostics Pack
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC165

Automatic Workload Repository (AWR)

AWR是提供不同的可管理性组件的一个基础架构。AWR包括两部分:通过V$动态性能视图访问内存中数据,和保存在数据库中的AWR快照代表着持续和历史部分。

  • AWR 数据从内存刷写到磁盘是用内核调用和一个后台进程 (MMON).
  • 数据收集不需要手动配置,和之前版本的Statspack快照是一样的。
  • AWR提供自动清除数据(默认是七天)
  • AWR快照可以保留而不清除。(这可以存储为基线进行更长远的对比)

AWR快照可以使用下面的语句产生:

EXECUTE dbms_workload_repository.create_snapshot();

Time Model

在Oracle 10g和之前的版本,数据库引入了不同的方式存储不同会话连接的时间消耗统计信息和指标。这些数据是快速分析性能问题的关键,在数据库里提供不同角度的时间用途。

下面将详细描述这些特征:

  • Time Model每个操作的新的时间模型存储在一个时间桶。因此,可以在数据库级别或会话级别确认时间分布的情况。例如这些桶是:”DB CPU”, “DB Time”, “background elapsed time”, “sql execute elapsed time”等。 查看下面视图获得更多的细节:

V$SYS_TIME_MODEL
V$SESS_TIME_MODEL

  • Wait Classes让分析等待事件更容易和更精确的诊断。基于解决方案空间的”wait classes”将时间事件分组,用来解决一个等待事件问题。查看下面视图获得更多的细节:

V$SYSTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS

  • Metrics指标会自动收集活动数据库基于基础统计信息跟踪比例的变化。大多数指标每60秒收集一次。一个经常使用的好的的指标”cache hit ratios”. 在10g之前的版本, cache hit ratios必须手动根据公式计算。 Cache hit ration现在在内存里计算和存储。查看下面视图获得更多的细节:

V$SYSMETRIC
V$SESSMETRIC
V$FILEMETRIC
V$EVENTMETRIC
V$WAITCLASSMETRIC
V$METRICNAME

  • OS StatisticsCPU和内存统计信息默认是收集的。帮助ADDM确定活动数据库的瓶颈。查看下面视图获得更多的细节:

V$OSSTAT

  • Active Session History (ASH)ASH是AWR资料库的一个组成部分,每秒从V$session(inactive的会话不捕捉)收集活动会话样本 (等待non-idle事件或在 CPU 工作) .当默认保存在AWR资料库, 十分之一的样本会持久保存。这些信息提供ADDM用来挖掘定位问题。在 “db file scattered read” 提供例子, ASH数据帮助advisorthe定位那个数据文件哪个块被等待事件引用最多。

V$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY

 

Performance Tuning Advisors

Automatic Database Diagnostic Monitor (ADDM)

ADDM是一个强大的自我诊断引擎直接构建在数据库的内核中。使用AWR,ADDM可以全面的分析系统,定位系统的主要问题(不是症状),并给出修正建议(有时可能需要单独使用Oracle 10g向导)。集中关注实际问题,ADDM也显示了没有问题的系统区域。

当一个新的AWR快照生成时会自动调用ADDM(默认是每60分钟),分析的结果存储在向导框架里,标记上快照id. 因此DBA 可以发现向导的历史执行和建议。ADDM可以手动使用ADDM报告生成器生成报告不同于快照设置。生成一个新的ADDM报告,执行下面脚本指定分析采样的间隔:

$ORACLE_HOME/rdbms/admin/addmrpt.sql

下面是一个ADDM报告的摘录:

FINDING 1: 28% impact (97 seconds)
----------------------------------
Individual database segments responsible for significant user I/O wait were found.

   RECOMMENDATION 1: Segment Tuning, 28% benefit (97 seconds)
      ACTION: Run "Segment Advisor" on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      ACTION: Investigate application logic involving I/O on TABLE "SCOTT.T" with object id 51924.
         RELEVANT OBJECT: database object with id 51924
      RATIONALE: The SQL statement with SQL_ID "5328shb1qxs6u" spent
         significant time waiting for User I/O on the hot object.
         RELEVANT OBJECT: SQL statement with SQL_ID 5328shb1qxs6u
         insert into a select * from t

   SYMPTOMS THAT LED TO THE FINDING:
      Wait class "User I/O" was consuming significant database time. (30%  impact [105 seconds])

ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.

参照:

Document 250655.1  How to use the Automatic Database Diagnostic Monitor

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 6 Automatic Performance Diagnostics
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/diag.htm

SQL Tuning Advisor

优化器使用当前对象的统计信息和SQL结构生成SQL语句的执行计划。优化器及时确定执行计划和语句的执行计划好的是重要的。因此,如果对象的统计信息没有反映真实的情况,或者写得不好的查询语句,或者丢失访问结构(如索引),优化器可能生成一个不是最优的执行计划。

除了”normal”模式的优化器,还可以以”tuning mode” 使用SQL调优向导(STA). 在调优模式,优化器可以有足够的时间和资源确定最好的执行计划。当统计信息不可用,或因为缺少定义的索引而无法访问路径,STA会收集对象上的辅助统计信息,包含或模拟生成假设存在索引的执行计划。STA验证下面事项生成最好的执行计划:

  • 检查失效或丢失的统计信息(统计信息检查优化模式):验证统计信息的有效性和收集辅助统计信息根据需要补偿偏差。
  • 确定一个新的执行计划和创建一个SQL配置文件 (计划调整优化模式):核实开销,选择性和动态抽样基数。这将确保由优化器产生的执行计划实际反应统计信息。
  • 检查丢失的索引(访问分析优化模式):确定需要创建,修改或消除的访问结构(索引,物化视图)
  • 调整 SQL(SQL分析优化模式): 确定SQL结构变动,可能导致一个更好的执行计划。

SQL调优向导会提供建议:刷新对象统计信息或创建访问对象。当确定一个好的执行计划,建议创建一个SQL配置文件。当SQL配置文件创建和被系统使用后,当执行语句时无需修改应用每次就能自动使用这个新的执行计划。

Note: Use of the SQL Tuning Advisor requires the Oracle Tuning Pack License:

Oracle® Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Tuning Pack
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC170
获取SQL调优向导的建议最好的路径是使用EM,也可以调用DBMS_SQLTUNE 包。下面是SQL调优向导提供的结果的摘录:

--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task10
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/09/2004 21:30:22
Completed at       : 06/09/2004 21:30:23

-------------------------------------------------------------------------------
SQL ID  : b296symum0xk1
SQL Text: SELECT /*+ ORDERED */ * FROM employees e, locations l, departments
          d WHERE e.department_id = d.department_id AND l.location_id =
          d.location_id AND e.employee_id < 10

-------------------------------------------------------------------------------
FINDINGS SECTION (5 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  Table "CLASS6"."DEPARTMENTS" and its indices were not analyzed.

  Recommendation
  --------------
    Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'CLASS6', tabname =>
            'DEPARTMENTS', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
            method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.
....
4- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 87.1%)
  -----------------------------------------
    Consider accepting the recommended SQL profile.
    execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
                             'my_sql_tuning_task10')

5- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  Recommendation
  --------------
    Consider removing the "ORDERED" hint.

  Rationale
  ---------
    The "ORDERED" hint might force the optimizer to generate a cartesian
    product. A cartesian product should be avoided whenever possible because
    it is an expensive operation and might produce a large amount of data.

参照:

Document 262687.1  How to use the Sql Tuning Advisor.
Document 271196.1  Automatic SQL Tuning - SQL Profiles.

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 17 Automatic SQL Tuning
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/sql_tune.htm

SQL Access Advisor

SQL访问向导识别可能访问数据路径使用索引或物化视图,提高访问数据的性能。SQL访问向导获取一个真实的工作负载输入和快速执行建议需要的访问结构。 通过更好的访问路径有利于识别SQL语句,SQL访问顾问直接从SQL缓存(V$SQL)获取工作负载, 或者用户定义的工作负载(输入表的形式或SQL调优集合). 这个向导考虑下面几点:

  • 使用索引,物化视图或者两者结合在一起的影响
  • 存储创建参数
  • 结合单一索引到一个索引
  • 删除不用的所有
  • 修改存在的索引

参照:

Document 259188.1 Oracle10g: Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-06
Chapter 18 SQL Access Advisor
http://docs.oracle.com/cd/E28271_01/server.1111/e16638/advisor.htm

Note: Use of the SQL Access Advisor requires the Oracle Tuning Pack License:

Oracle® Database Licensing Information
11g Release 2 (11.2)
Part Number E10594-18
Chapter 2 Options and Packs
Oracle Tuning Pack License
http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC139


Reporting

If reporting is still required then you should use AWR reports instead of statspack

在某些情况下理解底层数据是需要的,绑定数据库的报告可以满足这一目的。AWR 提供一个全面的报告类似于Statspack报告,但是多了很多Statspack没有的附加信息。10g还支持 STATSPACK 。AWR报告相对于Statspack报告有下面优势:

  • 资料库自动创建和管理
  • 报告可以生成text或html格式
  • 报告显示一些额外的信息,关于wait classes, metrics, OS stats,等

执行下面的脚本和指定两个你想使用的样本快照生成AWR报告(Oracle建议快照的间隔不要超过60分钟):

$ORACLE_HOME/rdbms/admin/awrrpt.sql

你还可以指定报告的格式 (text 或html) 。
参照:

Document 1363422.1 Automatic Workload Repository (AWR) Reports – Start Point

 

关注刘相兵的新浪微博

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

Speak Your Mind

沪公网安备 31010802001379号

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