LOB字段，NOCACHE 为什么还会导致db file sequential read？？！！

itpubshit

Yong Huang 发表于 2013-9-4 02:17
I think you had a typo in the following:

> 继续汇报一下进度，首先disbale 了 recyclebin

哥，trust me ！I really did it，not only typed on here in message #5。

搜索了metalink ，发现Bug 6060688 ，描述的场景很类似，但没有给出具体原因，感觉语焉不详，顾左右言其他。

Yong Huang

> 哥，trust me ！I really did it，not only typed on here in message #5。

So, can you try disabling recycle bin? Or you already tried and it still doesn't work?

> 发现Bug 6060688 ，描述的场景很类似，但没有给出具体原因，感觉语焉不详

The bug report says

It becomes faster when modifying one of next conditions.

- not using ASSM tablespace
- drop PK (index)
- execute step4 in serial
- using truncate instead of delete at step3

and also

WORKAROUND:
-----------
1) modify LOB chunk size (8K -> 32K)
or
2) rebuild index
SQL> alter table <table_name>modify lob(<column_name>) (rebuild freepools);

Can you try what's suggested?

Thanks for your update.

itpubshit

Yong Huang 发表于 2013-9-4 23:32
> 哥，trust me ！I really did it，not only typed on here in message #5。

So, can you try disablin ...

1、已经试过disable recycle bin了，still doesn't work。
2、关于Bug 6060688 ，我试了workaround的第二个方法rebuild index，同样没有效果。

我现在就怀疑是，读取lob的时候，请求logindex的块时，引起的db file sequential read，而oracle把这event 等待记在了对应的lobsegment头上（所谓Transparent），因为观察到的db file sequential read 等待的对象都是lobsegment 而不是lobindex ，所以也不是很确定。

我再想想，怎么来验证。谢谢版主一直回复，呵呵。

Yong Huang

I don't believe Oracle would record a db file sequential read that actually happened on the LOB index on its LOB segment, unless they had a coding error. Anyway, I can't think of anything for now. Open an SR with Oracle, and keep us posted. Thanks.

itpubshit

Yong Huang 发表于 2013-9-6 04:51
I don't believe Oracle would record a db file sequential read that actually happened on the LOB inde ...

找到一个metalink账号，起了个SR，交互过程不表，现将有用的信息汇报如下：Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table c_test ( sql_id varchar2(13), sql_fulltext clob ) lob (sql_fulltext) store as ( tablespace users enable storage in row chunk 8192 pctversion 10 nocache logging storage(initial 65536
next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default));

Table created.

SQL> insert into c_test select sql_id,sql_fulltext from v$sql;

280 rows created.

SQL> insert into c_test select * from c_test;

280 rows created.

SQL> /

560 rows created.

SQL> /

1120 rows created.

SQL> /

2240 rows created.

SQL> delete c_test;


4480 rows deleted.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> insert into c_test select sql_id,sql_fulltext from v$sql;

283 rows created.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF


===============10046 trace=====================

=====================
PARSING IN CURSOR #2 len=56 dep=0 uid=58 oct=2 lid=58 tim=7243224210 hv=970210658 ad='5d7e3e40'
insert into c_test select sql_id,sql_fulltext from v$sql
END OF STMT
PARSE #2:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=7243224205
BINDS #2:
WAIT #2: nam='db file sequential read' ela= 20942 file#=4 block#=32840 blocks=1 obj#=53538 tim=7243313403   <<<<<<<
WAIT #2: nam='i/o slave wait' ela= 73595 msg ptr=1 p2=48 p3=2147483647 obj#=-1 tim=7243391608
WAIT #2: nam='direct path write' ela= 105044 file number=4 first dba=32840 block cnt=1 obj#=-1 tim=7243423055
=====================



SQL> select object_id, object_name,object_type from dba_objects where object_id=53538;

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
--------- ------------------------------ -------------------
   53538 SYS_LOB0000053537C00002$$      LOB

