xxx第三人民醫院是一所集醫療、科研、教學為一體的綜合性三級甲等醫院。醫院現有病床650張，日門診量1000人次左右，住院人數500人左右。有26個臨床科室、11個醫技科室，并開設有60多個專科專病門診。

醫院目前使用了醫院信息系統標準版、門診醫生工作站系統、病案管理系統、LIS系統（LIS接口程序從ZLHIS中提取病人信息和并完成計費功能），沒有使用住院醫生工作站。高峰期用戶連接數160個左右，HIS系統工作站150臺左右。

醫院的軟硬件環境如下：

 

2006年3月8日，醫院系統管理人員報告，系統在業務高峰期的9:00:~11:00時段，門診收費時有停頓的現象，有時一張單據保存需要1分鐘左右的時間。LIS系統提取病人信息的速度非常慢，等待時間以分鐘計。由于整個系統的運行速度較慢，已影響到醫院正常業務的開展，醫院各科室的意見比較大。服務器CPU占用率近期從原來的15%增長到現在的40%左右。

市二院與市三院在業務規模和應用規模上比較接近，我們希望從兩家醫院的應用環境對比分析中找到一些解決問題的線索。

 

 

從上表中，我們看到市二院啟用了住院醫生工作站系統，沒有使用門診醫生工作站系統，二家醫院在用戶連接數和硬件環境上都比較接近。市二院使用ZLHIS的時間要比市三院長，數據量也比市三院大，但市二院在使用過程中沒有反映速度慢的問題，系統運行速度比較理想。

通過以上的對比分析，我們發現以下的主要差異：

兩個版本的性能差異對ZLHIS而言，主要體現以下兩個方面：

   1)、10.1.3使用的本地管理的表空間（LMT），而816默認情況下使用的是字典管理的表空間(DMT)。LMT很好的解決了DMT的遞歸SQL與熱塊爭用問題。可以帶來數據寫入和空間管理性能很大的提升。

   2)、10.1.3中本地管理的表空間，可以有效避免數據文件碎片，自動合并未使用的數據塊。

   3)、Oracle 10g新增的回退段自動管理功能，很好地解決了8i下的回退段設置問題，在8i下如果回退段設置不當，會嚴重地影響數據庫的性能。

   進一步分析數據庫參數，發現兩家醫院的cursor_sharing參數設置不相同。三院的設置為exact，也就是精確匹配。由于ZLHIS中綁定變量使用得不是很好，大量的SQL語句需要做硬分析(Hard Parse)。

   二院設置為similar，這是10g新增加的選項。簡單的講，為了解決僅僅因為列值不同而導致SQL語句不能共享的問題，引入了CURSOR_SHARING參數，使這類SQL也可以使用共享SQL，從而提高效率，降低耗費在SQL語句解析上的CPU等各種資源。但使用similar選項也可以帶來一些問題，比如把空格也解析成綁定變量 ；市二院的一日費用清單的數據源就出現過這種問題。最好的方式還是在編程時就避免硬編碼的SQL語句，使用綁定變量。從二院的實際應用效果，使用simimar選項，效果比較明顯。

3、市二院沒有使用LIS系統。

市三院的LIS系統與ZLHIS進行了連接，LIS系統接口從ZLHIS中讀取視圖(VIEW)提取病人信息，并調用ZLHIS數據庫的存儲過程(PROCEDURE)完成計費功能。

首先，我想到是否因為系統問題導致性能下降，所以到達現場后，依次進行了如下檢查：

1)、使用Windows 2000任務管理器查看CPU及內存使用情況，CPU利用率偏高，且波動較大,符合系統管理員的描述，內存利用率較為平穩。

2)、使用Windows 2000的事件查看器查看操作系統日志，沒有發現異常。

3)、查看Oracle的后臺進程文件(alert_orcl.log)，沒有發現異常。初步排除是Oracle錯誤引起的性能下降

4)、檢查磁盤陣列，沒有發現磁盤損壞，磁盤陣列工作正常。

5)、醫院管理人員配合檢查了網絡環境，確認網絡設備工作正常，沒有出現不穩定現象。

經過以上的檢查分析，排除了硬件環境和操作系統方面的故障引起的性能下降，接下來，主要從應用和數據庫方面進行分析與調整。

 

在診斷和調整過程中，主要使用Statspack和SQL_TRACE工具。

