有时懒得分析或语句太长，可以尝试用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%';

有时可能没有建议

此路不同，再想他法。