熊军(老熊)
云和恩墨西区总经理
Oracle ACED,ACOUG核心会员
这个案例发生在某天早上,运行在配置为128GB内存、64CPU的HP Superdome上的系统出现CPU占用将近100%,运行队列达到60~80,应用反应速度很慢的异常情况。
在用户反映速度很慢后,检查Oracle,发现很多的会话在等待latch free,latch#为98:
SQL> select * fromv$latchname where latch#=98; LATCH# NAME ---------- ---------------------------------------------------------------- 98 cache buffers chains
由于本章重点描述的是索引,关于“cache buffers chains latch”的等待,此处不做过多说明,这个latch的等待,通常情况下表明存在热点块,一般都是由于没有正确使用索引、SQL所使用的索引选择率不高引起。检查正在等待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 TABLE_A
where staff_id = 12345
and CREATED_DATE >= trunc(sysdate)
and state = ’C0C’
and operation_type in (’5KA’,’5KB’, ’5KC’, ’5KP’))
看起来这个SQL并不复杂,查看其执行计划:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |Pstart |Pstop |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 125K | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | |
| 2 | VIEW | | 1 | 26 | 125K | | |
| 3 | SORT AGGREGATE | | 1 | 30 | | | |
|* 4 | TABLEACCESS BY GLOBAL INDEX ROWID |TABLE_ | 19675 | 576K | 125K | ROWID |ROW L |
|* 5 | INDEX RANGE SCAN | IDX_A_3 | 1062K | | 3919 | | |
-------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
4 - filter(”TABLE_A”.”STAFF_ID”=12345 AND”TABLE_A”.”STATE”=’C0C’ AND
(”TABLE_A”.”OPERATION_TYPE”=’5KA’ OR”TABLE_A”.”OPERATION_TYPE”=’5KB’
OR ”TABLE_A”.”OPERATION_TYPE”=’5KC’ OR”TABLE_A”.”OPERATION_TYPE”=’5KP’))
5 -access(”TABLE_A”.”CREATED_DATE”>=TRUNC(SYSDATE@!))
Note: cpu costing is off
从中可以看到,Oracle评估出,利用索引扫描返回的行数高达100万行,可想而知,由于选择率过高,产生了大量的buffers chains latch争用。
检查PAYMENT表的索引:
SQL> select index_name,index_type from user_indexeswhere table_name=’TABLE_A’;
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_A_1 NORMAL
IDX_A_2 NORMAL
IDX_A_3 NORMAL
IDX_A_4 NORMAL
IDX_A_5 NORMAL
IDX_A_6 NORMAL
IDX_A_7 NORMAL
IDX_A_8 NORMAL
PK_TABLE_A NORMAL
SQL> selectindex_name,column_name,column_position from user_ind_columns where table_name=’TABLE_A’order by 1,3;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
IDX_A_1 SERIAL_NBR 1
IDX_A_2 A_ID 1
IDX_A_3 CREATED_DATE 1
IDX_A_4 METHOD 1
IDX_A_5 P_METHOD 1
IDX_A_6 S_ID 1
IDX_A_7 STAFF_ID 1
IDX_A_7 STATE_DATE 2
PK_TABLE_A TABLE_A_ID 1
以上输出是对真正的输出信息加工处理后的结果。
由上可知,执行计划中使用的索引IDX_A_3是在CREATED_DATE列上建立的单列索引。
这个SQL在之前没有出现过类似问题,那问题在哪里?
原来在当天凌晨做了一个大数量的业务操作,在TABLE_A中插入了大量的数据,因此用CREATED_DATE>=TRUNCATE(SYSDATE)这个条件时会从索引扫描中返回大量的行。而实际上回表之后用STAFF_ID和OPERATION_TYPE列上的条件过滤后的行数仅约2万行(这是评估的数据,实际的数据远远比这个少)。很显然,如果我们建立一个复合索引,那么索引扫描返回的行数将大大减少,这样也就大大减少了在表上访问并进行过滤的数据量。
以STAFF_ID列为前导列与CREATE_DATE列一起建立复合索引后,系统马上恢复正常。不过,有人会问,为什么要使用STAFF_ID列做索引的前导列,而不用CREATE_DATE列做前导列?很多文档不是介绍说,复合索引要把选择性最好的列放在最前面吗?要回答这个问题,得首先了解索引的基本原理,包括Oracle数据库对索引是如何存储的、是怎样通过索引来检索索引数据的。
B Tree索引的结构及特点
Oracle数据库中索引的存储结构使用的是B Tree的一种变体,称为B*Tree(B Star Tree),在数据库中存储数据以块为单位,索引也不例外,数据库中构建索引形成的BTree,与教科书中提到的B Tree有很明显的差异。下面以图11-1为例,介绍Oracle数据库中B Tree索引的结构及其特点。
图11-1 Oracle数据库中B Tree索引的结构及其特点示意图
图11-1是一个简单的B Tree索引示意图,图中虚线部分表示省略的部分。在介绍B Tree索引的特点之前,我们先来回顾一下数据结构中树的几个术语。
节点M的深度:从树根节点到节点M的最短路径长度。图中根节点Root的深度为0,节点L1-1的深度为1,节点L0-1的深度为2。 节点M的层数:节点M的层数与其深度,实际上是相同的。 树的高度:树的深度值最大的那个节点,其深度+1即为树的高度。比如图中树的高度为3。
Oracle数据库的索引,有以下几个特点:
--创建一个只有2列、4行的表:
SQL> create tablet1 as select object_id,object_name from dba_objects where rownum<=4; Table created.
--创建一个非唯一索引:
SQL> create indext1_idx1 on t1(object_id);
Index created.
SQL> set autot onstat
SQL> colobject_name for a30
--全表扫描(Table Full Scan):
SQL> select /*+full(t1) */ * from t1 where object_id=28;
OBJECT_ID OBJECT_NAME ---------- ------------------------------ 28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--索引范围扫描(Index Range Scan):
SQL> select /*+index(t1) */ * from t1 where object_id=28;
OBJECT_ID OBJECT_NAME ---------- ------------------------------ 28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotoff
--删除索引,重新创建一个唯一索引:
SQL> drop indext1_idx1;
Index dropped.
SQL> createunique index t1_idx1 on t1(object_id);
Index created.
--索引唯一扫描(Index Unique Scan):
SQL> set autot onstat
SQL> select /*+ index(t1) */ * from t1 whereobject_id=28;
OBJECT_ID OBJECT_NAME ---------- ------------------------------ 28 CON$
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在上面的测试中,创建了一个只有2列,4行的表,这个表只占用了1个数据块的空间。对同样的SQL,全表扫描、索引范围扫描、索引唯一扫描3种不同的访问方式,其逻辑读各不相同:
注意在实际的测试中,每一个SQL应至少执行两次,并以最后一次SQL执行后的逻辑读等统计数据为准,因为在SQL解析时有递归调用,产生了其他的逻辑读。
从上面的测试可以看到,对即使是很小的表,如果返回的数据量很小,使用索引都能够减少逻辑读,从而具有更好的性能。