老熊的三分地-Oracle、UNIX

QQ群里有位兄弟提出一个很有意思的问题，在一个db_cache_size为1.6G，几乎没有什么活动的数据库（版本为9.2.0.1)里面，一个简单的查询SELECT * FROM T，反复执行，这个查询的物理读始终很高。被查询的表不大，完全可以容纳在cache里面。根据直觉，频繁被读取的块，是应该cache在内存中的，不应该会有物理读，并且当时数据库没有其他的活动，cache中的块也不会被挤出去。

这个现象是违反直觉的，但注意，直觉的东西不一定不是正确的东西。那么为什么那个简单的SQL，在反复执行的情况下，怎么会有那么高的物理读呢？

这还得从Oracle的buffer cache管理说起。一个oracle进程在做全表扫描时，buffer会放置到LRU-AUX链表的尾端。如果在读块时，如果已经没有free buffer，那么进程就会查找可以被age out的块，这个查找过程是从LRU-AUX链表的尾端开始的。也就是说LRU-AUX链表尾端的buffer，总是第一个被替换的。

基于Oracle的这种LRU算法，我们假设，现在系统中已经没有free buffer，也就是buffer cache已经用完。实际上这种情况是最常见的，必竟现在的库相对于db_cache_size来说，总是大很多倍，buffer cache很快就会被填满。那么，在做全表扫描时，第1次多块读(multiblock read)的buffer会放置到LRU-AUX链表的尾端（不管这个buffer是从哪里得到的），第2次多块读时，需要buffer时，oracle也会从LRU-AUX链表的的尾端开始进行查找可以被替换的buffer，而刚好LRU-AUX链表的尾端的buffer正是上一次多块读时的buffer。也就是说前一次读到的块，很快就会被后面读的块替换掉了。

因此，不难理解，为什么在数据库活动很少时，反复扫描同一个表（这里不是同时扫描），其物理读仍然很高。并不是我们直觉的那样，这个表已经很“热”，应该全部在内存中命中。

下面我们通过一个测试来验证我们的推断。

测试环境：
Oracle 10.2.0.3 32Bit for Linux
我们设置如下的参数，以便让buffer cache的大小在我们控制的范围内：

SQL> alter system set shared_pool_size=100m;

System altered.

SQL> alter system set db_cache_size=30m;

System altered.

SQL> alter system set sga_target=200m;

Read the rest of this entry »

什么是global statistics？

大家都知道，dbms_stats是Oracle 9i及后续的版本中用于收集优化器统计信息的包，虽然analyze命令也一直可用，但是现在已经不推荐使用analyze来收集统计信息，而是使用dbms_stats。二者之间一个很大的不同，也是dbms_stats一个很突出的优点就是能够正确收集分区表的统计信息，换言之就是global statistics。而analyze命令只会收集最低层次对象的统计信息，然后推导和汇总出高一级的统计信息，如分区表只会收集分区信息，然后再汇总所有分区的统计信息，得到表一级的统计信息。

那什么是global statistics？简单地说global statistics就是指直接从对象本身这一级收集到的统计信息，而不是从下一级对象“推导”或“汇总”出来的统计信息。比如，表的global statistics指直接通过表收集到的统计信息，而不是从分区收集的统计信息进行汇总或推导出的。同样，分区的global statistics是指直接通过分区收集到的统计信息，而不是从子分区收集的统计信息进行汇总或推导出的。global statistics对优化器来说是非常重要的，一个SQL，除非其查询条件限定了数据只在部分分区上，否则在大多数情况下需要global statistics才能得到正确的执行计划。

