ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 行链接的模拟及消除示例

oracle 行链接的模拟及消除示例

原创 Linux操作系统 作者:xpj0515 时间:2011-04-25 13:13:26 0 删除 编辑
Migration和Chaining

    1)如果一行的数据太大以至一个单独的block容纳不下,会产生两种现象:
    A、Chaining:行数据太大以至一个空block容纳不下,oracle会将这一行的数据存放在一个或多个block 组成的block chain中,insert、update都可能导致这个问题,在某些情况下row chaining是不能避免的。
    B、Migration:一次update操作可能导致行数据增大,以至它所在的block容纳不下,oracle server会去寻找一个有足够自由空间容纳整行数据的block,如果这样的block存在,oracle server把整行移到新的block,在原位置保存一个指向新存放位置的镜像行,镜像行的rowid和原来的rowid一致。
    Chaining、Migration的弊端:insert、update的性能降低,索引查询增加了IO次数。

    2)检测migration和chaining:
    Analyize table table_name compute statistics;
    Select num_rows,chain_cnt from dba_tables where table_name=’...’;
    查询镜像行:
    Analyize table table_name list chained rows;
    Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
    产生Migration的原因可能是由于PCTFREE设置的太低以至没有保留足够的空间用于更新。
    可以通过增加PCTFREE的值避免行镜像产生。

    3)消除镜像行的步骤:
    运行analyize table ... list chained rows;
    复制镜像行到另一个表tmp;
    从源表中删除这些行;
    从tmp中将这些行插回到源表中。
    脚本:
    /* Get the name of the table with migrated rows */
    accept table_name prompt ’Enter the name of the table with migrated rows: ’
    /* Clean up from last execution */
    set echo off
    drop table migrated_rows;
    drop table chained_rows;
    /* Create the CHAINED_ROWS table */
    @?/rdbms/admin/utlchain
    set echo on
    spool fix_mig
    /* List the chained & migrated rows */
    analyze table &table_name list chained rows;
    /* Copy the chained/migrated rows to another table */
    create table migrated_rows as
    select orig.* from &table_name orig, chained_rows cr
    where orig.rowid = cr.head_rowid
    and cr.table_name = upper(’&table_name’);
    /* Delete the chained/migrated rows from the original table */
    delete from &table_name
    where rowid in ( select head_rowid from chained_rows );
    /* Copy the chained/migrated rows back into the original table */
    insert into &table_name select * from migrated_rows;
    spool off
    使用这个脚本时,必须将涉及到的外键约束去掉。
=============================================
                                                                            示例
=============================================

SYS@primary/2011-04-25 12:03:34>create table table_2 tablespace assm  as select rownum id,mod(rownum,20) t1,mod(rownum,1000) t2,trunc(dbms_random.normal*10000)+5432 as t3,dbms_random.string('U',50) as pad from dual connect by level<=100000;

Table created.

Elapsed: 00:00:18.02

SYS@primary/2011-04-25 12:04:32>set linesize 60
SYS@primary/2011-04-25 12:04:37>desc table_2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 T1                                     NUMBER
 T2                                     NUMBER
 T3                                     NUMBER
 PAD                                    VARCHAR2(4000)

set linesize 220
col owner for a8
col table_name for a8 heading "NAME"
col num_rows for 999999
col blocks for 999999
col empty_blocks for 99999 heading "EMPTY"
col avg_row_len for 99999
col row_movement for a8
col cache for a5
col chain_cnt for 99999
col last_analyzed for a20
col partitioned for a5 heading "PART"
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
  5  /
Enter value for tablename: table_2

OWNER    NAME        ROWS  BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- -------- ------- ------- ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2   100000    1033          70    119         0 2011-04-25 12:05:05  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.03


set linesize 200
col owner for a10
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
col file_id for 9999
col extent_id for 9999
col block_id for 9999999
col blocks for 99999
select owner,segment_name,segment_type,
tablespace_name,file_id,extent_id,block_id,bytes,blocks
from dba_extents
where  segment_name=upper('&seg_name')
  5  /
Enter value for seg_name: table_2

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE FILE_ID EXTENT_ID BLOCK_ID      BYTES BLOCKS
---------- --------------- ---------- ---------- ------- --------- -------- ---------- ------
SYS        TABLE_2         TABLE      ASSM             6         0     1104      65536      8
SYS        TABLE_2         TABLE      ASSM             6         1     1112      65536      8
SYS        TABLE_2         TABLE      ASSM             6         2     1120      65536      8
SYS        TABLE_2         TABLE      ASSM             6         3     1128      65536      8
SYS        TABLE_2         TABLE      ASSM             6         4     1136      65536      8
SYS        TABLE_2         TABLE      ASSM             6         5     1144      65536      8
SYS        TABLE_2         TABLE      ASSM             6         6     1152      65536      8
SYS        TABLE_2         TABLE      ASSM             6         7     1160      65536      8
SYS        TABLE_2         TABLE      ASSM             6         8     1168      65536      8
SYS        TABLE_2         TABLE      ASSM             6         9     1176      65536      8
SYS        TABLE_2         TABLE      ASSM             6        10     1184      65536      8
SYS        TABLE_2         TABLE      ASSM             6        11     1192      65536      8
SYS        TABLE_2         TABLE      ASSM             6        12     1200      65536      8
SYS        TABLE_2         TABLE      ASSM             6        13     1208      65536      8
SYS        TABLE_2         TABLE      ASSM             6        14     1216      65536      8
SYS        TABLE_2         TABLE      ASSM             6        15     1224      65536      8
SYS        TABLE_2         TABLE      ASSM             6        16     1280    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        17     1408    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        18     1536    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        19     1664    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        20     1792    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        21     1920    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        22     2048    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        23     2176    1048576    128

