在SQL优化过程，有时候需要查看哪些SQL具有多个执行计划（Multiple Executions Plans for the same SQL statement），因为同一个SQL有多个执行计划一般意味着代码有问题或某些其它原因，例如，SQL语句使用绑定变量，但是绑定变量的类型或长度可能不一致会导致同一SQL出现不同执行计划。
一、SQL多版本导致的问题
1、某生产数据库CPU使用突然100%，登录数据库查看，发现，都是某条查询的SQL在运行，latch free等等待事件。不得已，先记录下SQL_ID,再Kill 掉这些session。
select 'alter system kill session '||''''||s.SID||','||s.SERIAL#||''''||';',s.SID,s.blocking_session,s.MACHINE,s.OSUSER,s.PROGRAM, s.USERNAME,s.last_call_et,a.SQL_ID,s.LOGON_TIME,a.SQL_TEXT,a.SQL_FULLTEXT, w.EVENT,a.DISK_READS,a.BUFFER_GETS from v$process p,v$session s,v$sqlarea a,v$session_wait w where p.ADDR = s.PADDR and s.SQL_ID = a.sql_id and s.sid = w.SID and s.STATUS = 'ACTIVE' and s.PROGRAM !='plsqldev.exe' and s.OSUSER !='oracle' --and a.SQL_TEXT like 'select%' order by s.last_call_et desc;
2、记录下的SQL_ID，并查看SQL版本
select sql_id,plan_hash_value,child_number from v$sql where sql_id='an8watgthzfgj';
3、产看自执行计划的详细执行过程
1）查询SQL所有执行计划
SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('an8watgthzfgj',null))2）查询SQL的CHILD NUMBER为0的执行计划SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('an8watgthzfgj',0))3）查询SQL的CHILD NUMBER为1的执行计划SELECT * FROM TABLE(SYS.DBMS_XPLAN.DISPLAY_CURSOR('an8watgthzfgj',1))SQL_ID an8watgthzfgj, child number 0
------------------------------------- Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1090 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1090 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1090 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 339 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 339 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 751 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 1-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1097 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1097 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1097 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 338 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 338 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 759 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 2-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1070 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1070 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1070 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 339 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 339 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 731 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 3-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 530 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 530 (1)| 00:00:07 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 530 (1)| 00:00:07 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 915 | 530 (1)| 00:00:07 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 915 | 529 (0)| 00:00:07 | | || 7 | NESTED LOOPS | | 1 | 915 | 529 (0)| 00:00:07 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 383 | 266 (0)| 00:00:04 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 383 | 266 (0)| 00:00:04 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 265 (0)| 00:00:04 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 262 (0)| 00:00:04 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 262 (0)| 00:00:04 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 532 | 263 (0)| 00:00:04 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 4-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1017 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1017 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1017 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 343 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 343 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 674 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 5-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1106 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1106 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1106 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 355 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 355 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 751 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1)) SQL_ID an8watgthzfgj, child number 6-------------------------------------Plan hash value: 778449213 --------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |--------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 7 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 7 (0)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 7 (0)| 00:00:01 | | ||* 4 | FILTER | | | | | | | || 5 | NESTED LOOPS | | 1 | 916 | 7 (0)| 00:00:01 | | || 6 | NESTED LOOPS | | 1 | 916 | 7 (0)| 00:00:01 | | || 7 | PARTITION RANGE ITERATOR | | 1 | 533 | 4 (0)| 00:00:01 | KEY | KEY || 8 | TABLE ACCESS BY LOCAL INDEX ROWID| GWTRXS | 1 | 533 | 4 (0)| 00:00:01 | KEY | KEY ||* 9 | INDEX RANGE SCAN DESCENDING | INDX_GWT_CREATEDATE | 1 | | 3 (0)| 00:00:01 | KEY | KEY || 10 | PARTITION RANGE ITERATOR | | 1 | | 2 (0)| 00:00:01 | KEY | KEY ||* 11 | INDEX RANGE SCAN | INDX_GWO_CREATEDATE | 1 | | 2 (0)| 00:00:01 | KEY | KEY ||* 12 | TABLE ACCESS BY LOCAL INDEX ROWID | GWORDERS | 1 | 383 | 3 (0)| 00:00:01 | 1 | 1 |-------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - access("G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1 AND "G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd')) 11 - access("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1) 12 - filter(("O"."ORDERNUM"=:1 AND "G"."GWORDERS_ID"="O"."ID")) SQL_ID an8watgthzfgj, child number 7-------------------------------------Plan hash value: 3102792867 ----------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 5 (100)| | | ||* 1 | VIEW | | 1 | 5032 | 5 (20)| 00:00:01 | | ||* 2 | COUNT STOPKEY | | | | | | | || 3 | VIEW | | 1 | 5019 | 5 (20)| 00:00:01 | | ||* 4 | SORT ORDER BY STOPKEY | | 1 | 1011 | 5 (20)| 00:00:01 | | ||* 5 | FILTER | | | | | | | || 6 | NESTED LOOPS | | 1 | 1011 | 4 (0)| 00:00:01 | | || 7 | NESTED LOOPS | | 1 | 1011 | 4 (0)| 00:00:01 | | || 8 | PARTITION RANGE ITERATOR | | 1 | 377 | 2 (0)| 00:00:01 | KEY | KEY ||* 9 | TABLE ACCESS BY LOCAL INDEX ROWID| GWORDERS | 1 | 377 | 2 (0)| 00:00:01 | KEY | KEY ||* 10 | INDEX RANGE SCAN | INDX_GWO_ORDERNUM | 1 | | 1 (0)| 00:00:01 | KEY | KEY || 11 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 12 | INDEX RANGE SCAN | INDX_GWT_GWORDERS_ID | 1 | | 1 (0)| 00:00:01 | KEY | KEY ||* 13 | TABLE ACCESS BY LOCAL INDEX ROWID | GWTRXS | 1 | 634 | 2 (0)| 00:00:01 | 1 | 1 |---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("N">:8) 2 - filter(ROWNUM<=:6+:7) 4 - filter(ROWNUM<=:6+:7) 5 - filter((TO_DATE(:5,'yyyy-mm-dd')+1>TO_DATE(:3,'yyyy-mm-dd') AND TO_DATE(:4,'yyyy-mm-dd')+1>TO_DATE(:2,'yyyy-mm-dd'))) 9 - filter(("O"."CREATEDATE">=TO_DATE(:3,'yyyy-mm-dd') AND "O"."CREATEDATE"<TO_DATE(:5,'yyyy-mm-dd')+1)) 10 - access("O"."ORDERNUM"=:1) 12 - access("G"."GWORDERS_ID"="O"."ID") 13 - filter(("G"."CREATEDATE">=TO_DATE(:2,'yyyy-mm-dd') AND "G"."CREATEDATE"<TO_DATE(:4,'yyyy-mm-dd')+1))4、分析执行计划
从执行计划中，可以看到，第七个执行计划错误，没有使用上索引"O"."ORDERNUM"=:1，导致检索范围变得非常庞大。
从SQL分析，ORDERNUM比较唯一，得到的O.ID也是唯一，SQL一般情况只能得到一行数据。
因此，此子执行计划可能不正确。