朱有存，段建發(fā)

“軍衛(wèi)一號(hào)”醫(yī)囑擺藥查詢(xún)優(yōu)化

朱有存，段建發(fā)

目的：實(shí)現(xiàn)醫(yī)囑擺藥子系統(tǒng)擺藥結(jié)果查詢(xún)快速高效。方法：使用SQL語(yǔ)句跟蹤工具查找相關(guān)SQL語(yǔ)句，分析查看SQL語(yǔ)句執(zhí)行計(jì)劃，找出引起執(zhí)行全表掃描的表，通過(guò)建立合適的索引提高查詢(xún)語(yǔ)句的查詢(xún)效率。結(jié)果：在不升級(jí)硬件的條件下對(duì)SQL語(yǔ)句進(jìn)行優(yōu)化，使得與醫(yī)囑擺藥查詢(xún)相關(guān)的2條SQL語(yǔ)句的執(zhí)行效率得到了大幅提升。結(jié)論：通過(guò)對(duì)醫(yī)囑擺藥子系統(tǒng)擺藥結(jié)果查詢(xún)語(yǔ)句執(zhí)行計(jì)劃的分析，在相關(guān)表建立適當(dāng)索引，可大大提高醫(yī)囑擺藥查詢(xún)速度、縮短擺藥人員等待時(shí)間。

SQL語(yǔ)句執(zhí)行計(jì)劃；擺藥查詢(xún)優(yōu)化；軍衛(wèi)一號(hào)

0 引言

醫(yī)囑擺藥子系統(tǒng)軟件是“軍衛(wèi)一號(hào)”醫(yī)院管理信息系統(tǒng)中藥品管理分系統(tǒng)一個(gè)重要組成部分，是對(duì)臨床藥局（住院藥房）進(jìn)行擺藥管理的應(yīng)用軟件。醫(yī)囑擺藥子系統(tǒng)的主要功能是根據(jù)護(hù)士站和醫(yī)生站錄入的臨時(shí)或長(zhǎng)期醫(yī)囑，計(jì)算出擺藥區(qū)間內(nèi)醫(yī)囑擺藥的藥品名稱(chēng)、規(guī)格、單位、廠家、數(shù)量，并計(jì)算出患者的實(shí)收和應(yīng)收費(fèi)用[1]。該系統(tǒng)第2版從2000年研發(fā)運(yùn)行上線到現(xiàn)在已經(jīng)10多年了，多年來(lái)，一直安全、高效運(yùn)行，服務(wù)廣大患者和藥局工作人員，提高了中心擺藥室的工作效率。這是由于第2版的擺藥系統(tǒng)改變了第1版藥品系統(tǒng)的擺藥算法，擺藥運(yùn)算速度大幅提升，無(wú)論一個(gè)病區(qū)有多少患者，一個(gè)患者有多少醫(yī)囑，都能迅速完成擺藥處理[2]。但最近幾個(gè)月以來(lái)，中心擺藥室反映打印擺藥單，系統(tǒng)速度越來(lái)越慢。操作人員在主界面完成擺藥處理后，并不直接打印擺藥單，而是使用護(hù)理單元醫(yī)囑擺藥查詢(xún)窗口重新調(diào)入擺藥記錄進(jìn)行打印，這樣打印結(jié)果能滿(mǎn)足某些特殊需求。如圖1所示，點(diǎn)擊“查詢(xún)擺藥單”按鈕后，系統(tǒng)需等待16 s，選中某一護(hù)理單元后，點(diǎn)擊“查詢(xún)擺藥單明細(xì)”按鈕又需等待約15 s，下方的數(shù)據(jù)窗口才能顯示需要的數(shù)據(jù)，嚴(yán)重影響擺藥查詢(xún)和打印工作效率。經(jīng)過(guò)認(rèn)真分析系統(tǒng)運(yùn)行的軟硬件環(huán)境，認(rèn)為處于同一網(wǎng)絡(luò)環(huán)境的“軍衛(wèi)一號(hào)”其他子系統(tǒng)軟件運(yùn)行速度正常，不存在影響系統(tǒng)運(yùn)行的硬件瓶頸，如CPU運(yùn)算能力、內(nèi)存容量、磁盤(pán)讀取速度和網(wǎng)絡(luò)帶寬變化的影響。因此，應(yīng)是醫(yī)囑擺藥子系統(tǒng)自身存在某些瑕疵，個(gè)別SQL語(yǔ)句的執(zhí)行效率低下導(dǎo)致系統(tǒng)運(yùn)行速度變慢。所以，有必要分析系統(tǒng)運(yùn)行過(guò)程中執(zhí)行的SQL語(yǔ)句，優(yōu)化整個(gè)Oracle數(shù)據(jù)庫(kù)或者單個(gè)應(yīng)用程序或某條SQL語(yǔ)句來(lái)解決此問(wèn)題。

