复杂应用环境Oracle性能监控

日期：2015/6/28来源: IT猫扑网

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