Skip to content

Database - 59. page

ITL争用阻塞与ITL死锁

ITL(Interested Transaction List)是Oracle数据块内部的一个组成部分，用来记录该块所有发生的事务，一个itl可以看作是一个记录，在一个时间，可以记录一个事务（包括提交或者未提交事务）。当然，如果这个事务已经提交，那么这个itl的位置就可以被反复使用了，因为itl类似记录，所以，有的时候也叫itl槽位。

如果一个事务一直没有提交，那么，这个事务将一直占用一个itl槽位，itl里面记录了事务信息，回滚段的入口，事务类型等等。如果这个事务已经提交，那么，itl槽位中还保存的有这个事务提交时候的SCN号。如dump一个块，就可以看到itl信息：
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.002.0000158e 0x0080104d.00a1.6e –U- 734 fsc 0x0000.6c9deff0
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

对于已经提交的事务，itl槽位最好不要马上被覆盖，因为一致性读可能会用到这个信息，一致性读的时候，可能需要从这里获得回滚段的入口，并从回滚段中获得一致性读。

itl的个数，受参数initrans控制，最大的itl个数，受maxtrans控制，在一个块内部，默认分配了2个或3个itl的个数，如果这个块内还有空闲空间，那么Oracle是可以利用这些空闲空间并再分配itl。如果没有了空闲空间，那么，这个块因为不能分配新的itl，所以就可能发生itl等待。

如果在并发量特别大的系统中，最好分配足够的itl个数，其实它并浪费不了太多的空间，或者，设置足够的pctfree，保证itl能扩展，但是pctfree有可能是被行数据给消耗掉的，如update，所以，也有可能导致块内部的空间不够而导致itl等待。

以下是一个ITL等待的例子：
Luda@10gR2>create table test(a int) pctfree 0 initrans 1;
Table created.

我们这里指定pctfree为0，initrans为1，就是为了更观察到itl的真实等待情况，那么，现在，我们给这些块内插入数据，把块填满，让它不能有空间分配。
Luda@10gR2>begin
2 for i in 1..2000 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
Luda@10gR2>commit;
Commit complete.

我们再检查数据填充的情况：
Luda@10gR2>select f,b,count(*) from (
2 select dbms_rowid.rowid_relative_fno(rowid) f,
3 dbms_rowid.rowid_block_number(rowid) b
4 from test) group by f,b;

F B COUNT(*)
———- ———- ———-
1 29690 734
1 29691 734
1 29692 532

可以发现，这2000条数据分布在3个块内部，其中有2个块添满了，一个块是半满的。我们dump一个满的块，可以看到itl信息：
Luda@10gR2>alter system dump datafile 1 block 29690;

回到os，在udump目录下，检查跟踪文件，可以看到如下的信息
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.002.0000158e 0x0080104d.00a1.6e –U- 734 fsc 0x0000.6c9deff0
0x02 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000

发现，采用如上参数创建的表，块内部默认有2个itl槽位，如果这里不指定initrans 1，默认是有3个itl槽位的。

因为只有2个ITL槽位，我们可以用三个会话来模拟等待：

会话1，我们更新这个块内部的第一行:
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1;
1 row updated.

会话2，我们更新这个块内部的第2行:
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2;
1 row updated.

会话3(SID=153)，我们更新这个块内部的第三行，发现被阻塞:
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;

可以看到，会话被阻塞

观察这个时候的等待事件，我们可以发现是ITL等待：
Luda@10gR2>select EVENT from v$session_wait where sid=153
EVENT
—————————-
enq: TX – allocate ITL entry

因为该块只有2个itl槽位，而现在发生了3个事务，而且，因为该块被数据添满，根本没有剩余的空间来分配新的itl，所以发生了等待。如果我们这个实验发生在半满的块29692上面，就发现进程3不会被阻塞，因为这里有足够的空间可以分配新的itl。

3、ITL死锁

