Oracle db file sequential read事件引起长时间等待的原因？

Dragon95432

没解决，我不是做数据库这块的，完全靠平时的一些积累在查找问题。
今天查看v$session_wait视图的时候，突然发现P1 对应的文件号不停地变，查了下这个表的段对应到了5个dbf文件。当删除执行的时候，P1在变换，要250s左右才能执行完成。

Dragon95432

7#楼的朋友说得很对，应该是位oracle高手。
You can check to see if its parameters change during your long wait:
P1确实在变，因为我白天有些其他事情，没注意看贴，明天我按你得方式做观察。

谢谢 两位的关注！

Yong Huang

I just saw your uploaded attachment. You didn't show the SQL. It probably looks like

delete from ac_imessage where imessage_id = 7720164;

and the column is the primary key on this table. Obviously Oracle thinks the best way is to use the PK to find the row.

It's possible the plan you showed us is not the actual plan Oracle uses. You can find the actual plan from v$sql_plan or use dbms_xplan:

select * from table(dbms_xplan.display_cursor('&sql_id','','allstats'));

where the sql_id comes from your query of v$sql* view(s).

Your version is 10.2.0.3? Make sure the table and index stats are up-to-date.

Yong Huang

Dragon95432

开始我也怀疑没有用到唯一索引扫描，执行计划是假的。但是我加了索引提示还是一样(imessage_id is primary key field)，
sql:
   delete /*+index (ac_imessage imessage_id) */ from ac_imessage where imessage_id = 7720164;
每次执行5分钟左右。
表、索引的统计信息都是新的，手动做过。 今天没在现场，没法做10046 trace。
明天按 Yong Huang 的方式看下实际的执行计划。
不行再做10046 trace。

ccsnmoracle

等待LZ的好消息！

执行计划都不能正确显示，并且连是否使用索引的信息都能出错，这太可怕了吧！Oracle的产品太差了吧。。。。
当然，现在只是猜测。。。

Dragon95432

Yong Huang能否帮我看下awr? 在做delete语句的前中后各做了一次AWR.见附件5-6点，6-7点，7-8点。

Dragon95432

Yong Huang 发表于 2011-11-29 23:21
"db file sequential read" is usually but not always associated with single block index read. In your ...

Yong Huang:
执行计划如下：
select * from table(dbms_xplan.display_cursor('&sql_id','','allstats'));

1        SQL_ID  0g9psqu0jud2n, child number 0
2        -------------------------------------
3        delete from ac_imessage m where m.imessage_id = 7720158
4         
5        Plan hash value: 3651533437
6         
7        ---------------------------------------------------
8        | Id  | Operation          | Name        | E-Rows |
9        ---------------------------------------------------
10        |   0 | DELETE STATEMENT   |             |        |
11        |   1 |  DELETE            | AC_IMESSAGE |        |
12        |*  2 |   INDEX UNIQUE SCAN| PK_IMESSAGE |      1 |
13        ---------------------------------------------------
14         
15        Predicate Information (identified by operation id):
16        ---------------------------------------------------
17         
18           2 - access("M"."IMESSAGE_ID"=7720158)
19         
20        Note
21        -----
22           - Warning: basic plan statistics not available. These are only collected when:
23               * hint 'gather_plan_statistics' is used for the statement or
24               * parameter 'statistics_level' is set to 'ALL', at session or system level
25         

执行 select event, p1, p2, p3, seq# from v$session where sid = $sid;

event : db file sequential read
p1: 在几个文件号之间不停变换
P2: 在不停地变换
P3: 为1
#seq: 不停地增加

按版主的意思就是系统设置有错误？

Dragon95432

这个是 10046 trace 文件，不懂为什么会去访问另外一个表的索引。

