前言:
在單一的應用環境或業務相對簡單的系統下, 系統性能問題, 瓶頸所在往往是 不言自明, 解決問題的前提--定位問題是比較容易解決的, 但在一個復雜的應用 環境下, 各應用系統對系統資源往往是一種共享和競爭的關系, 而且應用系統之 間也可能存在著共生或制約的關系, 資源利益的均衡往往是此消彼長, 而這種環境 下的應用系統一旦出現資源競爭, 系統的瓶頸往往難以斷定, 甚至會發生不同應用 設計人員之間互相推诿責任的扯皮現象, 本文僅就此問題對Linux平台下各應用系 統對ORACLE數據庫的使用情況作一探討, ORACLE數據庫的TUNING不是一個可以一 言以蔽的主題, 本文無意概全, 內容僅涉及問題的定位及各應用對數據庫 資源的共享與競爭問題.
本文試驗及問題取證的環境:
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
為方便問題的討論, 應用系統已做簡化, 競爭方僅包括一個Pro*C的daemon程 序作為C/S模式的服務端, 和由Apache+PHP所支持的WEB網站業務.
1. 單個SQL語句的處理
首先, 最簡單的情況莫過於單個SQL語句的分析, SQL語句的優化也是數據庫 優化的一個最直接最立竿見影的因素. SQL語句的性能監控從監控工具來說 大致可分為由高級語言提供和由ORACLE本身提供, 高級語言以典型的應用C 語言和WEB開發語言PHP為例, C語言中可以用gettimeofday函數來在某一數據庫 操作之前和之後分別獲取一個時間值, 將兩個時間值之差做為衡量該數據庫操作 的效率, 在PHP中, 也可以用gettimeofday, 操作方法當然與C語言中有所不同. 當然, PHP中也有其它一些函數可以達到同樣的時間精度, 關於時間精度的考慮, 不能簡單以大小衡量微秒級的時間數值, 因為時鐘中斷的時間間隔從根本上決定了 時間計算所能達到的精度, 此外, 操作系統本身對進程的時間片分配, 及進程切 換的開銷等因素也在一定程度上影響時間數據的意義. 所以, 以下時間的計算最 理想的情況是對同一操作在盡可能避免緩存的情況下進行多次的循環操作, 取總 的時間值加以平均, 從而得到比較接近真實情況的時間值.
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 , 該操作消耗時間:$timestr<br>"; ?> 其中how_long函數的PHP版本如下: <? #作者: [email protected] #當前維護人: [email protected] #創建日期: 2001.12.04 00:18:00 #目的, 在一個操作之前或之後調用該函數的不同版本, 將得到一個記載了該操作 #耗費時間的字符串, 該函數本身的開銷不計入其中. define("TV_START", 0); define("TV_END", 1); function how_long($operation, &$str) #返回值: 0--成功, -1--傳遞了非法的參數. { 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; } } ?> ==========================================================
上面的數據庫操作開銷的計算僅限於對時間消耗的計算, 對同時使用同一數據 庫的其它應用軟件的影響, 對磁盤操作的頻繁程度, 數據庫操作所采取的具體策略 等等因素, 都未考慮在內, 高級語言也不可能提供這樣的參考數據. 而數據庫本身提供的監測手段彌補了這一不足. 最簡單的操作控制台:sqlplus
SQL> set timing on
將為每次執行的數據庫操作進行計時, 精度為1/100秒, 筆者對該功能的使用中發 現其時間的計算也有一定的偏差. 而且時間偏差很大, 嚴格說來, 已不屬於誤差 的范圍, 該歸錯誤了, 下面是一個例子中得到的數據:
[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 從系統的時間差來看, 為4秒左右, 但ORACLE卻報告了6.16秒!
如果說ORACLE工具在時間計算上太差強人意的話, 在SQL語句的執行方案上可算是 對SQL語句如何執行的最權威的诠釋了. 解讀這樣的信息需要對ORACLE內部對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 processed Execution Plan下的信息顯示ORACLE制定了一個什麼樣的計劃來完成SQL操作 的,SQL語言是一種4GL語言, 其特點是告訴系統做什麼, 而不提供如何做的信息. 當然, 最終的具體工作總得有人做的, 只是由數據庫自動制定而不是程序員人為指定 一個具體的操作步驟, 制作這個步驟當然要有所依據, ORACLE有兩個基本原則來 決定如何優化: cost-based(基於開銷的優化)和rule-based(基於規則的優化). 基於開銷的優化的工作方式依賴於數據庫對SQL語句所操作的數據對象(可簡單認 為就是表)的數據特征的統計特性進行收集和分析. 收集分析的工作由DBA來定期執行 , 時間間隔依數據變化頻率而定, 以保持統計數據一定的准確性, 具體操作請參照 analyze 語句. Oracle准備在將來的版本中取消對基於開銷的優化方案的支持, 因 為這種方案需要大量的數據收集與分析工作, 且總會有一定的誤差, 這造成最終 的執行方案往往不是最優的.
基於規則的優化則是依據一些數據操作效率的規則 進行選擇, 優化的核心在於效率, 時間上盡可能短, 空間上盡可能少進行IO 操作. 兩種優化方案都絕非十全十美, ORACLE雖將其稱為優化方案, 筆者的 觀察結果表明, ORACLE制定出一個不是最優或錯誤的執行方案也是完全可能的. 以上為例, Oracle的優化策略是Choose, 所謂Choose就是cost-based或rule-based , 讓ORACLE自己選擇, 可以通過數據庫啟動初始化文件initXXX.ora文件中的 optimizer_mode參數來指定.
言歸正傳, 上面的具體策略是Oracle對該表的一個唯一索引 進行全掃描, 因為在數據庫裡一個字段如果可以建立一個UNIQUE類型的索引, 那麼 它就與表中的記錄有一一對應的關系. 所以對該索引進行count(*)可以保證其值 等於對表進行count(*)操作. 對索引進行全掃描後的上層操作是一個集合操作, 即對找到的每個索引記錄進行計數. 對這些信息的觀察主要用來確定ORACLE是否選用了SQL程序員希望ORACLE選用的 索引操作.
Statistics給出了執行該SQL操作所消耗的資源的統計數據, 信息的表達一目 了然, 所有這些值都是越小越好, 以通過SQL*Net的數據吞吐量為例, 在OCI編程中使 用以下技術可顯著減少網絡流量:通過將Commit操作與Execute操作綁定為一個操 作.通過對數組進行成批數據的delete, insert, update, 通過對一個SELECT語句 指定一個預取記錄數. 這些統計數據中, 尤其需要避免的是涉及磁盤存取的操作, 因為多級存儲的操作速度是CPU >> Memory >> HD > Disc > network > disk
2. 對投入運營的系統中PHP程序的監控
理想的開發流程是 設計->文檔->編碼->測試->投入使用, 但實 際運行的系統往往是由良莠不齊的程序所組成, 有些缺乏文檔, 有些可讀性差, 有些程序極為脆弱.對於這樣的既成事實, 如果系統中出現了瓶頸, 不可能一條語 句一條語句地來進行測試, 只能是用一種統一的方法定位主要問題的所在. 由於 PHP程序中的SQL語句使用了所謂動態SQL語句, 即用戶可以在程序運行時動態生成 一個SQL語句, 所以如果對靜態的PHP程序文件進行搜索(如用grep工具)可能會搜 捕不到成形的完整SQL語句, 這就要求用一種動態方法來攔截實際執行的每一個完 整的SQL語句, 觀察PHP中關於ORACLE數據庫操作的函數簇, 發現OCIParse和Ora_Parse兩個函數 是SQL語句的入口, 而將這兩個函數統一替換為一個用戶自定義的函數即可實現 對SQL語句的攔截, 在筆者涉入的實際系統中, 是這樣解決的: 首先分析該系統中所有的PHP程序文件, 發現凡涉及ORACLE數據庫操作的都需要 包含一個以*.conf結尾的配置文件, 該配置文件是數據庫的用戶名, 密碼和連接 標識符的定義文件, 這些是開發初期定下的規范, 以便於對程序中共用的配置信息 進行統一的管理, 以下是一個oracle.conf
<? $oracle_user="oracle_user"; $oracle_password="oracle_password"; $oracle_dbid = "oracle_dbid"; ?>
在涉及數據庫操作的PHP程序中, 總有一行語句以引入該配置文件:
include("<path_to_file>/oracle.conf"); 設計一個函數如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); } ?> ==========================================================
然後, 統一將所有PHP程序中的OCIParse函數替換為debug_OCIParse函數, 並 要求PHP程序員以後使用debug_OCIParse函數進行開發, 如下
將
$stmt = OCIParse($conn, $sql); 替換為:
$stmt = debug_OCIParse($conn, $sql, __FILE__, __LINE__); 這個工作可由系統管理員統一做一次, 以後就要要求PHP程序員形成規范. 例, 可用如下腳本
find /home/httpd/html -name '*.ph*' | xargs -n1 | while read i do ex -c ':se ic|g/ociparse/s/ociparse/debug_&/|s/);$/,__FILE__,__LINE__&/' -c ':x!' $i done
這幾行腳本並非放之皆准, 但對於規范的php文件, 一般來說沒有問題, 筆者 的系統中用該方法維護幾百M的PHP程序, 少有例外, 由於這是只運行一次的腳本, 所以只要根據自己具體的系統做適當的調整即可, 如上, 如果對含有OCIParse的 程序行的內容不太確定, 可以用如下方法先進行查看:
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的行, 對 這些行執行如下編輯命令.
s/ociparse/debug_&/, s意為substitute, 將ociparse替換為debug_&, 這 其中&代表前面找到的匹配字符串, 由於是忽略大小寫的, 所以用&來保留前面 找到的不管是大小寫如何混合的字符串的原型. 這樣, ociparse就會被替換為 debug_ociparse, 而OCIParse將會被替換為debug_OCIParse.
接下來的|s/);$/,__FILE__,__LINE__&/是將ociparse語句的右括號進行替換, 將用於調試監控的兩個參數(PHP中的宏)加上, $不是指一個真正的字符, 而是指一 個特定的位置--行尾, 以避免無辜的);被替換掉.
另一個命令-c ':x!' 是將該文件存盤退出.
打出這麼一套組合拳需要你對這些命令了如指掌, 如果你對某個文件沒有寫 權, 或出了其它岔子, 那簡直是一場災難, 這種魔法級的指令總是高風險的, 搞不好會走火入魔, 讓你發下毒誓有生之年不再碰它. 所以謹慎與備份總是對的.
3. 對各種應用程序中的情況進行監控
假設一個系統中不僅僅有PHP程序, 還有C程序與數據庫進行連接, 那麼數據庫 系統一旦出了問題, 如資源消耗過多, 造成死鎖等, 僅憑
ps ax | grep oracleORCL 是看不出什麼東西的, 因為這個進程是Oracle的shadow進程, 命令名字都被改了, 從/proc文件系統中提供的信息中也搾不出什麼有用的東西了, 所以, 如果發現 一個進程(這是ps ax的實際輸出)如下,
10406 ? R 159:10 oracleORCL (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=
確定這個進程長時間處於running狀態的肇事者就成為一個難題, 首先, 進程 的運行者是oracle, 連接者卻可能是來自本機, 來自局域網絡, 來自internet的 nobody用戶, 所以冤無頭, 債無主.
查看v$session, v$process, v$..., 也沒有關於客戶端的足夠信息. 可以用 來縮小范圍的是SQL語句, 但仍不足以構成充分的說服力讓某一應用的開發人員 確信是自己的程序出了問題. 觀察字段豐富的v$session視圖, 裡面有一個十分 誘人的client_info字段, 顧名思義, 不能不讓人想入非非: 一定是關於ORACLE 客戶端的信息的, 可惜它一般是NULL值:-(, 筆者從ORACLE文檔中終於發現了
dbms_application_info.set_client_info(string); 是用來設置連接ORACLE的客戶端信息的一個包, 拿來PRO*C中運行:
EXEC SQL EXECUTE BEGIN dbms_application_info.set_client_info('某應用程序:其PID,文件名,行號'); END: END-EXEC; 運行該PRO*C程序, 執行一條SQL語句, 並在關閉光標之前故意讓它
sleep(1000); 以騰出足夠多的時間來觀察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); 正是!!! 你剛才設定的'某應用程序:其PID,文件名,行號'信息, 別嫌短, 這個 client_info字段是64個字節. 夠了.
看能不能讓這寶貴功能施於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語句進行更精確的跟蹤定位.
至此, 可以將數據庫服務器下某一oracle的shadow進程與具體哪一個應用程 序,甚至是哪一個源文件, 哪一行的信息以及所執行的SQL語句等一一對應起來, 有了這根主線, 其它問題的分析就可步步深入, 耗了多少時間, 讀了多少個數據塊 ,進行了多少次排序, 等等問題, 都可通過v$...視圖收集到足夠的信息. 本文重點 不在於此, 僅作拋磚, 就此打住.