DBA麻煩終結者之路 http://blog.itpub.net/xzh2000

DBA麻煩終結者之路

轉載

       或許你厭倦了朝五晚六的開發工作，開始考ocp；或許你剛走出象牙塔，立志在資料庫管理方面大幹一場？經過一翻努力，終於有了份dba的工作，忐忑不安地坐在電腦旁，激動得手心冒汗，卻不知如何去調整、最佳化資料庫；面對突如其來的故障，電話響個不停，老闆虎視耽耽地站在身旁，不知你些時是否能靜下心來？

       可能讀了許多資料庫管理、調優、備份與恢復、pl/sql開發方面的書，也可能做了很多故障排除的實驗，可當故障真正降臨時，卻顯得那麼可怕，通常正在運轉的生產資料庫一直處於效能惡化趨勢，麻煩總是從你意想不到的地方出現，阿門。

資料庫系統本身永遠是的值得注意的麻煩製造者：數不清的bug、物件失效、磁片碎片、索引重建以及很多沒有顧及到的突發事件等；沒有sql經驗的程式設計師也是很歷害的麻煩製造者：編寫效能不佳的sql以及建立一些效能較差的儲存物件；最可怕的麻煩製造者是誰呢？吼吼，正是來源於dba本身，對資料庫一個微小的修改，或許就導致一場災難。

做為一個新手dba來講,有關oracle體系統結構的概念非常重要，如果想比較透徹地理解這些概念，必須做大量的實驗，書上得來終覺少，絕知些事要躬行，呵呵，千萬不要在生產庫上進行哦；如果想從麻煩製造者成長為一個麻煩終結者，只顧自己埋頭苦學是不夠的，畢竟你的生產環境與學習環境產生的故障很有限，透過在相關論壇上閱讀貼子，從網友的經驗與教訓中汲取營養，擴充發現與解決問題的技巧。

獨立學習與思考是dba快速成長的關鍵。許多新手發現系統出現問題或未知的現象，第一時間總是去諮詢資深dba，其實這是壞習慣，儘量對問題進行分析與推理，如果實在沒有頭緒的話，可以在google或相關的論壇上發貼求助，網路上總會有許多意相不到的驚喜，相信90%的問題已經有了答案，關鍵是如何找到它。

不要對internal的東西費心費神，打好基礎才是主要的，要有一定的pl/sql程式設計技術，牢牢掌握資料庫備份與恢復，然後提高系統調優及SQL最佳化的能力，當技術累積到一定的層次時，對於許多internal的東西自然自然就領會啦。

良好的溝通能力有助於更快地解決問題。很多時間，可能已經解決了問題，卻不知為什麼會產生這種問題，這時可以諮詢一下專案負責人或相關程式設計師，儘量把問題的根源搞清楚，如果問題沒能根本解決，問題必然捲土重來。

作為dba，需要為專案組的程式設計師提供統一的《資料庫開發規範》，如果可能，也可做為程式設計師做sql編寫及sql最佳化技巧方面的培訓，儘量讓效能不佳的sql胎死腹中，新手dba，更要融入專案組，理解業務系統的需求，並掌握一定的資料庫建模知識，透過對資料庫結構的掌握，為資料庫結構最佳化與sql最佳化打下基礎。

努力學習對dba是必不可少的，需要注意的是：並不是方方面面的知識都需要熟記硬背。有選擇地去深入研究某個方面的技能，才能突破泛泛之境；不要太在意研究配置dataguard、安裝rac等瑣事，雕蟲小技而已；（）這是piner網友收集整理的oracle faq，相信無論新手熟手，都是可以翻翻的。

“工欲善其事，必先利其器”，做為dba來講，必須為自己及程式設計師搭建順手的工作環境（本文以linux平臺為例）。在linux平臺上，sqlplus是不具有回撥功能的，如何搭建具有回撥環境的sqlplus呢？（）大家可以參考fenng網友的貼子。還有就是安裝sqlplus的help及sql語法的help，具體方法大家可以參考下面這個貼子()。在9i以後的版本中sqlplus的help預設是安裝的，sql語法的help就必須自己安裝啦。

 