圖1 護(hù)理單元醫(yī)囑擺藥查詢(xún)界面截圖

1 系統(tǒng)優(yōu)化硬件和軟件需求

1.1 運(yùn)行環(huán)境

服務(wù)器硬件條件為惠普380G5 CPU P4 xeon 2.2 GHz，4 GB內(nèi)存；操作系統(tǒng)為Windows 2003 Server；數(shù)據(jù)庫(kù)為Oracle8.1.7；客戶(hù)端為512 MB內(nèi)存的運(yùn)行Windows XP的普通計(jì)算機(jī)，安裝Oracle8i客戶(hù)端和醫(yī)囑擺藥子系統(tǒng)。

1.2 測(cè)試診斷工具

首先，要找到影響系統(tǒng)運(yùn)行效率的某些SQL語(yǔ)句，這就需要借助部分第三方診斷工具軟件，如SQL語(yǔ)句執(zhí)行跟蹤工具——Statement Tracer for Oracle[3]。利用該軟件可以方便得到系統(tǒng)運(yùn)行時(shí)和Oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)交互使用的SQL語(yǔ)句。其次，使用SQL語(yǔ)句執(zhí)行計(jì)劃查看工具Autotrace[4]，該工具為Oracle系統(tǒng)自帶，借助它可以查看相關(guān)SQL語(yǔ)句的執(zhí)行計(jì)劃。

2 優(yōu)化法則和優(yōu)化過(guò)程

2.1 優(yōu)化的一般法則

“軍衛(wèi)一號(hào)”系統(tǒng)使用Oracle數(shù)據(jù)庫(kù)，其運(yùn)行性能對(duì)“軍衛(wèi)一號(hào)”的諸多軟件性能有著重要影響[5]。而Oracle數(shù)據(jù)庫(kù)的優(yōu)化是一個(gè)大的命題，包括數(shù)據(jù)庫(kù)本身的優(yōu)化和應(yīng)用軟件的優(yōu)化。系統(tǒng)建立之初一段時(shí)間就要對(duì)數(shù)據(jù)庫(kù)的各項(xiàng)參數(shù)進(jìn)行初步優(yōu)化設(shè)置，而且隨著系統(tǒng)的運(yùn)行，隨時(shí)監(jiān)控系統(tǒng)運(yùn)行情況，適時(shí)調(diào)整相關(guān)參數(shù)設(shè)置，保證系統(tǒng)處于最優(yōu)狀況[6]?，F(xiàn)有“軍衛(wèi)一號(hào)”的Oracle數(shù)據(jù)庫(kù)支持的應(yīng)用軟件有70多個(gè)，大多都能高效運(yùn)行，現(xiàn)只有擺藥子系統(tǒng)這個(gè)查詢(xún)打印功能運(yùn)行耗時(shí)較多，效率較低，因此可以肯定是和這個(gè)應(yīng)用的相關(guān)SQL語(yǔ)句出現(xiàn)了問(wèn)題。而要正確優(yōu)化SQL，就需要快速定位性能瓶頸，也就是找到SQL主要開(kāi)銷(xiāo)在哪里。大多數(shù)情況下，性能最低、速度最慢的設(shè)備會(huì)是瓶頸，表1是當(dāng)前主流計(jì)算機(jī)的設(shè)備性能指標(biāo)數(shù)據(jù)[7]。