Oracle Statspack工具從816中開始引入Oracle中，已成為DBA用來診斷數據庫性能的強有力工具。它通過收集一組數據庫運行數據，記錄數據庫的運行狀況，生成Statspack報告。通過對Statspack報告的分析確定性能的瓶頸所在。目前，Statspack工具已經在Oracle性能調整中得到了廣泛應用。

SQL_TRACE是Oracle提供的用于進行SQL跟蹤的手段。生成的Trace文件進行格式化后，提供了包括SQL文本，執行計劃，執行效率等重要信息；是強有力的輔助診斷工具.在日常的數據庫問題診斷和解決中，SQL_TRACE是非常常用的方法。

本次調整使用Statspack工具收集業務高峰期的系統運行情況，并以此作為調整的基礎。Sql_Trace主要用于跟蹤分析Statspack報告中的Top SQL部分的SQL語句，還用于定位引起個別功能運行速度較慢的SQL語句。

  經過對Statspack報告的分析，我們初判斷影響性能的主要原因有：

1、“病人費用匯總”表的行級鎖定。醫保事務需要更新當天的病人費用匯總表中的一行，獲得一個行級鎖定，當普通業務也需要更新相同的行時，必須等待醫院業務事務完成，這種等待就造成了系統速度慢甚至是死機的現象。

2、從高峰期生成的Statspack報告中關于SQL共享使用的指標都不理想，軟解析比率為：75.71%。原因是ZLHIS沒有很好的使用綁定變量，造成了語句大量的硬解析，耗費了大量的系統資源，這也是醫院CPU利用率增高的原因之一。

3、從報告的TOP SQL部分發現LIS接口提取病人信息的SQL邏輯讀很高，進一步使用SQL_Trace功能分析語句，發現這段SQL對“病人掛號記錄”和”病人信息”表都做了全表掃描，運行效率低下。

詳細的Statspack報告分析請參見附錄A和附錄B。

   基于上述分析，于2006年3月12日將Oracle數據庫系統升級到了10.2.0，升級后主要做了以下調整：

1、使用cursor_sharing=similar參數,以使ZLHIS的大量硬編碼的SQL語句能夠共享使用，解決綁定變量的問題，降低SQL硬解析的系統消耗。(關于使用綁定變量效率提升的實驗請參見附錄C)。

2、建立基于函數to_char的索引，改善LIS提取病人信息的SQL語句的執行效率。

3、使用定時刷新的物化視圖代替“病人費用匯總”表，并創建一個“病人費用匯總”視圖，然后在視圖上建立替代觸發器，屏蔽掉程序的DML語句。

4、表空間全部采用ASSM方式的本地管理表空間(LMT)，減少維護表空間的遞歸SQL消耗，避免Freelist的爭用。

經過升級調整后，觀察CPU占用率從原來的平均45%左右，降到了現在的20%左右。

 

 

經過升級調整后，LIS提取病人信息的速度有了明顯提高，檢驗科使用人員比較滿意，醫院各業務窗口部門普遍反映速度有了提高。

 

 

在Oracle8i下優化器默認為Choose方式(optimizer_mode=choose)，Oracle 10g下優化器默認為All_rows(CBO的一種)，為保證語句的執行計劃與在8i下的一致性，并考慮到程序有的地方強行使用了RBO，將優化器模式修改為choose。Oracle 10g對CBO有了更好的支持，但是由于優化器和其他環境的變化導致了部分SQL語句在10g下運行速度較慢。 主要體現在以下兩個方面：
    1)、升級后部分藥品相關的模塊運行速度有了下降，對這部分SQL進行了調整，運行速度恢復正常。

2)、部分報表執行速度較慢，接下來對系統中的報表SQL進行了集中優化和調整。

調整SQL時主要使用了以下幾種方法：

1)、對相關表作分析(Analyze)，收集準確的統計信息。

2)、禁用部分不合理的索引，消除其對執行計劃的影響，如：藥品收發記錄_IX_庫房ID。

3)、使用提示字(Hint)，穩定SQL語句的執行計劃。

