行链接消除测试
来源:互联网 发布:tv远程控制软件 编辑:程序博客网 时间:2024/06/10 17:32
上次博文测试了行迁移的产生以及如何消除,这次我们再来测试一下行链接的产生以及如何消除。
通常,产生行链接是因为当一行数据大于一个数据块,则ORACLE会同时分配两个数据块,并在第一个块上登记第二个块的地址,从而形成行链接。
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 10月 27 14:11:44 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ora10g> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 331350240 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> conn zlm/zlm
Connected.
--创建测试表EMPLOYEES及其索引
ZLM@ora10g> DROP TABLE EMPLOYEES PURGE;
Table dropped.
ZLM@ora10g> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;
Table created.
ZLM@ora10g> desc EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
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)
ZLM@ora10g> create index idx_emp_id on employees(employee_id);
Index created.
--修改表结构扩大字段
ZLM@ora10g> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify EMAIL VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000);
Table altered.
--填充相关字段(使其产生行链接)
ZLM@ora10g> UPDATE EMPLOYEES
SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'),
PHONE_NUMBER = LPAD('1', 2000, '*');
107 rows updated.
ZLM@ora10g> COMMIT;
Commit complete.
--创建chaind_rows表来观察行链接
ZLM@ora10g> set autotrace off
ZLM@ora10g> drop table chained_rows purge;
Table dropped.
ZLM@ora10g> @?/rdbms/admin/utlchain.sql
Table created.
ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES';
COUNT(*)
----------
107
此时可以看到,这107行数据都产生了行链接。
--尝试使用消除行迁移的方法来消除行链接
ZLM@ora10g> drop table EMPLOYEES_TMP;
Table dropped.
ZLM@ora10g> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Table created.
ZLM@ora10g> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);
107 rows deleted.
ZLM@ora10g> Insert into EMPLOYEES select * from EMPLOYEES_TMP;
107 rows created.
ZLM@ora10g> delete from chained_rows ;
107 rows deleted.
ZLM@ora10g> commit;
Commit complete.
ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES';
COUNT(*)
----------
107
与消除行迁移不同,使用以上的方法,仍然无法来去除行链接,再重新插入数据后,这107行数据仍然产生了行链接。
ZLM@ora10g> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------
/u01/app/oracle/oradata/ora10g/undotbs01.dbf
/u01/app/oracle/oradata/ora10g/zlm01.dbf
/u01/app/oracle/oradata/ora10g/example01.dbf
/u01/app/oracle/oradata/ora10g/users01.dbf
/u01/app/oracle/oradata/ora10g/sysaux01.dbf
/u01/app/oracle/oradata/ora10g/system01.dbf
6 rows selected.
create tablespace TBS_32k
blocksize 32K
datafile '/u01/app/oracle/oradata/ora10g/TBS_32K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
create tablespace TBS_32k
*
ERROR at line 1:
ORA-00382: 32768 not a valid block size, valid range [2048..16384]
ORA-00382提示我的10g版本不支持32k的block size,只允许2k..16k之间的大小。
create tablespace TBS_16k
blocksize 16k
datafile '/u01/app/oracle/oradata/ora10g/TBS_16K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
create tablespace TBS_16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
由于没有配置db_nk_block_size参数,因此不允许创建非标准block size的表空间。
ZLM@ora10g> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
ZLM@ora10g> alter system set db_16k_cache_size=1m scope=both;
System altered.
ZLM@ora10g> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
尽管只分配了1M给db_16k_block_size参数,但默认还是分配了4M,这个和oracle的granule有关,此处不详述。
--创建16k块大小的非标表空间TBS_16k
ZLM@ora10g> create tablespace TBS_16k
blocksize 16k
datafile '/u01/app/oracle/oradata/ora10g/TBS_16K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
Tablespace created.
ZLM@ora10g> DROP TABLE EMPLOYEES_BK PURGE;
DROP TABLE EMPLOYEES_BK PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
--创建基于16k块大小的表空间重新创建测试表EMPLOYEES_BK
ZLM@ora10g> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_16K AS SELECT * FROM EMPLOYEES;
Table created.
ZLM@ora10g> delete from chained_rows ;
107 rows deleted.
ZLM@ora10g> commit;
Commit complete.
ZLM@ora10g> analyze table EMPLOYEES_BK list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES_BK';
COUNT(*)
----------
0
通常,产生行链接是因为当一行数据大于一个数据块,则ORACLE会同时分配两个数据块,并在第一个块上登记第二个块的地址,从而形成行链接。
[oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 10月 27 14:11:44 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SYS@ora10g> startup
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 331350240 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ora10g> conn zlm/zlm
Connected.
--创建测试表EMPLOYEES及其索引
ZLM@ora10g> DROP TABLE EMPLOYEES PURGE;
Table dropped.
ZLM@ora10g> CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES ;
Table created.
ZLM@ora10g> desc EMPLOYEES;
Name Null? Type
----------------------------------------- -------- ----------------------------
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)
ZLM@ora10g> create index idx_emp_id on employees(employee_id);
Index created.
--修改表结构扩大字段
ZLM@ora10g> alter table EMPLOYEES modify FIRST_NAME VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify LAST_NAME VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify EMAIL VARCHAR2(2000);
Table altered.
ZLM@ora10g> alter table EMPLOYEES modify PHONE_NUMBER VARCHAR2(2000);
Table altered.
--填充相关字段(使其产生行链接)
ZLM@ora10g> UPDATE EMPLOYEES
SET FIRST_NAME = LPAD('1', 2000, '*'), LAST_NAME = LPAD('1', 2000, '*'), EMAIL = LPAD('1', 2000, '*'),
PHONE_NUMBER = LPAD('1', 2000, '*');
107 rows updated.
ZLM@ora10g> COMMIT;
Commit complete.
--创建chaind_rows表来观察行链接
ZLM@ora10g> set autotrace off
ZLM@ora10g> drop table chained_rows purge;
Table dropped.
ZLM@ora10g> @?/rdbms/admin/utlchain.sql
Table created.
ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES';
COUNT(*)
----------
107
此时可以看到,这107行数据都产生了行链接。
--尝试使用消除行迁移的方法来消除行链接
ZLM@ora10g> drop table EMPLOYEES_TMP;
Table dropped.
ZLM@ora10g> create table EMPLOYEES_TMP as select * from EMPLOYEES where rowid in (select head_rowid from chained_rows);
Table created.
ZLM@ora10g> Delete from EMPLOYEES where rowid in (select head_rowid from chained_rows);
107 rows deleted.
ZLM@ora10g> Insert into EMPLOYEES select * from EMPLOYEES_TMP;
107 rows created.
ZLM@ora10g> delete from chained_rows ;
107 rows deleted.
ZLM@ora10g> commit;
Commit complete.
ZLM@ora10g> analyze table EMPLOYEES list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES';
COUNT(*)
----------
107
与消除行迁移不同,使用以上的方法,仍然无法来去除行链接,再重新插入数据后,这107行数据仍然产生了行链接。
ZLM@ora10g> select file_name from dba_data_files;
FILE_NAME
-----------------------------------------------
/u01/app/oracle/oradata/ora10g/undotbs01.dbf
/u01/app/oracle/oradata/ora10g/zlm01.dbf
/u01/app/oracle/oradata/ora10g/example01.dbf
/u01/app/oracle/oradata/ora10g/users01.dbf
/u01/app/oracle/oradata/ora10g/sysaux01.dbf
/u01/app/oracle/oradata/ora10g/system01.dbf
6 rows selected.
create tablespace TBS_32k
blocksize 32K
datafile '/u01/app/oracle/oradata/ora10g/TBS_32K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
create tablespace TBS_32k
*
ERROR at line 1:
ORA-00382: 32768 not a valid block size, valid range [2048..16384]
ORA-00382提示我的10g版本不支持32k的block size,只允许2k..16k之间的大小。
create tablespace TBS_16k
blocksize 16k
datafile '/u01/app/oracle/oradata/ora10g/TBS_16K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
create tablespace TBS_16k
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes
由于没有配置db_nk_block_size参数,因此不允许创建非标准block size的表空间。
ZLM@ora10g> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
ZLM@ora10g> alter system set db_16k_cache_size=1m scope=both;
System altered.
ZLM@ora10g> show parameter cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
尽管只分配了1M给db_16k_block_size参数,但默认还是分配了4M,这个和oracle的granule有关,此处不详述。
--创建16k块大小的非标表空间TBS_16k
ZLM@ora10g> create tablespace TBS_16k
blocksize 16k
datafile '/u01/app/oracle/oradata/ora10g/TBS_16K.DBF' size 100M
autoextend on next 10M
extent management local
6 segment space management auto;
Tablespace created.
ZLM@ora10g> DROP TABLE EMPLOYEES_BK PURGE;
DROP TABLE EMPLOYEES_BK PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
--创建基于16k块大小的表空间重新创建测试表EMPLOYEES_BK
ZLM@ora10g> CREATE TABLE EMPLOYEES_BK TABLESPACE TBS_16K AS SELECT * FROM EMPLOYEES;
Table created.
ZLM@ora10g> delete from chained_rows ;
107 rows deleted.
ZLM@ora10g> commit;
Commit complete.
ZLM@ora10g> analyze table EMPLOYEES_BK list chained rows into chained_rows;
Table analyzed.
ZLM@ora10g> select count(*) from chained_rows where table_name='EMPLOYEES_BK';
COUNT(*)
----------
0
此时看到chained_rows表的记录为0,说明没有产生行链接了。因此,只有通过增加block size的大小,才能真正消除行迁移。
0 0
- 行链接消除测试
- 消除行链接
- 行链接 行迁移的消除
- css消除链接下划线
- 行链接和行迁移检测和消除方法
- 行链接和行迁移检测和消除方法
- oracle 关于行链接和行迁移和消除
- 行迁移的产生、消除和优化测试
- speex回声消除功能测试
- 测试链接
- 如何批量消除链接虚线框?
- 链接点击前后的颜色设置以及消除链接下划线
- speex aec回升消除效果测试
- 消除相同行 distinct
- 消除重复行
- 消除重复行distinct
- 消除行迁移
- 测试数据库链接数
- Find Minimum in Rotated Sorted Array
- MySQL InnoDB 共享表空间和独立表空间
- Android实战技巧:为从右向左语言定义复杂字串,代码和xml设置
- 如何进行iOS性能测试-------Instruments 使用教程
- 微信企业号接入时InvalidKeyException处理
- 行链接消除测试
- 什么是 MIME Type?
- ——黑马程序员——C语言基础--sizeof运算符和strlen 函数的区别总结
- 最短路——SPFA
- eclipse安装git
- JavaScript 闭包的用途
- 后台推送
- CSS3 pointer-events:none应用举例及扩展
- 初学NDK