有時懶得分析或語句太長，可以嘗試用oracle的dbms_sqldiag包進行sql優化，

--How To Use DBMS_SQLDIAG To Diagnose Query Performance Issues (Doc ID 1386802.1)
--診斷SQL 性能
SET ECHO ON
SET LINESIZE 132
SET PAGESIZE 999
SET LONG 999999
SET SERVEROUTPUT ONDECLAREv_sql_diag_task_id varchar2(100);
BEGIN
v_sql_diag_task_id := DBMS_SQLDIAG.CREATE_DIAGNOSIS_TASK (sql_id=>'&sql_id',problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_PERFORMANCE,time_limit => 900,task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(v_sql_diag_task_id,'_SQLDIAG_FINDING_MODE',DBMS_SQLDIAG.SQLDIAG_FINDINGS_FILTER_PLANS);
END;
/--觀察 Check the Task Created
col owner for a12
col task_name for a35
col advisor_name for a25
SELECT DISTINCT owner, task_name, advisor_name,status FROM DBA_ADVISOR_TASKS WHERE advisor_name ='SQL Repair Advisor' AND task_name like '%PERF%' ORDER BY 1;--執行任務
BEGINDBMS_SQLDIAG.EXECUTE_DIAGNOSIS_TASK (task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );
END;
/--看結果
SET LONG 9999999
SET PAGESIZE 500
SELECT DBMS_SQLDIAG.REPORT_DIAGNOSIS_TASK ('PROBLEM_TYPE_PERFORMANCE_task' ) as recommendations FROM DUAL;--如果覺得還行 接受結果
BEGINDBMS_SQLDIAG.ACCEPT_SQL_PATCH(task_name =>'PROBLEM_TYPE_PERFORMANCE_task',task_owner => 'M_LINCS',replace => TRUE);
END;
/--確認生效
SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';--打掃房屋 清理任務
BEGINDBMS_SQLDIAG.DROP_DIAGNOSIS_TASK (task_name => 'PROBLEM_TYPE_PERFORMANCE_task' );
END;
/--確認清理完畢
SELECT DISTINCT owner, task_name, advisor_name FROM DBA_ADVISOR_TASKS WHERE advisor_name ='SQL Repair Advisor'
AND task_name like '%PERF%' ORDER BY 1;--后悔了 刪除優化措施（sql補丁）
SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';---Drop the SQL Patch.
---Replace following patch name with actual name of the SQL Patch
--- from previous query output.
BEGINDBMS_SQLDIAG.DROP_SQL_PATCH (name=> 'SYS_SQLPTCH_<string>');
END;
/--確認刪除優化措施
--- Verify that the SQL Patch has been dropped.
---
SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';

有時可能沒有建議

此路不同，再想他法。