环境 AIX 5300-01 ORACEL 9208
前几天开发写的一个存储过程老是无法执行完成，24小时了一直卡在一个DELETE操作上，并遭遇大量LATCH FREE：cache buffers chains
检查发现是缺失索引，加了索引后秒级别效应
但是，我们这是一个开发数据库，没有什么压力，而且遭遇cache buffers chains的时候，我查看了系统就这一个LATCH FREE等待
后来加了索引解决问题。但是还是想不明白为什么会有LATCH FREE，而且测试的时候还不总是98 cache buffers chains，还出现过其他多种LATCH，以及一个模式6的TC锁定（一次只出现一种，并一直等待这个等待）
下面是后来进行的一个测试：
CREATE TABLE out_table (ID NUMBER,NAME NUMBER,age NUMBER)
CREATE TABLE in_table (ID NUMBER,NAME NUMBER,age NUMBER)
CREATE TABLE di_table (ID NUMBER,NAME NUMBER)
INSERT INTO out_table
SELECT ROWNUM,MOD(ROWNUM,20),MOD(ROWNUM,100) FROM
dba_objects a,dba_objects b WHERE ROWNUM <= 100000
INSERT INTO in_table
SELECT ROWNUM,MOD(ROWNUM,21),MOD(ROWNUM,101) FROM
dba_objects a,dba_objects b WHERE ROWNUM <= 100000
INSERT INTO di_table
SELECT ROWNUM,MOD(ROWNUM,10) FROM
dba_objects a WHERE ROWNUM <= 10
CREATE BITMAP INDEX idx_in_table_age ON in_table(age)
随便建立3张表，运行如下SQL语句：
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID);
--------------------------------------------------------------------
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT 1
4 FROM DI_TABLE C,IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 |
| 1 | DELETE | OUT_TABLE | | |
|* 2 | FILTER | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 4 | 52 |
| 4 | NESTED LOOPS | | 1 | 39 |
|* 5 | TABLE ACCESS BY INDEX ROWID | IN_TABLE | 1 | 26 |
| 6 | BITMAP CONVERSION TO ROWIDS| | | |
| 7 | BITMAP INDEX FULL SCAN | IDX_IN_TABLE_AGE | | |
|* 8 | TABLE ACCESS FULL | DI_TABLE | 1 | 13 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "IN_TABLE" "B","DI_TABLE" "C" WHERE
"B"."ID"="C"."ID" AND "B"."NAME"=:B1))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5 - filter("B"."NAME"=:B1)
8 - filter("B"."ID"="C"."ID")
22 rows selected
--这里使用了一个错误的索引，进行索引全扫描
启用10046，跟踪
=====================
PARSING IN CURSOR #2 len=143 dep=0 uid=66 oct=7 lid=66 tim=2692443100507 hv=3039241828 ad='5c91fcd0'
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID)
END OF STMT
PARSE #2:c=0,e=261,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2692443100501
BINDS #2:
长时间在这个地方卡住
通过explain发现，没有使用IN_TABLE上全表扫描，还是通过idx_in_table_age在访问表
--第一执行
*** 2009-04-14 14:04:36.119
WAIT #1: nam='latch free' ela= 132851 p1=504403159573671624 p2=102 p3=1
*** 2009-04-14 14:05:09.225
WAIT #1: nam='latch free' ela= 162595 p1=504403159573673208 p2=102 p3=1
*** 2009-04-14 14:05:55.084
WAIT #1: nam='latch free' ela= 1736 p1=504403159573673208 p2=102 p3=1
--中断在执行一次
*** 2009-04-14 14:07:46.530
WAIT #2: nam='latch free' ela= 9822 p1=504403159567582496 p2=98 p3=0
*** 2009-04-14 14:08:59.440
WAIT #2: nam='latch free' ela= 28830 p1=504403159567662424 p2=98 p3=0
*** 2009-04-14 14:09:25.749
WAIT #2: nam='latch free' ela= 95578 p1=504403159567662424 p2=98 p3=0
SQL> SELECT P1RAW FROM v$session_wait WHERE sid=316;
P1RAW
----------------
070000004D9C23C8
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from x$bh a, dba_objects b
where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = '070000004D9C23C8'
union
select hladdr, file#, dbablk, tch, obj, null
from x$bh
where obj in (select obj
from x$bh
where hladdr = '070000004D9C23C8'
minus
select object_id
from dba_objects
minus
select data_object_id from dba_objects)
and hladdr = '070000004D9C23C8'
order by 4
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
070000004D9C23C8 14 203689 0 235343 FT_JXKH_GZRWMX
070000004D9C23C8 14 318508 0 235746 FT_JXKH_GZRWMX
070000004D9C23C8 14 326700 0 235746 FT_JXKH_GZRWMX
070000004D9C23C8 14 334892 0 235820 FT_JXKH_GZRWMX
070000004D9C23C8 19 18216 0 235649 FT_JXKH_LSBL_MX
070000004D9C23C8 34 1435676 0 234890 FT_JXKH_GZRWMX
070000004D9C23C8 34 1501212 0 235232 FT_JXKH_GZRWMX
070000004D9C23C8 34 1517596 0 235306 FT_JXKH_GZRWMX
070000004D9C23C8 34 1525788 0 235380 FT_JXKH_GZRWMX
070000004D9C23C8 34 1558556 0 235783 FT_JXKH_GZRWMX
070000004D9C23C8 34 1566748 0 235857 FT_JXKH_GZRWMX
070000004D9C23C8 24 635297 1 230487 FT_SB_SBXX
070000004D9C23C8 34 788115 1 230487 FT_SB_SBXX
070000004D9C23C8 11 475667 315 235982 IN_TABLE
070000004D9C23C8 1 27020 1965 37 I_OBJ2
--为了修正这个错误的索引，使用提示
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT /*+full(B) */1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID);
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT /*+full(B) */1
4 FROM DI_TABLE C,IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 | 23 (22)|
| 1 | DELETE | OUT_TABLE | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 4 | 52 | 3 (34)|
| 4 | NESTED LOOPS | | 1 | 39 | 5 (20)|
|* 5 | TABLE ACCESS FULL| IN_TABLE | 1 | 26 | 3 (34)|
|* 6 | TABLE ACCESS FULL| DI_TABLE | 1 | 13 | 3 (34)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ FULL("B") */ 0 FROM "IN_TABLE"
"B","DI_TABLE" "C" WHERE "B"."ID"="C"."ID" AND "B"."NAME"=:B1))
5 - filter("B"."NAME"=:B1)
6 - filter("B"."ID"="C"."ID")
20 rows selected
--一样的不出结果，10046在解析后就没输出，这个语句也无法返回结果。
--添加索引：
CREATE BITMAP INDEX idx_in_table ON IN_TABLE(NAME);
CREATE BITMAP INDEX idx_di_table ON di_table(ID);
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT 1
4 FROM DI_TABLE C, IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 907 | 11791 | 2768
| 1 | DELETE | OUT_TABLE | | |
|* 2 | FILTER | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 907 | 11791 | 47
| 4 | NESTED LOOPS | | 1 | 39 | 3
| 5 | TABLE ACCESS BY INDEX ROWID | IN_TABLE | 1 | 26 | 3
| 6 | BITMAP CONVERSION TO ROWIDS| | | |
|* 7 | BITMAP INDEX SINGLE VALUE | IDX_IN_TABLE | | |
| 8 | BITMAP CONVERSION TO ROWIDS | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | IDX_DI_TABLE | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "IN_TABLE" "B","DI_TABLE" "C" WHERE
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"B"."ID"="C"."ID" AND "B"."NAME"=:B1))
7 - access("B"."NAME"=:B1)
9 - access("B"."ID"="C"."ID")
23 rows selected
--10046
PARSING IN CURSOR #4 len=130 dep=0 uid=66 oct=7 lid=66 tim=2694627102427 hv=1274456261 ad='60b6ba88'
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C, IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID)
END OF STMT
PARSE #4:c=0,e=2954,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=2694627102419
BINDS #4:
*** 2009-04-14 14:44:08.543
WAIT #4: nam='latch free' ela= 11733 p1=504403159567662424 p2=98 p3=0
WAIT #4: nam='latch free' ela= 9805 p1=504403159567662424 p2=98 p3=0
--这次可以执行完成，但是一样等待latch free，我的这个是个测试数据库，运行时已经查看没有其他latch free
想问下前面没有索引时，LATCH FREE是如何产生的
就算是做NEST LOOP,也不至于24小时也出不来结果，一直等待latch free
最主要的是等待的LATCH还不一定都是cache buffers chains
前几天开发写的一个存储过程老是无法执行完成，24小时了一直卡在一个DELETE操作上，并遭遇大量LATCH FREE：cache buffers chains
检查发现是缺失索引，加了索引后秒级别效应
但是，我们这是一个开发数据库，没有什么压力，而且遭遇cache buffers chains的时候，我查看了系统就这一个LATCH FREE等待
后来加了索引解决问题。但是还是想不明白为什么会有LATCH FREE，而且测试的时候还不总是98 cache buffers chains，还出现过其他多种LATCH，以及一个模式6的TC锁定（一次只出现一种，并一直等待这个等待）
下面是后来进行的一个测试：
CREATE TABLE out_table (ID NUMBER,NAME NUMBER,age NUMBER)
CREATE TABLE in_table (ID NUMBER,NAME NUMBER,age NUMBER)
CREATE TABLE di_table (ID NUMBER,NAME NUMBER)
INSERT INTO out_table
SELECT ROWNUM,MOD(ROWNUM,20),MOD(ROWNUM,100) FROM
dba_objects a,dba_objects b WHERE ROWNUM <= 100000
INSERT INTO in_table
SELECT ROWNUM,MOD(ROWNUM,21),MOD(ROWNUM,101) FROM
dba_objects a,dba_objects b WHERE ROWNUM <= 100000
INSERT INTO di_table
SELECT ROWNUM,MOD(ROWNUM,10) FROM
dba_objects a WHERE ROWNUM <= 10
CREATE BITMAP INDEX idx_in_table_age ON in_table(age)
随便建立3张表，运行如下SQL语句：
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID);
--------------------------------------------------------------------
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT 1
4 FROM DI_TABLE C,IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 |
| 1 | DELETE | OUT_TABLE | | |
|* 2 | FILTER | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 4 | 52 |
| 4 | NESTED LOOPS | | 1 | 39 |
|* 5 | TABLE ACCESS BY INDEX ROWID | IN_TABLE | 1 | 26 |
| 6 | BITMAP CONVERSION TO ROWIDS| | | |
| 7 | BITMAP INDEX FULL SCAN | IDX_IN_TABLE_AGE | | |
|* 8 | TABLE ACCESS FULL | DI_TABLE | 1 | 13 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "IN_TABLE" "B","DI_TABLE" "C" WHERE
"B"."ID"="C"."ID" AND "B"."NAME"=:B1))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5 - filter("B"."NAME"=:B1)
8 - filter("B"."ID"="C"."ID")
22 rows selected
--这里使用了一个错误的索引，进行索引全扫描
启用10046，跟踪
=====================
PARSING IN CURSOR #2 len=143 dep=0 uid=66 oct=7 lid=66 tim=2692443100507 hv=3039241828 ad='5c91fcd0'
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID)
END OF STMT
PARSE #2:c=0,e=261,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2692443100501
BINDS #2:
长时间在这个地方卡住
通过explain发现，没有使用IN_TABLE上全表扫描，还是通过idx_in_table_age在访问表
--第一执行
*** 2009-04-14 14:04:36.119
WAIT #1: nam='latch free' ela= 132851 p1=504403159573671624 p2=102 p3=1
*** 2009-04-14 14:05:09.225
WAIT #1: nam='latch free' ela= 162595 p1=504403159573673208 p2=102 p3=1
*** 2009-04-14 14:05:55.084
WAIT #1: nam='latch free' ela= 1736 p1=504403159573673208 p2=102 p3=1
--中断在执行一次
*** 2009-04-14 14:07:46.530
WAIT #2: nam='latch free' ela= 9822 p1=504403159567582496 p2=98 p3=0
*** 2009-04-14 14:08:59.440
WAIT #2: nam='latch free' ela= 28830 p1=504403159567662424 p2=98 p3=0
*** 2009-04-14 14:09:25.749
WAIT #2: nam='latch free' ela= 95578 p1=504403159567662424 p2=98 p3=0
SQL> SELECT P1RAW FROM v$session_wait WHERE sid=316;
P1RAW
----------------
070000004D9C23C8
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from x$bh a, dba_objects b
where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = '070000004D9C23C8'
union
select hladdr, file#, dbablk, tch, obj, null
from x$bh
where obj in (select obj
from x$bh
where hladdr = '070000004D9C23C8'
minus
select object_id
from dba_objects
minus
select data_object_id from dba_objects)
and hladdr = '070000004D9C23C8'
order by 4
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- --------------------------------------------------------------------------------
070000004D9C23C8 14 203689 0 235343 FT_JXKH_GZRWMX
070000004D9C23C8 14 318508 0 235746 FT_JXKH_GZRWMX
070000004D9C23C8 14 326700 0 235746 FT_JXKH_GZRWMX
070000004D9C23C8 14 334892 0 235820 FT_JXKH_GZRWMX
070000004D9C23C8 19 18216 0 235649 FT_JXKH_LSBL_MX
070000004D9C23C8 34 1435676 0 234890 FT_JXKH_GZRWMX
070000004D9C23C8 34 1501212 0 235232 FT_JXKH_GZRWMX
070000004D9C23C8 34 1517596 0 235306 FT_JXKH_GZRWMX
070000004D9C23C8 34 1525788 0 235380 FT_JXKH_GZRWMX
070000004D9C23C8 34 1558556 0 235783 FT_JXKH_GZRWMX
070000004D9C23C8 34 1566748 0 235857 FT_JXKH_GZRWMX
070000004D9C23C8 24 635297 1 230487 FT_SB_SBXX
070000004D9C23C8 34 788115 1 230487 FT_SB_SBXX
070000004D9C23C8 11 475667 315 235982 IN_TABLE
070000004D9C23C8 1 27020 1965 37 I_OBJ2
--为了修正这个错误的索引，使用提示
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT /*+full(B) */1
FROM DI_TABLE C,IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID);
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT /*+full(B) */1
4 FROM DI_TABLE C,IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 4 | 52 | 23 (22)|
| 1 | DELETE | OUT_TABLE | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 4 | 52 | 3 (34)|
| 4 | NESTED LOOPS | | 1 | 39 | 5 (20)|
|* 5 | TABLE ACCESS FULL| IN_TABLE | 1 | 26 | 3 (34)|
|* 6 | TABLE ACCESS FULL| DI_TABLE | 1 | 13 | 3 (34)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ FULL("B") */ 0 FROM "IN_TABLE"
"B","DI_TABLE" "C" WHERE "B"."ID"="C"."ID" AND "B"."NAME"=:B1))
5 - filter("B"."NAME"=:B1)
6 - filter("B"."ID"="C"."ID")
20 rows selected
--一样的不出结果，10046在解析后就没输出，这个语句也无法返回结果。
--添加索引：
CREATE BITMAP INDEX idx_in_table ON IN_TABLE(NAME);
CREATE BITMAP INDEX idx_di_table ON di_table(ID);
SQL> explain plan for
2
2 DELETE FROM OUT_TABLE A
3 WHERE EXISTS (SELECT 1
4 FROM DI_TABLE C, IN_TABLE B
5 WHERE B.NAME = A.NAME
6 AND B.ID = C.ID);
Explained
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 907 | 11791 | 2768
| 1 | DELETE | OUT_TABLE | | |
|* 2 | FILTER | | | |
| 3 | TABLE ACCESS FULL | OUT_TABLE | 907 | 11791 | 47
| 4 | NESTED LOOPS | | 1 | 39 | 3
| 5 | TABLE ACCESS BY INDEX ROWID | IN_TABLE | 1 | 26 | 3
| 6 | BITMAP CONVERSION TO ROWIDS| | | |
|* 7 | BITMAP INDEX SINGLE VALUE | IDX_IN_TABLE | | |
| 8 | BITMAP CONVERSION TO ROWIDS | | | |
|* 9 | BITMAP INDEX SINGLE VALUE | IDX_DI_TABLE | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "IN_TABLE" "B","DI_TABLE" "C" WHERE
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"B"."ID"="C"."ID" AND "B"."NAME"=:B1))
7 - access("B"."NAME"=:B1)
9 - access("B"."ID"="C"."ID")
23 rows selected
--10046
PARSING IN CURSOR #4 len=130 dep=0 uid=66 oct=7 lid=66 tim=2694627102427 hv=1274456261 ad='60b6ba88'
DELETE FROM OUT_TABLE A
WHERE EXISTS (SELECT 1
FROM DI_TABLE C, IN_TABLE B
WHERE B.NAME = A.NAME
AND B.ID = C.ID)
END OF STMT
PARSE #4:c=0,e=2954,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=1,tim=2694627102419
BINDS #4:
*** 2009-04-14 14:44:08.543
WAIT #4: nam='latch free' ela= 11733 p1=504403159567662424 p2=98 p3=0
WAIT #4: nam='latch free' ela= 9805 p1=504403159567662424 p2=98 p3=0
--这次可以执行完成，但是一样等待latch free，我的这个是个测试数据库，运行时已经查看没有其他latch free
想问下前面没有索引时，LATCH FREE是如何产生的
就算是做NEST LOOP,也不至于24小时也出不来结果，一直等待latch free
最主要的是等待的LATCH还不一定都是cache buffers chains
