有时候对表进行了大量的更新操作或者在表中增加了一列含有默认值的列，那么就有可能出现表的行迁移情况，这种情况造成了
对数据访问的过多的逻辑读
HR@orcl>create table employee_test as select * from employees;
表已创建。
HR@orcl>desc employee_test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@orcl>alter table employee_test modify first_name varchar2(2000);
表已更改。
HR@orcl>alter table employee_test modify last_name varchar2(2000);
表已更改。
HR@orcl>alter table employee_test modify phone_number varchar2(2000);
表已更改。
HR@orcl>update employee_test set first_name = lpad(first_name,4000,'*'),last_name = lpad(last_name,4
000,'*'),phone_number = lpad(phone_number,4000,'*");
ERROR:
ORA-01756: 引号内的字符串没有正确结束
HR@orcl>update employee_test set first_name = lpad(first_name,2000,'*'),last_name = lpad(last_name,2000,'*'),phone_number = lpad(phone_number,2000,'*');
已更新107行。
HR@orcl>commit;
提交完成。
HR@orcl>create table employee_test_bak as select * from employee_test;
表已创建。
HR@orcl>conn / as sysdba
已连接。
SYS@orcl>@ D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sql;
表已创建。
SYS@orcl>analyze table hr.employee_test list chained rows into chained_rows;
表已分析。
SYS@orcl>analyze table hr.employee_test_bak list chained rows into chained_rows;
表已分析。
SYS@orcl>select count(*) from chained_rows where table_name = 'EMPLOYEE_TEST';
COUNT(*)
----------
106
SYS@orcl>select count(*) from chained_rows where table_name = 'EMPLOYEE_TEST_BAK';
COUNT(*)
----------
0
SYS@orcl>set linesize 1000
SYS@orcl>alter system flush shared_pool;
系统已更改。
SYS@orcl>alter system flush buffer_cache;
系统已更改。
SYS@orcl>select * from hr.employee_test where employee_id = 100;
执行计划
----------------------------------------------------------
Plan hash value: 3391947858
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6202 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_TEST | 2 | 6202 | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
539 recursive calls
0 db block gets
256 consistent gets
123 physical reads
0 redo size
7066 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@orcl>select * from hr.employee_test_bak where employee_id = 100;
执行计划
----------------------------------------------------------
Plan hash value: 2853427965
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6202 | 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_TEST_BAK | 2 | 6202 | 27 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
501 recursive calls
0 db block gets
240 consistent gets
114 physical reads
0 redo size
7066 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@orcl>select * from hr.employee_test where employee_id = 100;
执行计划
----------------------------------------------------------
Plan hash value: 3391947858
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6202 | 26 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_TEST | 2 | 6202 | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
114 consistent gets
0 physical reads
0 redo size
7066 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@orcl>select * from hr.employee_test_bak where employee_id = 100;
执行计划
----------------------------------------------------------
Plan hash value: 2853427965
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 6202 | 27 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEE_TEST_BAK | 2 | 6202 | 27 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
7066 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--再看读取全表的情况
SYS@orcl>alter system flush shared_pool;
系统已更改。
SYS@orcl>alter system flush buffer_cache;
系统已更改。
SYS@orcl>select * from hr.employee_test;
已选择107行。
执行计划
----------------------------------------------------------
Plan hash value: 3391947858
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 302K| 26 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE_TEST | 100 | 302K| 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
1053 recursive calls
0 db block gets
341 consistent gets
134 physical reads
0 redo size
652970 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
107 rows processed
SYS@orcl>select * from hr.employee_test_bak;
已选择107行。
执行计划
----------------------------------------------------------
Plan hash value: 2853427965
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 296K| 27 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEE_TEST_BAK | 98 | 296K| 27 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
501 recursive calls
0 db block gets
240 consistent gets
114 physical reads
0 redo size
652334 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
107 rows processed
全表逻辑读、物理读都减少了比较多，这还只是106条数据发生迁移
所以如果怀疑某些表存在行迁移的情况，那么我们可以制定相应的脚本，在系统不那么忙的时候收集他的
信息到chained_rows中
HR@orcl>select 'analyze table ' || table_name || ' list chained rows into chained_rows;' from user_tables;
'ANALYZETABLE'||TABLE_NAME||'LISTCHAINEDROWSINTOCHAINED_ROWS;'
---------------------------------------------------------------------------------
analyze table REGIONS list chained rows into chained_rows;
analyze table LOCATIONS list chained rows into chained_rows;
analyze table JOBS list chained rows into chained_rows;
analyze table EMPLOYEE_TEST_BAK list chained rows into chained_rows;
analyze table COUNTRIES list chained rows into chained_rows;
analyze table DEPARTMENTS list chained rows into chained_rows;
analyze table EMPLOYEES list chained rows into chained_rows;
analyze table EMPLOYEE_TEST list chained rows into chained_rows;
analyze table JOB_HISTORY list chained rows into chained_rows;
analyze table EMPLOYEE_TEST_BAK2 list chained rows into chained_rows;
已选择10行。
阅读(1769) | 评论(0) | 转发(0) |
