??
SELECT Q.RECIPIENT_ID,Q.DESCR,P.* FROM temp20160912 P, PS_RECIPIENT Q WHERE 1 =1 AND P.D_EFFDT = (SELECT MIN (P2.D_EFFDT) FROM temp20160912 P2 WHERE P.A_EMPLID = P2.A_EMPLID AND P.A_EMPL_RCD = P2.A_EMPL_RCD AND P.A_EFFDT = P2.A_EFFDT AND P.A_EFFSEQ = P2.A_EFFSEQ ) AND P.A_EMPLID IN (SELECT H.EMPLID FROM PS_GP_CAL_PRD I, PS_GP_CALENDAR J, PS_GP_PAYMENT H, PS_GP_PIN E, PS_RECIPIENT G WHERE 1 =1 AND H.CAL_RUN_ID = H.ORIG_CAL_RUN_ID AND H.CAL_ID = J.CAL_ID AND J.RUN_TYPE = 'SC AUSPAY' AND J.CAL_PRD_ID = I.CAL_PRD_ID AND H.PIN_NUM =E.PIN_NUM AND (H.RECIPIENT_ID ='S0000001' OR H.RECIPIENT_ID = 'S0000000') AND H.RECIPIENT_ID = G.RECIPIENT_ID AND I.PRD_BGN_DT <= P.D_EFFDT AND I.PRD_END_DT >= P.A_EFFDT AND H.EMPLID = P.A_EMPLID ) AND Q.RECIPIENT_ID IN (SELECT G.RECIPIENT_ID FROM PS_GP_CAL_PRD I, PS_RECIPIENT G WHERE 1 =1 AND H.CAL_RUN_ID = H.ORIG_CAL_RUN_ID AND H.CAL_ID = J.CAL_ID AND J.RUN_TYPE = 'SC AUSPAY' AND J.CAL_PRD_ID = I.CAL_PRD_ID AND H.PIN_NUM =E.PIN_NUM AND (H.RECIPIENT_ID ='S0000001' OR H.RECIPIENT_ID = 'S0000000') AND H.RECIPIENT_ID = G.RECIPIENT_ID AND I.PRD_BGN_DT <= P.D_EFFDT AND I.PRD_END_DT >= P.A_EFFDT AND H.EMPLID = P.A_EMPLID );
declare my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := ' SELECT Q.RECIPIENT_ID,'|| ' PS_RECIPIENT Q '|| 'WHERE 1 =1 '|| 'AND P.D_EFFDT = '|| ' (SELECT MIN (P2.D_EFFDT) '|| ' FROM temp20160912 P2 '|| ' WHERE P.A_EMPLID = P2.A_EMPLID '|| ' AND P.A_EMPL_RCD = P2.A_EMPL_RCD '|| ' AND P.A_EFFDT = P2.A_EFFDT '|| ' AND P.A_EFFSEQ = P2.A_EFFSEQ '|| ' ) '|| 'AND P.A_EMPLID IN '|| ' (SELECT H.EMPLID '|| ' FROM PS_GP_CAL_PRD I,'|| ' PS_GP_CALENDAR J,'|| ' PS_GP_PAYMENT H,'|| ' PS_GP_PIN E,'|| ' PS_RECIPIENT G '|| ' WHERE 1 =1 '|| ' AND H.CAL_RUN_ID = H.ORIG_CAL_RUN_ID '|| ' AND H.CAL_ID = J.CAL_ID '|| ' AND J.RUN_TYPE = ''SC AUSPAY'' '|| ' AND J.CAL_PRD_ID = I.CAL_PRD_ID '|| ' AND H.PIN_NUM =E.PIN_NUM '|| ' AND (H.RECIPIENT_ID =''S0000001'' '|| ' OR H.RECIPIENT_ID = ''S0000000'') '|| ' AND H.RECIPIENT_ID = G.RECIPIENT_ID '|| ' AND I.PRD_BGN_DT <= P.D_EFFDT '|| ' AND I.PRD_END_DT >= P.A_EFFDT '|| ' AND H.EMPLID = P.A_EMPLID '|| ' ) '|| 'AND Q.RECIPIENT_ID IN '|| ' (SELECT G.RECIPIENT_ID '|| ' FROM PS_GP_CAL_PRD I,'|| ' PS_RECIPIENT G '|| ' WHERE 1 =1 '|| ' AND H.CAL_RUN_ID = H.ORIG_CAL_RUN_ID '|| ' AND H.CAL_ID = J.CAL_ID '|| ' AND J.RUN_TYPE = ''SC AUSPAY'' '|| ' AND J.CAL_PRD_ID = I.CAL_PRD_ID '|| ' AND H.PIN_NUM =E.PIN_NUM '|| ' AND (H.RECIPIENT_ID =''S0000001'' '|| ' OR H.RECIPIENT_ID = ''S0000000'') '|| ' AND H.RECIPIENT_ID = G.RECIPIENT_ID '|| ' AND I.PRD_BGN_DT <= P.D_EFFDT '|| ' AND I.PRD_END_DT >= P.A_EFFDT '|| ' AND H.EMPLID = P.A_EMPLID '|| ' )';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, user_name => 'SYSADM', scope => 'COMPREHENSIVE', task_name => 'my_sql_tuning_task', description => 'Task to tune a query on a complex sql'); END; /
exec dbms_sqltune.execute_tuning_task('my_sql_tuning_task');
--Below report will show some suggestions,such as create index (indices 复数of index) SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task') FROM DUAL;
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task',task_owner => 'SYSADM',FORCE_MATCH=> TRUE,replace => TRUE);
exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task');
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01571d917e380002') ;
--find SQL_ID select * from v$sql where sql_text like '%SELECT Q.RECIPIENT_ID,%';
--Based on the above SQL_ID to find when the SQL Profile was executed SELECT t.sql_id,t.sql_profile,t.child_number,to_char(t.last_active_time,'yyyy-mm-dd hh24:mi:ss'),t.sql_fulltext FROM v$sql t WHERE t.sql_id ='3wpjppj8b682b'; (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|