表1 設(shè)備各層次性能匯總

表1中的每種設(shè)備有2個(gè)指標(biāo)——延時(shí)和帶寬，其中延時(shí)（響應(yīng)時(shí)間）表示硬件的突發(fā)處理能力，而帶寬（吞吐量）代表硬件的持續(xù)處理能力。從中看出，計(jì)算機(jī)系統(tǒng)的硬件性能從高到低依次為：CPU、Cache（L1、L2、L3）、內(nèi)存、網(wǎng)卡、普通硬盤(pán)。

根據(jù)Oracle數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)，CPU及內(nèi)存主要完成緩存數(shù)據(jù)訪問(wèn)、比較、排序、SQL解析等，網(wǎng)絡(luò)完成SQL請(qǐng)求、結(jié)果數(shù)據(jù)傳輸，硬盤(pán)完成數(shù)據(jù)的存儲(chǔ)、讀出、寫(xiě)入、日志記錄、磁盤(pán)排序和某些大表連接操作。根據(jù)以上指標(biāo)及操作內(nèi)容制定優(yōu)化法則：

（1）做最少的事就是減少數(shù)據(jù)訪問(wèn)（減少磁盤(pán)訪問(wèn)）；

（2）返回更少的數(shù)據(jù)和減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸和磁盤(pán)訪問(wèn)）；

（3）CPU開(kāi)銷(xiāo)（減少CPU和內(nèi)存開(kāi)銷(xiāo)）；

（4）利用更多資源。

本著自上而下的步驟，利用最低的成本取得最大的效能，優(yōu)先對(duì)低速設(shè)備（硬盤(pán)）進(jìn)行優(yōu)化，也就是減少對(duì)存儲(chǔ)在硬盤(pán)上數(shù)據(jù)的訪問(wèn)，即對(duì)應(yīng)用的SQL語(yǔ)句進(jìn)行必要的分析，創(chuàng)建并合理地使用索引，減少磁盤(pán)讀取次數(shù)和時(shí)間，而不是首先想到增加資源以解決性能問(wèn)題。

2.2 優(yōu)化過(guò)程

（1）網(wǎng)上下載SQL語(yǔ)句跟蹤工具Statement tracer for Oracle安裝在客戶(hù)端計(jì)算機(jī)上，啟動(dòng)跟蹤工具Statement tracer for Oracle，再啟動(dòng)應(yīng)用程序擺藥單查詢(xún)，跟蹤得到運(yùn)行最慢的2條SQL語(yǔ)句。

（2）Oracle基于成本的優(yōu)化方式（CBO）大大提高了SQL執(zhí)行效率，優(yōu)化過(guò)程中，為了了解、掌握SQL語(yǔ)句的執(zhí)行情況，需要查看SQL語(yǔ)句的執(zhí)行計(jì)劃，這就需要先安裝執(zhí)行計(jì)劃查看工具Autotrace，該工具為Oracle自帶。

以sys用戶(hù)運(yùn)行utlxplan.sql

Sqlplus/as sysdba

Sql＞@$ORACLE_HOME/rdbms/admin/utlxplan. sql

創(chuàng)建plustrace角色

Sql＞@$ORACLE_HOME/rdbms/admin/plustrce. sql

對(duì)用戶(hù)授權(quán)：Sql＞grant plustrace to pharmacy

（3）查看語(yǔ)句執(zhí)行計(jì)劃，以pharmacy用戶(hù)連接數(shù)據(jù)庫(kù)，并執(zhí)行以下步驟：

CONN pharmacy/pharmacy@DBSERVER

SET TIMING ON——計(jì)時(shí)語(yǔ)句執(zhí)行時(shí)間

SET AUTOTRACE TRACEONLY——顯示解釋執(zhí)行計(jì)劃和統(tǒng)計(jì)，但不顯示結(jié)果集