有的统计值可以从对象的下一级对象进行汇总后得到，比如表的总行数，可以通过各分区的行数相加得到。但有的统计值则不能从下一级对象得到，比如列上的唯一值数量(distinct value）以及密度值（density）。

怎样收集global statistics？

global statistics只能通过dbms_stats包来收集。注意，用analyze命令得到的统计信息，虽然也会有表一级的统计值，但是，那些值是从分区或子分区推导和汇总出来的，是不精确的。后面的实验中，将会验证这一点。

使用dbms_stats收集统计信息时，参数granularity（比如gather_table_stats过程） 指定了哪个级别上的统计信息会被收集：

• Global -- Table （表）
• Partition -- Partition （分区）
• SubPartition -- SubPartition （子分区）
• Default -- Table + Partition （表+分区）
• All -- Table + Partition + Subpartit （表+分区+子分区）

比如，要在一个做为子分区的表上，以’ALL’级别收集统计信息时，会收集”表+分区+子分区“上的统计信息，相当于需要执行下面的三类SQL：

• select …. from table
• select …. from table (partition P1) -- 在每个分区上都重复一次
• select …. from table (subpartition SP1) -- 在每个子分区上重复一次

可以看到，dbms_stats需要比analyze命令更多的时间来进行统计信息的收集。对于有子分区的表，dbms_stats至少要多花三倍的时间。
Read the rest of this entry »

故障发生时间：12月6日早上
系统环境：HP Superdome系列，128G内存，64CPU,Oracle 9.2.0.8
故障现象：CPU占用将近100%，运行队列达到60-80，应用反应速度很慢。

这是一个省级电信的核心系统。

在用户反映速度很慢后，在主机上检查发现CPU很高，将近100%，而运行队列达到了60-80。检查Oracle，发现很多的会话在等待latch free，latch#为98

SQL> select * from v$latchname where latch#=98;

    LATCH# NAME
---------- ----------------------------------------------------------------
        98 cache buffers chains

检查正在等待latch free的会话正在执行的SQL，大部分都在执行类似于下面的SQL：

SELECT SUM(cnt),
       to_char(nvl(SUM(nvl(amount, 0)) / 100, 0), ’FM9999999999990.90′) amount
  FROM (select count(payment_id) cnt, SUM(amount) amount
          from payment
         where staff_id = 592965
           and CREATED_DATE >= trunc(sysdate)
           and state = ’C0C’
           and operation_type in (’5KA’, ’5KB’, ’5KC’, ’5KP’))

看起来这个SQL并不复杂，查看其执行计划：
Read the rest of this entry »

本文源起小荷博客中的文章“一次cpu的user比例过高的调优”，文章中提到的占用CPU比较高的SQL语句，消耗的CPU比逻辑读还要高的SQL还要多。这两条SQL是：

SELECT NETWORKTYPEID FROM PROBLEM_TAB WHERE PREFIXNUM = SUBSTR(:B1 , 1, 7)
select provinceid into :b0 from PROBLEM_TAB where PREFIXNUM=substr(:b1,1,7)

我关注这个的原因是，为啥这两个SQL比较消耗CPU，比逻辑读更高的SQL消耗得还多。这里让我感兴趣的是，这里使用了函数，函数的参数使用了绑定变量。那么，有一个问题，那就是这个函数会被调用多少次？1次还是与表中所有行相同的次数？我的推测是对于结果确定性（deterministic）的函数，如果传入参数有绑定变量，那么会是被引用（如值的比较）的次数，如果传入的参数是确定的值，那么应该是1次。这个推测是不是正确的呢，让我们来做一些测试。

首先创建测试用的函数：

create or replace package pkg_test is

g_cnt number;

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic;

end pkg_test;

create or replace package body pkg_test is

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic is
begin
g_cnt:=g_cnt+1;
return substr(iv_str, iv_pos, iv_len);
end;
end pkg_test;

Read the rest of this entry »

经常遇到客户和其他一些Oracle开发与维护人员，问我为啥使用了RAC，没有感受到业务系统有明显的性能提升，有时反而觉得性能有所下降。这种认为RAC一定能够提高性能的想法，有着广泛的“群众基础”。可以说，使用RAC来提高性能是一种存在于广大ORACLE数据库使用者之间的误解。

这里我不想过多于技术上去解答这个问题，而是从下面这个类比来说明这个问题：

这里我们要谈论的是大部分的业务系统类型，事务处理型，也就是OLTP。虽然很多OLTP类型的系统还兼有生成一些报表和统计数据的功能，但那只是一部分小的功能，主要还是事务处理。

大家都去过银行，假设一个银行营业厅有6个业务窗口，来这个营业厅办理业务的客户一般为3至5个人，最多6个人。由于每个人办理业务的时间，是跟他（她）的业务类型有关的，比如取款2分钟，存款2分钟，开户要5分钟等等，不会以窗口数的增多而减少时间。以这个例子来说，6个窗口已经足够了，因为6个窗口数大于同时办理业务的客户数，而一个客户只会在一个窗口办理业务，就算再多的业务处理窗口，也不会对每个客户办理业务有速度上的提升。

现在假设银行的业务有了很大的发呢，银行营业厅里面的客户比较多了，同时来办理业务的常常超过10人，这个时候就是银行营业厅的窗口不够了（资源不足），客户存在了排队，严重影响了客户办理业务的效率。而营业厅由于受面积的限制，不能增加窗口了（对于机器来说，不能扩容了），这个时候银行在附近又开了一个新的营业厅（增加了一个新的结点），那样部分客户分流到了新的营业厅，这样消除了客户的排队，客户又能够高效率地在银行办理业务了。

使用RAC类似于上面提到的银行，如果业务系统能够在单台机器上跑，这个时候由于资源足够，增加新的结点不会带来性能上的提升，而如果随着业务的发展，机器资源受限，不能为更多的用户服务，这个时候增加新的结点，能够使业务系统能够为更多的用户服务。

然而在现实生活中，很多业务系统并没有为RAC进行一些优化，同时RAC的结点之间由于数据同步的代价比较高，因而使用RAC后往往感受到业务系统并没有更快，有时感觉反而更慢。

RAC的作用更体现于高可用性、水平可扩展性，其次才是某些条件下的性能提升（比如针对于某些DSS系统）。

下面一条SQL能够使用索引吗？

select object_id,object_name,object_type from t1 where object_id is null;

有人会说，索引不存储null值，所以这个SQL不会使用索引；也有人会说，能够使用索引啊，只要在object_id列上建位图索引就可以使用索引了，另外对于CLUSTER表的KEY列，null值也可以存储在索引中（也就是能够进行索引）。

本文要探讨的是，null值不能进行索引的真正含义：
实际上对于null值，除了位图索引、CLUSTER表的KEY列，也是有可能索引的。那就是复合索引，也就是多列索引。对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。
那么对于上面的SQL语句，我们就可以想办法让它走索引。下面举例说明：

SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- --------------
OWNER VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> create index t1_idx on t1(object_id,object_name);
SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;

表已分析。
SQL> select object_id,object_name,object_type from t1 where object_id is null;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
XTY DATABASE LINK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=38)
1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘T1′ (Cost=3 Card=1 Bytes
=38)

