Oracle11g温习-第十章:存储架构
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle11g温习-第十章:存储架构,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含13833字,纯文字阅读大概需要20分钟。
内容图文
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> <span style="colo 本文系统来源:http://www.cnblogs.com/iyoume2008/p/7525281.html |
内容总结
以上是互联网集市为您收集整理的Oracle11g温习-第十章:存储架构全部内容,希望文章能够帮你解决Oracle11g温习-第十章:存储架构所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。