（4）執(zhí)行（1）跟蹤得到的SQL語(yǔ)句：

SQL＞SELECT"DRUG_DISPENSE_REC"."DISPENSING_DATE_TIME",

2"DRUG_DISPENSE_REC"."DISPENSING_ PROVIDER",

3"DEPT_VS_WARD"."WARD_CODE",

4 count(*)order_count

5 FROM"DRUG_DISPENSE_REC",

6"DEPT_VS_WARD"

7 WHERE(drug_dispense_rec.ordered_by=dept_vs_ward.dept_code(+))and

8("DRUG_DISPENSE_REC"."DISPENSING_ DATE_TIME"＞=TO_DATE('7-Jan-2012 00：00：00', 'DD-MON-YYYY HH24：MI：SS'))AND

9("DRUG_DISPENSE_REC"."DISPENSING_ DATE_TIME"＜TO_DATE('8-Jan-2012 00：00：00', 'DD-MON-YYYY HH24：MI：SS'))AND

10("DEPT_VS_WARD"."WARD_CODE"= '100004'OR

11'100004'IS NULL)

12 GROUP BY"DRUG_DISPENSE_REC"."DISPENSING_DATE_TIME",

13"DRUG_DISPENSE_REC"."DISPENSING_ PROVIDER",

14"DEPT_VS_WARD"."WARD_CODE";

8 rows selected.

Elapsed:00∶00∶16.06

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT(GROUP BY)

2 1 FILTER

3 2 NESTED LOOPS(OUTER)

4 3 TABLE ACCESS(FULL)OF'DRUG_ DISPENSE_REC'

5 3 TABLE ACCESS(BY INDEX ROWID) OF'DEPT_VS_WARD'

65 INDEX (UNIQUE SCAN)OF'PK_ DEPT_VS_WARD'(UNIQUE)

（5）分析原因?？梢钥吹絆racle執(zhí)行上述語(yǔ)句，使用16.06 s，返回8行數(shù)據(jù)，關(guān)鍵的執(zhí)行計(jì)劃部分。我們看到Oracle在執(zhí)行該語(yǔ)句時(shí)，系統(tǒng)采用了基于成本的優(yōu)化模式，但是在表Drug_Dispense_Rec上執(zhí)行了全表掃描（執(zhí)行計(jì)劃中下劃線部分），而這個(gè)表正是擺藥系統(tǒng)記錄數(shù)最多的一個(gè)表，經(jīng)過(guò)查詢(xún)，該表現(xiàn)在有820萬(wàn)條記錄，可以預(yù)測(cè)，隨著擺藥系統(tǒng)的日常使用，該表記錄數(shù)的不斷增加，系統(tǒng)勢(shì)必將越來(lái)越慢。點(diǎn)擊“查詢(xún)擺藥單明細(xì)”按鈕得到的SQL語(yǔ)句和上面情況類(lèi)似，執(zhí)行計(jì)劃也是在Drug_Dispense_Rec上發(fā)生了嚴(yán)重的全表掃描。而像Oracle數(shù)據(jù)庫(kù)這樣的大型關(guān)系數(shù)據(jù)庫(kù)，它的精髓就在于通過(guò)關(guān)系表進(jìn)行規(guī)范化的數(shù)據(jù)存儲(chǔ)，并通過(guò)各種表鏈接技術(shù)和各種類(lèi)型的索引技術(shù)來(lái)進(jìn)行信息的檢索和處理。合理的索引策略將保證各種數(shù)據(jù)提取的高效和快捷，沒(méi)有合理的索引，將不得不按表進(jìn)行全部遍歷，即全表掃描，這意味著倒退回到文件系統(tǒng)的處理模式，更意味著資源、時(shí)間的大量消耗。本例出現(xiàn)上述問(wèn)題的可能原因就是擺藥查詢(xún)時(shí)Oracle沒(méi)有在Drug_Dispense_Rec表上找到可用的合適索引，不得已采用了全表掃描這種效率最低下的執(zhí)行方式。查詢(xún)表Drug_Dispense_Rec上的索引名為PK_DRUG_DISPENSE_REC，且該主鍵的索引列是（patient_id，vis it_id，order_no，order_sub_no，DISPENSING_DATE_ TIME.），該索引是個(gè)復(fù)合索引，因?yàn)閺?fù)合索引有前綴性和選擇性2大原則，而此處違反了前綴性原則，也即在SQL語(yǔ)句中只有將復(fù)合索引的第1個(gè)字段（此處為patient_id）作為約束條件，該復(fù)合索引才會(huì)啟用，因而這個(gè)復(fù)合索引在該SQL語(yǔ)句的執(zhí)行中是無(wú)法用的[8]，Oracle只有采取全表掃描。

