Oracle执行计划

　　建立与oracle的web程序，经常性出现sql性能不高导致的问题，比如程序好好的突然数据库查询变得很慢，几乎加载不了，这时候就有可能是oracle查询计划出错的原因。

 1 <sql id="queryCalendarBySeqnos">
 2         
 3        SELECT * FROM(     
 4        select /*+ leading(ii) index(ii CALENDAR_INVITE_IDX1) */ ii.RECMYSMS,ii.RECMYEMAIL,ii.RECMOBILE,ii.RECEMAIL,ii.enable,ii.status,
 5        case(ii.inviteauth) when -1 then 0 else 1 end as isInvitedCalendar,
 6        0 as isSharedCalendar,
 7        0 as isSubCalendar,
 8        nvl(l.labelname,sl.labelname) as labelname,
 9        nvl(l.color,sl.color) as color,
10        nvl(l.gid,sl.gid) as lableGid,
11        i.createtime,
12        <include refid="calendarInfoColumnNamesForSelf"/>
13        FROM calendar_info i, calendar_invite_info ii,
14        calendar_label l,calendar_sys_label sl
15        WHERE 
16        i.seqno = ii.calseqno
17        and i.labelid = l.seqno(+) 
18        and i.labelid = sl.seqno(+)
19        AND ii.inviteruin = #uin#
20        and i.isdelflag = 0
21        and ii.isdelflag = 0
22        <![CDATA[
23        AND ii.status <> 2
24         ]]>
25        AND i.seqno in 
26        <iterate property="gids" open="(" close=")" conjunction=",">  
27                #gids[]#  
28         </iterate>
29        
30        UNION ALL 
31        
32        select /*+ leading(ls) index(ls CALENDAR_LABEL_SHARE_IDX2) */ 0 as RECMYSMS,0 as RECMYEMAIL,'' as RECMOBILE,'' as RECEMAIL,0 as enable,1,
33        0 as isInvitedCalendar,
34        1 as isSharedCalendar,
35        0 as isSubCalendar,
36        l.labelname,ls.color,
37        l.gid as lableGid,
38        i.createtime,
39        <include refid="calendarInfoColumnNames"/>
40       from calendar_info i, calendar_label_share_info ls,
41       calendar_label l
42       <![CDATA[
43       where 
44       i.labelid = ls.labelid
45       and i.labelid = l.seqno
46       and ls.sharetype <> -1
47       and ls.shareuin = #uin#
48       and ls.status = 1
49       and i.isdelflag = 0
50 
51        ]]>
52       AND i.seqno in
53         <iterate property="gids" open="(" close=")" conjunction=",">  
54                #gids[]#  
55         </iterate>
56        
57        
58        UNION ALL 
59        
60        select /*+ leading(cs) index(cs UNI_CALENDAR_SUBSCRIPTION) */ 0 as RECMYSMS,0 as RECMYEMAIL,'' as RECMOBILE,'' as RECEMAIL,0 as enable,1,
61        0 as isInvitedCalendar,
62        0 as isSharedCalendar,m
63        1 as isSubCalendar,
64        l.labelname,cs.color,
65        l.gid as lableGid,
66        i.createtime,
67        <include refid="calendarInfoColumnNames"/>
68       from calendar_info i, calendar_subscription cs,
69       calendar_label l
70       <![CDATA[
71       where 
72       i.labelid = cs.labelid
73       and i.labelid = l.seqno
74       and cs.uin=#uin#
75       and l.isPublic=1
76       and i.isdelflag = 0
77        ]]>
78       AND i.seqno in
79         <iterate property="gids" open="(" close=")" conjunction=",">  
80                #gids[]#  
81         </iterate>
82        )
83     </sql>

View Code

如上图的代码，就出现了问题。

所以学一下oracle的查询计划吧。

1、设置oracle查询计划，如下步骤：

2、在sql-plus查询sql的执行计划，如图：

SQL> explain plan for select count(*) from   calendar01.calendar_label@to_calendar.LOCALDOMAIN  where    istodoemail =1  and color <>'#f2b73a';
 
Explained
 
SQL> 
SQL> 
SQL> 
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4083529702
--------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|                |     1 |    21 |     6   (0)| 0
|   1 |  SORT AGGREGATE        |                |     1 |    21 |            |
|*  2 |   TABLE ACCESS FULL    | CALENDAR_LABEL |    12 |   252 |     6   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A1"."ISTODOEMAIL"=1 AND "A1"."COLOR"<>'#f2b73a')
Note
-----
   - fully remote statement
 
18 rows selected
 
SQL>

我们需要掌握它的执行先后顺序和每个字段代表的含义。

执行计划的执行顺序为：
先从计划开头一直往右看，直到最右边并列的代码部分，如果见到并列的，就从上往下看，对于并列的步骤，靠上的先执行，对于不并列的步骤，靠右的先执行

中文参照：http://www.cnblogs.com/kerrycode/archive/2012/05/24/2517210.html

英文参照：http://perumal.org/how-to-read-an-oracle-sql-execution-plan/