更為詳細SQL調整技巧請參見相關文檔。

 

 此次數據庫分析調整印證了“60%的性能問題都是應用問題”這句名言。使用cursor_sharing=similar只是權宜之際，并可能會帶來一定的副作用，我們的程序在綁定變量的使用上還有很長的路要走。“病人費用匯總”這種在當初看起來不錯的設計，由于醫保業務的發展，在今天卻成為了一個嚴重影響性能的缺陷。我們在數據庫結構設計中不能默守陳規，必須適應不斷變化的業務需要。

 有時，一條不良的SQL語句可能會導致整個系統的性能下降，影響客戶對整個系統的評價，最好的方式是在程序開發階段就把這些劣質SQL扼殺在搖籃里，有時甚至為了性能，犧牲部分不實用的功能也是值得的，我們需要在性能與功能之間做出平衡。出現性能問題時我們需要用科學的方法分析解決問題，做出正確的判斷，而不是依據經驗和主觀意識進行判斷。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

我們利用Oracle 的Statspack工具生成了上午10:15至11:00的報告，用以作為分析和調整的基礎。

 

 1)、Redo size:   16,906.18: 每秒鐘產生16,906.18字節的重做日志。每分鐘產生約1M左右的重做日志(16,906.18*60/1024)，系統壓力相對還是比較大。

2)、Physical reads 92.41物理讀為92.41，很少基本可以忽略，初步判斷，高整緩沖的設置沒有太大的問題。

3)、Transactions: 1.91 每秒鐘2個事務左右，也證明系統壓力較大。

 

1)、Buffer Hit   Ratio  99.72 從這部分看高速緩存命中率為99.72，比較理想，不需要再加大高速緩存。

2)、Soft Parse Ratio 75.71 。這項值低于90%，一般證明系統沒有很好的使用綁定變量。ZLHIS在綁定變量的使用上還有很長的路要走。建議升級到Oracle 9i/10g 使用cursor_sharing=similar參數，可以取得較好的效果。

3)、In-memory Sort Ratio:       99.90，體現出排序基本上都在內存中進行，不需要調整排序區。

 

 

1) db file sequential read 為最主要的等待事件(87.46%)，這通常是指單一的數據塊讀操作（例如，索引的讀取）。該值過大說明表的連接順序很糟糕。或者使用了非選擇性的索引。數據庫的主要調整方向為：優化SQL，使用正確的索引，改善表連接效果。

2)enqueue閂鎖是底層的隊列機制，用于保護系統的全局區（SGA）的內存結構。使用本地管理的表空間(LMT)可以有效降低這部分等待事件,目前ZLHIS使用的是字典管理的表空間（DMT），會造成Fet$、Uet$系統表的爭用（字典管理表空間使用這兩個表記錄字典管理表空間的使用情況，LMT使用了位圖(BITMAP)管理表空間，顯著地提高了效率。

3) SQL*Net more data from dblink、SQL*Net message from dblink，為系統空閑事件，可不必關注，造成這兩者較高的原因可能是ZLHIS通過網絡向醫保前置機大量取數產生的。

4)enqueue 進而查看報告的另一部分

(1)、唯一索引中的重復索引，需要提交(commit)或回段(rollback)事務。

(2)、位圖索引的更新，由于ZLHIS中沒有使用位圖索引，所以這應該不是主要原因。

(3)、多個用戶同時更新同一個塊，即熱點塊爭用。典型的就是“病人費用匯總”表的塊爭用問題，可以判定這是Enqueue等待事件較高的原因。

從statspack報告的磁盤IO部分來看，發現ZLEXPENSE和ZLPATIENT的磁盤IO最大。ZLEXPENSE上面存儲了費用相關的表，IO大是正常的。ZL9PATIENT的IO偏大是不正常的，可以判定是由于LIS系統提取病人信息的的全表掃描引起大量的磁盤IO。可以看到，解決LIS系統提取病人信息的全表掃描，是系統調整的一個關鍵點。

 

 

