在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一般情况只能得到一行数据。

因此，此子执行计划可能不正确。

posted on 2019-01-15 12:01 xibuhaohao 阅读(689) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

一、SQL多版本导致的问题

2、记录下的SQL_ID，并查看SQL版本

3、产看自执行计划的详细执行过程

4、分析执行计划

公告