Chinaunix首页 | 论坛 | 博客
  • 博客访问: 503142
  • 博文数量: 65
  • 博客积分: 1158
  • 博客等级: 少尉
  • 技术积分: 1261
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-18 22:07
文章分类

全部博文(65)

文章存档

2016年(1)

2014年(2)

2013年(9)

2012年(53)

分类: Oracle

2013-05-25 12:51:23

有时候对表进行了大量的更新操作或者在表中增加了一列含有默认值的列,那么就有可能出现表的行迁移情况,这种情况造成了
对数据访问的过多的逻辑读

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行。
阅读(1470) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~