從SQL ordered by Gets部分可以看出，大部分的低效SQL大多數都與“病人費用匯總”有關，而且多以update操作為主。在ZLHIS中很多存儲過程都要update“病人費用匯總”表，且很多update語句沒有很好用到索引。還有就是update語句需要獲得Oracle行級排它鎖，這也是造成Enqueue等待事件較高的原因。解決”病人費用匯總”行級鎖定和訪問效率是提高運行速度的又一關鍵點。

 在32位平臺上，Oracle進程的內存尋址空間有2G的限制，實際上Oracle.exe進程能夠使用的內存為1.75G左右。那么SGA+PGA只能有1.75G左右。市三院在高峰期會話數有180左右，最大時達到230個session。由于使用專用服務器，每個Session大概需要2M左右的合內存。所以整個 SGA不能大于1290M (1750M-460M)。

 在實例SGA設置時，正是遵循了上述的原則。要使用更大的SGA，有兩種解決方案：

 1)、使用共享服務器模式。但由于ZLHIS系統有很多的大事務要運行，這種模式會帶來，會話事務處理的等待。

 2)、將平臺升級到64bit。這需要大量的硬件和軟件投資。

 由于從Statspack的分析來看，SGA各個部分的使用效果都不錯，所以沒有調整SGA的必要。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

升級后Oracle10g并進行調整后，我們在同一時間段生成Staspack報告，發現各項指標有了明顯改善。報告的前半部分如下：

 

 

 

 

 

 

 

相比8i下的statspack主要有以下幾點改善：

1)、Soft Parse %:   87.47

Sql語句軟解析比率有了很大的提高，提高了10%左右，主要是在10g使用了cursor_sharing=similar參數。

2)、Enqueue等待事件不再是主要的等待事件，主要是得益于將“病人費用匯總”用物化視圖代替，減少了表的行級鎖定。

3)、最主要的等待事件變成了CPU time，此事件為Oracle空閑事件，空閑事件是不能避免的，在性能調整中，不必太關注此類事件。

4)、TOP 5 部分的幾個等待事件等待時間都很小。對比8i下的top5中的幾個等待事件的等待時間減少了很多。 如db file sequential read 由原來的138,851ms，減少到現在的718ms。可見改善的幅度還是很大的。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1)、表空間的處理。

由于10G與8I的數據文件默認存放路徑不一致，不能直接導入表空間，需要先創建相關的表空間。我在8i下用SQL生成SQL的方式重新創建表空間的腳本，對生成的后稍加修改，就是創建表空間的腳本。

 

2)、IMP – 00020錯誤的解決。

在導入“病人信息”表時出現IMP-00020錯誤。

導入了                                                        263066 行

 

   病人信息不能導入，查詢Oracle MetaLink此錯誤的解釋為：

由于在”病人信息”上使用了基于to_char的函數索引，先懷疑是函數的問題。按照metaLink的解釋，先試著加大導入時的buffer和使用commit=y、constrains=n、indexes=n參數，還是出現同樣的錯誤。于是初步判斷是由于導出時，使用了direct=y造成的，是遇上了816的一個bug。于是決定使用冷備份還原，重新導出“病人信息”表， 重新導入成功，至此問題解決。

主要參數說明：

1)、SGA使用手動管理。從實際市二院等醫院的使用情況來看，自動管理SGA的效果不是很好，可能跟ZLHIS的應用有關。各SGA的大小參照8i下的SGA設置，因為從前面的分析來看，8i下的SGA沒有什么大的問題，運行效果較好。

 

2)、Cursor_sharing = similar參數的使用，使用此參數的目的在于SQL語句軟件解析的比率。此參數，可以讓一些硬編碼的SQL語句，使用綁定變量，這由Oracle自動完成，下面是Trace出來一段業務SQL。

   可以看到Oracle已經對SQL語句使用了綁定變量，也就節約了解析時間，但這個參數可能會帶來一些副作用，比如把空格翻譯成綁定變量等，但從目前使用的情況來看，可以帶來一定的性能提升。最根本解決綁定變量問題的途徑只有一個：修改軟件,使用綁定變量。

   下面通過一個實驗來看使用與不使用綁定變量之間的性能差別：

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 我們看到同樣插入100000行數據，使用綁定變量用了5秒鐘時間，而不使用綁定變量，共用了1分30秒鐘。兩者的性能差別居然有18倍的差距！可以說目前ZLHS最大的性能問題，主要是沒有使用綁定變量。

3)、processes，默認為150，太小，加大到400。

   用VB寫了個小程序，使用ADO循環連接Oracle,測試可以連接的最大用戶數。發現只有200個SESSION左右。雖然醫院的高峰期的會話數只有180左右，但為了保證有擴展性，決定調整Oracle.exe進程的堆棧大小。注意必須停止Oracle服務才能調整，且當前目錄必須為Oracle.exe的當前目錄。

 

 

 

 

