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';