2 1 INDEX (RANGE SCAN) OF ‘T1_IDX’ (NON-UNIQUE) (Cost=2 Card
=1)

我们可以看到，这个SQL语句走了我们建的那个索引T1_IDX。
然而，如果我们把object_name列改为允许NULL值，那么情况就发生了变化：

SQL>alter table t1 modify (object_name null);
SQL> select object_id,object_name,object_type from t1 where object_id is null;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
XTY DATABASE LINK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=128 Card=1 Bytes=38)
1 0 TABLE ACCESS (FULL) OF ‘T1′ (Cost=128 Card=1 Bytes=38)

把object_name列改为允许NULL值后，object_id和object_name列 有可能同时为NULL值，这个时候就存在着不被索引的行（就是有可能存在object_id值为null的行没有被索引），这样就只能走全表扫描了。

我们也有一种更简便的方法，不需要与其他列建立复合索引，而是与一个常量值建立复合索引：

SQL> create index t1_idx on t1(object_id,0);

SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;

SQL> explain plan for select object_id,object_name,object_type from t1 where object_id is null;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    81 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |    81 |     3 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX      |     1 |       |     2 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

2 - access(”T1″.”OBJECT_ID” IS NULL)

Note: cpu costing is off

引申开来，如果有类似于col_name is null这样的条件的SQL语句，希望能够使用索引，那么将col_name与其他NOT NULL的列或一个常量值建一个复合索引，也是一个可能的选择。