Oracle SQL Profile使用



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_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         
  );

declare
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := ' 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_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          '||
'  )';

 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, Q.DESCR,P.* FROM temp20160912 P,%';

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