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>
如上图的代码，就出现了问题。
所以学一下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/