D:\oracle\product\10.2.0\db_1\BIN>

   調整后的連接數可以達到250左右。

 

 

 

是由于升級到10g后，默認使用CBO優化器，而CBO必須依賴準確的統計信息來確定SQL語句的執行計劃。統計信息錯誤，將會影響SQL語句的執行計劃，使Oracle使用錯誤的執行路徑，從而影響執行速度。使用ANALYZE命令對表進行分析：

用SQL生成SQL的方式產生分析腳本對表進行全表分析。

病人費用記錄和藥品收發記錄由于數據量太大，對其只進行5%的采樣分析。

通過分析報表數據源，發現<發藥時間>參數SQL對藥品收發記錄使用了全表掃描。

SQL語句：

select distinct A.審核日期,名稱

是由于升級到10g后，使用了CBO優化器，而藥品收發記錄的統計信息還沒有更新，導致Oracle使用了全表掃描，而不是使用審核日期上的建立的索引。前面做5%的采樣分析獲得的統計信息不準確。決定對藥品收發記錄表進行全表分析。

 

 

 

  使用SQL_TRACE功能產生Trace文件。分析產生的Trace文件發現如下一段：

**************************************************************************************************************************

 

 

發現這段提取病人信息的SQL使用“病人費用記錄”表來實現只顯示產生費用的病人信息。這段SQL的物理讀非常大(16541，紅色字體部分) 進一步分析下面的執行計劃發現，使用的”病人費用記錄_IX_病人ID”索引，由于當前在院的病人數較多，效率很低。一日清單一般只需要查詢當天的費用，應該“登記時間”索引的效率。問題原因與7.1一樣，都是對象的統計信息不準確造成的。解決辦法同樣是對表作全表分析。

 

使用服務器管理工具對導入的數據進行檢查、修正。以非ZLHIS用戶進行各種業務的測試。至此升級完成。

 

檢驗科LIS系統通過接口程序提取zlhis病人信息速度很慢，以分鐘計算。

可能是提取病人信息的VIEW的SQL的執行效率低下，根據執行計劃調整SQL語句。

查看原始的視圖語句：

 

提取LIS接口程序讀取病人信息的SQL語句，查看語句的執行計劃：

分析語句的執行計劃，發現病人信息、病人掛號記錄表等為全表掃描。首先需要消除對“病人掛號記錄“的全表掃描。

 

發現病人掛號記錄有478299條記錄，數據量還是比較大。進一步分析語句，發現條件中有A.門診號=J.門診號，其中A表為病人信息，J表為病人掛號記錄。查看兩個表的索引情況。

 

   發現病人掛號記錄的門診號上都沒有建立索引,在表的“門診號”字段上建立索引。

   改造原來的(select distinct (select 編碼 from 部門表 where id=執行部門id) as 科室號,門診號 from 病人掛號記錄 )語句，將子查詢的方式改為直接表連接。

改造后的SQL語句如下：

改造后的SQL語句性能有了很大的很高，提取一個病人的信息由原來的30秒鐘左右，提高到現在的5秒鐘左右。再次查看執行計劃：

SELECT STATEMENT, GOAL = CHOOSE                                             

   發現病人費用記錄已經使用索引，但病人信息表還是全表掃描。下一步就是消除病人信息的全表掃描。

通過住院號進行查詢，應該是可以用到建立在住院號上的索引，但這里沒有用到，原因是視圖中對住院號使用了to_char()函數，即：to_char(A.住院號) as 住院號。經咨詢醫院和公司的其他人員，必須使用to_char進行轉換，否則LIS的接口程序不能提取病人的信息。要徹底解決此問題，有兩種方式，1、使用基本函數的索引。2、通知三晶公司修改LIS結口。但第二種可能性很小。于是決定采用基于函數的索引。

1、修改init.ora參數

     query_rewrite_enabled = TRUE

2、創建基于函數的索引

    create index 病人信息_IX_char住院號 on 病人信息(to_char(住院號)) tablespace zl9patient 

3、由于只有在CBO 下才能使用基于函數的索引，對”病人掛號記錄”等作分析。

4、改造SQL語句，加入提示字(hints：/*+frist_rows*/強制使用CBO。

調整后的SQL語句執行計劃：

經過調整，LIS系統提取病人信息的語句，速度得到了很大的提高，大概在1秒左右。此至問題解決。