ORACLE dbms_sqltune

最新推荐文章于 2025-02-11 10:13:15 发布

原创最新推荐文章于 2025-02-11 10:13:15 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ORACLE #database #index #dba #sql

oracle 专栏收录该内容

56 篇文章

订阅专栏

本文介绍了如何使用Oracle DBMS_SQLTUNE自动工具进行SQL调优，包括创建调优任务、执行任务及检查进度等步骤。通过手动和Oracle企业管理器、网格控制等方式的应用实例，深入探讨了SQL性能优化的有效策略。

As usually, we do some sql tuning, and we also can do it with the help of oracle dbms_tunne which is the oracle sql tuning advisor.

After oracle 10g, oracle provide one function for sql tuning automatically named dbms_sqltune.

Where can it be used?

We often use it with oracle enterprise manager, grid control, addmrpt or manually do it.

For manually as:

DECLARE
  SQLTUNING_TASK VARCHAR2(30);
  SQL_TEXT CLOB;
BEGIN
  SQL_TEXT := 'select * from t1 where object_id = 2';
  SQLTUNING_TASK := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    SQL_TEXT => SQL_TEXT,
    USER_NAME => 'WINKEY',
    SCOPE => 'COMPREHENSIVE',
    TIME_LIMIT => 15,
    TASK_NAME => 'my_task',
    DESCRIPTION => 'winkey'
    );
END;
/

SELECT TASK_NAME FROM DBA_ADVISOR_LOG;   --sqladvisor log

--execute sql tuing task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( TASK_NAME => 'my_task');
END;
/

--checking for sql tuing task
SELECT status
FROM   USER_ADVISOR_TASKS
WHERE  TASK_NAME = 'my_task';

-- Checking the Progress of SQL Tuning Advisor
SELECT SOFAR, TOTALWORK
FROM   V$ADVISOR_PROGRESS
WHERE  USER_NAME = 'WINKEY' AND TASK_NAME = 'my_task';

SET LONG 2000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_task')
FROM   DUAL;

Just do it !