构筑高速运行的SQL语句(一)

摘自《成功之路：Oracle 11g学习笔记》

第17章  构筑高速运行的SQL语句

本章重点介绍SQL语句的性能调整，在此之前，我们将先对数据库性能调整进行概述。

17.1  数据库性能调整简介

数据库性能调整是DBA必备的技能之一，也是开发人员最关注的话题。Oracle从诞生的那天起，以其高性能赢得大家的青睐。但是，早期的Oracle性能调整比较复杂，许多工作需要DBA手工完成。伴随着Oracle新版本的推出，数据库的性能调整越来越智能化。

数据库性能调整包括实例调整（Instance Tuning）和SQL语句调整（SQL Tuning）。两者相互联系和相互影响。如果实例出现性能问题，无论你如何优化SQL语句，也不能使SQL语句高速运行；反之，即使实例的调整已经到位，一条效率低下的SQL语句也足以使整个数据库性能大大降低。

17.2  实例调整

实例调整的重点应该放在数据库的设计上。一旦数据库设计付诸实施，我们将很难在生产环境中改变数据库的设计。数据库的设计能够避免日后的性能瓶颈。实例调整涉及内存调整、I/O分布和操作系统调整等。实例调整针对的是整个实例的表现，而不是针对单条SQL语句。

实例调整的过程如图17-1所示。

图17-1  实例调整的过程

 17.2.1  自动工作负载库（Automatic Workload Repository，AWR）

自动工作负载库是Oracle公司提供的一个工具，其自动收集、处理、维护性能相关的统计信息。这些统计信息可以协助我们找出Oracle的性能瓶颈。

1．AWR的启用

在默认情况下，Oracle启用数据库统计收集这项功能（即启用AWR）。是否启用AWR由初始化参数STATISTICS_LEVEL控制。通过SHOW PARAMETER命令可以显示STATISTICS_LEVEL的当前值：

SQL> SHOW PARAMETER STATISTICS_LEVEL

SQL语句的执行结果是：

NAME                                 TYPE        VALUE

------------------------------------    ----------- ----------------

statistics_level                   string      TYPICAL

如果STATISTICS_LEVEL的值为TYPICAL或者 ALL，表示启用AWR；如果STATISTICS_LEVEL的值为BASIC，表示禁用AWR。

2．快照（SNAPSHOT）

每隔一小时，内存监控进程（MMON）自动地采集一次统计信息，并把这些信息存放到负载库中，一次采样就是一个快照。为了节省空间，采集的数据在7天后自动清除。快照的频率和保留时间可以由用户修改。

用户也可以使用下面的命令手工采样（手工生成快照）：

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

END;

通过查询视图DBA_HIST_SNAPSHOT，可以知道系统中产生了哪些快照。

3．产生AWR报告

Oracle可以产生两种类型的AWR报告：文本格式和HTML 格式。HTML格式的报告界面更加友好。AWR报告用于显示两个快照或者两个时间点之间捕捉到的数据。AWR报告其实就是一张数据库健康体检表，它显示了数据库健康的各项指标。

有针对整个数据库的AWR报告，有针对某个实例的AWR报告（在集群环境），有针对单条SQL语句的AWR报告。通过运行Oracle自带的SQL脚本产生AWR报告，只是产生不同的AWR报告，需要运行不同的脚本。

¢ 产生整个数据库的AWR报告，运行脚本awrrpt.sql。

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

¢ 产生某个实例的AWR报告，运行脚本awrrpti.sql。

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

¢ 产生某条SQL语句的AWR报告，运行脚本awrsqrpt.sql。

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

注： $ORACLE_HOME代表Oracle的主目录。

示例：产生整个数据库的AWR报告。本例中，Oracle的主目录是C:\app\Administrator\ product\11.1.0\db_3。

 运行脚本awrrpt.sql。

SQL> @C:\app\Administrator\product\11.1.0\db_3\rdbms\admin\awrrpt.sql

 选择报告的类型。

输入 report_type 的值:  html

本例中选择HTML。

 列出最近两天产生的快照（主要是获得快照ID）

输入 num_days 的值:  2

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

zdb          ZDB               386 23 10月 2009 09:08     1

                                400 24 10月 2009 09:58     1

                                401 24 10月 2009 11:00     1

                                402 24 10月 2009 11:03     1

 指定起始快照的ID和结束快照的ID。

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

