| | |
| | |
前言:
在单一的应用环境或业务相对简单的系统下, 系统性能问题, 瓶颈所在往往是不言自
明, 解决问题的前提--定位问题是比较容易解决的, 但在一个复杂的应用环境下, 各应用系统
对系统资源往往是一种共享和竞争的关系, 而且应用系统之间也可能存在着共生或制约的关系
, 资源利益的均衡往往是此消彼长, 而这种环境下的应用系统一旦出现资源竞争, 系统的瓶颈
往往难以断定, 甚至会发生不同应用设计人员之间互相推诿责任的扯皮现象, 本文仅就此问题
对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
将为每次执行的数据库操作进行计时, 精度为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 操作的过程有一定了
解, 下面是该功能的一样典型示例:
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文件中的
言归正传, 上面的具体策略是Oracle对该表的一个唯一索引进行全扫描, 因为在数据
库里一个字段如果可以建立一个UNIQUE类型的索引, 那么它就与表中的记录有一一对应的关系
. 所以对该索引进行count(*)可以保证其值等于对表进行count(*)操作. 对索引进行全扫描后
的上层操作是一个集合操作, 即对找到的每个索引记录进行计数. 对这些信息的观察主要用来
确定ORACLE是否选用了SQL程序员希望ORACLE选用的索引操作.
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("/oracle.conf");
设计一个函数如debug_OCIParse如下, 以替换OCIParse, 并将该文件放入一个叫
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 $debug_sql ";
$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
done
这几行脚本并非放之皆准, 但对于规范的php文件, 一般来说没有问题, 笔者的系统
中用该方法维护几百M的PHP程序, 少有例外, 由于这是只运行一次的脚本, 所以只要根据自己
具体的系统做适当的调整即可, 如上, 如果对含有OCIParse的程序行的内容不太确定, 可以用
如下方法先进行查看:
这段脚本中的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将会被替
接下来的|s/);$/,__FILE__,__LINE__&/是将ociparse语句的右括号进行替换, 将用
于调试监控的两个参数(PHP中的宏)加上, $不是指一个真正的字符, 而是指一个特定的位置--
另一个命令-c ':x!' 是将该文件存盘退出.
打出这么一套组合拳需要你对这些命令了如指掌, 如果你对某个文件没有写权, 或出
了其它岔子, 那简直是一场灾难, 这种魔法级的指令总是高风险的, 搞不好会走火入魔, 让你
3. 对各种应用程序中的情况进行监控
假设一个系统中不仅仅有PHP程序, 还有C程序与数据库进行连接, 那么数据库系统
一旦出了问题, 如资源消耗过多, 造成死锁等, 仅凭
ps ax | grep oracleORCL
是看不出什么东西的, 因为这个进程是Oracle的shadow进程, 命令名字都被改了, 从
/proc文件系统中提供的信息中也榨不出什么有用的东西了, 所以, 如果发现一个进程(这是
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值:-(,
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
> and a.sql_hash_value=b.hash_value and a.paddr=c.addr);
正是!!! 你刚才设定的'某应用程序:其PID,文件名,行号'信息, 别嫌短, 这个
看能不能让这宝贵功能施于PHP:
$conn = OCILogon("username", "password", "dblink");
$stmt_client = OCIParse($conn, "call dbms_application_in
fo.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中的
至此, 可以将数据库服务器下某一oracle的shadow进程与具体哪一个应用程序,甚至
是哪一个源文件, 哪一行的信息以及所执行的SQL语句等一一对应起来, 有了这根主线, 其它
问题的分析就可步步深入, 耗了多少时间, 读了多少个数据块 ,进行了多少次排序, 等等问题
, 都可通过v$...视图收集到足够的信息. 本文重点不在于此, 仅作抛砖, 就此打住.
| |
|
|
| |
| |
|
