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...
转播
