10.Oracle性能诊断和SQL优化.pdf

lastdance

235

68页

0次

2021-03-07

100墨值下载

第

章

性能

诊断与

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

书名书名书名书名书名书名书名书名书名书名书名书名书名书名

·2·

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 有几个常用选项，简单说明

如下：

! SET AUTOTRACE OFF ---------------- 不生成 AUTOTRACE 报告，这是缺省模式

! SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE 只显示优化器执行路径报告

! SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息

! SET AUTOTRACE ON ----------------- 包含执行计划和统计信息

! SET AUTOTRACE TRACEONLY ------ 同 set autotrace on，但是不显示查询输出

在 SQL*Plus 中，autotrace 的基本输出大致类似：

SQL> set autotrace on

SQL> select * from v$version where rownum <2;

BANNER

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

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 COUNT (STOPKEY)

2 1 FIXED TABLE (FULL) OF 'X$VERSION'

of 68

100墨值下载

database

关注

评论