监控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 "/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