最需要新手注意的網址： 

關於作業系統/網路引數的調整

做為dba，對linux/unix應該有相當的基礎。理解raid、raw、lvm、ocfs、asm等與儲存相關的概念；能夠安裝oracle軟體及打補丁；理解linux/unix常用的命令rpm、cpio、tar、ftp、top、vmstat、iostat、sar、netstat、crontab等；應用伺服器的調整有一定的瞭解；關於linux/unix的問題，可以到 去尋找答案。

關於初始化引數（sga）的調整

深刻理解oracle的初始化引數是dba必不可少的功課，卻不能把調整引數做為提高效能的救命稻草，不合適的引數必將帶來效能上的下降，甚至資料丟失的危險；不要以為使用隱藏引數為榮，做事要有未雨調繆的打算，在系統故障時可以坦然對之。

沒有任何工式可以滿足sga調整的需要，通常都是經過多次調整，才能達到比較合諧的效果，

http://blog.csdn.net/biti_rainy/archive/2004/07/03/learn_oracle_20040703_7.aspx

這個貼子是biti_rainy關於sga調整的總結，基本可以適合大多數情況。

在32bit的作業系統中，sga有1.7g的限制，如果相在32bit的作業系統上突破1.7g的限制，就需要使用特殊的手段，

（http://www.itpub.net/showthread.php?s=&threadid=124424）這個貼子是coolyl網友針對各個平臺sga突破1.7g的限制的總結。

在64bit的作業系統中，sga不需要特殊方法可以上到3.9g，如果想突破4g的話，方法與32bit系統中突破1.7g的方法類似，也就是說必須使引數use_indirect_data_buffers=true，然後使用db_block_buffers來設定buffer cache的大小。

關於statspack的若干建議

不要對statspack報太大希望，它只能告訴你過去某段時間資料庫的執行狀態，以及預測將來一段時間的效能趨勢（初始化引數沒能重大調整及業務沒能巨劇變化的情況下），透過statspack的報表，dba可以對初始化引數進一步進行微調。

statspack可以告訴你效能瓶頸所在，僅此而已，引起效能瓶頸的根本原因必須dba親自動手查；當然引起效能瓶頸的原因也可能已經收集到啦，在眾多收集到的sql中需要仔細斟別哦，如果sql語句太長，就比較麻煩，因為在statspack中，過長的sql會被截斷的；無論如何，statspack都是dba不可卻少的助手，（）這是eygle網友關於statspack的系列研究貼子，希望對你有用。

如果你需要經常製做statspack的效能趨勢報表，一般可以用excel來做，就是麻煩了一些，偶寫了一款專門製做statspack報表的工具，不僅可以更快更方便地製作出漂亮的報表，而且可以對知識進行管理。（）

關於logmnr在調優中的運用

一直以來，logmnr都不是調優所推薦的工具，主要用於安全審計方面，其實在追究系統瓶頸上logmnr可是得天獨厚，透過對日誌的審查（需要dba有足夠的耐心哦），可以更清楚地知道oracle在某段時間內做了什麼，這樣做是不是合理？當然logmnr並不能告訴你什麼合理，你必須自己判斷。

在b/s結構的應用中，在session連線時用dbms_application_info.set_client_info設定session的client_info，這樣在用logmnr進行日誌挖掘時，就知道是那個頁面執行了這個操作，範圍就比較小；在c/s結構的應用中，那是通常每個client連線後，都可能需要很久才斷開session，客戶每開啟某個業務模組，最好用dbms_application_info.set_client_info設定該session的client_info資訊。

關於materialized view在調優中的運用

在olap環境中，mview是以空間換時間的一種有效手段，更少的物理讀/寫，更少的cpu時間，更快的響應速度，所以它不適合高階的oltp環境；在oltp環境中，規模較大的報表適合使用mview來提高查詢效能。（http://www.itpub.net/224536.html）這個貼子可以下載到《expert one on one oracle》中文掃描版，該書的第13章專門講述mview的運用。

關於stored outlines在sql最佳化中的運用

