前言:
在单一的应用环境或业务相对简单的系统下, 系统性能问题, 瓶颈所在往往是 不言自明, 解决问题的前提--定位问题是比较容易解决的, 但在一个复杂的应用 环境下, 各应用系统对系统资源往往是一种共享和竞争的关系, 而且应用系统之 间也可能存在着共生或制约的关系, 资源利益的均衡往往是此消彼长, 而这种环境 下的应用系统一旦出现资源竞争, 系统的瓶颈往往难以断定, 甚至会发生不同应用 设计人员之间互相推诿责任的扯皮现象, 本文仅就此问题对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$...视图收集到足够的信息. 本文重点 不在于此, 仅作抛砖, 就此打住.