一個哥們QQ問我，這個SQL怎麼優化，它要跑160秒
SQL> explain plan for select a.so_region_code so_region_code,
2 a.so_county_code so_county_code,
3 a.so_org_id so_org_id,
4 d.org_type_id org_type_id,
5 a.op_id op_id,
6 nvl(c.brand, 0) brand,
7 e.res_code,
8 a.busi_code,
9 a.so_nbr,
10 decode(a.isnormal,
11 2,
12 -count(distinct a.so_nbr),
13 0,
14 count(distinct a.so_nbr),
15 0) so_amount,
16 sum(decode(b.book_item_id, 23000002, item_total, 0)) / 100 sim_fee,
17 sum(decode(b.book_item_id, 23000001, item_total, 0)) / 100 sim_fee_add,
18 sum(decode(b.book_item_id, 27000003, item_total, 0)) / 100 sim_fee_discount,
19 sum(decode(b.book_item_id, 21000013, 0, b.item_total)) / 100 total_fee
20 from zk.cm_busi_201108 a,
21 zk.cm_busi_charge_201108 b,
22 zk.cm_user c,
23 xg.sys_organizations d,
24 zy.res_sim e
25 where a.so_nbr = b.so_nbr(+)
26 and a.serv_id = c.serv_id
27 and c.sim_id = e.sim_id
28 and a.so_org_id = d.org_id
29 and (b.book_item_id in (23000001, 23000002, 27000003) or
30 a.busi_code in (1,
31 2,
32 4,
33 5,
34 8,
35 11,
36 14,
37 15,
38 17,
39 18,
40 19,
41 21,
42 24,
43 25,
44 28,
45 99,
46 101,
47 104,
48 105,
49 201,
50 204,
51 205,
52 206,
53 2201,
54 1023,
55 1006,
56 3312,
57 2251))
58 and a.op_id != 71010264
59 and a.so_date >= to_date('20110831000000', 'yyyymmddhh24miss')
60 and a.so_date <= to_date('20110831235959', 'yyyymmddhh24miss')
61 and a.so_county_code =7111
62 and a.so_nbr is not null
63 group by a.so_region_code,
64 a.so_county_code,
65 a.so_org_id,
66 d.org_type_id,
67 a.op_id,
68 c.brand,
69 e.res_code,
70 a.busi_code,
71 a.so_nbr,
72 a.isnormal;
已解釋。
已用時間: 00: 00: 00.03
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 | 11741 | 1703 (1)| | |
| 1 | SORT GROUP BY | | 59 | 11741 | 1703 (1)| | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN OUTER | | | | | | |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | |
| 5 | NESTED LOOPS | | 46 | 7820 | 1670 (1)| | |
| 6 | NESTED LOOPS | | 49 | 7056 | 146 (2)| | |
| 7 | NESTED LOOPS | | 46 | 5244 | 53 (2)| | |
|* 8 | TABLE ACCESS BY INDEX ROWID | CM_BUSI_201108 | 46 | 4784 | 7 (15)| | |
|* 9 | INDEX RANGE SCAN | DX_BUSI_SO_DATE_201108 | 166K| | 3 (34)| | |
| 10 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | |
|* 11 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L |
|* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | |
| 14 | PARTITION RANGE ALL | | | | | 1 | 10 |
|* 15 | INDEX RANGE SCAN | IDX_SIM_SIM | 1 | | 31 (4)| 1 | 10 |
| 16 | TABLE ACCESS FULL | CM_BUSI_CHARGE_201108 | 474 | 13746 | 32 (4)| | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM_ID"=23000002 OR "B"."BOOK_ITEM_ID"=27000
"A"."BUSI_CODE"=1 OR "A"."BUSI_CODE"=2 OR "A"."BUSI_CODE"=4 OR "A"."BUSI_CODE"=5 OR "A"."BUSI
"A"."BUSI_CODE"=11 OR "A"."BUSI_CODE"=14 OR "A"."BUSI_CODE"=15 OR "A"."BUSI_CODE"=17 OR "A"."
"A"."BUSI_CODE"=19 OR "A"."BUSI_CODE"=21 OR "A"."BUSI_CODE"=24 OR "A"."BUSI_CODE"=25 OR "A"."
"A"."BUSI_CODE"=99 OR "A"."BUSI_CODE"=101 OR "A"."BUSI_CODE"=104 OR "A"."BUSI_CODE"=105 OR "A
"A"."BUSI_CODE"=204 OR "A"."BUSI_CODE"=205 OR "A"."BUSI_CODE"=206 OR "A"."BUSI_CODE"=1006 OR
"A"."BUSI_CODE"=2201 OR "A"."BUSI_CODE"=2251 OR "A"."BUSI_CODE"=3312)
3 - access("A"."SO_NBR"="B"."SO_NBR"(+))
8 - filter("A"."SO_COUNTY_CODE"=7111 AND "A"."OP_ID"<>71010264 AND "A"."SO_NBR" IS NOT NULL)
9 - access("A"."SO_DATE">=TO_DATE(' 2011-08-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SO_D
2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
11 - access("A"."SO_ORG_ID"="D"."ORG_ID")
13 - access("A"."SERV_ID"="C"."SERV_ID")
15 - access("C"."SIM_ID"="E"."SIM_ID")
已選擇40行。
已用時間: 00: 00: 00.20
CM_BUSI_201108 是大表，有3千多萬的資料，CM_USER也是一個大表，有3千多萬的資料 其他表都是小表
注意觀察第9行，CBO認為它傳回166k的資料，回表的時候又過濾有filter過濾，這個時候CBO認為它傳回46行，先不管這46行 CBO計算是對是錯，單單就是索引掃描傳回166k到表
CM_BUSI_201108 去做166k次應該也很耗費時間。是以給出優化建議 對表CM_BUSI_201108進行分區，可以根據SO_DATE做range分區，另外SO_COUNTRY_CODE可以檢視值多不多，如果不多可以做 range-list分區
他最終隻做了range分區，并且讓他建立了一個本地有字首的組合索引(他最開始建立的是global索引，沒有起到優化效果)
create index YI_XXX ON CM_BUSI_201108(SO_DATE,SO_COUNTRY_CODE) LOCAL
執行計劃如下：
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 3264 | 635 (1)| | |
| 1 | TABLE ACCESS BY LOCAL INDEX ROWID | RES_SIM | 1 | 26 | 32 (4)| | |
| 2 | NESTED LOOPS | | 17 | 3264 | 635 (1)| | |
| 3 | NESTED LOOPS | | 18 | 2988 | 75 (2)| | |
|* 4 | FILTER | | | | | | |
| 5 | NESTED LOOPS OUTER | | | | | | |
| 6 | NESTED LOOPS | | 17 | 1870 | 24 (5)| | |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| CM_BUSI_201108 | 17 | 1700 | 7 (15)| 6 | 6 |
|* 8 | INDEX RANGE SCAN | YI_XXX | 61917 | | 3 (34)| 6 | 6 |
| 9 | TABLE ACCESS BY INDEX ROWID | SYS_ORGANIZATIONS | 1 | 10 | 2 (50)| | |
|* 10 | INDEX UNIQUE SCAN | PK_SYS_ORGANIZATIONS | 1 | | | | |
|* 11 | INDEX RANGE SCAN | PK_CM_BUSI_CHARGE_201108 | 1 | 26 | 2 (50)| | |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | CM_USER | 1 | 30 | 3 (34)| ROWID | ROW L |
|* 13 | INDEX UNIQUE SCAN | PK_ZK_CM_USER | 1 | | 2 (50)| | |
| 14 | PARTITION RANGE ALL | | | | | 1 | 10 |
|* 15 | INDEX RANGE SCAN | IDX_SIM_SIM | 1 | | 31 (4)| 1 | 10 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("B"."BOOK_ITEM_ID"=23000001 OR "B"."BOOK_ITEM_ID"=23000002 OR "B"."BOOK_ITEM_ID"=27000003)
7 - filter("A"."OP_ID"<>71010264)
8 - access("A"."SO_DATE">=TO_DATE(' 2011-08-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."SO_COUNTY_CODE"=7111 AND
"A"."SO_DATE"<=TO_DATE(' 2011-08-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
filter("A"."SO_COUNTY_CODE"=7111)
10 - access("A"."SO_ORG_ID"="D"."ORG_ID")
11 - access("A"."SO_NBR"="B"."SO_NBR"(+))
13 - access("A"."SERV_ID"="C"."SERV_ID")
15 - access("C"."SIM_ID"="E"."SIM_ID")
分區之後，需要過濾的資料量大大減少，這樣嵌套循環執行的次數也大大減少，最終SQL能在4秒左右跑完，其實這個優化方案并不是最優的，由于不能連接配接到他的資料庫，這個SQL的優化就暫時告一段落。分區對SQL的優化還是非常有幫助的。
![Cocos、Lua遊戲記憶體釋放之我見[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)