ORACLE dbms_sqltune

浏览数：20 / 时间：2015年06月12日

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 !

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

ORACLE dbms_sqltune