前言: 在單一的應(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è)計人員之間互相推諉責(zé)任的扯皮現(xiàn)象, 本文僅就此問題對linux平臺下各應(yīng)用系統(tǒng)對Oracle數(shù)據(jù)庫的使用情況作一探討, ORACLE數(shù)據(jù)庫的TUNING不是一個可以一言以蔽的主題, 本文無意概全, 內(nèi)容僅涉及問題的定位及各應(yīng)用對數(shù)據(jù)庫資源的共享與競爭問題.本文試驗及問題取證的環(huán)境:RedHat6.1 Web server(Apache1.3.9+php4.0)+Client/Server(PRo*C)之Server端RedHat6.2 + Oracle8.1.6.1.0RedHat7.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ù)據(jù)庫操作的效率, 在PHP中, 也可以用gettimeofday, 操作方法當然與C語言中有所不同. 當然, PHP中也有其它一些函數(shù)可以達到同樣的時間精度, 關(guān)于時間精度的考慮, 不能簡單以大小衡量微秒級的時間數(shù)值, 因為時鐘中斷的時間間隔從根本上決定了時間計算所能達到的精度, 此外, 操作系統(tǒng)本身對進程的時間片分配, 及進程切換的開銷等因素也在一定程度上影響時間數(shù)據(jù)的意義. 所以, 以下時間的計算最理想的情況是對同一操作在盡可能避免緩存的情況下進行多次的循環(huán)操作, 取總的時間值加以平均, 從而得到比較接近真實情況的時間值.C語言的例子:==========================================================#define TV_START 0#define TV_END 1int 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 , 該操作消耗時間:$timestr<br>";?>其中how_long函數(shù)的PHP版本如下:<?#作者: slimzhao@21cn.com#當前維護人: slimzhao@21cn.com#創(chuàng)建日期: 2001.12.04 00:18:00#目的, 在一個操作之前或之后調(diào)用該函數(shù)的不同版本, 將得到一個記載了該操作#耗費時間的字符串, 該函數(shù)本身的開銷不計入其中.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ù)庫操作開銷的計算僅限于對時間消耗的計算, 對同時使用同一數(shù)據(jù)庫的其它應(yīng)用軟件的影響, 對磁盤操作的頻繁程度, 數(shù)據(jù)庫操作所采取的具體策略等等因素, 都未考慮在內(nèi), 高級語言也不可能提供這樣的參考數(shù)據(jù). 而數(shù)據(jù)庫本身提供的監(jiān)測手段彌補了這一不足. 最簡單的操作控制臺:sqlplusSQL> set timing on將為每次執(zhí)行的數(shù)據(jù)庫操作進行計時, 精度為1/100秒, 筆者對該功能的使用中發(fā)現(xiàn)其時間的計算也有一定的偏差. 而且時間偏差很大, 嚴格說來, 已不屬于誤差的范圍, 該歸錯誤了, 下面是一個例子中得到的數(shù)據(jù):[bash$] cat tmp.sqlset timing onhost date;select count(*) from users;host date;SQL> @tmp.sqlWed Dec 5 00:21:01 CST 2001 COUNT(*)---------- 1243807Elapsed: 00:00:06.16Wed Dec 5 00:21:05 CST 2001從系統(tǒng)的時間差來看, 為4秒左右, 但ORACLE卻報告了6.16秒! 假如說ORACLE工具在時間計算上太差強人意的話, 在SQL語句的執(zhí)行方案上可算是對SQL語句如何執(zhí)行的最權(quán)威的詮釋了. 解讀這樣的信息需要對ORACLE內(nèi)部對SQL 操作的過程有一定了解, 下面是該功能的一樣典型示例: SQL> set autotrace onSQL> select count(*) from users; COUNT(*)---------- 1243807Execution 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 processed Execution Plan下的信息顯示ORACLE制定了一個什么樣的計劃來完成SQL操作的,SQL語言是一種4GL語言, 其特點是告訴系統(tǒng)做什么, 而不提供如何做的信息. 當然, 最終的具體工作總得有人做的, 只是由數(shù)據(jù)庫自動制定而不是程序員人為指定一個具體的操作步驟, 制作這個步驟當然要有所依據(jù), ORACLE有兩個基本原則來決定如何優(yōu)化: cost-based(基于開銷的優(yōu)化)和rule-based(基于規(guī)則的優(yōu)化). 基于開銷的優(yōu)化的工作方式依靠于數(shù)據(jù)庫對SQL語句所操作的數(shù)據(jù)對象(可簡單認為就是表)的數(shù)據(jù)特征的統(tǒng)計特性進行收集和分析. 收集分析的工作由DBA來定期執(zhí)行 , 時間間隔依數(shù)據(jù)變化頻率而定, 以保持統(tǒng)計數(shù)據(jù)一定的準確性, 具體操作請參照 analyze 語句. Oracle預(yù)備在將來的版本中取消對基于開銷的優(yōu)化方案的支持, 因為這種方案需要大量的數(shù)據(jù)收集與分析工作, 且總會有一定的誤差, 這造成最終的執(zhí)行方案往往不是最優(yōu)的.
基于規(guī)則的優(yōu)化則是依據(jù)一些數(shù)據(jù)操作效率的規(guī)則進行選擇, 優(yōu)化的核心在于效率, 時間上盡可能短, 空間上盡可能少進行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對該表的一個唯一索引進行全掃描, 因為在數(shù)據(jù)庫里一個字段假如可以建立一個UNIQUE類型的索引, 那么它就與表中的記錄有一一對應(yīng)的關(guān)系. 所以對該索引進行count(*)可以保證其值等于對表進行count(*)操作. 對索引進行全掃描后的上層操作是一個集合操作, 即對找到的每個索引記錄進行計數(shù). 對這些信息的觀察主要用來確定ORACLE是否選用了SQL程序員希望ORACLE選用的索引操作. Statistics給出了執(zhí)行該SQL操作所消耗的資源的統(tǒng)計數(shù)據(jù), 信息的表達一目了然, 所有這些值都是越小越好, 以通過SQL*Net的數(shù)據(jù)吞吐量為例, 在OCI編程中使用以下技術(shù)可顯著減少網(wǎng)絡(luò)流量:通過將Commit操作與Execute操作綁定為一個操作.通過對數(shù)組進行成批數(shù)據(jù)的delete, insert, update, 通過對一個SELECT語句指定一個預(yù)取記錄數(shù). 這些統(tǒng)計數(shù)據(jù)中, 尤其需要避免的是涉及磁盤存取的操作, 因為多級存儲的操作速度是CPU >> Memory >> HD > Disc > network > disk2. 對投入運營的系統(tǒng)中PHP程序的監(jiān)控 理想的開發(fā)流程是 設(shè)計->文檔->編碼->測試->投入使用, 但實際運行的系統(tǒng)往往是由良莠不齊的程序所組成, 有些缺乏文檔, 有些可讀性差, 有些程序極為脆弱.對于這樣的既成事實, 假如系統(tǒng)中出現(xiàn)了瓶頸, 不可能一條語句一條語句地來進行測試, 只能是用一種統(tǒng)一的方法定位主要問題的所在. 由于 PHP程序中的SQL語句使用了所謂動態(tài)SQL語句, 即用戶可以在程序運行時動態(tài)生成一個SQL語句, 所以假如對靜態(tài)的PHP程序文件進行搜索(如用grep工具)可能會搜捕不到成形的完整SQL語句, 這就要求用一種動態(tài)方法來攔截實際執(zhí)行的每一個完整的SQL語句, 觀察PHP中關(guān)于ORACLE數(shù)據(jù)庫操作的函數(shù)簇, 發(fā)現(xiàn)OCIParse和Ora_Parse兩個函數(shù)是SQL語句的入口, 而將這兩個函數(shù)統(tǒng)一替換為一個用戶自定義的函數(shù)即可實現(xiàn)對SQL語句的攔截, 在筆者涉入的實際系統(tǒng)中, 是這樣解決的: 首先分析該系統(tǒng)中所有的PHP程序文件, 發(fā)現(xiàn)凡涉及ORACLE數(shù)據(jù)庫操作的都需要包含一個以*.conf結(jié)尾的配置文件, 該配置文件是數(shù)據(jù)庫的用戶名, 密碼和連接標識符的定義文件, 這些是開發(fā)初期定下的規(guī)范, 以便于對程序中共用的配置信息進行統(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è)計一個函數(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:$line/n/t$debug_sql/n"; $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ù)進行開發(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 ido ex -c ':se icg/ociparse/s/ociparse/debug_&/s/);$/,__FILE__,__LINE__&/' -c ':x!' $idone 這幾行腳本并非放之皆準, 但對于規(guī)范的php文件, 一般來說沒有問題, 筆者的系統(tǒng)中用該方法維護幾百M的PHP程序, 少有例外, 由于這是只運行一次的腳本, 所以只要根據(jù)自己具體的系統(tǒng)做適當?shù)恼{(diào)整即可, 如上, 假如對含有OCIParse的程序行的內(nèi)容不太確定, 可以用如下方法先進行查看: find /home/httpd/html -name '*.ph*' xargs grep -in ociparse > ~/list這段腳本中的ex命令稍作解釋: ex是vi編輯器的后端工具, 可以在命令行上使用一些編輯命令, 每個編輯命令以-c 選項開頭, 如上 :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語句的右括號進行替換, 將用于調(diào)試監(jiān)控的兩個參數(shù)(PHP中的宏)加上, $不是指一個真正的字符, 而是指一個特定的位置--行尾, 以避免無辜的);被替換掉. 另一個命令-c ':x!' 是將該文件存盤退出. 打出這么一套組合拳需要你對這些命令了如指掌, 假如你對某個文件沒有寫權(quán), 或出了其它岔子, 那簡直是一場災(zāi)難, 這種魔法級的指令總是高風(fēng)險的, 搞不好會走火入魔, 讓你發(fā)下毒誓有生之年不再碰它. 所以謹慎與備份總是對的.3. 對各種應(yīng)用程序中的情況進行監(jiān)控 假設(shè)一個系統(tǒng)中不僅僅有PHP程序, 還有C程序與數(shù)據(jù)庫進行連接, 那么數(shù)據(jù)庫系統(tǒng)一旦出了問題, 如資源消耗過多, 造成死鎖等, 僅憑 ps ax grep oracleORCL是看不出什么東西的, 因為這個進程是Oracle的shadow進程, 命令名字都被改了, 從/proc文件系統(tǒng)中提供的信息中也榨不出什么有用的東西了, 所以, 假如發(fā)現(xiàn)一個進程(這是ps ax的實際輸出)如下, 10406 ? R 159:10 oracleORCL (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL= 確定這個進程長時間處于running狀態(tài)的肇事者就成為一個難題, 首先, 進程的運行者是oracle, 連接者卻可能是來自本機, 來自局域網(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中運行: EXEC SQL EXECUTE BEGIN dbms_application_info.set_client_info('某應(yīng)用程序:其PID,文件名,行號'); END:END-EXEC; 運行該PRO*C程序, 執(zhí)行一條SQL語句, 并在關(guān)閉光標之前故意讓它 sleep(1000); 以騰出足夠多的時間來觀察v$session中的client_info字段, [bash$] sqlplus sys/change_on_install@orclSQL> 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語句進行更精確的跟蹤定位.
至此, 可以將數(shù)據(jù)庫服務(wù)器下某一oracle的shadow進程與具體哪一個應(yīng)用程序,甚至是哪一個源文件, 哪一行的信息以及所執(zhí)行的SQL語句等一一對應(yīng)起來, 有了這根主線, 其它問題的分析就可步步深入, 耗了多少時間, 讀了多少個數(shù)據(jù)塊 ,進行了多少次排序, 等等問題, 都可通過v$...視圖收集到足夠的信息. 本文重點不在于此, 僅作拋磚, 就此打住.
