Oracle11g温习-第十章:存储架构
2013年4月27日 星期六
10:38
|
1、oracle 存储架构: |
||||
|
1) database ------------tablespace-------------segment(对象) ------------extent----------data block tablespace------------ datafiles ----------------------------------data block |
||||
|
2、oralce segment(段对象) 的类型 |
||||
|
1) table segment ---------建立table时,表有记在存储数据时,记录在写入数据块时候,是 随机的无序的,访问采用全表扫描。 2) partition table--------对大的table(>2G) 进行分区,减少资源竞争,提高访问速度。 3) cluster(簇) -------------------用于优化表与表的连接查询(用于表与表之间主外键连接查询,加快读取速度) 4) index-------------------提高表的访问速度 5) index organized table(IOT) -------------用于以主键方式建立索引的表,将表中的数据和索引表的数据放在一起 提高访问速度。 |
||||
|
3、存储空间分配:以extent 为最小单位(extent 由data block 组成) |
||||
|
4、data block |
||||
|
|
||||
|
5、数据块的free space 管理 |
||||
|
1) 手动:在数据字典上建立free list 2) 自动:在本地管理的表空间,通过bitmap来管理data block 的free space(oracle 10g 默认,除了临时、undo表空间)
SYS @ prod >select tablespace_name,contents ,extent_management “ext_man”,segment_space_management “seg_man” from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXT_MAN SEG_MAN --------------------------------------- ---------- ---------------------------------------- SYSTEM PERMANENT LOCAL MANUAL UNDOTBS1 UNDO LOCAL MANUAL SYSAUX PERMANENT LOCAL AUTO TEMP TEMPORARY LOCAL MANUAL USERS PERMANENT LOCAL AUTO TEST PERMANENT LOCAL AUTO |
||||
|
6、建立对象的存储信息: |
||||
|
SQL> create table test (id int) tablespace users;
SQL> insert into test values (1) ;
SQL> insert into test values (2) ;
SQL> insert into test values (3) ;
SQL> commit;
【HWM :High water Market 高水位线,在segment用来区分已经使用的块和从未使用的块(在做全表扫描时,会一直扫描到HWM) 】
SQL> begin for i in 4..100000 loop insert into test values (i) ; end loop; end; /
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME TABLESPACE_NAME PCT_FREE INI_TRANS MAX_TRANS NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------------------- ---------------------- ------------ TEST USERS 10 1 255 100000 182 330 |
||||
|
建立autotrace ,跟踪sql语句 |
||||
|
SQL> conn /as sysdba
Connected.
------------对test 做全表扫描 SQL> set autotrace traceonly;
SQL> select * from test;
Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 390K| 49 (15) | 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 100K| 390K| 49 (15) | 00:00:01 | --------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6851 consistent gets 0 physical reads 0 redo size 1855907 bytes sent via SQL*Net to client 73710 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 100000 rows processed
SQL> delete from test; delete from test * ERROR at line 1:
【ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_TBS01'】
---------undo 表空间太小,不能 auto extend
SQL> /
FILE_NAME FILE_ID TABLESPACE_NAME -------------------------------------------------- ---------- ------------------------------ /u01/app/oracle/oradata/lx02/system01.dbf 1 SYSTEM /u01/app/oracle/oradata/lx02/lx02.dbf 11 LX02 /u01/app/oracle/oradata/lx02/OLTP01.DBF 7 OLTP /u01/app/oracle/oradata/lx02/indx01.dbf 6 INDX /u01/app/oracle/oradata/lx02/example01.dbf 5 EXAMPLE /u01/app/oracle/oradata/lx02/user01.dbf 4 USERS /u01/app/oracle/oradata/lx02/sysaux01.dbf 3 SYSAUX /u01/app/oracle/oradata/lx02/rtbs01.dbf 2 RTBS /u01/app/oracle/oradata/lx02/test01.dbf 8 TEST /u01/app/oracle/oradata/tbs16k01.dbf 9 TBS_16K /u01/app/oracle/oradata/lx02/lx01.dbf 10 LX01 /u01/app/oracle/oradata/lx02/undotbs01.dbf 12 UNDO_TBS01 /u01/app/oracle/oradata/lx02/test02.dbf 13 TEST /u01/app/oracle/oradata/bigtbs01.dbf 14 BIG_TBS
SQL> alter database datafile 12 autoextend on next 10m maxsize 500m; Database altered.
-----------undo tablespace自动扩展
SQL> delete from test; 100000 rows deleted.
SQL> set autotrace off
SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME TABLESPACE_NAME PCT_FREE INI_TRANS MAX_TRANS NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------- ---------- ---------- ------------- ---------- TEST USERS 10 1 255 0 182 330
----------【delete 删除数据 不会重置高水位 没有释放占用的数据块 Truncate 删除数据 并且重置高水位 释放占用的数据块】
SQL> insert into test values (1) ;
SQL> insert into test values (2) ;
SQL> commit;
SQL> set autotrace on;
SQL> select * from test;
ID ---------- 1 2
Execution Plan ---------------------------------------------------------- Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 26 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 2 | 26 | 2 (0) | 00:00:01 | --------------------------------------------------------------------------
Note ----- - dynamic sampling used for this statement
Statistics ---------------------------------------------------------- 28 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 451 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) 2 rows processed
---------【delete 不释放已经使用的块,所以两行记录在做全表扫描的时候也要访问所有已经使用过的块(扫描到HWM) 】
SQL> set autotrace off
SQL> truncate table test;
Table truncated.
SQL> analyze table test compute statistics;
SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME TABLESPACE_NAME PCT_FREE INI_TRANS MAX_TRANS NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ------------ TEST USERS 10 1 255 0 0 512
SQL> insert into test values (1) ;
SQL> insert into test values (2) ;
SQL> commit;
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME TABLESPACE_NAME PCT_FREE INI_TRANS MAX_TRANS NUM_ROWS BLOCKS EMPTY_BLOCKS ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ---------- ------------ TEST USERS 10 1 255 2 54 458
SQL> set autotrace on
SQL> select * from test;
ID ---------- 1 2
Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 4 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 2 | 4 | 2 (0) | 00:00:01 | --------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 451 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) 2 rows processed
------------【truncate操作会释放 该对象的存储空间的高水位线】
|
|
|
|
【delete 删除数据 不会重置高水位 不会释放占用的数据块】 【Truncate 删除数据 并且重置高水位 释放占用的数据块】
Execution Plan Execution Plan ---------------------------------------------------------- ----------------------------------------------------------
Plan hash value: 1357081020 Plan hash value: 1357081020
-------------------------------------------------------------------------- -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- --------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 43 (3)| 00:00:01 | | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 43 (3)| 00:00:01 | | 1 | TABLE ACCESS FULL| TEST | 1 | 13 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------- --------------------------------------------------------------------------
Execution Plan Execution Plan ---------------------------------------------------------- ---------------------------------------------------------- Plan hash value: 1357081020 Plan hash value: 1357081020
Statistics Statistics
---------------------------------------------------------- ---------------------------------------------------------- 1 recursive calls 1 recursive calls 0 db block gets 0 db block gets 185 consistent gets 22 consistent gets 0 physical reads 0 physical reads 0 redo size 0 redo size 451 bytes sent via SQL*Net to client 451 bytes sent via SQL*Net to client 384 bytes received via SQL*Net from client 384 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (memory)
0 sorts (disk) 0 sorts (disk) 2 rows processed 2 rows processed |