oracle如何批量查询行迁移

oracle如何批量查询行迁移

 

总结：

       1．行迁移消耗更多的资源导致性能下降，尽量避免行迁移

2．Oracle找出批量行迁移的表的方法：

              chained_rows

       3．解决行迁移的方法：数据重建

 

chained_rows                 

SQL> @?/rdbms/admin/utlchain.sql <=sys

SQL> grant select,insert,delete on sys.chained_rowsto doudou; <=sys;分析doudou用户下的表，所以要给doudou用户授权

select 'analyze table '||table_name||' list chainedrows into sys.chained_rows ;' from user_tables; <=生成分析并插入SQL，执行即可

select table_name,count(table_name) fromsys.chained_rows group by table_name; <=查看行迁移的表和行数

 

 

建议实验表

SQL> create table doudou as select * fromscott.emp;

 

Table created.

 

SQL> desc doudou

 Name                                     Null?    Type

 ------------------------------------------------- ----------------------------

 EMPNO                                             NUMBER(4)

 ENAME                                             VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                               NUMBER(4)

 HIREDATE                                           DATE

 SAL                                               NUMBER(7,2)

 COMM                                              NUMBER(7,2)

 DEPTNO                                            NUMBER(2)

 

为制作行迁移做准备

SQL> alter table doudou modify ENAMEvarchar2(2000);

 

Table altered.

 

SQL> alter table doudou modify JOB varchar2(2000);

 

Table altered.

 

开启autotracereport

SQL> set autot on

SQL> select * from doudou;

Execution Plan

----------------------------------------------------------

Plan hash value: 845489848

 

----------------------------------------------------------------------------

| Id  |Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECTSTATEMENT  |        |   14 | 29092 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DOUDOU |    14 | 29092 |     3  (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Note

-----

   - dynamicsampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

       1454  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

 

制作行迁移

把10个字节左右的字节扩大到2000个字节，并填满数据，这必然导致原先大量行迁移产生

SQL> update doudou setename=LPAD('1',2000,'*'),job=LPAD('1',2000,'*');

 

14 rows updated.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 845489848

 

----------------------------------------------------------------------------

| Id  |Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECTSTATEMENT  |        |   14 | 29092 |     8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| DOUDOU |    14 | 29092 |     8  (0)| 00:00:01 |

----------------------------------------------------------------------------

 

Note

-----

   - dynamicsampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

      57660  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         14  rows processed

 

分析2次select * from doudou;

Select * from doudou;	consistent gets	cost
没有行迁移	4	3
有行迁移	24	8

 

显然，行迁移带来更多的性能消耗！所以我们应该尽量的减少行迁移！

 

如果发现行迁移呢？

 

使用utlchain.sql创建chained_rows相关表

SQL> conn /as sysdba

Connected.

SQL> @?/rdbms/admin/utlchain.sql

 

Table created.

 

使用下面的sql，分析并把表的相关信息插入chained_rows

select'analyze table '||table_name||' list chained rows into sys.chained_rows ;' fromuser_tables;

select table_name,count(table_name) fromsys.chained_rows group by table_name;

 

授予doudou用户对chained_rows权限

SQL>  grantselect,insert,delete on sys.chained_rows to doudou;

 

Grant succeeded.

 

SQL> select table_name,count(table_name) fromsys.chained_rows group by table_name;

 

TABLE_NAME                     COUNT(TABLE_NAME)

------------------------------ -----------------

DOUDOU                                        13

 

解决行迁移的方法：重构数据

SQL> drop table doudou purge;

 

Table dropped.

 

SQL> create table doudou as select * fromscott.emp;

 

Table created.

 

 

参考：《收获，不止ORACLE》

 这本书，适合做事思路混乱的人看，培养成一种学习ORACLE的思维！让你清晰的认识到自己学习中方法的不足！

       继续参读……

Chained and Migrated Rows

http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#BABEEAAE