Oracle中资源的共享与竞争及问题定位

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