24 rows selected.


SYS@primary/2011-04-25 12:15:23>desc chained_rows
ERROR:
ORA-04043: object chained_rows does not exist


SYS@primary/2011-04-25 12:16:16>@$ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 12:17:19>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 12:18:20>select * from chained_rows where table_name=upper('table_2');

no rows selected

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 12:20:09>create index ind__table_2_pad on table_2 (pad);

Index created.

Elapsed: 00:00:01.23

SYS@primary/2011-04-25 12:20:16>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        11

1 row selected.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 12:23:39>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    11 |   594 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')


SYS@primary/2011-04-25 12:23:41>select /*+ full(table_2)*/ count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        11

1 row selected.

Elapsed: 00:00:00.04
SYS@primary/2011-04-25 12:24:53>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  23bsz4wjw3hjs, child number 0
-------------------------------------
select /*+ full(table_2)*/ count(*) from table_2 where id between 10000
and 20000 and pad like 'KA%'

Plan hash value: 1055150476

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |   284 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE_2 |    11 |   594 |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "PAD" LIKE 'KA%' AND "ID">=10000))

SYS@primary/2011-04-25 12:24:56>update table_2 set pad=dbms_random.string('U',300) where id between 10000 and 20000 ;

10001 rows updated.

Elapsed: 00:00:12.36

SYS@primary/2011-04-25 12:27:55>analyze table table_2 compute statistics;

Table analyzed.

Elapsed: 00:00:01.94
set linesize 220
col owner for a8
col table_name for a8 heading "NAME"
col num_rows for 999999
col blocks for 999999
col empty_blocks for 99999 heading "EMPTY"
col avg_row_len for 99999
col row_movement for a8
col cache for a5
col chain_cnt for 99999
col last_analyzed for a20
col partitioned for a5 heading "PART"
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
 /
Enter value for tablename: table_2

OWNER    NAME        ROWS  BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- -------- ------- ------- ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2   100000    1530          96      6      7783 2011-04-25 12:28:36  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.00

set linesize 200
col owner for a10
col segment_name for a15
col segment_type for a10
col tablespace_name for a10
col file_id for 9999
col extent_id for 9999
col block_id for 9999999
col blocks for 99999
select owner,segment_name,segment_type,
tablespace_name,file_id,extent_id,block_id,bytes,blocks
from dba_extents
where  segment_name=upper('&seg_name')
 /
Enter value for seg_name: table_2

OWNER      SEGMENT_NAME    SEGMENT_TY TABLESPACE FILE_ID EXTENT_ID BLOCK_ID      BYTES BLOCKS
---------- --------------- ---------- ---------- ------- --------- -------- ---------- ------
SYS        TABLE_2         TABLE      ASSM             6         0     1104      65536      8
SYS        TABLE_2         TABLE      ASSM             6         1     1112      65536      8
SYS        TABLE_2         TABLE      ASSM             6         2     1120      65536      8
SYS        TABLE_2         TABLE      ASSM             6         3     1128      65536      8
SYS        TABLE_2         TABLE      ASSM             6         4     1136      65536      8
SYS        TABLE_2         TABLE      ASSM             6         5     1144      65536      8
SYS        TABLE_2         TABLE      ASSM             6         6     1152      65536      8
SYS        TABLE_2         TABLE      ASSM             6         7     1160      65536      8
SYS        TABLE_2         TABLE      ASSM             6         8     1168      65536      8
SYS        TABLE_2         TABLE      ASSM             6         9     1176      65536      8
SYS        TABLE_2         TABLE      ASSM             6        10     1184      65536      8
SYS        TABLE_2         TABLE      ASSM             6        11     1192      65536      8
SYS        TABLE_2         TABLE      ASSM             6        12     1200      65536      8
SYS        TABLE_2         TABLE      ASSM             6        13     1208      65536      8
SYS        TABLE_2         TABLE      ASSM             6        14     1216      65536      8
SYS        TABLE_2         TABLE      ASSM             6        15     1224      65536      8
SYS        TABLE_2         TABLE      ASSM             6        16     1280    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        17     1408    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        18     1536    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        19     1664    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        20     1792    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        21     1920    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        22     2048    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        23     2176    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        24     2304    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        25     2432    1048576    128
SYS        TABLE_2         TABLE      ASSM             6        26     2560    1048576    128

