修補(bǔ)DBA短板：監(jiān)控SQL優(yōu)化案例兩則

前幾天跟銷售拜訪潛在客戶時(shí)，客戶提到他們近期很頭疼的問題：他們自己寫的一個(gè)查詢表空間的語(yǔ)句從上周開始跑不出來(lái)了，做了很多嘗試未果，比較苦惱，畢竟作為DBA不知道庫(kù)的空間使用情況，客戶心里是有點(diǎn)發(fā)毛的。聽完客戶描述，我也馬上回復(fù)說(shuō)這個(gè)問題很常見，都在我們?nèi)粘Ｑ矙z的范圍內(nèi)，解決起來(lái)很簡(jiǎn)單。一般查詢表空間的語(yǔ)句都是用到了DBA_FREE_SPACE這張視圖，查詢起來(lái)慢，常見原因一般如下：數(shù)據(jù)字典信息/固化視圖統(tǒng)計(jì)信息過(guò)舊；回收棧內(nèi)對(duì)象過(guò)多。處理方式：exec dmbs_stats.gather_fixed_objects_stats;exec dbms_stats.gather_dictionary_stats(degree =>8,cascade =>true);purge recyclebin;客戶聽完介紹的解決方案，表示認(rèn)同并希望現(xiàn)場(chǎng)幫他們處理好問題，熱情地帶我去他的工位。雖然庫(kù)并非生產(chǎn)數(shù)據(jù)庫(kù)，但訪問這個(gè)庫(kù)依然需要通過(guò)堡壘機(jī)，而且命令需要手敲?？蛻舻难菔究蛻敉ㄟ^(guò)shell腳本SQLplus進(jìn)入數(shù)據(jù)庫(kù)后運(yùn)行語(yǔ)句類似以下SQL：SELECT UPPER(F.TABLESPACE_NAME) AS "表空間名稱",ROUND(D.AVAILB_BYTES ,2) AS "表空間大小(G)",ROUND(D.MAX_BYTES,2) AS "最終表空間大小(G)",ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS "已使用空間(G)",TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,2), '999.99') AS "使用比",ROUND(F.USED_BYTES, 6) AS "空閑空間(G)",F.MAX_BYTES AS "最大塊(M)"FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6) MAX_BYTESFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 4 DESC客戶運(yùn)行了腳本，果然卡住了一樣，無(wú)返回結(jié)果。演示完，客戶讓出了座位，示意我可以上機(jī)操作了。因?yàn)椴皇巧a(chǎn)庫(kù)，客戶也表示可以隨意操作，于是沒有去進(jìn)一步確認(rèn)信息，順利完成了上面列的操作。有趣的地方來(lái)了客戶開始驗(yàn)證效果的時(shí)候，比較尷尬，依然卡在那兒?？蛻舻胤?jīng)]有監(jiān)控，也沒有我習(xí)慣的腳本，客戶打開plSQLdeveloper后，我看了一下在運(yùn)行的SQL等待事件是單塊兒讀，我有點(diǎn)猶豫要不要手敲那些工具SQL的時(shí)候，有趣的地方也來(lái)了~ 客戶的DBA開始討論起了原因：一個(gè)客戶DBA說(shuō)，他覺得是IO太慢，因?yàn)閹?kù)不是放在存儲(chǔ)上的，而且raid可能用的是raid 5之類。另一個(gè)客戶DBA說(shuō)，可能是數(shù)據(jù)庫(kù)太大了，性能有影響。為了避免問題走偏，我快速打了個(gè)快照，做了awr報(bào)告，確認(rèn)了一下單塊讀約3ms，數(shù)據(jù)文件個(gè)數(shù)約800個(gè)。證偽了以上假設(shè)。那到底為什么SQL查詢還是不快呢？我有點(diǎn)猶豫，畢竟堡壘機(jī)命令都手敲不能直接跑自己的腳本包?？蛻糁饕?fù)責(zé)的DBA此時(shí)主動(dòng)給臺(tái)階讓我下，讓我找時(shí)間再看看，回去了再研究，畢竟他們也快下班了。意外的效果這個(gè)臺(tái)階當(dāng)然不能下…… 在10046跟moitor報(bào)告中，個(gè)人還是更傾向monitor報(bào)告，于是腳本加了monitor的hint，打算再跑一次。另外一個(gè)會(huì)話準(zhǔn)備編寫腳本的時(shí)候，SQL瞬間出了結(jié)果。常用腳本這里也貼一下：set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feeDBAck offspool &1..&2--active/html/EMselect dbms_SQLtune.report_SQL_monitor(type=>'&2', SQL_id=>'&1', SQL_exec_id=>null, report_level=>'ALL') monitor_report from dual;spool off客戶DBA說(shuō)這個(gè)hint有效果，但是我也不打算糊弄客戶，跟客戶解釋了這個(gè)hint跟跑起來(lái)快了沒什么關(guān)系，并用 /*+ xxx */這個(gè)改動(dòng)，再次運(yùn)行，果然也很快?？蛻暨\(yùn)行原來(lái)的語(yǔ)句，依然跑不出來(lái)。這時(shí)候，基本已經(jīng)知道原因了。雖然講道理，收集了統(tǒng)計(jì)，SQL的執(zhí)行計(jì)劃應(yīng)該是會(huì)失效，并重新解析的，這里顯然那個(gè)SQL的執(zhí)行計(jì)劃應(yīng)該還是原來(lái)的，沒有變動(dòng)?？紤]到不是生產(chǎn)庫(kù)，就直接flush了shared pool。再次運(yùn)行那個(gè)問題SQL，順利秒出結(jié)果。二、ASH信息采集慢某客戶反饋監(jiān)控上線后，在監(jiān)控的數(shù)十套數(shù)據(jù)庫(kù)中有一套庫(kù)監(jiān)控SQL的數(shù)據(jù)庫(kù)時(shí)間占比較高，監(jiān)控自身顯示是一條采集ASH數(shù)據(jù)的語(yǔ)句。經(jīng)過(guò)了解，其他庫(kù)該語(yǔ)句執(zhí)行均在1s以內(nèi)，在其中一個(gè)數(shù)據(jù)庫(kù)上運(yùn)行時(shí)間可達(dá)4分鐘，監(jiān)控中我們馬上發(fā)現(xiàn)了該SQL的監(jiān)視報(bào)告。監(jiān)視報(bào)告快速定位打開報(bào)告，通過(guò)Wait Activity中可發(fā)現(xiàn)55%時(shí)間是read by other session等待事件，還有36%為gc相關(guān)等待事件，結(jié)合2GB的IO，可以基本可知問題原因?yàn)镾QL讀取IO量過(guò)多，在RAC高并發(fā)環(huán)境下，性能問題被放大。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 一、表空間信息查詢慢 前幾天跟銷售拜訪潛在客戶時(shí)，客戶提到他們近期很頭疼的問題：他們自己寫的一個(gè)查詢表空間的語(yǔ)句從...
    rewq123閱讀 461評(píng)論 0贊 0
  • 一、建立用戶/表空間/分配權(quán)限/刪除表空間 1）注意表空間存放目錄 2）/*分為四步 */ /*第1步：創(chuàng)建臨時(shí)表...
    MrGago閱讀 859評(píng)論 0贊 0
  • 本文轉(zhuǎn)自 http://www.cnblogs.com/jack204/archive/2012/04/30/24...
    Michael_林閱讀 907評(píng)論 0贊 0
  • 如何將小表放入keep池中? alter table xxx storage(buffer_pool keep);...
    一點(diǎn)也不想吃辣閱讀 463評(píng)論 0贊 2
  • 莫名的感慨…… 到底是時(shí)間流逝的太快……還是…… 我們奔走的步伐太過(guò)匆忙…… 踏進(jìn)光陰的隧道，雖能賞遍一路風(fēng)景...
    妥妥滴圓閱讀 370評(píng)論 4贊 16

友情鏈接更多精彩內(nèi)容