DBA優化之路(zt)

http://www.itpub.net/thread-298019-1-1.html

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

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

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

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

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

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

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

最需要新手注意的網址：http://tahiti.oracle.com http://metalink.oracle.com

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

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

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

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

沒有任何工式可以滿足sga調整的需要，而且olap應用與oltp應用初始化引數的調整是有很大區別的，通常是對初始化引數經過多次調整，才能達到比較合諧的效果，（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的限制的總結，sga並不是越大越好，這個最好具體情況，慎重待之。

關於statspack的若干建議

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

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

如果你需要經常製做statspack的效能趨勢報表，一般可以用excel來做，就是麻煩了一些，本人寫了一款專門製做statspack報表的工具，不僅可以更快更方便地製作出漂亮的報表，而且可以對知識進行管理。（http://www.cnoug.org/viewthread.php?tid=20115）

關於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環境，如果mview基表的事務非常多，那mview的重新整理將對系統造成一定的壓力；在oltp環境中，規模較大的報表卻適合使用mview來提高查詢效能。（http://www.itpub.net/224536.html）這個貼子可以下載到《expert one on one oracle》中文掃描版，該書的第13章專門講述mview的運用。

也可以看看本人關於mview所作的測試（http://blog.itpub.net/post/96/7535），建立與使用mview一定要小心，特別在分割槽表上（http://blog.itpub.net/post/96/3809），如果對分割槽進行分割（alter table &table_name split partition ...）時，該分割槽表上的mview將不能被fast refresh，這時所有針對該分割槽表的事務將會被失敗。

關於stored outlines在sql優化中的運用

stored outlines是為了維持sql執行計劃穩定性而推出的功能，主要適用於無法對原始碼進行修改等情況下，為了保證產品資料庫的良好執行，需要穩定SQL的執行計劃，人為調整某些特定的sql的執行計劃，需要慎重地確定某個sql所需要的outlines。

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

當初始化引數cursor_sharing=EXACT時，如果查詢（where id = 2 / where id = 3）等這種情況下，就沒有辦法使用stored outlined對該型別的SQL進行執行計劃的穩定，除非對該SQL先使用bind variable或將cursor_sharing=SIMILAR或FORCE。

用dbms_profiler測試儲存過程的效能

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

http://www.samoratech.com/PLSQLProfiler.htm

http://pages.videotron.com/orautils/pages/dbms_profile.htm

如何對sql進行調整及優化

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

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

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

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

如何收集與勘別出效能不佳的sql呢？通常要綜合以下效能指標（response time/consistent gets/physical reads/resultset size）進行判斷；要根據自己的情況從v$sql、v$sqlarea、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 executions > 0

and (disk_reads/executions > 500 or buffer_gets/executions > 20000);

上面的這個查詢主要將physical reads > 500及consistent gets > 20000的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 教程 http://www.cnoug.org/viewthread.php?tid=22327

quest toad 教程 http://www.cnoug.org/viewthread.php?tid=3242（向原作者致謝）

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

在優化sql時，需要一層層地對sql進行分析。首先對sql的語法進行分析，剔除冗餘的或錯誤的查詢條件（有可能是程式設計師手誤），花得工夫不是很多，效能可得到極大的提高；其次對sql涉及表的結構進行分析，特別是複雜的sql，要檢查是否有更佳的連線路線，連線欄位是否有合適的索引，索引的選擇性如何等；第三償試用不同的hints改變表的的驅動次序。http://www.adp-gmbh.ch/ora/sql/hints.html 這個貼子是oracle hints的一個列表,hints具體用法可查http://tahiti.oracle.com。

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

http://www.dbonline.cn/source/oracle/20031218/oracle%20SQL%20performance%20tuning1.html

如何對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有許多執行引數，可以參考http://tahiti.oracle.com文件

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#1216（需要到otn註冊免費的帳號）。

基於等待事件的效能診斷方法

等待事件（wait event）是oracle核心程式碼的一個命名部分，有兩種型別的等待事件：空閒事件（idle event）與非空閒事件（non-idle event）,空閒事件指oracle正在等待某種工作，常見的空閒等待事件：client message、null event、pipe get、pmon/smon timer、rdbms rpc message及sql*net等；非空閒等待事件：buffer busy waits、db file scattered read、db file sequential read、enqueue、free buffer waits、latch free、log file sync、log file paralle write等。

什麼是瓶頸？一旦熟悉了系統的等待事件，就能夠把握問題的關鍵，並能夠用相應的方法去處理阻塞系統的瓶頸，一定不要隨意的進行優化，否則一波不行又起一波，可以通過v$system_event熟悉系統總的等待情況，然後通過v$session_event檢視系統中session的待情況，最後通過v$session_wait定位瓶頸物件。v$session_wait是會話級的，它包含session的實時資訊，最重要的是：它顯示了等待事件與相應資源的更深入資訊，可明確地定位出要優化的範圍。

v$session_wait的p1、p2、p3告訴我們等待事件的具體含義，如果wait event是db file scattered read，p1=file_id/p2=block_id/p3=blocks，然後通過dba_extents即可確定出熱點物件；如果是latch free的話,p2為閂鎖號，它指向v$latch。

col event format a32

col name format a32

select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name

from v$session_wait sw,v$latch l

where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'

and sw.p2 = l.latch#(+);

--求等待事件及其對應的latch

col owner format a18

col segment_name format a32

col segment_type format a32

select owner,segment_name,segment_type

from dba_extents

where file_id = &file_id and &block_id between block_id and block_id + blocks - 1;

--求等待事件及其熱點物件

select sw.sid,event,l.name,de.segment_name

from v$session_wait sw,v$latch l,dba_extents de

where event not like '%SQL%' and event not like '%rdbms%' and event not like '%mon%'

and sw.p2 = l.latch#(+) and sw.p1 = de.file_id(+) and p2 between de.block_id and de.block_id + de.blocks - 1;

--綜合以上兩條sql，同時顯示latch及熱點物件(速度較慢)

select sql_text

from v$sqltext_with_newlines st,v$session se

where st.address=se.sql_address and st.hash_value=se.sql_hash_value

and se.sid =&wait_sid order by piece;

--如果是非空閒等待事件，通過等待會話的sid可以求出該會話在執行的sql

通過等待事件找出系統中消耗資源較嚴重的sql，是dba進行系統診斷的手段之一，只是過程稍嫌煩瑣，由於session是動態的，往往是瞬息萬變，不可捕獲（但有針對性），但可能通過對v$sql或v$sqlarea進行過濾，按[如何對sql進行調整及優化]一節中提供的sql可以找出存在效能問題的sql,長時間地對v$sql進行捕獲，並對抓到的sql進行分析處理，可以在很大程度上解決效能問題。

基於資源限制的效能診斷方法

如果想用resource limit功能，就必須將初始化引數resource_limit=true,當然也可以指定相關的resource_manager_plan引數更細緻地管理資源；針對某個使用者的資源限制，可以通過使用者的profile來實現。關於create profile

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-85028/，如需轉載，請註明出處，否則將追究法律責任。