一次RAC环境性能诊断过程
一次协助公司其他部门解决数据库性能的过程。问题的解决过程并没有完整的记录，这里只能根据部分记录和描述来说明问题的解决过程。
这是一个两节点的RAC环境，版本为10.2.0.3 for Solaris Sparc。这个数据库最初的搭建，包含后期的一次迁移都是我实施的，因此对这个数据库还是很了解的。
数据库一直运行比较稳定，不过最近两天运行速度很慢，无论是数据库还是WEBLOGCI服务器负载都是很大，前台页面反应很慢，经常运行得不到结果。
登陆数据库后，检查了一下系统的等待：
SQL> select inst_id, event, count(*)
2 from gv$session
3 where username is not null
4 group by inst_id, event
5 having count(*) > 5
6 order by 2, 1;
INST_ID EVENT COUNT(*)
---------- ---------------------------------------------------------------- ----------
1 SQL*Net message from client 20
2 SQL*Net message from client 27
1 enq: TX - row lock contention 10
2 enq: TX - row lock contention 10
1 gc buffer busy 49
2 gc buffer busy 47
1 gc cr multi block request 18
2 gc cr multi block request 18
1 gc cr request 8
2 gc cr request 6
已选择10行。
发现绝大部分等待都是与RAC有关的全局等待事件。第一反应是不会是两个节点间的通信存在什么问题。
检查了操作系统和CLUSTER的状态，并没有发现什么异常。
前不久碰到了过由于CLUSTER组件本身的问题，导致RAC数据库两个节点在相互通信时存在异常，并最终导致数据库崩溃的情况。不过检查CLUSTER组件，并没有发现异常之处，除了GC相关的等待特别多以外，其他的细节和以前碰到的情况并不相符。
而且对于CLUSTER组件造成的问题，在整个CLUSTER环境重启之后就回消失，而当前的问题，就是重启CLUSTER和数据库，问题仍然会出现。
显然这并不由于通信和CLUSTER组件导致的问题，而是数据库本身的问题。
上面的等待时间中，如果排除gc相关的，那么基本上就剩下enq: TX - row lock contention了。查询对应这个事件对应的SQL发现是在对ORD_ORDER_RECEIVE表进行删除。
删除操作并不奇怪，但是这些删除操作持有锁的时间都出奇的长，检查这些删除语句的执行计划：
SQL> explain plan for
2 Delete from ord_order_receive
3 where order_item_id = '805D972E6F5153DC38E5AD26'
4 and send_lot = 1
5 ;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3884926111
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 140 | 71799 (1)| 00:16:46 |
| 1 | DELETE | ORD_ORDER_RECEIVE | | | | |
|* 2 | TABLE ACCESS FULL| ORD_ORDER_RECEIVE | 1 | 140 | 71799 (1)| 00:16:46 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ORDER_ITEM_ID"='805D972E6F5153DC38E5AD26' AND "SEND_LOT"=1)
已选择14行。
这张表的记录总数有1000万，但是发现上面的删除语句却没有走索引，不仅如此，很多处于等待的查询语句同样选择了全表扫描，而没有选择索引。
而用来查询记录的ORDER_ITEM_ID列的选择度是非常高的，在这个表中仅次于主键，没有使用索引是很奇怪的。
SQL> SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PK_ORD_ORDER_RECEIVE ID 1
TU_ORD_ORDER_RECEIVE_PROD_ID PRODUCT_ID 1
IND_ORD_ORDER_REC_REC_DATE RECEIVE_DATE 1
IND_ORD_RECEIVE_BUYERSENDER BUYER_ID 1
IND_ORD_RECEIVE_BUYERSENDER SENDER_ID 2
查询这个表上建立的索引，竟然发现这个列上没有创建任何索引。这是不可思议的，从数据库的大量运行的SQL可以看出，这个表的这个字段是经常且大量被访问的，而且这个列的选择度有很高，没有道理不创建索引：
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, DENSITY
2 FROM USER_TAB_COLUMNS
3 WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE'
4 AND COLUMN_NAME = 'ORDER_ITEM_ID';
COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ORDER_ITEM_ID 8433019 1.1858E-07
幸好这个系统存在一个结构完全一致的测试环境，且这个测试环境是不久前才建立的，登陆测试环境，检查这张表的索引情况：
SQL> SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
2 FROM USER_IND_COLUMNS
3 WHERE TABLE_NAME = 'ORD_ORDER_RECEIVE';
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PK_ORD_ORDER_RECEIVE ID 1
TU_ORD_ORDER_RECEIVE_PROD_ID PRODUCT_ID 1
TU_ORD_ORDER_COMB_ITEM_FLAG ORDER_ITEM_ID 1
TU_ORD_ORDER_COMB_ITEM_FLAG RECEIVE_FLAG 2
IND_ORD_ORDER_REC_REC_DATE RECEIVE_DATE 1
IND_ORD_RECEIVE_BUYERSENDER BUYER_ID 1
IND_ORD_RECEIVE_BUYERSENDER SENDER_ID 2
已选择7行。
可以看到，测试环境中是存在ORDER_ITEM_ID列上的索引的。
产品环境前一段时间一直正常，只有最近两天才出现了性能问题，怀疑问题多半就是由于这个索引被误删除所致。
刚才查询的是enq: TX - row lock contention等待时间，而最开始检查系统时发现绝大部分等待时间都与gc的等待有关，那么下面检查一下，是否gc相关的等待也与ORD_ORDER_RECEIVE表有关。
在数据库中任意找一个等待事件为gc buffer busy会话，检查这个会话执行的SQL语句：
SQL> select sql_text
2 from v$sqltext_with_newlines
3 where address in
4 (select sql_address
5 from v$session
6 where sid = 484)
7 order by address, piece;
SQL_TEXT
----------------------------------------------------------------
SELECT ORD_ORDER_RECEIVE.ID, ORD_ORDER_RECEIVE.PRODUCT_ID, ORD_O
RDER_RECEIVE.SEND_LOT, ORD_ORDER_RECEIVE.LOT_NO, ORD_ORDER_RECEI
VE.RECEIVE_QTY, ORD_ORDER_RECEIVE.RECEIVE_USERID, ORD_ORDER_RECE
IVE.RECEIVE_DATE, ORD_ORDER_RECEIVE.RECEIVE_REMARK, ORD_ORDER_RE
CEIVE.BALANCE_FLAG, ORD_ORDER_RECEIVE.BALANCE_DATE, ORD_ORDER_RE
CEIVE.ORDER_ITEM_ID, ORD_ORDER_RECEIVE.READY_USERID, ORD_ORDER_R
ECEIVE.READY_DATE, ORD_ORDER_RECEIVE.RECEIVE_FLAG, ORD_ORDER_REC
EIVE.INVOICE_NO, ORD_ORDER_RECEIVE.INVOICE_DATE, ORD_ORDER_RECEI
VE.SYNC_STATE, ORD_ORDER_RECEIVE.REPOSITORY_ID, ORD_ORDER_RECEIV
E.ORDER_ID, ORD_ORDER_RECEIVE.READY_REMARK, ORD_ORDER_RECEIVE.IN
VOICE_EFFECT_DATE, ORD_ORDER_RECEIVE.INVOICE_EXPIRE_DATE, ORD_OR
DER_RECEIVE.INVOICE_TOTAL, ORD_ORDER_RECEIVE.INVOICE_TRADE_PRICE
, ORD_ORDER_RECEIVE.INVOICE_RETAIL_PRICE, ORD_ORDER_RECEIVE.INVO
ICE_DISCOUNT_RATE, ORD_ORDER_RECEIVE.TRADE_QTY, ORD_ORDER_RECEIV
E.OPERATOR_FLAG, ORD_ORDER_RECEIVE.APP_NUM, ORD_ORDER_RECEIVE.BU
YER_ID, ORD_ORDER_RECEIVE.SENDER_ID, ORD_ORDER_RECEIVE.PLAT_ID F
ROM ORD_ORDER_RECEIVE WHERE ORD_ORDER_RECEIVE.ORDER_ITEM_ID='ZJJ
Y10000000000043680032'
已选择18行。
果然，这个会话也于ORD_ORDER_RECEIVE表的ORDER_ITEM_ID列有关，勿庸置疑，这个SQL语句的执行计划也是全部扫描：
SQL> select count(*)
2 from v$sqltext
3 where address in
4 (select sql_address
5 from v$session
6 where event = 'gc buffer busy')
7 and lower(sql_text) like '%order_item_id%';
COUNT(*)
----------
93
不难看出，导致大量gc buffer busy等待事件的SQL，都包含ORDER_ITEM_ID列，现在已经可以判断，正是这个列上索引的缺失造成了系统的性能问题。
根据测试环境中的索引定义创建索引：
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'TU_ORD_ORDER_COMB_ITEM_FLAG') FROM DUAL;
DBMS_METADATA.GET_DDL('INDEX','TU_ORD_ORDER_COMB_ITEM_FLAG')
--------------------------------------------------------------------------------
CREATE INDEX "ZHEJIANG"."TU_ORD_ORDER_COMB_ITEM_FLAG" ON "ZHEJIANG"."ORD_ORDER_RECEIVE" ("ORDER_ITEM_ID", "RECEIVE_FL
AG")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ZHEJIANG"
由于产品环境中这张表被大量并非的会话锁定，因此创建索引的操作段时间内无法完成，利用GV$SESSION找到所有锁住创建索引的会话，在操作系统中将这些会话杀掉：
SQL> SELECT INST_ID, SID
2 FROM GV$LOCK
3 WHERE ID1 =
4 (SELECT OBJECT_ID
5 FROM DBA_OBJECTS
6 WHERE WNER = 'ZHEJIANG'
7 AND OBJECT_NAME = 'ORD_ORDER_RECEIVE'
8 AND OBJECT_TYPE = 'TABLE');
INST_ID SID
---------- ----------
1 489
1 491
1 495
1 496
1 496
1 496
1 498
1 501
.
.
.
2 1068
2 1085
2 1090
已选择31行。
SQL> SELECT SID
2 FROM V$SESSION S, V$SQL SQL
3 WHERE S.SQL_ADDRESS = SQL.ADDRESS
4 AND S.SQL_HASH_VALUE = SQL.HASH_VALUE
5 AND SQL_TEXT LIKE 'CREATE INDEX TU_ORD_ORDER_COMB_ITEM_FLAG%';
SID
----------
496
SQL> SELECT 'kill -9 ' || SPID
2 FROM V$PROCESS
3 WHERE ADDR IN
4 (SELECT PADDR
5 FROM V$SESSION A, V$LOCK B
6 WHERE A.SID = B.SID
7 AND A.SID != 496
8 AND ID1 =
9 (SELECT OBJECT_ID
10 FROM DBA_OBJECTS
11 WHERE WNER = 'ZHEJIANG'
12 AND OBJECT_NAME = 'ORD_ORDER_RECEIVE'
13 AND OBJECT_TYPE = 'TABLE'));
'KILL-9'||SPID
--------------------
kill -9 16504
kill -9 29364
.
.
.
kill -9 22151
kill -9 26579
已选择19行。
SQL> host
$ kill -9 16504
$ kill -9 29364
$ kill -9 19703
.
.
.
$ kill -9 22151
$ kill -9 26579
SQL> SELECT 'kill -9 ' || SPID
2 FROM GV$PROCESS
3 WHERE INST_ID = 2
4 AND ADDR IN
5 (SELECT PADDR
6 FROM GV$SESSION A, GV$LOCK B
7 WHERE A.SID = B.SID
8 AND A.INST_ID = B.INST_ID
9 AND B.INST_ID = 2
10 AND ID1 =
11 (SELECT OBJECT_ID
12 FROM DBA_OBJECTS
13 WHERE WNER = 'ZHEJIANG'
14 AND OBJECT_NAME = 'ORD_ORDER_RECEIVE'
15 AND OBJECT_TYPE = 'TABLE'));
'KILL-9'||SPID
--------------------
kill -9 17781
kill -9 10302
.
.
.
kill -9 16031
kill -9 15904
已选择15行。
在节点2上kill对应的进程：
bash-3.00$ kill -9 17781
bash-3.00$ kill -9 10302
bash-3.00$ kill -9 1667
.
.
.
bash-3.00$ kill -9 16031
bash-3.00$ kill -9 15904
当创建索引的语句执行完成后，数据库实例逐渐恢复了正常。
导致问题产生的原因很简单，有人误删除了一个重要的索引。通过这个问题得到的经验和教训却不少。
首先诊断问题时切忌想当然，比如这个问题，如果一看到大部分等待事件都是gc有关，就从RAC的CLUSTER方面入手，可能就会走很多的弯路，甚至是误入歧途。当前问题之所以导致了大量gc相关的等待，是因为两个节点上都存在大量的会话对丢失索引的表执行全表扫描操作，从9i开始，Oracle认为利用RAC节点间通信获取远端节点内存中的BLOCK缓存要比本地读取物理文件中的BLOCK快，每个实例的DB_CACHE中都保存了表的一部分缓存，于是两个节点之间在不停交换CACHE，这就是开始检查数据库时发现绝大部分会话都在等待gc buffer的原因。
第二点，有一个结构和产品环境保持一致的测试环境至关重要。用了测试环境，不但可以减少出现错误的机会，像当前这种出现了结构不一致，也便于快速的查找定位问题。
第三点，产品数据库的维护、管理操作应该有专门的DBA来负责，如果很多人都可以随意登陆甚至是修改数据库结构，安全性将无法保证。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-669915/ ，如需转载，请注明出处，否则将追究法律责任。
