10g,11g sql auto tuning 測試

DBMS_SQLTUNE包的使用

[@more@]

11g

select
DBMS_SQLTUNE.REPORT_SQL_MONITOR(
session_id=>sys_context('userenv','sid'),
report_level=>'ALL') as report
from dual;

10g
SELECT a.status,c.piece,c.sql_text
FROM v$session a,v$sqltext c WHERE a.sql_address=c.address(+)
and a.audsid=userenv('sessionid') ORDER BY c.piece;

SELECT a.status,c.sql_text,c.sql_id
FROM v$session a,v$sql c WHERE a.sql_address=c.address(+)
and a.sid=749 ORDER BY c.sql_id;

SELECT c.*
FROM V$SESSION_WAIT_HISTORY c WHERE c.sid=userenv('sid');

grant advisor to cnrnd;

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '7nyjdhqzy6n37',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '7nyjdhqzy6n37_AWR_tuning_task',
description => 'Tuning task for statement 7nyjdhqzy6n37 in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

declare
l_task_id varchar2(20);
l_sql varchar2(2000);
begin
l_sql := q'{select yyyymmdd,count(*) from dyl3_07 where yyyymmdd>=sysdate-30 group by yyyymmdd,login_id}';
dbms_sqltune.drop_tuning_task ('DYL_TEST');
l_task_id := dbms_sqltune.create_tuning_task (
sql_text => l_sql,
user_name => 'CNRND',
scope => 'COMPREHENSIVE',
time_limit => 120,
task_name => 'DYL_TEST'
);
dbms_sqltune.execute_tuning_task ('DYL_TEST');
end;
/

set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task('DYL_TEST') from dual;