使用with as優化sql解決filter
檢視跑2個小時以上的sql
SELECT SQ.SQL_TEXT,
S.SQL_ID,
S.SID,
S.SERIAL#,
S.LAST_CALL_ET,
TO_CHAR(S.LOGON_TIME, 'yyyymmdd hh24:mi:ss') LOGON_TIME
FROM V$SESSION S,
V$SQLTEXT SQ
WHERE S.SQL_ID = SQ.SQL_ID AND
LAST_CALL_ET > 10000 AND
TO_CHAR(LOGON_TIME,'yyyymmdd') =TO_CHAR(SYSDATE,'yyyymmdd');
NTERVAL '7' DAY < D.COMPLETE_DT) 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
移機','資費變更') AND A.COMPLETE_DT - I 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A.BO_ACTION_NAME IN ('新裝', ' 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A.PROD_ID != D.PROD_ID AND 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
ARTY_ID AND A.BO_ID != D.BO_ID AND 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
TAFF_ID LIKE '36%' AND A.PARTY_ID = D.P 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
FROM DW_BO_ORDER D WHERE D.S 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
歡樂送之E6套餐') AND NOT EXISTS (SELECT * 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
'新春歡樂送之E8套餐', '新春 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
) 全省_緊密融合型E9套餐產品規格', 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
緊密融合型E9套餐產品規格', '(新 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
'普通E9','普通新版E8', '全省_ 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
2S','E6移動版', 'E9版1M(老版)', 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
', 'ADSL','LAN', '手機', 'E8 - 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
移機','資費變更') AND B.PROD_SPEC IN ('普通電話 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
D LIKE '36%' AND A.BO_ACTION_NAME IN ('新裝',' 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A.CHANNEL_ID = C.CHANNEL_ID AND A.SO_STAFF_I 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A WHERE A.PROD_ID = B.PROD_ID AND 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
DW_CRM_DAY_USER B, DW_BO_ORDER 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A.PROD_ID FROM DW_CHANNEL C, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
A.SO_STAFF_ID, A.ATOM_ACTION_ID, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
B.START_DT, A.BO_ACTION_NAME, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
B.ACCESS_NUMBER, B.PROD_SPEC, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
CHANNEL_NAME, B.NAME PARTY_NAME, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
B.AREA_NAME, C.NAME 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
SELECT B.AREA_ID, A.PARTY_ID, 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
CREATE TABLE TMP_DTBB_FMT_10000_1 NOLOGGING AS 4kyzp58tz0xya 232 811 15400 20111110 06:27:38
找到的sql為如下:
SELECT
B.AREA_ID,
A.PARTY_ID,
B.AREA_NAME,
C.NAME CHANNEL_NAME,
B.NAME PARTY_NAME,
B.ACCESS_NUMBER,
B.PROD_SPEC,
B.START_DT,
A.BO_ACTION_NAME,
A.SO_STAFF_ID,
A.ATOM_ACTION_ID,
A.PROD_ID
FROM DW_CHANNEL C,
DW_CRM_DAY_USER B,
DW_BO_ORDER A
WHERE A.PROD_ID = B.PROD_ID AND
A.CHANNEL_ID = C.CHANNEL_ID AND
A.SO_STAFF_ID LIKE '36%' AND
A.BO_ACTION_NAME IN ('新裝','移機','資費變更') AND
B.PROD_SPEC IN ('普通電話', 'ADSL','LAN', '手機',
'E8 - 2S','E6移動版', 'E9版1M(老版)',
'普通E9','普通新版E8',
'全省_緊密融合型E9套餐產品規格',
'(新) 全省_緊密融合型E9套餐產品規格',
'新春歡樂送之E8套餐',
'新春歡樂送之E6套餐') AND
NOT EXISTS (SELECT *
FROM DW_BO_ORDER D
WHERE D.STAFF_ID LIKE '36%' AND
A.PARTY_ID = D.PARTY_ID AND
A.BO_ID != D.BO_ID AND
A.PROD_ID != D.PROD_ID AND
A.BO_ACTION_NAME IN
('新裝', '移機','資費變更') AND
A.COMPLETE_DT - INTERVAL '7' DAY < D.COMPLETE_DT)
下面根據sql語句去查詢相關的session,發現該語句已經跑了將近4個小時,從早上6點多開始跑。
SQL> SELECT S.SQL_ID,to_char(S.LOGON_TIME,'yyyymmdd hh24:mi:ss') LOGON_TIME,S.LAST_CALL_ET,S.STATE FROM V$SESSION S WHERE S.SID=232;
SQL_ID LOGON_TIME LAST_CALL_ET STATE
------------- ----------------- ------------ -------------------
4kyzp58tz0xya 20111110 06:27:38 13647 WAITED SHORT TIME
SQL> select 13647/3600 from dual;
13647/3600
----------
3.79083333
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') sysdat from dual;
SYSDAT
-----------------
20111110 10:18:39
OK,現在知道導致資料出的慢的原因,現在就開始來優化這條sql,首先收集一下統計圖,然後看執行計劃
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'LBAS',
tabname => 'DW_BO_ORDER',
estimate_percent => 20,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 4,
granularity => 'ALL',
cascade => TRUE);
END;
/
執行計劃裡面有個filter在裡面,這個是導致這條sql跑的慢的原因。主要語句就是在not exists 裡面居然存在!=, 哎,這sql語句寫得真坑爹,由於有!=的存在,CBO不能選擇 HASH_AJ join的方式,準備修改sql語句,但是不管怎麼改,業務邏輯總不對,最後的結果也不對。得想辦法吧filter搞掉,加多種hint,最後的結果還是一樣的,在朋友的幫下,最後使用了了with as. good最後居然實現了在12秒以內出資料。下面看一下
WITH D AS
(SELECT /*+ materialize */
A.PARTY_ID,
A.BO_ID,
A.PROD_ID,
A.COMPLETE_DT
FROM DW_BO_ORDER A
WHERE STAFF_ID LIKE '36%' AND
A.BO_ACTION_NAME IN ('新裝',
'移機',
'資費變更'))
SELECT B.AREA_ID,
A.PARTY_ID,
B.AREA_NAME,
C.NAME CHANNEL_NAME,
B.NAME PARTY_NAME,
B.ACCESS_NUMBER,
B.PROD_SPEC,
B.START_DT,
A.BO_ACTION_NAME,
A.SO_STAFF_ID,
A.ATOM_ACTION_ID,
A.PROD_ID
FROM DW_CHANNEL C,
DW_CRM_DAY_USER B,
DW_BO_ORDER A
WHERE A.PROD_ID = B.PROD_ID AND
A.CHANNEL_ID = C.CHANNEL_ID AND
A.SO_STAFF_ID LIKE '36%' AND
A.BO_ACTION_NAME IN ('新裝',
'移機',
'資費變更') AND
B.PROD_SPEC IN ('普通電話',
'ADSL',
'LAN',
'手機',
'E8 - 2S',
'E6移動版',
'E9版1M(老版)',
'普通E9',
'普通新版E8',
'全省_緊密融合型E9套餐產品規格',
'(新) 全省_緊密融合型E9套餐產品規格',
'新春歡樂送之E8套餐',
'新春歡樂送之E6套餐') AND
NOT EXISTS (SELECT *
FROM D
WHERE A.PARTY_ID = D.PARTY_ID AND
A.BO_ID != D.BO_ID AND
A.PROD_ID != D.PROD_ID AND
A.COMPLETE_DT - INTERVAL '7'
DAY < D.COMPLETE_DT);
問題解決啦,這次過程中也學到了很多東西。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28673746/viewspace-757815/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用explain優化sqlAI優化SQL
- SQL優化案例-使用with as優化Subquery Unnesting(七)SQL優化
- 使用SQL Profile進行SQL優化案例SQL優化
- 8個SQL講解優化SQL優化
- 在EM中使用SQL Tuning Advisor(SQL優化建議)優化SQLSQL優化
- 使用優化實用工具來優化SQL Server效能優化SQLServer
- 【已解決】Error filter ListenerStartErrorFilter
- 踩坑CBO,解決那些坑爹的SQL優化問題SQL優化
- MySQL之SQL優化詳解(一)MySql優化
- MySQL之SQL優化詳解(二)MySql優化
- MySQL之SQL優化詳解(三)MySql優化
- 【SQL優化】SQL優化工具SQL優化
- Hive使用Calcite CBO優化流程及SQL優化實戰Hive優化SQL
- sql優化之多列索引的使用SQL優化索引
- SQL Server優化之SQL語句優化SQLServer優化
- 大資料SQL優化之資料傾斜解決案例全集大資料SQL優化
- SQL優化SQL優化
- with as優化sql優化SQL
- 效能優化案例-SQL優化優化SQL
- 深入瞭解 TiDB SQL 優化器TiDBSQL優化
- SQL優化----dbms_sqltune詳解(1)SQL優化
- [sql 優化]使用TKPROF格式化TRACE輸出SQL優化
- Oracle 'or exists/in'結合使用引起的filter執行計劃 的優化OracleFilter優化
- SQL使用模糊查詢like的優化SQL優化
- SQL優化(二)(聯合索引的使用)SQL優化索引
- 使用dbms_sqltune進行SQL優化SQL優化
- 使用Filter介面編寫過濾器解決post亂碼Filter過濾器
- 使用SQL調整顧問得到SQL優化建議SQL優化
- 資料庫優化 - SQL優化資料庫優化SQL
- sql優化之邏輯優化SQL優化
- SQL優化:limit分頁優化SQL優化MIT
- MySQL 效能優化之SQL優化MySql優化
- SQL優化--用各種hints優化一條SQLSQL優化
- 【SQL優化】SQL優化的10點注意事項SQL優化
- SQL SERVER中SQL優化SQLServer優化
- SQL優化指南SQL優化
- SQL SERVER優化SQLServer優化
- sql效能優化SQL優化