（6）解決方案。經(jīng)過(guò)以上分析，已然明朗問(wèn)題所在和如何解決，即在表DRUG_DISPENSE_REC上建立以DISPENSING_DATE_TIME為索引列的索引：

Sql＞Create index ind_drug_dispense_rec on DRUG_DISPENSE_REC(DISPENSING_DATE_TIME) Tablespace tsp_pharmacy;

Elapsed:00∶06∶365.15

3 優(yōu)化應(yīng)用效果

重新運(yùn)行上述語(yǔ)句，得到執(zhí)行計(jì)劃。

...............//此處省略執(zhí)行的語(yǔ)句

Elapsed:00∶00∶00.08

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT(GROUP BY)

2 1 FILTER

3 2 NESTED LOOPS(OUTER)

4 3 TABLE ACCESS(BY INDEX ROWID) OF'DRUG_DISPENSE_REC'

54 INDEX (RANGE SCAN)OF'IND_ DRUG_DISPENSE_REC'(NON-UNIQUE)

65 TABLE ACCESS(BY INDEX ROWID) OF'DEPT_VS_WARD'

7 6 INDEX(UNIQUESCAN)OF'PK_DEPT_ VS_WARD'(UNIQUE)

Statistics

1 recursive calls

0 db block gets

255 consistent gets

2 physical reads

0 redo size

2812 bytes sent via SQL*Net to client

372 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts(memory)

0 sorts(disk)

8 rows processed

現(xiàn)在可以看到，執(zhí)行時(shí)間僅用0.08 s，速度顯著

（????）（????）提升。執(zhí)行計(jì)劃中，對(duì)表DRUG_DISPENSE_REC的訪問(wèn)從全表掃描改為了訪問(wèn)剛剛建立的索引IND_DRUG_DISPENSE_REC，效率大大提高。優(yōu)化后的系統(tǒng)經(jīng)過(guò)2 a多的運(yùn)行證明運(yùn)行速度更快、效率更高，很好地滿(mǎn)足了藥房日常擺藥需求，患者和藥工人員都很滿(mǎn)意，因此優(yōu)化取得了理想的效果。

4 結(jié)語(yǔ)

通常，程序開(kāi)發(fā)人員很少注意SQL語(yǔ)句的效率，更多著眼于功能實(shí)現(xiàn)，性能問(wèn)題常被忽視[9]。程序開(kāi)發(fā)之初，擺藥記錄表DRUG_DISPENSE_REC可能也沒(méi)有太多的數(shù)據(jù)量，系統(tǒng)使用全表掃描方式對(duì)應(yīng)用軟件的性能影響也不明顯，但隨著記錄數(shù)的日漸增多，對(duì)擺藥記錄表的全表掃描引起的性能影響就不可忽視。本文通過(guò)在DRUG_DISPENSE_REC上建立合適索引，使Oracle基于成本的優(yōu)化方式能從全表掃描改為應(yīng)用索引掃描，系統(tǒng)的檢索性能能從16 s減少到0.08 s，點(diǎn)擊“查詢(xún)擺藥單明細(xì)”按鈕對(duì)應(yīng)的SQL語(yǔ)句執(zhí)行速度從15.0 s提高到0.06 s，大大提高了擺藥查詢(xún)打印效能。推而廣之，只要遇到類(lèi)似SQL執(zhí)行效率問(wèn)題，即可以采用以上步驟，分析可疑執(zhí)行效率低下的SQL語(yǔ)句的執(zhí)行計(jì)劃，看看是否出現(xiàn)某些大表的全表掃描，通過(guò)建立合適的索引，提高SQL語(yǔ)句執(zhí)行效率，常常能起到事半功倍的效果。當(dāng)然事物都是一分為二的，在表上建立索引能提高數(shù)據(jù)庫(kù)訪問(wèn)性能，但也會(huì)使數(shù)據(jù)庫(kù)表在增、刪、改時(shí)增加索引維護(hù)的成本和代價(jià)，這就要求在訪問(wèn)性能和索引成本之間作一平衡。