那么，理解了itl的阻塞，我们也就可以分析itl的死锁了，因为有阻塞，一般就能发生死锁。还是以上的表，因为有2个itl槽位，我们需要拿2个满的数据块，4个进程来模拟itl死锁：

会话1
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1;
1 row updated.

会话2
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2;
1 row updated.

会话3
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=1;
1 row updated.

会话4
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=2;
1 row updated.

以上4个进程把2个不同块的4个itl槽位给消耗光了，现在的情况，就是让他们互相锁住，达成死锁条件，回到会话1，更新块2，注意，以上4个操作，包括以下的操作，更新的根本不是同一行数据，主要是为了防止出现的是TX等待。
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;

发现被阻塞

那我们在会话3，更新块1，当然，也不是同一行
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;

被阻塞

注意，如果是9i，在这里就报死锁了，在进程1，我们可以看到
Luda@9iR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;
update test set a=a
where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

但是，在10g里面，这个时候，死锁是不会发生的，因为这里的进程1还可以等待进程4释放资源，进程3还可以等待进程2释放资源，只要进程2与进程4释放了资源，整个环境又活了，那么我们需要把这两个进程也塞住。

会话2，注意，我们也不是更新的同一行数据
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=4;

被阻塞

还有最后一个进程，进程4，我们也不更新同一行数据
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29690
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=4;

虽然，以上的每个更新语句，更新的都不是同一个数据行，但是，的确，所有的进程都被阻塞住了，那么，死锁的条件也达到了，马上，我们可以看到，进程1出现提示，死锁：
Luda@10gR2>update test set a=a
2 where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
3 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3;

update test set a=a
where dbms_rowid.ROWID_BLOCK_NUMBER(rowid)=29691
and dbms_rowid.ROWID_ROW_NUMBER(rowid)=3
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

4、ITL等待与死锁的避免

为了避免以上的情况发生，我们一定要注意在高并发环境下的表中，正确的设置itl个数，如4个，8个等等，保证该块有足够的itl槽位，保证事务能顺利的进行，而没有itl的等待。关于itl的等待，在statspack的段报告中，也能很明显的看到：
Top 5 ITL Waits per Segment for DB: TEST Instance: test Snaps: 13013 -13014
-> End Segment ITL Waits Threshold: 100

Subobject Obj. ITL
Owner Tablespace Object Name Name Type Waits %Total
———- ———- ——————– ———- —– ———— ——-
TEST TBS_EL_IND IDX_LLORDER_ORDERID INDEX 3 75.00
TEST TBS_INDEX2 IDX_AUC_FEED_FDATE INDEX 1 25.00

如果出现的频率很小，象上面的情况，一般可以不用干预，但是，如果waits很多，则表示这个对象有很严重的itl争用情况，需要增加itl个数。
如果想增加initrans个数，参数可以动态修改，但是，只是针对以后的新块起效，以前的块如果想生效，需要在新参数下，重整表数据，如重建该表，或者move该表。

Data access tuning Oracle performance tuning 数据技术 | Guang Cai Li |

redo 日志产生量的监控以及测试

用实验说明
一、在非归档模式下：
view plaincopy
SQL> archive log list
数据库日志模式 非存档模式
自动存档 禁用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 2491
当前日志序列 2493

用sys用户创建查询redo size的视图（方便查询）
view plaincopy
SQL> create or replace view redo_size
2 as
3 select value
4 from v$mystat, v$statname
5 where v$mystat.statistic# = v$statname.statistic#
6 and v$statname.name = ‘redo size’;

视图已创建。
用sys用户创建同义词
view plaincopy
SQL> create public synonym redo_size for redo_size;

同义词已创建。

以下用scott操作
创建测试表
view plaincopy
SQL> create table test_redos as select * from dba_objects where 1=2;

表已创建。

查看当前redo量
view plaincopy
SQL> select * from redo_size;

VALUE
———-
736

插入数据，看结果
view plaincopy
SQL> insert into test_redos select * from dba_objects;

已创建73104行。

SQL> select * from redo_size;

VALUE
———-
8473536

