前言 : 在單一的應用環境或業務相對簡單的系統下,系統性能問題 , 瓶頸所在往往是不言自明,解決問題的前提 -- 定位問題是比較輕易解決的 , 但在一個復雜的應用環境下,各應用系統對系統資源往往是一種共享和競爭的關系,而且應用系統之間也可能存在著共生或制約的關系,資源利益的均衡往往是此消彼長,而這種環境下的應用系統一旦出現資源競爭,系統的瓶頸往往難以斷定,甚至會發生不同應用設計人員之間互相推諉責任的扯皮現象 , 本文僅就此問題對 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 版本如下 : <? # 作者 : slimzhao@21cn.com # 當前維護人 : slimzhao@21cn.com # 創建日期 : 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 processedExecution 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:$linent$debug_sqln'; $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 icg/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$... 視圖收集到足夠的信息。本文重點不在于此,僅作拋磚,就此打住。