[1]傅征，任連仲.醫(yī)院信息系統(tǒng)建設(shè)與應(yīng)用[M].北京：人民軍醫(yī)出版社，2002：241-242.

[2]楊春蓮，安愛(ài)軍，呂雙叢，等.“軍衛(wèi)一號(hào)”醫(yī)囑擺藥子系統(tǒng)應(yīng)用中的問(wèn)題及改進(jìn)建議[J].醫(yī)療衛(wèi)生裝備，2012，33（4）：54-56.

[3]Carelesswhisper187 Statement Tracer for Oracle[EB/OL].[2012-08-13].http：//www.csdn.net/detail/soft/Carelesswhisper187/4496838.

[4]Kyte T.Expert Oracle Database Architecture：9i/10g programming Techniques and Solution[M].USA：Apress，2005：466-533.

[5]袁慧穎，翁盛鑫，程少平，等.ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化[J].海軍總醫(yī)院學(xué)報(bào)，2007，21（3）：179-181.

[6]linfen.大型ORACLE數(shù)據(jù)庫(kù)優(yōu)化設(shè)計(jì)方案[EB/OL].[2010-03-16]. http：//www.cnblogs.com/luluping/archive/2010/03/16/1686822.html.

[7]蓋國(guó)強(qiáng)，楊廷琨.Oracle DBA手記3[M].北京：電子工業(yè)出版社，2011：75-96.

[8]羅敏.品悟性能優(yōu)化[M].北京：清華大學(xué)出版社，2011：59-64.

[9]蓋國(guó)強(qiáng)，馮春培，葉梁.Oracle數(shù)據(jù)庫(kù)性能優(yōu)化[M].北京：人民郵電出版社，2005：307-317.

（收稿：2014-09-08 修回：2014-12-15）

Optimization design of prescription dispensing inquiry of No.1 Military Medical Project

ZHU You-cun1,DUAN Jian-fa2
（1.The 536th Hospital of the PLA,Xining 810007,China; 2.The 538th Hospital of the PLA,Hanzhong 723102,Shaanxi Province,China）

ObjectiveTo realize rapid inquiry of the dispensing results in prescription dispensing subsystem.MethodsSQL statement tracer was used to determine corresponding SQL statement,and the executive plan was analyzed to find the desired table.Proper indexes were established to enhance the efficiency of the inquiry statement.ResultsThe optimization of SQL statements made the executive efficiency increased greatly without the need for hardware upgrade.ConclusionThe analysis on the executive plan of the dispensing inquiry statements and the indexes on corresponding tables increases the prescription dispensing inquiry speed while decrease the waiting time of the staff.[Chinese Medical Equipment Journal，2015，36（9）：63-65，89]

SQL statement executive plan;optimization of dispensing inquiry;NO.1 Military Medical Project

R318；TP311.13

A

1003-8868（2015）09-0063-04

10.7687/J.ISSN1003-8868.2015.09.063

朱有存（1966—），男，高級(jí)工程師，主要從事醫(yī)院信息化管理和系統(tǒng)開(kāi)發(fā)方面的研究工作，E-mail：jfj536zyc@163.com。

810007西寧，解放軍536醫(yī)院（朱有存）；723102陜西漢中，解放軍538醫(yī)院（段建發(fā)）