输入 begin_snap 的值:  400

Begin Snapshot Id specified: 400

 

输入 end_snap 的值:  402

End   Snapshot Id specified: 402

本例中起始快照的ID是400，结束快照的ID是401。

 指定报告的名字。

输入 report_name 的值:  myreport.html

 17.2.2  性能监测器（Automatic Database Diagnostic Monitor，ADDM）

性能监控器（ADDM）是Oracle提供的强大工具，用于对Oracle的性能进行诊断。自动工作负载（AWR）是性能检测器的基础，在使用时，ADDM对AWR收集的数据进行分析，找出性能问题的根本原因，并自动提出问题的解决之道。与此同时，ADDM也会标识出没有问题的地方，以此消除我们的猜疑。ADDM可以诊断的问题主要有：

Ø  CPU的瓶颈

Ø 内存问题

Ø  I/O问题

Ø 高耗能SQL

Ø  PL/SQL或Java引起的性能问题

Ø 与RAC相关的性能问题

Ø 数据库配置问题

Ø 并发性问题

Ø 热点对象的问题

1．ADDM的启用

ADDM是否启用由两个初始化参数CONTROL_MANAGEMENT_PACK_ACCESS和 STATISTICS_LEVEL共同控制。在默认情况下，ADDM被Oracle自动启用。

Ø  CONTROL_MANAGEMENT_PACK_ACCESS的值为DIAGNOSTIC或者DIAGNOSTIC+TUNING都将启用ADDM；如果值为NONE，将禁用ADDM。

Ø 如果STATISTICS_LEVEL的值为TYPICAL或者ALL，将启用ADDM；如果值为BASIC，则会禁用ADDM。

可以使用SHOW PARAMETER命令显示CONTROL_MANAGEMENT_PACK_ ACCESS的当前值。

SQL> SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS;

SQL语句的执行结果是：

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------------------

control_management_pack_access       string      DIAGNOSTIC+TUNING

此外，启用ADDM时，还有另外一个参数DBIO_EXPECTED，I/O性能的分析在部分程度上依赖于该参数。这个参数的含义是读取一个数据块所花费的平均时间（以微秒为单位）。

2．查看ADDM报告

查看ADDM报告需要经历两个过程：

Ø 创建并执行“分析任务”

Ø 查看报告

查看ADDM报告的详细过程说明如下。

 查看快照信息。

SQL> SELECT * FROM dba_hist_snapshot ORDER BY snap_id;

SQL语句的执行结果是：

SNAP_ID DBID    INSTANCE_NUMBER STARTUP_TIME         

-------     ---------- ---------------             ----------------

400     2235959226   1               24-10月-09 09.47.06.000 上午

401     2235959226   1               24-10月-09 09.47.06.000 上午

402     2235959226   1               24-10月-09 09.47.06.000 上午

403     2235959226   1               24-10月-09 09.47.06.000 上午

404     2235959226   1               24-10月-09 09.47.06.000 上午

405     2235959226   1               24-10月-09 09.47.06.000 上午

406     2235959226   1               24-10月-09 09.47.06.000 上午

这里主要想得到快照的ID。

 创建并执行“分析任务”。

VAR taskname VARCHAR2(100);--定义绑定变量

BEGIN

:taskname := 'the whole database analyze';--指定任务的名字

DBMS_ADDM.ANALYZE_DB(:taskname, 404, 406);

END;

其中，taskname是任务的名字。404及406是快照的ID，404表示分析的起点，406表示分析的终点。DBMS_ADDM.ANALYZE_DB用于分析整个数据库；DBMS_ ADDM.ANALYZE_INST用于分析指定的实例；DBMS_ADDM.ANALYZE_PARTIAL用于分析数据库的一部分。

 查看报告。

SET LONG 100000--

SET PAGESIZE 50000

SELECT DBMS_ADDM.GET_REPORT(:taskname) FROM DUAL;

其中，taskname是前面步骤中定义的绑定变量。DBMS_ADDM.GET_REPORT的返回值是CLOB类型，因此，需要对输出进行格式化。

Oracle推荐使用企业管理（OEM）运行ADDM，企业管理器是图形界面工具，界面友好，方便查看ADDM报告。

p1.jpg

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

转载于:http://blog.itpub.net/13804621/viewspace-683178/