SQL> select table_name,column_name,segment_name from dba_lobs where segment_name='SYS_LOB0000053537C00002$$';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SEGMENT_NAME
------------------------------
C_TEST
SQL_FULLTEXT
SYS_LOB0000053537C00002$$

SQL> alter system dump datafile 4 block 32840;

Start dump data blocks tsn: 4 file#: 4 minblk 32840 maxblk 32840
buffer tsn: 4 rdba: 0x01008048 (4/32840)
scn: 0x0048.def37167 seq: 0x02 flg: 0x04 tail: 0x71672802
frmt: 0x02 chkval: 0xc898 type: 0x28=PAGETABLE MANAGED LOB BLOCK <<<<<
Hex dump of block: st=0, typ_found=1

Summary
=======
We can reproduce this issue.

itpubshit

亲爱的客户，

我们可以通过test-case来重现此问题，并且发现Bug 3546482(被关闭成'Not a Bug')描述的问题与此类似，这是正常行为，参见我的前一个更新。
另外我还在与内部专家对此问题进行讨论，如果有结果，我会随时更新，感谢您的耐心等待。


-- Analysis --

Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information. This is research only, and may NOT be applicable to your specific situation.

Bug 3546482 : DB FILE SEQ. READ AND DIRECT PATH READ WHEN LOADING LOB DATA (NOCACHE MODE)

Closes as 'Not a Bug';

@ This is not a bug. In 10g, the caller of kdl_write(), tells kdl_write if
@ there is space to write to the row. If there is, and either we are streaming
@ data (the amount of data is not known), or the buffer passed into kdl_write()
@ is smaller than 3964 bytes, we write to the row.

itpubshit

潘老师，您好！

感谢您的辛勤工作，重现了此问题。：）
这里还有另一个疑问，如果这DB FILE SEQ.READ 是因为在批量delete之后的insert的正常行为的话，为什么通过SQL_ID查询不到相应的sqltext呢？

期待您的回答！
=========================================================================================

亲爱的客户，

其实执行过的SQL并不是都能被v$sql全部记录的，一种可能的情况就是它被刷出了shared_pool。
在11g上，我们可以通过event++对某一个SQL来进行10046 trace，这样就能找到该SQL的文本，比如：

alter session set events 'sql_trace[sql: g3yc1js3g2689]';

但是在10g上，要找到该SQL文本，除非全库trace，否则比较困难。

参考：
11g Event++ Quick Reference And Best Practices (Doc ID 1234835.1)

Yong Huang

Thanks for this update. Now I think I understand more. It looks like if the LOB data is in-line (and it's not overflown into out-of-line, hence the "magic" 3964 bytes limit), the event for the read is db file sequential read. Right? At least in 10gR2. It may be different for 11g, and out-of-line LOB (disable storage in row). I assume this statement in note 66431.1 is still valid:

"In-line LOBS are not affected by the CACHE option as they reside in the actual table block (which is typically accessed via the buffer cache any way)."

So, as long as it's in-line, the LOB data is treated like normal table data. Event "db file sequential read" may be seen. If that thinking is true, I guess "db file scattered read" is possible too, if multiple LOB blocks are read at once?

Keep us posted. Maybe you can try out-of-line LOB and in 11g.

itpubshit

亲爱的客户，

关于此问题，我们进行了探讨并作了一些测试，得出的结论是：

1. Oracle在重用已经标记为删除的lob extent时，会产生db file sequential read等待。

2. 之前提到的Bug 3546482对此有一定的影响。

3. 尚未有文档来详细描述Oracle在重用lob extent时Oracle的行为，如果您的数据库因此遇到性能问题，我们需要开启一个bug让开发人员来进一步分析，这需要您提供一个extended suport CSI.

请告知我们您的问题，谢谢。

Yong Huang

So "db file sequential read" will be seen if the LOB extents marked as deleted are used. How interesting! Do they have to be in-line? If they can be out-of-line, do they have to have CACHE option? Is this behavior version-dependent? I wish there was some document on MOS about it. Thanks for the update.