SQL> insert /*+ append */ into test_redos select * from dba_objects;

已创建73100行。

SQL> select * from redo_size;

VALUE
———-
8504856

SQL> select (8473536-736)普通插入,(8504856-8473536) append插入 from dual;

普通插入 APPEND插入
———- ———-
8472800 31320

以上结果说明在非归档模式下，append插入数据产生的redo要少得多。

二、在归档模式下（在数据库和表空间级别为设置force logging的情况下，默认非force logging）：
view plaincopy
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archive1
Oldest online log sequence 114
Next log sequence to archive 116
Current log sequence 116

同上（非归档里面）建立测试表
①：在表为logging的情况下
view plaincopy
SQL> create table test_redos as select * from dba_objects where 1=2;

Table created.

SQL> select * from redo_size;

VALUE
———-
26812

SQL> insert into test_redos select * from dba_objects;

71971 rows created.

SQL> select * from redo_size;

VALUE
———-
8490044

SQL> insert /*+ append */ into test_redos select * from dba_objects;

71971 rows created.

SQL> select * from redo_size;

VALUE
———-
17001396

SQL> select (8490044-26812)普通插入,(17001396-8490044) append插入 from dual;

普通插入 APPEND插入
———- ———-
8463232 8511352

可以看出在归档模式表logging（默认）的情况下，append插入产生的redo量并不会减少。
②：在表nologging的情况下
将表设置为nologging模式
view plaincopy
SQL> alter table test_redos nologging;

Table altered.
继续测试
view plaincopy
SQL> select * from redo_size;

VALUE
———-
8397184

SQL> insert into test_redos select * from dba_objects;

71971 rows created.

SQL> select * from redo_size;

VALUE
———-
16801072

SQL> insert /*+ append */ into test_redos select * from dba_objects;

71971 rows created.

SQL> select * from redo_size;

VALUE
———-
16836516

SQL> select (16801072-8397184)普通插入,(16836516-16801072) append插入 from dual;

普通插入 APPEND插入
———- ———-
8403888 35444

可以看出在表nologging的模式下，append可以减少大量减少redo量的产生。
三、在归档force logging模式下：
改变SCOTT用户的默认表空间为force logging模式
view plaincopy
SQL> select username,default_tablespace from dba_users where username=’SCOTT’;

USERNAME DEFAULT_TABLESPACE
—————————— ——————————
SCOTT USERS
–在数据级别置为force logging模式语句为 alter database force logging;
SQL> alter tablespace users force logging;

Tablespace altered.
继续测试
view plaincopy
SQL> select * from redo_size;

VALUE
———-
25488368

SQL> insert into test_redos select * from dba_objects;

72010 rows created.

SQL> select * from redo_size;

VALUE
———-
33973556

SQL> insert /*+ append */ into test_redos select * from dba_objects;

72010 rows created.

SQL> select * from redo_size;

VALUE
———-
42492396

SQL> select (33973556-25488368)普通插入,(42492396-33973556) append插入 from dual;

普通插入 APPEND插入
———- ———-
8485188 8518840

可以看出在表空间在force logging模式下append不能减少redo量

Redo Redo and Undo 数据技术 | Guang Cai Li | |   redo

_disable_logging对数据库产生的影响

测试下_disable_logging将对数据库产生的影响，由于是隐含参数，所以通过如下方法获取对这个参数的描述：

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%logging';

KSPPINM              KSPPDESC
------------------------------------
_disable_logging     Disable logging

将其改为ture，也就是启用了不记录日志的方式：

SQL> alter system set "_disable_logging"=true scope=both;
System altered.

创建一个，并模拟事务运行，生成大量的redo,

SQL> create table mm tablespace marvener as select * from dba_objects;
Table created.

SQL> insert into mm  select * from dba_objects;
45167 rows created.

SQL> /
 45167 rows created.

此时模拟掉电，shutdown abort关闭数据库：

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2084296 bytes
Variable Size             385876536 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14692352 bytes
Database mounted.
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 81435 change 856029 time 01/30/2012
15:50:39
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/marven/redo01.log'