WAIT #1: nam='db file sequential read' ela= 7063 file#=7 block#=973146 blocks=1 obj#=16177 tim=6040099561050
WAIT #1: nam='db file sequential read' ela= 389 file#=7 block#=968156 blocks=1 obj#=16177 tim=6040099561504
WAIT #1: nam='db file sequential read' ela= 381 file#=7 block#=971887 blocks=1 obj#=16177 tim=6040099561949
WAIT #1: nam='db file sequential read' ela= 544 file#=7 block#=969783 blocks=1 obj#=16177 tim=6040099562556
WAIT #1: nam='db file sequential read' ela= 374 file#=7 block#=968220 blocks=1 obj#=16177 tim=6040099563016
WAIT #1: nam='db file sequential read' ela= 535 file#=7 block#=972009 blocks=1 obj#=16177 tim=6040099563616
WAIT #1: nam='db file sequential read' ela= 210 file#=7 block#=966845 blocks=1 obj#=16177 tim=6040099563889
WAIT #1: nam='db file sequential read' ela= 543 file#=7 block#=973426 blocks=1 obj#=16177 tim=6040099564496
WAIT #1: nam='db file sequential read' ela= 383 file#=7 block#=971951 blocks=1 obj#=16177 tim=6040099564944
WAIT #1: nam='db file sequential read' ela= 544 file#=7 block#=972073 blocks=1 obj#=16177 tim=6040099565552
WAIT #1: nam='db file sequential read' ela= 4452 file#=7 block#=973210 blocks=1 obj#=16177 tim=6040099570067
WAIT #1: nam='db file sequential read' ela= 536 file#=7 block#=972137 blocks=1 obj#=16177 tim=6040099570666
WAIT #1: nam='db file sequential read' ela= 553 file#=7 block#=973274 blocks=1 obj#=16177 tim=6040099571282
WAIT #1: nam='db file sequential read' ela= 775 file#=7 block#=969847 blocks=1 obj#=16177 tim=6040099572120
WAIT #1: nam='db file sequential read' ela= 518 file#=7 block#=969911 blocks=1 obj#=16177 tim=6040099572693
WAIT #1: nam='db file sequential read' ela= 1164 file#=7 block#=973338 blocks=1 obj#=16177 tim=6040099573938
WAIT #1: nam='db file sequential read' ela= 742 file#=7 block#=971376 blocks=1 obj#=16177 tim=6040099574732
WAIT #1: nam='db file sequential read' ela= 832 file#=7 block#=972015 blocks=1 obj#=16177 tim=6040099575616
WAIT #1: nam='db file sequential read' ela= 955 file#=7 block#=971440 blocks=1 obj#=16177 tim=6040099576621
WAIT #1: nam='db file sequential read' ela= 1033 file#=7 block#=970857 blocks=1 obj#=16177 tim=6040099577705
WAIT #1: nam='db file sequential read' ela= 9678 file#=7 block#=973490 blocks=1 obj#=16177 tim=6040099587434
WAIT #1: nam='db file sequential read' ela= 357 file#=7 block#=968284 blocks=1 obj#=16177 tim=6040099587841
WAIT #1: nam='db file sequential read' ela= 365 file#=7 block#=968348 blocks=1 obj#=16177 tim=6040099588257
WAIT #1: nam='db file sequential read' ela= 359 file#=7 block#=968412 blocks=1 obj#=16177 tim=6040099588664
WAIT #1: nam='db file sequential read' ela= 260 file#=7 block#=970921 blocks=1 obj#=16177 tim=6040099588973
WAIT #1: nam='db file sequential read' ela= 175 file#=7 block#=970985 blocks=1 obj#=16177 tim=6040099589197
WAIT #1: nam='db file sequential read' ela= 516 file#=7 block#=973402 blocks=1 obj#=16177 tim=6040099589762

文件20M多，基本一样的内容,file#在变(7, 8, 9 , 11)，block#在变, blocks不变，一直单块读。
obj#=16177 不变, obj#是不是object_id,我根据这个来查发现它是另外一个表的索引，但是删除并没有做级联，怎么会用到外键的索引?

Dragon95432

问题解决了，太粗心了，都外键惹的祸。Oracle为了保证数据完整性会去扫描外键obj#=16177，导致时间超长。谢谢两位。

Yong Huang

Thanks for the feedback! If your database is big as a data warehouse, you can choose to set the foreign key to disabled but rely, so CBO still generates the best plan considering the FK, and yet (I'm pretty sure but I didn't test) the execution will not do the extra read for the purpose of verifying data integrity. Needless to say, you as the data owner must ensure the data integrity is maintained.

It's always nice of the person to post back his solution after he solved the problem.

Yong Huang