27 rows selected.

Elapsed: 00:00:00.01


set linesize 150
undefine table_name
col owner_name for a8
col table_name for a15
col head_rowid for a25
col timestamp for a25
select owner_name,table_name,
head_rowid,analyze_timestamp from chained_rows where table_name
=upper('&table_name')
  4  /
Enter value for table_name: table_2


SYS      TABLE_2         AAAOoeAAGAAAAVTABR        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABT        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABU        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABV        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABW        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABY        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABZ        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABa        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABb        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABd        2011-04-25 12:31:28
SYS      TABLE_2         AAAOoeAAGAAAAVTABe        2011-04-25 12:31:28

7783 rows selected.

Elapsed: 00:00:01.18

SYS@primary/2011-04-25 12:38:35>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.04
SYS@primary/2011-04-25 12:39:20>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    19 |  1520 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')

SYS@primary/2011-04-25 12:39:32>select /*+ full(table_2)*/ count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.07
SYS@primary/2011-04-25 12:43:50>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  23bsz4wjw3hjs, child number 0
-------------------------------------
select /*+ full(table_2)*/ count(*) from table_2 where id between 10000
and 20000 and pad like 'KA%'

Plan hash value: 1055150476

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |       |       |   418 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS FULL| TABLE_2 |    19 |  1520 |   418   (1)| 00:00:06 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "PAD" LIKE 'KA%' AND "ID">=10000))


SYS@primary/2011-04-25 12:43:53>create table migration as select table_2.*  from table_2 ,chained_rows where table_2.rowid=chained_rows.head_rowid;

Table created.

Elapsed: 00:00:00.43
SYS@primary/2011-04-25 12:51:43>select count(*) from migration;

  COUNT(*)
----------
      7783

1 row selected.

Elapsed: 00:00:00.01


SYS@primary/2011-04-25 12:55:13>delete from  table_2 where rowid in( select head_rowid from chained_rows);

7783 rows deleted.

Elapsed: 00:00:01.15

SYS@primary/2011-04-25 12:57:54>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.05
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
where table_name = upper('&tablename')
  5   /
Enter value for tablename: table_2

OWNER    NAME               ROWS BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- --------------- ------- ------ ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2          100000   1530          96      6      7783 2011-04-25 12:28:36  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 12:58:29>select count(*) from table_2;

  COUNT(*)
----------
     92217

1 row selected.

Elapsed: 00:00:00.01

SYS@primary/2011-04-25 12:59:03>insert into table_2 select * from migration;

7783 rows created.

Elapsed: 00:00:01.00
SYS@primary/2011-04-25 12:59:58>select count(*) from table_2;

  COUNT(*)
----------
    100000

1 row selected.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 13:00:02>commit;

Commit complete.

Elapsed: 00:00:00.00

SYS@primary/2011-04-25 13:00:54>analyze table table_2 list chained rows;

Table analyzed.

Elapsed: 00:00:00.05
SYS@primary/2011-04-25 13:01:16>analyze table table_2 compute statistics;

Table analyzed.

Elapsed: 00:00:01.82
select owner,table_name,num_rows,blocks,avg_row_len,empty_blocks,
chain_cnt,last_analyzed,row_movement,cache,partitioned
from dba_tables
  4  where table_name = upper('&tablename')
  5  /
Enter value for tablename: table_2

OWNER    NAME               ROWS BLOCKS AVG_ROW_LEN  EMPTY CHAIN_CNT LAST_ANALYZED        ROW_MOVE CACHE PART
-------- --------------- ------- ------ ----------- ------ --------- -------------------- -------- ----- -----
SYS      TABLE_2          100000   1530          96      6         0 2011-04-25 13:01:26  DISABLED     N NO

1 row selected.

Elapsed: 00:00:00.04

SYS@primary/2011-04-25 13:03:09>select count(*) from table_2 where id between 10000 and 20000 and pad like 'KA%';

  COUNT(*)
----------
        19

1 row selected.

Elapsed: 00:00:00.01
SYS@primary/2011-04-25 13:03:31>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7ut6n7azbaddb, child number 0
-------------------------------------
select count(*) from table_2 where id between 10000 and 20000 and pad
like 'KA%'

Plan hash value: 2092335737

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |       |    20 (100)|          |
|   1 |  SORT AGGREGATE              |                  |     1 |    80 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TABLE_2          |    19 |  1520 |    20   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND__TABLE_2_PAD |    17 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("ID"<=20000 AND "ID">=10000))
   3 - access("PAD" LIKE 'KA%')
       filter("PAD" LIKE 'KA%')


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24890594/viewspace-693652/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    55210