如上。可以发现数据库无法正常打开，并提示重做日志块头损坏，在告警中可见大量的告警，
即使通过Resetlogs方式打开数据库：

alter system set "_allow_resetlogs_corruption"=true scope=spfile;
shutdown abort
startup

数据库仍然会显然如下告警，并强制关闭实例：

SMON: enabling cache recovery
Mon Jan 30 16:15:41 2012
Errors in file /u01/app/oracle/admin/marven/udump/marven_ora_2900.trc:
ORA-00600: internal error code, arguments: [2662], [0], [855728], [0], [855937], [8388649], [], []
Mon Jan 30 16:15:42 2012
Errors in file /u01/app/oracle/admin/marven/udump/marven_ora_2900.trc:
ORA-00600: internal error code, arguments: [2662], [0], [855728], [0], [855937], [8388649], [], []
Mon Jan 30 16:15:42 2012
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 2900
ORA-1092 signalled during: alter database open resetlogs...
Mon Jan 30 16:16:34 2012

然而可以通过推进Oracle的SCN来解决此类问题:

增进SCN有几种常用方法，这里介绍其中2种:
1.通过immediate trace name方式(在数据库Open状态下)
alter session set events ‘IMMEDIATE trace name ADJUST_SCN level x’;
2.通过10015事件(在数据库无法打开，mount状态下)
alter session set events ‘10015 trace name adjust_scn level x’;
注:10015的推进级别有个计算算法，具体可以参考adjust的官方说明。

alter session set events '10015 trace name adjust_scn level 10';

SQL> alter database open;

Database altered.
MDATA Oracle恢复 Redo Redo and Undo 异常恢复 数据技术 | Guang Cai Li | |   10015 _disable_logging，adjust

ASSM三级位图结构

L1、L2、L3块的作用：–方便查找数据块。

L1中有指向L3的指针，L2有指向L3的指针，L3中有多个数据块的指针和状态。
1、每个L3中，有多个L2的地址（第一个L3是段头）。
2、每个L2中，有多个L1的地址。
3、每个L1中，有多个数据块地址。
Oracle最多支持三级位图。
一级位图用于管理具体数据块的使用。
二级位图块记录了一级位图块的地址。
三级位图块记录了二级位图块的地址。Segment Heade可以管理极大数据量的对象的空间，很难出现另一个三级位图块。

bmp

1.如何查找段头–第一个L3块

BYS@ bys3>create tablespace test2 datafile ‘/u01/oradata/bys3/test22.dbf’ size 10m;
Tablespace created.
BYS@ bys3>create table test2(aa varchar2(10)) tablespace test2;
Table created.
BYS@ bys3>insert into test2 values(789);
1 row created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),dbms_rowid.ROWID_RELATIVE_FNO(rowid) ,aa from test2;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AA
———————————— ———————————— ———-
131 7 789
BYS@ bys3>select header_block,header_file fromdba_segments where segment_name=’TEST2′ and owner=’BYS’;
HEADER_BLOCK HEADER_FILE ——-从这语句找到段头
———— ———–
130 7
BYS@ bys3>select object_id,data_object_id from dba_objects where object_name=’TEST2′ and owner=’BYS’;
OBJECT_ID DATA_OBJECT_ID
———- ————–
23199 23199
BYS@ bys3>alter table test2 allocate extent (size 960k);
Table altered.
BYS@ bys3>alter system dump datafile 7 block 130;
System altered.
BYS@ bys3>select value from v$diag_info where name like ‘De%’;
VALUE
—————————————————————————————————-
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_4215.trc
################

段头–第一个L3块的解读：

