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(*)
----------
28
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',EMAIL=LPAD('1',
phone_number=lpad('1','*');
sql>commit;
进行查询
sql>set autotrace traceonly
Set linesize 1000
sql> Select * from employees;
107 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id| Operation | Name| Rows | Bytes | Cost (%cpu)|Time |
-------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 107 | 7383 |3 (0)| 00:00:01 |
| 1| TABLE ACCESS FULL| EMPLOYEES | 107 |7383 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
2recursive 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
9sql*Net roundtrips to/from client
0sorts (memory)
0sorts (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
----------------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 839K|38 (0)| 00:00:01 |
| 1| TABLE ACCESS FULL| EMPLOYEES_BK | 107 |839K| 38 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5recursive 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
9sql*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
107 rows processed
原先20个字节的长度被更新为200个字节长度。
消除行迁移的一个简单的办法，就是数据重建。
sql> select pct_free from user_tables wheretable_name='EMPLOYEES';
PCT_FREE
----------
10
修改PCTFREE这个参数
Alter table employees pctfree 20;
sql> select pct_free from user_tables wheretable_name='EMPLOYEES';
PCT_FREE
----------
20
这个参数要设置多大，需要深入了解和测试。
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块太大，容易导致大量并发查询及更新操作都指向同一个数据块，从而产生热点块竞争。
原文链接：https://www.f2er.com/oracle/207082.html