前言:
在單一的應用環境或業務相對簡單的系統下, 系統性能問題, 瓶頸所在往往是不言自明, 解決問題的前提--定位問題是比較輕易解決的, 但在一個復雜的應用環境下, 各應用系統對系統資源往往是一種共享和競爭的關系, 而且應用系統之間也可能存在著共生或制約的關系, 資源利益的均衡往往是此消彼長, 而這種環境下的應用系統一旦出現資源競爭, 系統的瓶頸往往難以斷定, 甚至會發生不同應用設計人員之間互相推諉責任的扯皮現象, 本文僅就此問題對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 "/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
";
?>
其中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 processed
Execution Plan下的信息顯示ORACLE制定了一個什么樣的計劃來完成SQL操作的,SQL語言是一種4GL語言, 其特點是告訴系統做什么, 而不提供如何做的信息. 當然, 最終的具體工作總得有人做的, 只是由數據庫自動制定而不是程序員人為指定一個具體的操作步驟, 制作這個步驟當然要有所依據, ORACLE有兩個基本原則來決定如何優化: cost-based(基于開銷的優化)和rule-based(基于規則的優化). 基于開銷的優化的工作方式依靠于數據庫對SQL語句所操作的數據對象(可簡單認為就是表)的數據特征的統計特性進行收集和分析. 收集分析的工作由DBA來定期執行 , 時間間隔依數據變化頻率而定, 以保持統計數據一定的準確性, 具體操作請參照 analyze 語句. Oracle預備在將來的版本中取消對基于開銷的優化方案的支持, 因為這種方案需要大量的數據收集與分析工作, 且總會有一定的誤差, 這造成最終的執行方案往往不是最優的.
基于規則的優化則是依據一些數據操作效率的規則進行選擇, 優化的核心在于效率, 時間上盡可能短, 空間上盡可能少進行IO 操作. 兩種優化方案都絕非十全十美, ORACLE雖將其稱為優化方案, 筆者的觀察結果表明, ORACLE制定出一個不是最優或錯誤的執行方案也是完全可能的. 以上為例, Oracle的優化策略
