對造成資料庫壓力翻倍的bad sql的解決方案
早上看到資料庫一個節點的load比昨天翻倍,細查之下,發現主要是由新上線的一條sql引起的,查詢語句如下
select b.sid,substr(b.username,1,10) username,decode(program, null, machine,replace(program,' (TNS V1-V3)','')||decode(machine,null,'@'||terminal)) machine,
substr(
(case
when a.event like 'enq%' then a.event|| ':' || chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||'-'||bitand(a.p1, 65535)||':'||ROW_WAIT_FILE#||':
'||ROW_WAIT_BLOCK#||':'|| ROW_WAIT_ROW#
else a.event
end),1,30) event,
a.p1||'/'||a.p2||'/'||a.p3 param,a.wait_time w,a.SECONDS_IN_WAIT WT,b.sql_id||'/'||b.PREV_SQL_ID SQL, decode(b.status,'ACTIVE','A','INACTIVE','I','KILLED','K',STATUS) status,last_call_et LT,
(sysdate -logon_time )*24*3600 LOGON_TIME
from v$session_wait a,v$session b
where ((a.event not like 'SQL*Net message%' and a.event not like 'rdbms%') or b.status='ACTIVE') and a.sid=b.sid
and b.type='USER'
and b.sid <>(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
order by sql_hash_value;
捕獲的Sql語句結構如下
select a.col1,
b.col2,
...
from a
left join b
on a.b_id = b.id
WHERE sub_id in (:1, :2)
and a.parent_id = 0
order by .id desc;
其中表a有800多萬條記錄,其上的欄位sub_id和parent_id均有索引;而表b只有20條;
結果發現該sql執行計劃的cost很低,只有6,但是buffer gets卻很高,有6位數。
剛開始感覺是統計資訊陳舊,檢視後發現表A是昨天收集的,而表B也是最近收集的,因此排除這個可能。
仔細檢視執行計劃,發現其選擇了parent_id上的索引,而非sub_id,這點比較可疑。
經檢視parent_id=0的記錄佔了全表記錄的80%,而且該列上沒有直方圖,導致CBO選擇執行計劃的時候誤用了基於parent_id列的索引。
原因找到了,接下來是如何解決。
首先,重新收集表a的統計資訊並收集parent_id上的直方圖,考慮表a的訪問十分繁忙,如果現在就讓該遊標失效會導致大量硬解析DB有可能因此而hang住,如果不讓其失效load又一直居高不下,因此在呼叫
Dbms_stats.gather_table_stats的時候指定了no_invalidate為auto(也可以不標註,因為預設就是auto),決定讓oracle自動決定何時去invalidate該遊標。
之後觀察了一段時間,發現該sql還是使用原來的錯誤執行計劃,load還是居高不下。
我們的系統是10.2.0.5的,而oracle好像是10.2以後就可以呼叫dbms_shared_pool.purge了。
Support.oracle.com查了一下,發現呼叫該功能之前還必須要設定事件 alter session set events '5614566 trace name context forever';
由於不能判定其造成的影響,決定暫不使用該功能。
此刻,既不能對錶做grant select on a to dba操作,也不能使用dbms_shared_pool.purge,而bad sql仍在執行。
忽然,我們組長想到了一個方案,對錶b做grant select on b to dba操作(表b訪問熱度相對就少了很多)。
執行過後,sql立即選擇了sub_id上的索引,buffer gets下降到了兩位數,資料庫壓力不到1分鐘就下降到了正常狀態。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-701997/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 造成黏包的原因,及解決方案
- 海量資料庫解決方案資料庫
- 關於資料庫壓力測試的故事資料庫
- SQL Server和Oracle間的資料同步解決方案SQLServerOracle
- Sql server資料庫記錄修改追蹤和恢復的解決方案SQLServer資料庫
- SQL Server資料庫從高版本遷移到低版本的解決方案SQLServer資料庫
- 雲資料庫安全解決方案資料庫
- 資料庫回檔解決方案資料庫
- [資料庫]000 - ?Sysbench 資料庫壓力測試工具資料庫
- 一次資料庫壓力測試的故事資料庫
- 從<<提問的智慧>>引發出對資料庫的一套解決方案資料庫
- 兩臺SQL Server資料同步解決方案SQLServer
- iOS端資料庫解決方案分析iOS資料庫
- 分庫解決方案—資料儲存
- 異構資料的SQL一站式解決方案SQL
- SQL Server置疑資料庫解決方法SQLServer資料庫
- SQL Server 資料庫無法重新命名的解決SQLServer資料庫
- sql server資料庫附加錯誤的解決過程SQLServer資料庫
- SQL Server裡附加資料庫出錯的解決方法SQLServer資料庫
- 兩臺SQL Server資料同步解決方案(轉)SQLServer
- Navicat Premium for Mac:多資料庫管理的終極解決方案REMMac資料庫
- 使用 localhost 無法連線 MySQL 資料庫的解決方案localhostMySql資料庫
- 共享一勞永逸的資料庫編碼解決方案資料庫
- mysql壓縮解決方案MySql
- 解決無法連線SQL Server資料庫的方法BWSQLServer資料庫
- SQL Server 05資料庫被置為“可疑”的解決方法SQLServer資料庫
- 壓塊機PLC資料採集物聯網解決方案
- NewSQL資料庫壓力測試工具系列——SysbenchSQL資料庫
- 一次資料庫匯入解決方案資料庫
- 大型資料庫應用解決方案總結資料庫
- 資料庫大型應用解決方案總結資料庫
- 資料庫自動收縮造成的阻塞資料庫
- 打造基於 PostgreSQL/openGauss 的分散式資料庫解決方案SQL分散式資料庫
- sql server 資料庫備份方案SQLServer資料庫
- SQL SERVER 資料庫被標記為“可疑”的解決辦法SQLServer資料庫
- SQL SERVER 資料庫遷移孤立使用者的解決方法SQLServer資料庫
- SQL Server資料庫對大容量表的操作SQLServer資料庫
- 大資料解決方案大資料