8.读书笔记收获不止Oracle之逻辑体系三扩展和性能

来源：互联网发布：巨人城刷龙啸翻牌软件编辑：程序博客网时间：2024/04/30 20:58

8.读书笔记收获不止Oracle之逻辑体系三扩展和性能

1. 扩展与性能

如果表记录增长的特别快，可以考虑把这个EXTENT的大小设置得大一点，比如initial extent 和 incremental extent .

做个试验：

SQL> Create tablespace tbs_toad_a

Datafile '+DATA/ORCL/DATAFILE/tbs_toad_a.dbf' size 1M autoextend on uniform size64k;Set

SQL> Create tablespace tbs_toad_b

Datafile '+DATA/ORCL/DATAFILE/tbs_toad_b.dbf' size 500M

Set timing on

Create table t_a (id int) tablespacetbs_toad_a;

Create table t_b (id int) tablespacetbs_toad_b;

插入比较速度：

SQL> insert into t_a select rownum from dual connect bylevel<=1000000;

1000000 rows created.

Elapsed: 00:00:04.84

SQL> insert into t_b select rownum from dual connect bylevel<=1000000;

1000000 rows created.

Elapsed: 00:00:00.88

速度差了很多。

查看扩展的次数：

SQL> select count(*) from user_extentswhere segment_name='T_A';

COUNT(*)

----------

194

Elapsed: 00:00:00.19

SQL> select count(*) from user_extentswhere segment_name='T_B';

COUNT(*)

----------

Elapsed: 00:00:00.13

T_A扩展了194次，而T_B扩展了28次才，所以速度相差几倍。

在申请扩大空间时候，花费了大量时间。

表空间扩大是要格式化操作系统文件成为ORACLE可以识别的数据库，开销的确很大。

2. PCTFREE与性能

先创建HR 用户的相关数据，没有就执行hr_main.sql脚本。

链接如下：

http://download.csdn.net/detail/notbaron/9101265

执行后

#sqlplus hr/hr

SQL> create table employees as select * from hr.employees;

Table created.

SQL> 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)

扩大字段

Alter table employees modify first_namevarchar2(2000);

Alter table employees modify last_namevarchar2(2000);

Alter table employees modify emailvarchar2(2000);

Alter table employees modify phone_numbervarchar2(2000);

然后填满数据，将导致原先大量的行迁移产生：

SQL> update employees setfirst_name=LPAD('1',2000,'*'),LAST_name=lpad('1',2000,'*'),EMAIL=LPAD('1',2000,'*'),

phone_number=lpad('1',2000,'*');

SQL>commit;

进行查询

SQL>set autotrace traceonly

Set linesize 1000

SQL> Select * from employees;

107 rows selected.

Execution Plan

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

Plan hash value: 1445457117

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

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

| 0| SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |

| 1| TABLE ACCESS FULL| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |

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

Statistics

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

2 recursive calls

1 dbblock gets

318 consistent gets

0 physicalreads

184 redo size

867264 bytes sent via SQL*Net toclient

628 bytes received via SQL*Net from client

9 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

107 rows processed

产生了318个逻辑读。

然后再复制一份，再进行查询

SQL> create table employees_bk as select * from employees;

Table created.

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> select * from employees_bk;

107 rows selected.

Execution Plan

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

Plan hash value: 2676497765

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

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

| 0 | SELECT STATEMENT | | 107 | 839K| 38 (0)| 00:00:01 |

| 1| TABLE ACCESS FULL| EMPLOYEES_BK | 107 | 839K| 38 (0)| 00:00:01 |

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

Statistics

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

5 recursive calls

0 dbblock gets

252 consistent gets

124 physical reads

0 redosize

867115 bytes sent via SQL*Net toclient

628 bytes received via SQL*Net from client

9 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

107 rows processed

原先20个字节的长度被更新为200个字节长度。

消除行迁移的一个简单的办法，就是数据重建。

查询数据库的PCTFREE

SQL> select pct_free from user_tables wheretable_name='EMPLOYEES';

PCT_FREE

----------

修改PCTFREE这个参数

Alter table employees pctfree 20;

SQL> select pct_free from user_tables wheretable_name='EMPLOYEES';

PCT_FREE

----------

这个参数要设置多大，需要深入了解和测试。

3. 行迁移与优化

创建一个chained_rows

#sqlplus / as sysdba

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

对EMPLOYEES表和EMPLOYEES_BK做分析。

Analyze table employees list chained rowsinto chained_rows;

Analyze table employees_bk list chainedrows into chained_rows;

进行查询

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

SQL> select count(*) from chained_rows where table_name='EMPLOYEES_BK';

如下脚本发现行迁移严重的情况：

SQL> select 'analyze table' ||table_name||'list chained rows intochained_rows;' from user_tables;

'ANALYZETABLE'||TABLE_NAME||'LISTCHAINEDROWSINTOCHAINED_ROWS;'

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

analyze tableTlist chained rows intochained_rows;

analyze tableT2list chained rows intochained_rows;

analyze tableT_Alist chained rows intochained_rows;

analyze tableT_Blist chained rows intochained_rows;

analyze tableEMPLOYEESlist chained rowsinto chained_rows;

analyze tableCHAINED_ROWSlist chained rowsinto chained_rows;

analyze tableEMPLOYEES_BKlist chained rowsinto chained_rows;

7 rows selected.

SQL> select * from chained_rows;

4. 块大小与应用

BLOCK是ORACLE最小的单位。

如果块越大，装的行记录就越多，需要的块就越少。产生的IO就越少。

对于OLAP应用，一般倾向于BLOCK尽量大。OLTP倾向于 BLOCK尽量不要太大。

索引读返回少量记录这样的OLTP主打环境下，块的大小对性能影响不大。

OLTP块太大，容易导致大量并发查询及更新操作都指向同一个数据块，从而产生热点块竞争。

阅读全文

0 0

8.读书笔记收获不止Oracle之 逻辑体系三 扩展和性能

1. 扩展与性能

2. PCTFREE与性能

3. 行迁移与优化

4. 块大小与应用

8.读书笔记收获不止Oracle之逻辑体系三扩展和性能