stored outlines是為了維持sql執行計劃穩定性而推出的功能，主要適用於測試環境到產品資料庫環境的遷移、當蒐集統計資訊以取樣方式執行、蒐集統計資訊可能給某些特定SQL帶來危害、無法對原始碼進行修改等情況下，為了保證產品資料庫的良好執行，我們需要穩定執行計劃。人為的調整某些特定的sql，我們可以使用sql謹慎的確定某個sql所需要的outlines。（摘自biti_rainy原話,原url如下。）http://blog.csdn.net/biti_rainy/archive/2004/06/29/biti_rainy_learn_oracle_20040629_1.aspx

（單擊此處的url將不能開啟相關連結，複製到ie位址列中即可）關於stored outlines的使用，

http://blog.itpub.net/post/96/1548 也可以參考本人拙作。曾對stored outlines抱有厚望，但在實際運用中卻發現outlines並不是那麼很好伺候，一般當sql使用bind variable的情況下用outlines來穩定計劃會更合適一些。

當初始化引數cursor_sharing=EXACT時，如果查詢條件不同，就沒有辦法使用stored

outlined；如果把業務邏輯封裝在stored procedure中，procedure中的變數將以bind variable的形式出現，這時可以用stored outlines來穩定執行計劃，具體操作見本人拙作；如果sql中沒有文字變數（常數），則可以用stored outlines。

   如何用dbms_profiler測試stored procedure

    關於dbms_profiler package主要用於pl/sql block與stored procedure的效能測試，在開發階段程式設計師或dba需要對開發的各種儲存物件進行效能測試，透過dbms_profiler package可以找出儲存物件中效能不佳的地方，然後進行改行；可以看出dbms_profile與outline的區別是：一個用於開發階段，需要修改程式，一個用於正式執行階段，不必去修改程式，只改變sql的執行計劃而已。關於dbms_profiler package的兩個貼子：

   

   

   如何對sql進行調整及最佳化

    最佳化sql是最能體現dba智慧與價值的地方。通常在statspack的top 5的wati event主要由效能不佳的sql引起的；磁碟排序及temp tablespace瀑漲等大多與sql有關，不排除建立與重建索引這方面，但這方面的原因應該是dba負責，大表在建立或重建索引必須在系統空閒時。

效能不佳的sql是如何產生的呢？這裡面問題就比較複雜一些：不良的資料庫結構必將導致不良的sql；還有就是程式設計師的sql編寫技能引起的；不要奢望程式設計師是sql編寫方面的專家，根據偶自己做開發的經歷，最快時間完成專案才是最重要的，所以程式設計師不會太關心sql的效能，即是關心，也是很有限的。

對程式設計師進行合適的關於sql最佳化的培訓，提高他們的責任感，針對系統中出現的案例進行講解，程式設計師潛意識中就會努力避免很多低階的錯誤；要多與程式設計師交流，儘量載入程式員描述他在資料庫方面感到困難的地方，並提出指導性意見及解決方案。

對新手dba而言，通常都很有興趣對系統引數或sql進行調優，卻不知如何動手。在系統引數方面本身要有一定的理解，也可以請教與資深dba進行探討，效能提高上奉勸不要抱太大的希望，也可以根據statspack的報表進行分析，對系統引數進行微調；在sql調優方面，必須能夠勘別出效能不佳的sql。

如何勘別出效能不佳的sql呢？通常要綜合以下效能指標（response time/consistent gets/physical reads）進行判斷；要根據自己的情況從v$sql或v$sqltext_new_withlines字典表中把符合條件的sql查詢出來：

set lines 99

col sql_text format a81

col bgets_per format 99999999.9

set long 99999999999

set pagesize 9999

select address,hash_value,disk_reads,elapsed_time/1000000 as

"elapsd_time(s)",cpu_time/1000000 as "cpu_time(s)",

       buffer_gets/executions bgets_per,first_load_time,sql_text

 from v$sql

