一個2600萬資料的SQL統計,SQL調優(原創)
昨天工程師反映客戶一個sql的統計等的n慢,我plsql跑了一下,需要1110秒,近20分鐘,這個現狀肯定無法忍受。後進行分析調整之後查詢速度在6-12秒之內,調整步驟如下:
現狀:linux, oracle 10g ,4G記憶體,sga1.5G, 調整shared_pool300M,這個shared_pool的調整有些懷疑。先擱置。
表BCM_MONTHGASFEE 資料=26494361
原sql
-————————————————————————
select substr(f.dataenddate, 0, 7) gasdate,
min(o.name) officename,
to_char(sum(f.gasmonthcost)) cost
from BCM_MONTHGASFEE f, OPM_ORGAN O
where f.officecode = o.code
and f.ChargeMethodCode = '1'
and (f.bcharge = 0 or
(f.bcharge = 1 and
f.ChargeTime >
to_timestamp('2010-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')))
and (f.officecode like '110%'
and f.dataenddate between '2005-01-01' and '2010-07-31')
group by substr(f.dataenddate, 0, 7), o.code
order by substr(f.dataenddate, 0, 7), o.code
OPM_ORGAN O=90條
——————————————————————————
進行sql語句分析,得結果如下:
select substr(f.dataenddate, 0, 7) gasdate,
f.officecode,
to_char(sum(f.gasmonthcost)) cost
from BCM_MONTHGASFEE f
where
((f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and f.bcharge = 1) or f.bcharge = 0 )
and f.ChargeMethodCode = '1'
and f.dataenddate between '2005-01-01' and '2010-07-31'
and f.officecode like '110%'
group by substr(f.dataenddate, 0, 7), f.officecode
-----------------------------------
進行計劃分析
explain plan for
....{sql語句}
select * from table(dbms_xplan.display());
是全表掃描,後來建立索引1:officecode, dataenddate ;索引2:bcharge, ChargeTime 進行逐步分析,仍然是全表掃描。
後刪除索引1和索引2,建立索引3:OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, 到最後仍然是全表掃描,後來發現,原因在
sum(f.gasmonthcost)這個條語句上,懷疑是sum,去掉sum仍然全表,後來看因為gasmonthcost列未在索引範圍之內,後把該索引加上,最終索引是:create index IDX_BCM_MONTHGASFEE_OFF1
on BCM_MONTHGASFEE (OFFICECODE, DATAENDDATE, BCHARGE, CHARGETIME, ChargeMethodCode, GASMONTHCOST) local;
語句調整如下:
select a.gasdate, b.name, a.cost
from (select substr(f.dataenddate, 0, 7) gasdate,
f.officecode,
to_char(sum(f.gasmonthcost)) cost
from BCM_MONTHGASFEE f
where
((f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and f.bcharge = 1) or f.bcharge = 0 )
and f.ChargeMethodCode = '1'
and f.dataenddate between '2005-01-01' and '2010-07-31'
and f.officecode like '110%'
group by substr(f.dataenddate, 0, 7), f.officecode
)a, opm_organ b
where b.officecode=a.code
order by a.gasdate, a.officecode;
執行Sql,查詢出結果25秒.
經過以上的Sql調整邏輯讀和物理讀已經大大縮小了
但是邏輯讀還是特別大
112514 consistent gets
72207 physical reads
下面進行調整邏輯讀
調整sql如下:
--------------------------------------------
select a.gasdate, b.name, a.cost
from (select gasdate, officecode, to_char(sum(cost)) cost
from (select substr(f.dataenddate, 0, 7) gasdate,
f.officecode,
f.gasmonthcost cost
from BCM_MONTHGASFEE f
where
(f.ChargeTime > to_timestamp('2009-08-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and f.bcharge = 1)
and f.ChargeMethodCode = '1'
and f.dataenddate between '2005-01-01' and '2010-07-31'
and f.officecode like '110%'
union
select substr(f.dataenddate, 0, 7) gasdate,
f.officecode,
f.gasmonthcost cost
from BCM_MONTHGASFEE f
where f.bcharge = 0
and f.ChargeMethodCode = '1'
and f.dataenddate between '2005-01-01' and '2010-07-31'
and f.officecode like '110%'
) x
group by x.gasdate, x.officecode
)a, opm_organ b
where a.officecode=b.code
order by a.gasdate, a.officecode;
經調整之後:
54533 consistent gets
8392 physical reads
執行Sql,查詢出結果9秒.
達到預期。
但是資料和原sql查詢出來的資料有出入,調整之後的資料512條,原sql查詢是477條,有些疑惑!!!
但是查詢數的資料一樣的啊,奇怪!!
---一下查詢結果一樣 26494957條資料
select count(1)
from BCM_MONTHGASFEE f, OPM_ORGAN O
where f.officecode = o.code ;
select count(1)
from BCM_MONTHGASFEE f;
---關於sql優化,望大家討論
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/3090/viewspace-670347/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【sql調優】系統資訊統計SQL
- 又一個複合索引的SQL調優索引SQL
- 資料庫SQL調優的幾種方式資料庫SQL
- 一條大sql的調優SQL
- 一次SQL調優 聊一聊 SQLSERVER 資料頁SQLServer
- SQL Server一次SQL調優案例SQLServer
- 生產sql調優之統計資訊分析SQL
- SQL調優SQL
- 調優: 一個SQL現在30分鐘,而原來只要2秒SQL
- 一個sql的優化SQL優化
- SQL 調優一般思路SQL
- 記一次SQL Server刪除SQL調優SQLServer
- 【資料操作】SQL語句效能調整原則SQL
- 一次捕獲SQL調優資訊的指令碼 sql9.sqlSQL指令碼
- 一次捕獲SQL調優資訊的指令碼 sql10.sqlSQL指令碼
- sql調優1SQL
- oracle sql調優OracleSQL
- 一個SQL優化SQL優化
- 有關效能調整的查詢和pub上的一個sql調優!SQL
- 使用USE_HASH Hint調優一個SQL語句SQL
- 生產系統pl/sql調優案例SQL
- Teradata SQL調優SQL
- 一個SQL語句的優化SQL優化
- 記一次SQL調優過程SQL
- 【sql調優】使用繫結變數(一)SQL變數
- sql調優一例---索引排序hintSQL索引排序
- 一條sql語句的建議調優分析SQL
- MySQL調優篇 | SQL調優實戰(5)MySql
- 資料庫優化SQL資料庫優化SQL
- sql海量資料優化SQL優化
- 簡單Sql語句統計每年每個月的資料,每個月為資料的每列,簡單SQL練習SQL
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- sql統計各種奇葩的資料庫表資料SQL資料庫
- MySQL索引和SQL調優MySql索引
- SQL調優真實案例SQL