--sql_tunning advisor
DECLARE
MY_TASK_NAME VARCHAR2(30);
MY_SQLTEXT CLOB;
MY_SQLID varchar2(30);
BEGIN
MY_SQLTEXT :='select * from
(Select t1.Indicator_Endtime,t1.Indicator_Value, t1.Tab_Name,t1.Tab_Column,T2.MAC_IDX_PAR 参数编码,T2.MAC_IDX_PAR2 参数代码二,t1.Tab_Id From Ccxe2.indu_Data T1
Left Join Ccxe2.INDU_BLME_TRAN_INFO T2 On T2.Indicator_Id = T1.Indicator_Id
And T2.Isvalid = 1 And T2.MAC_IDX_PAR Is Not Null And T2.MAC_IDX_PAR2 Is Not Null
Where T1.Isvalid = 1) a,
(select ''INDU_NONFE_SPOT_CJYS''tab,end_date,MAC_IDX_PAR,MAC_IDX_PAR2,A001,A002,A003,id
from ccxe2. INDU_NONFE_SPOT_CJYS @progather where isvalid=1 )b
where a.indicator_endtime=b.end_date and a.tab_name=b.tab and a.参数编码=b.MAC_IDX_PAR
and a.参数代码二=b.MAC_IDX_PAR2
and ( (tab_column=''A001'' and nvl(a.indicator_value,0)<>nvl(round(b.A001,6),0)) or
(tab_column=''A002'' and nvl(a.indicator_value,0)<>nvl(round(b.A002,6),0)) or
(tab_column=''A003'' and nvl(a.indicator_value,0)<>nvl(round(b.A003,6),0)) or
a.tab_id is null or a.tab_id<>b.id )';
MY_SQLID :='3mknc4bd7fpyg';
--dbms_output.put_line(my_sqltext);
MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
BIND_LIST=>NULL,
USER_NAME => 'SNOWHILL',
SCOPE=>'COMPREHENSIVE',
TIME_LIMIT => 120,
TASK_NAME => 'SQL_TUNING_TEST',
DESCRIPTION=>'TUNING TASK'
);
END;
--执行
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK('SQL_TUNING_TEST'); END;
--查询状态
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='SQL_TUNING_TEST';
--查询调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST') FROM DUAL;
--删除
BEGIN dbms_sqltune.drop_tuning_task('SQL_TUNING_TEST'); END;
SELECT * FROM USER_ADVISOR_TASKS T WHERE TASK_NAME='SQL_TUNING_TEST';
SELECT * FROM DBA_SQLTUNE_STATISTICS
SELECT * FROM DBA_SQLTUNE_BINDS
SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID=13009
create index CCXE2.IDX_INDU_DATA_ID on CCXE2.INDU_DATA(INDICATOR_ID) tablespace ccxe2_idx;
select * from dba_advisor_tasks where owner<>'SYS'
execute dbms_sqltune.accept_sql_profile(task_name => 'SQL_TUNING_TEST',
task_owner => 'SNOWHILL', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
select * from dba_sql_profiles;