前言 在單一的應(yīng)用環(huán)境或業(yè)務(wù)相對簡單的系統(tǒng)下,系統(tǒng)性能問題 , 瓶頸所在往往是不言自明,解決問題的前提 -- 定位問題。定位問題是比較輕易解決的 , 但在一個復(fù)雜的應(yīng)用環(huán)境下,各應(yīng)用系統(tǒng)對系統(tǒng)資源往往是一種共享和競爭的關(guān)系,而且應(yīng)用系統(tǒng)之間也可能存在著共生或制約的關(guān)系,資源利益的均衡往往是此消彼長,而這種環(huán)境下的應(yīng)用系統(tǒng)一旦出現(xiàn)資源競爭,系統(tǒng)的瓶頸往往難以斷定,甚至?xí)l(fā)生不同應(yīng)用設(shè)計(jì)人員之間互相推諉責(zé)任的扯皮現(xiàn)象。本文僅就此問題對 Linux 平臺下各應(yīng)用系統(tǒng)對 Oracle 數(shù)據(jù)庫的使用情況作一探討,ORACLE 數(shù)據(jù)庫的 TUNING 不是一個可以一言以蔽的主題,本文無意概全,內(nèi)容僅涉及問題的定位及各應(yīng)用對數(shù)據(jù)庫資源的共享與競爭問題。 本文試驗(yàn)及問題取證的環(huán)境 : RedHat6.1 Web server(Apache1.3.9+PHP4.0)+Client/Server(Pro*C) 之 Server 端 RedHat6.2 + Oracle8.1.6.1.0 RedHat7.1 Web server(Apache1.3.20+PHP4.06) + Oracle8.1.7.0.0 為方便問題的討論,應(yīng)用系統(tǒng)已做簡化,競爭方僅包括一個 Pro*C 的 daemon 程序作為 C/S 模式的服務(wù)端, 和由 Apache+PHP 所支持的 WEB 網(wǎng)站業(yè)務(wù)。 1. 單個 SQL 語句的處理 首先,最簡單的情況莫過于單個 SQL 語句的分析 , SQL 語句的優(yōu)化也是數(shù)據(jù)庫優(yōu)化的一個最直接最立竿見影的因素。SQL 語句的性能監(jiān)控從監(jiān)控工具來說大致可分為由高級語言提供和由ORACLE 本身提供,高級語言以典型的應(yīng)用 C 語言和 WEB 開發(fā)語言 PHP 為例 , C語言中可以用 gettimeofday 函數(shù)來在某一數(shù)據(jù)庫操作之前和之后分別獲取一個時(shí)間值,將兩個時(shí)間值之差做為衡量該數(shù)據(jù)庫操作的效率,在 PHP中,也可以用 gettimeofday, 操作方法當(dāng)然與 C 語言中有所不同。當(dāng)然,PHP中也有其它一些函數(shù)可以達(dá)到同樣的時(shí)間精度 , 關(guān)于時(shí)間精度的考慮,不能簡單以大小衡量微秒級的時(shí)間數(shù)值。因?yàn)闀r(shí)鐘中斷的時(shí)間間隔從根本上決定了時(shí)間計(jì)算所能達(dá)到的精度。此外,操作系統(tǒng)本身對進(jìn)程的時(shí)間片分配 , 及進(jìn)程切換的開銷等因素也在一定程度上影響時(shí)間數(shù)據(jù)的意義。所以,以下時(shí)間的計(jì)算最理想的情況是對同一操作在盡可能避免緩存的情況下進(jìn)行多次的循環(huán)操作,取總的時(shí)間值加以平均,從而得到比較接近真實(shí)情況的時(shí)間值。 C 語言的例子 : #define TV_START 0 #define TV_END 1 int how_long(int cmd, char *res); strUCt CMD_TIME{ int times; /* times occured within specified package number */ struct timeval time; /* total time consumed by the cmd */ }; void foo() { int id; how_long(TV_START, NULL); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL WHENEVER NOT FOUND CONTINUE; EXEC SQL select user_id into :id from users where name='slimzhao';2; how_long(TV_END, time_consume); puts(time_consume); } int how_long(int cmd, char *res) /* return value: -1 error, 0 sucess , res: 20 bytes is enough */ { static struct timeval before, after; if(cmd == TV_START) { gettimeofday(&before, NULL); return 0; } else if(cmd == TV_END) { gettimeofday(&after, NULL); if(res) { if(after.tv_usec > before.tv_usec) { sprintf(res, '%ld %ld', after.tv_sec - before.tv_sec, after.tv_usec - before.tv_usec); } else { sprintf(res, '%ld %ld', after.tv_sec - before.tv_sec - 1, 1000000 + after.tv_usec - before.tv_usec); } } return 0; } else { return -1; } }下面是一個 PHP 的例子( 為簡化起見 , 程序的錯誤檢查被忽略) <? include '<path_to_file>/how_long.inc'; how_long(TV_START, $timestr); $conn = OCILogon('username', 'passWord', 'dblink'); $stmt = OCIParse($conn, 'select ID from users where name='slimzhao''); OCIDefineByName($stmt, ID, $id); OCIExecute($stmt); OCIFetch($stmt); OCIFreeStatement($stmt); OCILogoff($conn); how_long(TV_END, $timestr); echo ' 用戶 ID: $id , 該操作消耗時(shí)間 :$timestr<br>'; ?> 其中 how_long 函數(shù)的 PHP 版本如下 : <? # 作者 : slimzhao@21cn.com # 當(dāng)前維護(hù)人 : slimzhao@21cn.com # 創(chuàng)建日期 : 2001.12.04 00:18:00 # 目的 , 在一個操作之前或之后調(diào)用該函數(shù)的不同版本 , 將得到一個記載了該操作 # 耗費(fèi)時(shí)間的字符串 , 該函數(shù)本身的開銷不計(jì)入其中 . define('TV_START', 0); define('TV_END', 1); function how_long($operation, &$str) # 返回值 : 0-- 成功 , -1-- 傳遞了非法的參數(shù) . { global $before_SQL, $after_SQL; if($operation == TV_START) { $before_SQL = gettimeofday(); return 0; } else if($operation == TV_END) { $after_SQL = gettimeofday(); if($before_SQL['usec'] > $after_SQL['usec']) { $str = ($after_SQL['sec'] - $before_SQL['sec'] - 1).' 秒 '. ($after_SQL['usec'] + 1000*1000 -$before_SQL['usec']).' 微秒 '; } else { $str = ($after_SQL['sec'] - $before_SQL['sec']).' 秒 '. ($after_SQL['usec']-$before_SQL['usec']).' 微秒 '; } } else { return -1; } } ?>上面的數(shù)據(jù)庫操作開銷的計(jì)算僅限于對時(shí)間消耗的計(jì)算,對同時(shí)使用同一數(shù)據(jù)庫的其它應(yīng)用軟件的影響,對磁盤操作的頻繁程度,數(shù)據(jù)庫操作所采取的具體策略等等因素 , 都未考慮在內(nèi) , 高級語言也不可能提供這樣的參考數(shù)據(jù)。而數(shù)據(jù)庫本身提供的監(jiān)測手段彌補(bǔ)了這一不足。最簡單的操作控制臺 : sqlplus SQL> set timing on將為每次執(zhí)行的數(shù)據(jù)庫操作進(jìn)行計(jì)時(shí),精度為 1/100 秒 , 筆者對該功能的使用中發(fā)現(xiàn)其時(shí)間的計(jì)算也有一定的偏差。而且時(shí)間偏差很大,嚴(yán)格說來,已不屬于誤差的范圍,該歸錯誤了,下面是一個例子中得到的數(shù)據(jù) : [bash$] cat tmp.sql set timing on host date; select count(*) from users; host date; SQL> @tmp.sql Wed Dec 5 00:21:01 CST 2001 COUNT(*) ---------- 1243807 Elapsed: 00:00:06.16 Wed Dec 5 00:21:05 CST 2001從系統(tǒng)的時(shí)間差來看,為 4 秒左右 , 但 ORACLE 卻報(bào)告了 6.16 秒 ! 假如說 ORACLE 工具在時(shí)間計(jì)算上太差強(qiáng)人意的話,在 SQL 語句的執(zhí)行方案上可算是對 SQL 語句如何執(zhí)行的最權(quán)威的詮釋了。解讀這樣的信息需要對 ORACLE 內(nèi)部對 SQL 操作的過程有一定了解,下面是該功能的一樣典型示例 : SQL> set autotrace on SQL> select count(*) from users; COUNT(*) ---------- 1243807 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'USER_BASEINFO$NAME' (UNIQUE) (Cost=4 Card=1244840) Statistics 0 recursive calls 4 db block gets 3032 consistent gets 3033 physical reads 0 redo size 370 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedExecution Plan 下的信息顯示 ORACLE 制定了一個什么樣的計(jì)劃來完成 SQL 操作的,SQL語言是一種 4GL 語言,其特點(diǎn)是告訴系統(tǒng)做什么,而不提供如何做的信息。當(dāng)然 , 最終的具體工作總得有人做的,只是由數(shù)據(jù)庫自動制定而不是程序員人為指定一個具體的操作步驟 , 制作這個步驟當(dāng)然要有所依據(jù), ORACLE 有兩個基本原則來決定如何優(yōu)化:cost-based( 基于開銷的優(yōu)化 ) 和 rule-based( 基于規(guī)則的優(yōu)化 )。基于開銷的優(yōu)化的工作方式依靠于數(shù)據(jù)庫對 SQL 語句所操作的數(shù)據(jù)對象 ( 可簡單認(rèn)為就是表 ) 的數(shù)據(jù)特征的統(tǒng)計(jì)特性進(jìn)行收集和分析。收集分析的工作由 DBA 來定期執(zhí)行 , 時(shí)間間隔依數(shù)據(jù)變化頻率而定,以保持統(tǒng)計(jì)數(shù)據(jù)一定的準(zhǔn)確性,具體操作請參照 analyze 語句。Oracle 預(yù)備在將來的版本中取消對基于開銷的優(yōu)化方案的支持,因?yàn)檫@種方案需要大量的數(shù)據(jù)收集與分析工作 , 且總會有一定的誤差,這造成最終的執(zhí)行方案往往不是最優(yōu)的。 基于規(guī)則的優(yōu)化則是依據(jù)一些數(shù)據(jù)操作效率的規(guī)則進(jìn)行選擇, 優(yōu)化的核心在于效率,時(shí)間上盡可能短,空間上盡可能少進(jìn)行 IO 操作。兩種優(yōu)化方案都絕非十全十美 , ORACLE 雖將其稱為優(yōu)化方案,筆者的觀察結(jié)果表明,ORACLE 制定出一個不是最優(yōu)或錯誤的執(zhí)行方案也是完全可能的。以上為例,Oracle 的優(yōu)化策略是 Choose, 所謂 Choose 就是 cost-based 或 rule-based , 讓 ORACLE 自己選擇,可以通過數(shù)據(jù)庫啟動初始化文件 initXXX.ora 文件中的 optimizer_mode 參數(shù)來指定。 言歸正傳,上面的具體策略是 Oracle 對該表的一個唯一索引進(jìn)行全掃描 , 因?yàn)樵跀?shù)據(jù)庫里一個字段假如可以建立一個 UNIQUE 類型的索引,那么它就與表中的記錄有一一對應(yīng)的關(guān)系。所以對該索引進(jìn)行 count(*) 可以保證其值等于對表進(jìn)行 count(*) 操作。對索引進(jìn)行全掃描后的上層操作是一個集合操作 , 即對找到的每個索引記錄進(jìn)行計(jì)數(shù)。對這些信息的觀察主要用來確定 ORACLE 是否選用了 SQL 程序員希望 ORACLE 選用的索引操作。 Statistics 給出了執(zhí)行該 SQL 操作所消耗的資源的統(tǒng)計(jì)數(shù)據(jù) , 信息的表達(dá)一目了然, 所有這些值都是越小越好, 以通過 SQL*Net 的數(shù)據(jù)吞吐量為例, 在 OCI 編程中使用以下技術(shù)可顯著減少網(wǎng)絡(luò)流量 : 通過將 Commit 操作與 Execute 操作綁定為一個操作。通過對數(shù)組進(jìn)行成批數(shù)據(jù)的 delete, insert,update,通過對一個 SELECT 語句指定一個預(yù)取記錄數(shù)。這些統(tǒng)計(jì)數(shù)據(jù)中, 尤其需要避免的是涉及磁盤存取的操作 , 因?yàn)槎嗉壌鎯Φ牟僮魉俣仁?CPU >> Memory >> HD > Disc > network > disk。2. 對投入運(yùn)營的系統(tǒng)中 PHP 程序的監(jiān)控 理想的開發(fā)流程是 設(shè)計(jì) -> 文檔 -> 編碼 -> 測試 -> 投入使用,但實(shí)際運(yùn)行的系統(tǒng)往往是由良莠不齊的程序所組成,有些缺乏文檔 , 有些可讀性差 , 有些程序極為脆弱。對于這樣的既成事實(shí) , 假如系統(tǒng)中出現(xiàn)了瓶頸,不可能一條語句一條語句地來進(jìn)行測試 , 只能是用一種統(tǒng)一的方法定位主要問題的所在。由于 PHP 程序中的 SQL 語句使用了所謂動態(tài) SQL 語句 , 即用 戶可以在程序運(yùn)行時(shí)動態(tài)生成一個 SQL 語句， 所以假如對靜態(tài)的 PHP 程序文件進(jìn)行。 ( 如用 grep 工具 ) 可能會搜捕不到成形的完整 SQL 語句 , 這就要求用一種動態(tài)方法來攔截實(shí)際執(zhí)行的每一個完整的 SQL 語句,觀察 PHP 中關(guān)于 ORACLE 數(shù)據(jù)庫操作的函數(shù)簇 , 發(fā)現(xiàn) OCIParse 和 Ora_Parse 兩個函數(shù)是 SQL 語句的入口,而將這兩個函數(shù)統(tǒng)一替換為一個用戶自定義的函數(shù)即可實(shí)現(xiàn)對 SQL 語句的攔截。在筆者涉入的實(shí)際系統(tǒng)中 , 是這樣解決的 : 首先分析該系統(tǒng)中所有的 PHP 程序文件,發(fā)現(xiàn)凡涉及 ORACLE 數(shù)據(jù)庫操作的都需要包含一個以 *.conf 結(jié)尾的配置文件 , 該配置文件是數(shù)據(jù)庫的用戶名,密碼和連接標(biāo)識符的定義文件 , 這些是開發(fā)初期定下的規(guī)范,以便于對程序中共用的配置信息進(jìn)行統(tǒng)一的治理 , 以下是一個 oracle.conf： <? $oracle_user='oracle_user'; $oracle_password='oracle_password'; $oracle_dbid = 'oracle_dbid'; ?>在涉及數(shù)據(jù)庫操作的 PHP 程序中 , 總有一行語句以引入該配置文件 : include('<path_to_file>/oracle.conf');設(shè)計(jì)一個函數(shù)如 debug_OCIParse 如下 , 以替換 OCIParse, 并將該文件放入一個叫 debug.conf 的別一個配置文件中 , 如下 : oracle.conf: <? global $impossible_conflit_with_this_oracle,$user,$password,$dbname; if(!$impossible_conflit_with_this_oracle) require('/home/httpd/debug.conf'); $impossible_conflit_with_this_oracle=1; $user='username'; $password='password'; $dbname='dblink'; ?> ========================================================== debug.conf: ========================================================== <? function debug_OCIParse($debug_conn, $debug_sql, $filename, $line) { debug_WriteLog($debug_sql, $filename, $line); return OCIParse($debug_conn, $debug_sql); } function debug_Ora_Parse($debug_conn, $debug_sql, $filename, $line) { debug_WriteLog($debug_sql, $filename, $line); return Ora_Parse($debug_conn, $debug_sql); } function debug_WriteLog($debug_sql, $filename, $line) { #if(!strstr($filename,'message.pHtml')) return; $string = date('Y-m-d H:i:s').' $filename:$linent$debug_sqln'; $fp = fopen('/home/httpd/sql.log', 'a'); fwrite($fp, $string, strlen($string)); fclose($fp); } ?>然后,統(tǒng)一將所有 PHP 程序中的 OCIParse 函數(shù)替換為 debug_OCIParse 函數(shù),并要求 PHP 程序員以后使用 debug_OCIParse 函數(shù)進(jìn)行開發(fā),如下將： $stmt = OCIParse($conn, $sql);替換為 : $stmt = debug_OCIParse($conn, $sql, __FILE__, __LINE__);這個工作可由系統(tǒng)治理員統(tǒng)一做一次,以后就要要求 PHP 程序員形成規(guī)范。可用如下腳本： find /home/httpd/html -name '*.ph*' xargs -n1 while read i do ex -c ':se icg/ociparse/s/ociparse/debug_&/s/);$/,__FILE__,__LINE__&/' -c ':x!' $i done這幾行腳本并非放之皆準(zhǔn),但對于規(guī)范的 php 文件,一般來說沒有問題 , 筆者的系統(tǒng)中用該方法維護(hù)幾百 M 的 PHP 程序。少有例外,由于這是只運(yùn)行一次的腳本,所以只要根據(jù)自己具體的系統(tǒng)做適當(dāng)?shù)恼{(diào)整即可。如上,假如對含有 OCIParse 的程序行的內(nèi)容不太確定,可以用如下方法先進(jìn)行查看: find /home/httpd/html -name '*.ph*' xargs grep -in ociparse > ~/list這段腳本中的 ex 命令稍作解釋: ex 是vi編輯器的后端工具 , 可以在命令行上使用一些編輯命令,每個編輯命令以 -c 選項(xiàng)開頭 , 如上:se ic是改變編輯器對大小寫不敏感 , 全稱是 :set ignorecase。 號用來間隔多個編輯命令。 g/ociparse/s/ociparse/debug_&/ 的編輯語意為 : 找到含有 ociparse 的行 , 對這些行執(zhí)行如下編輯命令。 s/ociparse/debug_&/, s 意為 substitute, 將 ociparse 替換為 debug_&, 這其中 & 代表前面找到的匹配字符串 , 由于是忽略大小寫的 , 所以用 & 來保留前面找到的不管是大小寫如何混合的字符串的原型 . 這樣 , ociparse 就會被替換為 debug_ociparse, 而 OCIParse 將會被替換為debug_OCIParse。 接下來的 s/);$/,__FILE__,__LINE__&/ 是將 ociparse 語句的右括號進(jìn)行替換,將用于調(diào)試監(jiān)控的兩個參數(shù)(PHP 中的宏 ) 加上,$ 不是指一個真正的字符,而是指一個特定的位置 -- 行尾,以避免無辜的 ); 被替換掉。另一個命令 -c ':x!' 是將該文件存盤退出。 打出這么一套組合拳需要你對這些命令了如指掌 , 假如你對某個文件沒有寫權(quán) , 或出了其它岔子 , 那簡直是一場災(zāi)難 , 這種魔法級的指令總是高風(fēng)險(xiǎn)的,搞不好會走火入魔,讓你發(fā)下毒誓有生之年不再碰它。所以謹(jǐn)慎與備份總是對的。3. 對各種應(yīng)用程序中的情況進(jìn)行監(jiān)控 假設(shè)一個系統(tǒng)中不僅僅有 PHP 程序,還有 C 程序與數(shù)據(jù)庫進(jìn)行連接,那么數(shù)據(jù)庫系統(tǒng)一旦出了問題,如資源消耗過多,造成死鎖等。 ps ax grep oracleORCL是看不出什么東西的,因?yàn)檫@個進(jìn)程是 Oracle 的 shadow 進(jìn)程,命令名字都被改了,從 /proc 文件系統(tǒng)中提供的信息中也榨不出什么有用的東西了,所以,假如發(fā)現(xiàn)一個進(jìn)程 ( 這是 ps ax 的實(shí)際輸出 ) 如下: 10406 ? R 159:10 oracleORCL (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=確定這個進(jìn)程長時(shí)間處于 running 狀態(tài)的肇事者就成為一個難題 , 首先 , 進(jìn)程的運(yùn)行者是Oracle,連接者卻可能是來自本機(jī) , 來自局域網(wǎng)絡(luò) , 來自 internet 的 nobody 用戶 , 所以冤無頭,債無主。查看 v$session, v$process, v$..., 也沒有關(guān)于客戶端的足夠信息。可以用來縮小范圍的是 SQL 語句 , 但仍不足以構(gòu)成充分的說服力讓某一應(yīng)用的開發(fā)人員確信是自己的程序出了問題。觀察字段豐富的 v$session 視圖,里面有一個十分誘人的 client_info 字段 , 顧名思義 , 不能不讓人想入非非:一定是關(guān)于 Oracle客戶端的信息的,可惜它一般是 NULL 值, 筆者從 ORACLE 文檔中終于發(fā)現(xiàn)了 dbms_application_info.set_client_info(string);是用來設(shè)置連接 ORACLE 的客戶端信息的一個包 , 拿來 PRO*C 中運(yùn)行: EXEC SQL EXECUTE BEGIN dbms_application_info.set_client_info(' 某應(yīng)用程序 : 其 PID, 文件名 , 行號 '); END: END-EXEC;運(yùn)行該 PRO*C 程序 , 執(zhí)行一條 SQL 語句 , 并在關(guān)閉光標(biāo)之前故意讓它： sleep(1000);以騰出足夠多的時(shí)間來觀察 v$session 中的 client_info 字段： [bash$] sqlplus sys/change_on_install@orcl SQL> select distinct * from (select a.client_info,b.sql_text,c.spid > from v$session a,v$sql b , v$process c where a.client_info is not null > and a.sql_hash_value=b.hash_value and a.paddr=c.addr);正是你剛才設(shè)定的某應(yīng)用程序:其PID,文件名,行號信息 , 別嫌短,這個 client_info 字段是 64 個字節(jié)夠了。看能不能讓這寶貴功能施于 PHP: <? $conn = OCILogon('username', 'password', 'dblink'); $stmt_client = OCIParse($conn, 'call dbms_application_info.set_client_info('PHP:$filename:$line')'); OCIExecute($stmt_client); OCIFreeStatement($stmt_client); $stmt = OCIParse($conn, 'select ID from users where name='slimzhao''); OCIDefineByName($stmt, ID, $name); OCIExecute($stmt); OCIFetch($stmt); sleep(1000); // 故意的 OCIFreeStatement($stmt); OCILogoff($conn); ?>到 SQLPLUS 下一看 , 果不其然!將該功能加入前面的配置文件中,將會對 PHP 中的 SQL 語句進(jìn)行更精確的跟蹤定位。 至此,可以將數(shù)據(jù)庫服務(wù)器下某一Oracle 的 shadow 進(jìn)程與具體哪一個應(yīng)用程序,甚至是哪一個源文件,哪一行的信息以及所執(zhí)行的 SQL 語句等一一對應(yīng)起來,有了這根主線,其它問題的分析就可步步深入,耗了多少時(shí)間,讀了多少個數(shù)據(jù)塊 , 進(jìn)行了多少次排序,等等問題,都可通過 v$... 視圖收集到足夠的信息。本文重點(diǎn)不在于此,僅作拋磚,就此打住。