oracle sqltune,ORACLEdbms_sqltune

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 !

http://www.dengb.com/oracle/702385.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/702385.htmlTechArticleAs 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 fo...