where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 50000);

    上面的這個查詢主要將physical reads > 1000及consistent gets > 50000的sql語句找了出來，當然你也可以將響應時間也進行限制,通常onsistent gets較大或physical reads較大的sql，它的response time也必然會比較大。

    如何在sql執行時產生執行計劃呢？在sqlplus上輸入set autot on就可以產生比較詳細的執行計劃；set autot off是讓sqlplus取消產生執行計劃；set autot traceonly只顯示sql影響的行數、執行計劃、執行的統計資訊、不輸出結果集；set autot on exp輸出執行後的結果集及執行計劃；set autot on stat輸出執行後的結果集及統計資訊。explain plan只對sql進行分析，產生執行樹，用select * from table(dbms_xplan.display)輸出explain plan產生執行計劃。

set autot[race] {off|on|trace[only]}[exp[lain]] [stat[istics]]

explain plan [set statement_id = &item_id] for &sql;  

select * from table(dbms_xplan.display);

    如何對效能不佳的sql進行最佳化，想來對任何一個dba都有挑戰性。在這個環節上，dba必須掌握如何檢視sql的執行計劃，並對返回的結果有一定的瞭解；如果是新手，可以藉助一些sql最佳化工具進行調優,可借用的工具有lecco sql expert及quest toad,鑑與新手對工具的理解有些難度，本人為lecco sql expert寫了中文圖解。

    sql expert 教程

    quest toad 教程 （向原作者致謝）

    任何工具都是比較低智慧的，如果你覺得lecco或toad比較順手，千萬勿沉溺其中，它們只是一個柺杖而已，你必須超越它，否則你的價值就值得懷疑；針對sql的最佳化，必須自己多動手測試，而且也要閱覽眾書，從別人的經驗中激發靈感。

    在最佳化sql時，需要一層層地對sql進行分析。首先對sql的語法進行分析，剔除冗餘的或錯誤的查詢條件（有可能是程式設計師手誤），花得工夫不是很多，效能可得到極大的提高，不要太相信程式設計師，他們寫得必未正確；其次對sql涉及表的結構進行分析，特別是複雜的sql，要檢查是否有更佳的連線路線，連線欄位是否有索引，索引的選擇性如何等；第三償試用不同的hints改變表的的驅動次序。 這個貼子是oracle hints的一個列表,hints具體用法可查。

    關於sql調優的細節很多，不可能一一列舉，具體環境必須以執行計劃為準，透過對sql的理解，提升到對資料庫結構的合理性進行揣測，合理的資料庫結構，將對sql的效能有較大的提高；有些情況下，修改了資料庫結構，並不需要在程式上進行相應的改動，比如將大表進行分割槽、建立mview等。關於sql最佳化大家也可以好好研究一下網友black_snail的系列貼子，有詳細的示例：

   如何對session進行跟蹤及tkprof的使用

    跟蹤session的活動，oracle提供了很多種手段，不僅可以對當前連線的session進行跟蹤，也可以對其它使用者的session進行跟蹤；透過對trace檔案的分析，不僅可以掌握該session的活動，也可以找出這個session中的瓶頸所在，對session的跟蹤是dba進行系統調優、故障診斷的常用方法。

    alter session set sql_trace=true/false

    對當前會話的活動進行跟蹤及停止跟蹤

exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);

    可以對當前session、其它使用者的session進行跟蹤及停止跟蹤

alter session set events '&event trace name context forever,level &level';

alter session set events '&event trace name context off';

exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

oradebug event 10046 trace name context forever,level 12

關於event跟蹤的詳細論述大家可以參考hrb_qiuyb的貼子：

http://blog.csdn.net/hrb_qiuyb/archive/2004/06/30/30559.aspx

event、sql trace等工具收集正在執行的sql的效能狀態資料並記錄到跟蹤檔案中. 這個跟蹤檔案提供了許多有用的資訊,例如解析次數.執行次數,CPU使用時間、物理讀、邏輯讀等.這些資料將可以用來最佳化你的系統.user_dump_dest引數說明了生成跟蹤檔案的目錄，設定sql trace首先要在init&sid.ora中設定timed_statistics為true, 這樣才能得到那些重要的時間狀態. 由於sql trace生成的trace檔案讀起來很困難,所以要用tkprof對其進行轉換,TKPROF有許多執行引數，可以參考