第9章性能诊断与SQL优化.PDF

第 9 章 性能诊断与 SQL 优化 第 9 章 性能诊断与 SQL 优化 对于一个数据库系统，从应用的角度来说，通常我们最期望有良好的性能，稳定的运行。 所以怎样维持一个数据库高性能稳定运行就变得非常重要，对于大多数数据库维护人员来说， 直接面对的问题就是在问题出现时，需要快速发现并迅速解决数据库性能等问题，高系统持 续高效运行。 本章我们将通过一些实际生产中遇到的案例进行剖析讲解，希望大家能够从中领会到诊 断性能问题的思路和方法，并对具体问题，特别是 SQL 问题进行常规处理。 9.1 使用 AutoTrace 功能辅助 SQL 优化 Oracle SQL*Plus 供一个 autotrace 的功能，可以用于跟踪 SQL 的执行计划，收集统计信 息，经常被作为 SQL 的优化工具之一被广泛使用。 9.1.1 Autotrace 功能的启用 在 Oracle10g 之前，缺省的 autotrace 功能并未打开，需要通过以下步骤手工启用该功能： 1 ．创建基础表 这可以通过运行$ORACLE_HOME\rdbms\admin\utlxplan 脚本完成，该脚本用于创建 plan_talbe 表： SQL connect / as sysdba 已连接。 SQL @?\rdbms\admin\utlxplan 表已创建。 为了使多个用户可以共享同一个 plan_table ，可以为它创建一个同义词，并授权给 Public ： SQL create public synonym plan_table for plan_table; 同义词已创建。 SQL grant all on plan_table to public ; 授权成功。 2 ．创建 plustrace 角色 这需要运行$ORACLE_HOME\sqlplus\admin\plustrce.sql 脚本： SQL @?\sqlplus\admin\plustrce SQL SQL drop role plustrace; drop role plustrace 书名书名书名书名书名书名书名书名书名书名书名书名书名书名 * ERROR 位于第 1 行: ORA-01919: 角色PLUSTRACE不存在 SQL create role plustrace; 角色已创建 SQL grant select on v_$sesstat to plustrace; 授权成功。 SQL grant select on v_$statname to plustrace; 授权成功。 SQL grant select on v_$session to plustrace; 授权成功。 SQL grant plustrace to dba with admin option; 授权成功。 SQL set echo off 3 ．一点增强 DBA 用户首先被授予了 plustrace 角色，然后我们可以手工把 plustrace 授予 public ，这样 所有用户都将拥有 plustrace 角色的权限，所有数据库用户也就拥有了使用 autotrace 功能的权 限。 SQL grant plustrace to public ; 授权成功。 完成以上步骤我们就可以使用 AutoTrace 的功能了。Autotrace 有几个常用选项，简单说明 如下： u SET AUTOTRACE OFF 不生成 AUTOTRACE 报告，这是缺省模式 u SET AUTOTRACE ON EXPLAIN AUTOTRACE 只显示优化器执行路径报告 u SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息 u SET AUTOTRACE ON 包含执行计划和统计信息 u SET AUTOTRACE TRACEONLY 同 set autotrace on