ANBOB™

无论是行链接还是行迁移，都会影响数据库的性能。Oracle在读取这样的记录的时候，Oracle会扫描多个数据块，执行更多的I/O。

下面做个行链接的测试
在一个8k blocksize 的tablespace 上

anbob@ANBOB> create table testchain(id int ,a char(2000),b char(2000),c char(2000) , d varchar2
  2  (4000));

Table created.

anbob@ANBOB> insert into testchain(id,a,b,c) values(1,'a','b','c');

1 row created.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> analyze table testchain compute statistics;

Table analyzed.

anbob@ANBOB> select table_name,blocks,num_rows,chain_cnt from user_tables where table_name='testchain';

TABLE_NAME               BLOCKS   NUM_ROWS  CHAIN_CNT
-------------------- ---------- ---------- ----------
testchain                            5          1          0

anbob@ANBOB> update testchain set d=rpad('d',4000,' ');

1 row updated.

anbob@ANBOB> commit;

Commit complete.

anbob@ANBOB> analyze table testchain compute statistics;

Table analyzed.

anbob@ANBOB> select table_name,blocks,num_rows,chain_cnt from user_tables where table_name='testchain';

TABLE_NAME               BLOCKS   NUM_ROWS  CHAIN_CNT
-------------------- ---------- ---------- ----------
testchain                            5          1          1

Use the ANALYZE statement to collect statistics, for example, to:

1.Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

2.Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

3.Identify migrated and chained rows of a table or cluster.

note:Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.
You must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

1.To use the VALIDATE or LIST CHAINED ROWS clauses
2.To collect information on freelist blocks

指出不让用analyze table compute 再收集信息，只是为了向后兼容，现在用dbms_stats代替，dbms_stats真的可以么？现在做个测试

anbob@ANBOB> EXEC DBMS_STATS.DELETE_TABLE_STATS(USER,'testchain');

PL/SQL procedure successfully completed.

anbob@ANBOB> select table_name,blocks,num_rows,chain_cnt from user_tables where table_name='testchain';

TABLE_NAME               BLOCKS   NUM_ROWS  CHAIN_CNT
-------------------- ---------- ---------- ----------
testchain

anbob@ANBOB> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'testchain');

PL/SQL procedure successfully completed.

anbob@ANBOB> select table_name,blocks,num_rows,chain_cnt from user_tables where table_name='testchain';

TABLE_NAME               BLOCKS   NUM_ROWS  CHAIN_CNT
-------------------- ---------- ---------- ----------
testchain                            5          1          0

anbob@ANBOB> analyze table testchain compute statistics;

Table analyzed.

anbob@ANBOB> select table_name,blocks,num_rows,chain_cnt from user_tables where table_name='testchain';

TABLE_NAME               BLOCKS   NUM_ROWS  CHAIN_CNT
-------------------- ---------- ---------- ----------
testchain                            5          1          1

显然dbms_stats 没有收集到chain_cnt的信息，chain_cnt 是什么呢？Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID，也就是行链接，oracle是行存储的，一个块行存不下这一行，就会物理的存在另一个或多个block上，并在块保留双向指针进行链接。这样查询时就会增加IO降低性能，有时不能避免，但是如果列可以分开就可以考虑别建新表减少行长度

可以用analyze table LIST CHAINED ROWS 收集行链接的数据,收集的话要先建一个保存收集的表chained_rows,运行两个建表脚本之一，两者区别rowid和utlchain.sql不能用于索引组织表，因为我们上面建的是堆表，所以可任选。

LIST CHAINED ROWS lets you identify migrated and chained rows of the analyzed table or cluster. You cannot use this clause when analyzing an index.

In the INTO clause, specify a table into which Oracle Database lists the migrated and chained rows. If you omit schema, then the database assumes the chained-rows table is in your own schema. If you omit this clause altogether, then the database assumes that the table is named CHAINED_ROWS. The chained-rows table must be on your local database.

You can create the CHAINED_ROWS table using one of these scripts:

1.UTLCHAIN.SQL uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
2.UTLCHN1.SQL uses universal rowids, so it can accommodate rows from both conventional and index-organized tables.

If you create your own chained-rows table, then it must follow the format prescribed by one of these two scripts.

If you are analyzing index-organized tables based on primary keys (rather than universal rowids), then you must create a separate chained-rows table for each index-organized table to accommodate its primary-key storage. Use the SQL scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the BUILD_CHAIN_ROWS_TABLE procedure, and then execute this procedure to create an IOT_CHAINED_ROWS table for each such index-organized table.

我们创建收集信息表
anbob@ANBOB> @?/rdbms/admin/utlchain.sql
Table created.

其实脚本很简单就是一个建表语句，比较一下两个也就是head_rowid 类型不一样，
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);

create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid urowid,
analyze_timestamp date
);

我们再insert 一条好区别，插入一个一行能放的下的

anbob@ANBOB> insert into testchain(id,a,b,c) values(2,'a','b','c');

1 row created.

anbob@ANBOB> commit;
anbob@ANBOB> truncate table chained_rows;

Table truncated.

anbob@ANBOB> analyze table testchain list chained rows;

Table analyzed.

anbob@ANBOB> select table_name,head_rowid from chained_rows;

TABLE_NAME           HEAD_ROWID
-------------------- ------------------
testchain                   AAAMrjAAGAAAABdAAA

anbob@ANBOB> select rowid r,id from testchain;

R                          ID
------------------ ----------
AAAMrjAAGAAAABdAAA          1
AAAMrjAAGAAAABfAAA          2

这下看到了吧，就是id为1的行,这个收集不包含统计信息表数据

在数据库层面行链接与行迁移是同样一类，所以上面收集命令也会收集到行迁移数据行

解决方法，对于行迁移可以根据rowid，转到临时表再delete,insert回原表
对于行链接可以考虑把改表move 表更大的block size 的tablespace上

打赏

微信扫一扫，打赏作者吧～