Start dump data blocks tsn: 7 file#:7 minblk 130 maxblk 130
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=29360258
BH (0x20fe6d64) file#: 7 rdba: 0x01c00082 (7/130) class: 4 ba: 0x20cb6000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 23199 objn: 23199 tsn: 7 afn: 7 hint: f
hash: [0x2a39d804,0x2a39d804] lru: [0x217f7ccc,0x21befb08]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [0x21befb20,0x22beab34] objaq: [0x2443a408,0x2
1befb28]
st: XCURRENT md: NULL fpin: ‘ktswh03: ktscts’ tch: 2
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
Block dump from disk: –下面的才是物理数据文件中内容
buffer tsn: 7 rdba: 0x01c00082 (7/130)
scn: 0x0000.0082b051 seq: 0x01 flg: 0x04 tail: 0xb0512301
frmt: 0x02 chkval: 0xf0d6 type: 0x23=PAGETABLE SEGMENT HEADER –数据块类型：段头
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB6BC0600 to 0xB6BC2600
B6BC0600 0000A223 01C00082 0082B051 04010000 [#…….Q…….]–B6BC0600 0000A223，这里的23，是数据块中的块头的块类型的信息，与type: 0x23=对应。
Repeat 185 times ——-省略了大部分无关输出
B6BC25F0 00000000 00000000 00000000 B0512301 [………….#Q.]
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 18 #blocks: 384 –有18个区，384个块==16个区-8 个块，2个区-128个块，16*8+2*128=384
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8
高水位的块DBA–0x01c00088 -136号块，是第二个区的第一个块。插入只会插入高水位以下的块< =，以区为单位推进--也可能是区的一半-据说算法很复杂。查询时也会找到高水位，查询高水位下的块。 并发插入时候，高水位的位置会影响限制并发插入，因为只能插入高水位以下的块，如8KBLOCK时；1M区，128个块，超过128个并发会有热块，从而产生buffer busy waits.； 如果是8M的区，会有1024个块，超过1024并发同样会有热块产生。 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : --低高水位，低高水位之下的数据都已经使用了。低高水位和高水位之间，有的数据块已经使用，有的未使用-未格式化。 格式化是在块头加：OJBECT号，数据字典中：dba_objects.DATA_OBJECT_ID Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8 #blocks in seg. hdr's freelists: 0 #blocks below: 5 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x01c00080 Level 1 BMB for Low HWM block: 0x01c00080 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x01c00081 ----二级位图块的DBA，换算为：0000 0001 1100 ，前10位文件号是二进制111-十进制7，16进制81--十进制129.插入时会选择这个L2下的L1的块，直到此L2下的所有L1中的块用完，才会用其它L2--在此处更改L1的DBA。 Last Level 1 BMB: 0x01c00181 Last Level II BMB: 0x01c00081 Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 18 obj#: 23199 flag: 0x10000000 Inc # 0 Extent Map --区地图，段下有几个区，区的起始地址以及包含的块数 ----------------------------------------------------------------- 0x01c00080 length: 8 0x01c00088 length: 8 0x01c00090 length: 8 0x01c00098 length: 8 0x01c000a0 length: 8 0x01c000a8 length: 8 0x01c000b0 length: 8 0x01c000b8 length: 8 0x01c000c0 length: 8 0x01c000c8 length: 8 0x01c000d0 length: 8 0x01c000d8 length: 8 0x01c000e0 length: 8 0x01c000e8 length: 8 0x01c000f0 length: 8 0x01c000f8 length: 8 0x01c00100 length: 128 0x01c00180 length: 128 Auxillary Map --辅助表，区由哪个L1管理及所管理的区-数据块的起始位置DBA--非Metadata块始地址。。辅助区地址可看出哪几个区内的块共用同一个L1，如下： -------------------------------------------------------- Extent 0 : L1 dba: 0x01c00080 Data dba: 0x01c00083 --131，第一个可用的块的地址 Extent 1 : L1 dba: 0x01c00080 Data dba: 0x01c00088 --136，可以看到这个L1和上一个L1相同，一个L1管理了两个区。 Extent 2 : L1 dba: 0x01c00090 Data dba: 0x01c00091 --145 Extent 3 : L1 dba: 0x01c00090 Data dba: 0x01c00098 Extent 4 : L1 dba: 0x01c000a0 Data dba: 0x01c000a1 Extent 5 : L1 dba: 0x01c000a0 Data dba: 0x01c000a8 Extent 6 : L1 dba: 0x01c000b0 Data dba: 0x01c000b1 Extent 7 : L1 dba: 0x01c000b0 Data dba: 0x01c000b8 Extent 8 : L1 dba: 0x01c000c0 Data dba: 0x01c000c1 Extent 9 : L1 dba: 0x01c000c0 Data dba: 0x01c000c8 Extent 10 : L1 dba: 0x01c000d0 Data dba: 0x01c000d1 Extent 11 : L1 dba: 0x01c000d0 Data dba: 0x01c000d8 Extent 12 : L1 dba: 0x01c000e0 Data dba: 0x01c000e1 Extent 13 : L1 dba: 0x01c000e0 Data dba: 0x01c000e8 Extent 14 : L1 dba: 0x01c000f0 Data dba: 0x01c000f1 Extent 15 : L1 dba: 0x01c000f0 Data dba: 0x01c000f8 Extent 16 : L1 dba: 0x01c00100 Data dba: 0x01c00102 --128个块了，L1一个管理 一个区。在8KB BLOCK，8M的区时，一个L1管理可以1024个块，再大的区就未实验过了。 Extent 17 : L1 dba: 0x01c00180 Data dba: 0x01c00182 -------------------------------------------------------- Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x01c00081 --二级位图块的DBA，这里只有一个L2，如果有多个L2，都会在这里显示出来的。 End dump data blocks tsn: 7 file#: 7 minblk 130 maxblk 130 #################################################3 DUMP L2块--根据段头找到L2的DBA BYS@ bys3>alter system dump datafile 7 block 129;
System altered.
BYS@ bys3>select value from v$diag_info where name like ‘De%’;
VALUE
—————————————————————————————————-
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_4982.trc
#######
Start dump data blocks tsn: 7 file#:7 minblk 129 maxblk 129
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=29360257
Block dump from disk:
buffer tsn: 7 rdba: 0x01c00081 (7/129)
scn: 0x0000.0082b04b seq: 0x03 flg: 0x04 tail: 0xb04b2103
frmt: 0x02 chkval: 0xcf4e type: 0x21=SECOND LEVEL BITMAP BLOCK –L2
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB6B43600 to 0xB6B45600
B6B43600 0000A221 01C00081 0082B04B 04030000 [!…….K…….] 0000A221中21与type: 0x21= 对应
Repeat 496 times
B6B455F0 00000000 00000000 00000000 B04B2103 [………….!K.]
Dump of Second Level Bitmap Block
number: 12 nfree: 12 ffree: 0 pdba: 0x01c00082 –父DBA，也就是L3的地址-130号块， number: 12 –L1的总数，nfree: 12–空闲的L1个数，
Inc #: 0 Objd: 23199
opcode:1
xid:
L1 Ranges :–多个L1时，按L1 DBA顺序插入/////???????插入数据时：再根据PID进行HASH，得到一个随机值，根据此值选择L2中的L1–受高水位影响的
——————————————————–
0x01c00080 Free: 5 Inst: 1 —128号块，Free: 5标记L1中可用空间状态
0x01c00090 Free: 5 Inst: 1 –144号块，Free: 0 FULL状态
0x01c000a0 Free: 5 Inst: 1
0x01c000b0 Free: 5 Inst: 1
0x01c000c0 Free: 5 Inst: 1
0x01c000d0 Free: 5 Inst: 1
0x01c000e0 Free: 5 Inst: 1
0x01c000f0 Free: 5 Inst: 1
0x01c00100 Free: 5 Inst: 1
0x01c00101 Free: 5 Inst: 1
0x01c00180 Free: 5 Inst: 1
0x01c00181 Free: 5 Inst: 1

——————————————————–
End dump data blocks tsn: 7 file#: 7 minblk 129 maxblk 129

#####################################

DUMP L3块–根据L2中的 L1 Ranges的找到 第一个L1块的DBA

BYS@ bys3>alter system dump datafile 7 block 128;
System altered.
BYS@ bys3>select value from v$diag_info where name like ‘De%’;
VALUE
—————————————————————————————————-
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_5162.trc
@@@@@@@@@@@@@@@
Start dump data blocks tsn: 7 file#:7 minblk 128 maxblk 128
Block dump from cache:
Dump of buffer cache at level 4 for tsn=7 rdba=29360256
Block dump from disk:
buffer tsn: 7 rdba: 0x01c00080 (7/128)
scn: 0x0000.0082afe2 seq: 0x01 flg: 0x04 tail: 0xafe22001
frmt: 0x02 chkval: 0x9d2d type: 0x20=FIRST LEVEL BITMAP BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB6CBD600 to 0xB6CBF600
B6CBD600 0000A220 01C00080 0082AFE2 04010000 [ ……………] 0000A220的20，表示：type: 0x20=FIRST LEVEL BITMAP BLOCK
B6CBD610 00009D2D 00000000 00000000 00000000 [-……………]
B6CBD620 00000000 00000000 00000000 00000000 […………….]
Repeat 1 times
Repeat 8 times
B6CBD780 00000000 00000000 00000000 55551511 […………..UU]
B6CBD790 00000000 00000000 00000000 00000000 […………….]
Repeat 485 times
B6CBF5F0 00000000 00000000 00000000 AFE22001 […………. ..]
Dump of First Level Bitmap Block
——————————–
nbits : 4 nranges: 2 parent dba: 0x01c00081 poffset: 0 –parent dba: 上一级DBA，L2的地址——129号块
unformatted: 8 total: 16 first useful block: 3
owning instance : 1
instance ownership changed at 01/22/2014 11:22:40
Last successful Search 01/22/2014 11:22:40
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5

Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn: 3.0
Flag: 0x00000001 (-/-/-/-/-/HWM)
Inc #: 0 Objd: 23199
HWM Flag: HWM Set
Highwater:: 0x01c00088 ext#: 0 blk#: 8 ext size: 8 高水位在 0x01c00088 136号块，下一个区的第一个块
#blocks in seg. hdr’s freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
——————————————————–
DBA Ranges : –这个L1管理的数据块的范围
——————————————————–
0x01c00080 Length: 8 Offset: 0
0x01c00088 Length: 8 Offset: 8

0:Metadata 1:Metadata 2:Metadata 3:75-100% free —Metadata 是无数据，这里存放的是L3 L2 L1的块。
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted 13:unformatted 14:unformatted 15:unformatted 可以DUMP 12和5，对比格式化与未格式化的差别。
在L1块中关于数据块的状态有7种格式：Unformat 75-100% 50-75% 25-50% 0-25% FULL Metadata
——————————————————–
End dump data blocks tsn: 7 file#: 7 minblk 128 maxblk 128

转自互联网仅作记载。

Data access tuning Data model Oracle 数据技术 | Guang Cai Li | |   assm bitmap

统计信息误差导致执行计划错误：一例

日前在客户现场对一些sp进程程序调优，中间碰到不少问题

列举其一：

 

select item_code from inv_master where  NVL (COST_FLAG,'N') = 'Y';
 
其中在表inv_master的quota_hours字段创建函数索引
create index idx_inv_master_02 on inv_master(nvl(COST_FLAG,'N') online tablespace idxdata;
 
select count(*) from inv_master;
 
————
636687
 
select count(*) from inv_master where  NVL (COST_FLAG,'N') = 'Y';
 
————
123
 
根据单表选择率这里语句

select item_code from inv_master where  NVL (QUOTA_HOURS,'N') = 'Y';

应该会走indx_inv_master_02这个索引按照 range scan方式扫描，但是在客户的库中还是会全表扫描，由于这个sql在sp中要被反复执行3万多次，这个是一个非常严重的IO问题，

在现场首先做了一次

analyze table INV_MASTER compute statistics;

analyze index IDX_INV_MASTER_02 compute statistics; 
sql还是不走索引，没办法只好做一次10053
——————————————————————————————————–
oradebug setmypid
oradebug unlimit
oradebug event 10053 trace name context forever,level 10
select item_code from inv_master where  NVL (QUOTA_HOURS,'N') = 'Y';
oradebug event 10053 trace name context off;
oradebug tracefile_name
 
TRC文件底部显示:
Table Stats::
  Table: INV_MASTER  Alias: INV_MASTER
    #Rows: 63487  #Blks:  3268  AvgRowLen:  364.00
Index Stats::
  Index: IDX_INV_MASTER_BFLAG  Col#: 68
    LVLS: 1  #LB: 70  #DK: 2  LB/K: 35.00  DB/K: 1451.00  CLUF: 2903.00
  Index: IDX_INV_MASTER_COOP_FLAG  Col#: 169
    LVLS: 1  #LB: 116  #DK: 2  LB/K: 58.00  DB/K: 2229.00  CLUF: 4458.00
  Index: IDX_INV_MASTER_COST_FLAG  Col#: 167
    LVLS: 1  #LB: 116  #DK: 2  LB/K: 58.00  DB/K: 1618.00  CLUF: 3236.00
  Index: IDX_INV_MASTER_FLAG_FC  Col#: 168 167
    LVLS: 1  #LB: 133  #DK: 4  LB/K: 33.00  DB/K: 1098.00  CLUF: 4395.00
  Index: IDX_INV_MASTER_FLAG_PHFL  Col#: 168
    LVLS: 1  #LB: 116  #DK: 3  LB/K: 38.00  DB/K: 1461.00  CLUF: 4384.00
  Index: PK_INV_MASTER  Col#: 3
    LVLS: 1  #LB: 236  #DK: 63487  LB/K: 1.00  DB/K: 1.00  CLUF: 28932.00
***************************************
SINGLE TABLE ACCESS PATH
  —————————————–
  BEGIN Single Table Cardinality Estimation
  —————————————–
  Column (#167): SYS_NC00167$(NVARCHAR2)
    AvgLen: 1.00 NDV: 2 Nulls: 0 Density: 0.5
  Column (#56): COST_FLAG(NVARCHAR2)
    AvgLen: 1.00 NDV: 2 Nulls: 16 Density: 0.5
  Table: INV_MASTER  Alias: INV_MASTER
    Card: Original: 63487  Rounded: 31751  Computed: 31751.50  Non Adjusted: 31751.50
  —————————————–
  END   Single Table Cardinality Estimation
  —————————————–
  Access Path: TableScan
    Cost:  725.29  Resp: 725.29  Degree: 0
      Cost_io: 717.00  Cost_cpu: 107074906
      Resp_io: 717.00  Resp_cpu: 107074906
kkofmx: index filter:NVL("INV_MASTER"."COST_FLAG",U'N')=U'N'
  Access Path: index (AllEqRange)
    Index: IDX_INV_MASTER_COST_FLAG
    resc_io: 1678.00  resc_cpu: 57991026
    ix_sel: 0.50013  ix_sel_with_filters: 0.50013
    Cost: 1682.49  Resp: 1682.49  Degree: 1
  Access Path: index (FullScan)
    Index: IDX_INV_MASTER_FLAG_FC
    resc_io: 2333.00  resc_cpu: 69001720
    ix_sel: 1  ix_sel_with_filters: 0.50013
    Cost: 2338.69  Resp: 2338.69  Degree: 1
 
CBO在选择执行计划时候是根据统计信息来选择的，在这里可以发现统计信息中走这个索引的消耗为1682.49，走全表扫才717的消耗，
可以看出这里的索引统计信息肯定有问题，可以从dba_segements可以计算出索引idx_inv_master_cost_flag总的块为10多个，和这里明显不符，重新收集统计信息后，全表扫改为INDEX RANGE SCAN
 
 
 
over.
exec dbms_stats.gather_table_stats('RS10','INV_MASTER');
Data access tuning performance tuning | Guang Cai Li |