Oracle 11g系统调优之dbms_sqltune包的使用

最新推荐文章于 2025-05-21 09:23:28 发布

转载

最新推荐文章于 2025-05-21 09:23:28 发布 · 751 阅读

文章标签：

#数据库

本文介绍了Oracle 11g中的DBMS_SQLTUNE包，用于SQL调优。通过v$session_longops动态视图找出执行时间长的SQL，然后使用DBMS_SQLTUNE包的CREATE_TUNING_TASK、EXECUTE_TUNING_TASK和ACCEPT_SQL_PROFILE等过程进行调优。文章通过实例展示了如何创建和执行调优任务，并分析调优结果，提供SQL_PROFILE的创建、应用和删除方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前沿：随着数据库版本的提升，Oracle也提供了越来越多的性能诊断工具，针对SQL的调优，DBMS_SQLTUNE就是其中一个比较优秀的包。
DBMS_SQLTUNE最开始是在10G里面出现，11G里面则对其进行了加强，使得其更加符合实际需求。

1.查找系统可能存在问题的SQL

一般什么样的SQL可能会存在性能问题呢？
我们第一时间能想到的肯定是执行时间很长的SQL、其次是IO很高的SQL，这里就针对执行时间很长的SQL来做测试。
获取类似的SQL有多种方法，AWR、ADDR、动态视图等，这里我们就通过动态视图v$session_longops来获取，因为这个视图里面的语句是最近执行的，有比较强的及时性。

以下语句可查询最近数据库中执行时间比较长的SQL，包括执行时间。

点击(此处)折叠或打开

select tt1.sql_text,tt1.sql_fulltext,tt2.sql_id,tt2.sums
from v$sqlarea tt1,
(select sql_id,sum(elapsed_seconds) as sums
from v$session_longops where opname=\'Table Scan\'
group by sql_id
) tt2
where tt1.sql_id=tt2.sql_id
order by tt2.sums desc;

执行结果如下：

其中SUMS列为此SQL执行的总时间，上面我主要选取了‘Table Scan’这个类型的操作作为主要的时间损耗，从实际上来看也是如此，表扫描的方式直接关系SQL执行的效率，
表扫描占整个SQL执行时间的比重最大。

从上面，我们选取一条SQL，ID为“3c3ch9a4xdwn1”作为需要优化的SQL。

2.DBMS_SQLTUNE包
DBMS_SQLTUNE包提供了很多的子程序来对SQL进行诊断和对执行计划进行处理，这里我们只是简单的测试一下DBMS_SQLTUNE的调优功能，主要涉及到3个子过程。

DBMS_SQLTUNE.CREATE_TUNING_TASK    #创建一个SQL调优任务

DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id           IN VARCHAR2,                                             ----------&gtSQL ID，必填项
plan_hash_value IN NUMBER    := NULL,                           -----------&gt执行计划的HASN值(选填)
scope            IN VARCHAR2 := SCOPE_COMPREHENSIVE, -----------&gt任务类型，有limited和comprehensive两种
time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,       -----------&gt此任务最长的执行时间
task_name        IN VARCHAR2 := NULL,                           -----------&gt任务名
description      IN VARCHAR2 := NULL)                             -----------&gt任务描述
RETURN VARCHAR2;

EXECUTE_TUNING_TASK                              #执行一个SQL调优任务

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,                                      ------------&gt任务名
   execution_name    IN VARCHAR2               := NULL,         ------------&gt执行时的名称，可为空
   execution_params IN dbms_advisor.argList   := NULL,        ------------&gt执行参数，默认可为空
   execution_desc    IN VARCHAR2               := NULL);         ------------&gt执行描述

DROP_TUNING_TASK                                   #删除一个SQL调优任务

DBMS_SQLTUNE.DROP_TUNING_TASK(
task_name         IN VARCHAR2);                                      -------------&gt任务名

ACCEPT_SQL_PROFILE                               #接受及应用一个SQL_PROFILE执行计划给某条SQL

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN VARCHAR2,                                         --------------&gt执行优化的任务名
   object_id    IN NUMBER   := NULL,                                --------------&gt对象编号，一般不填
   name         IN VARCHAR2 := NULL,                               --------------&gt制定的sql_profile名称，如果不填则由系统指派
   description IN VARCHAR2 := NULL,                               --------------&gt该执行计划的描述信息
   category     IN VARCHAR2 := NULL);                              -------------&gt需要与该SESSION的sqltune_category参数相匹配
   task_owner   IN VARCHAR2 := NULL,                             -------------&gt任务的所有者
   replace      IN BOOLEAN   := FALSE,                               -------------&gt如果此sql_profile已存在，则决定是否替换，默认值为不替换
   force_match IN BOOLEAN   := FALSE,                           -------------&gt是否强制匹配此执行计划与所有HASH值相同的SQL，类似CURSOR_SHARING参数的FORCE
   profile_type IN VARCHAR2 := REGULAR_PROFILE);          -------------&gtsql_profile的类型，默认为REGULAR_PROFILE，可修改为PX_PROFILE，表示此执行计划变更为并行执行

DROP_SQL_PROFILE                                     #删除一个SQL_PROFILE的应用，让系统自动选择

DBMS_SQLTUNE.DROP_SQL_PROFILE (
   name          IN VARCHAR2,
   ignore        IN BOOLEAN := FALSE);

3.具体演示过程

SQL Profile是10g中的新特性，作为自动SQL调整过程的一部分，由Oracle企业管理器来管理。除了OEM，SQL Profile可以通过DBMS_SQLTUNE包来进行管理。

查询优化器有时候会因为缺乏足够的信息，而对一条SQL语句做出错误的估计，生成糟糕的执行计划。而自动SQL调整通过SQL概要分析来解决这个问题，自动调整优化器会生成这条SQL语句的一个概要，称作SQL Profile。它由针对这条语句的一些辅助统计信息组成，通过采样和局部执行技术来确认，必要的话，会调整执行计划中的估计值。在SQL概要分析中，自动调整优化器还可以通过一条SQL语句的执行历史信息来设置合适的优化器参数，比如将OPTIMIZER_MODE参数由ALL_ROWS改为 FIRST_ROWS。

换句话说，SQL概要是一个对象，它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优化器所做评估的修正信息。它的最大优点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。(《Oracle性能诊断艺术》)

SQL Profile中包含的并非单个执行计划的信息，必须注意的是，SQL Profile不会固定一个SQL语句的执行计划。当表的数据增长或者索引创建、删除，使用同一个SQL Profile的执行计划可能会改变，而储存在SQL Profile中的信息会继续起作用。然而，经过一段很长的时间之后，它的信息有可能会过时，需要重新生成。

SQL Profile的作用范围由CATEGORY属性来控制，这个属性决定了哪些用户会话可以应用这个概要。你可以从DBA_SQL_PROFILES中的 CATEGORY字段来查看这个属性。默认情况下，所有概要文件都创建为DEFAULT范畴，这意味着所有SQLTUNE_CATEGORY初始化参数为 DEFAULT的用户会话都可以使用这个概要。你可以修改这个属性，比如将其改为DEV，则SQLTUNE_GATEGORY参数为DEV的用户会话才能使用它，利用这个功能，你可以在一个受限制的环境中来测试一个SQL Profile。

简单来说，Sql_Profile是用来影响数据库执行计划生成的一组信息文件的集合，可以在不改变原有SQL语句的前提下，达到类似HINTS改变其执行计划的目的。

创建一个sqltune的调优任务(即创建sql_profile的相关信息) </