8.读书笔记收获不止Oracle之 逻辑体系三 扩展和性能
|
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个字节长度。 消除行迁移的一个简单的办法，就是数据重建。 查询数据库的PCTFREE 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块太大，容易导致大量并发查询及更新操作都指向同一个数据块，从而产生热点块竞争。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |