第十章:  存储架构
 
 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 :oracle 10g 标准块:8k,支持2~32k
                有block header 、free space 、data 组成
               
          数据块并发访问:由数据块的事务槽来分配
           initrans :初始化事务槽的个数(1·255),表默认1,index 默认为2;
           maxtrans: 最大的事务槽个数 (默认255)
           pctfree:预留给update 操作的空间,free space 大于pctfree 可以insert,小于,不允许insert
 5、数据块的free space 管理:
                  1)手动:在数据字典上建立free list
                 
                  2)自动:在本地管理的表空间,通过bitmap来管理data block 的free space(oracle 10g 默认,除了临时、undo表空间)
      
          
               
          03:32:36 SQL> select tablespace_name,contents ,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN SEGMEN
------------------------------ --------- ---------- ------
SYSTEM                         PERMANENT DICTIONARY MANUAL
RTBS                           UNDO      LOCAL      MANUAL
SYSAUX                         PERMANENT LOCAL      AUTO
TEMP                           TEMPORARY LOCAL      MANUAL
USERS                          PERMANENT LOCAL      AUTO
EXAMPLE                        PERMANENT LOCAL      AUTO
INDX                           PERMANENT LOCAL      AUTO
OLTP                           PERMANENT LOCAL      AUTO
TEST                           PERMANENT LOCAL      AUTO
LX01                           PERMANENT LOCAL      AUTO
LX02                           PERMANENT DICTIONARY MANUAL
UNDO_TBS01                     UNDO      LOCAL      MANUAL
TMP01                          TEMPORARY LOCAL      MANUAL
TBS_16K                        PERMANENT LOCAL      AUTO
BIG_TBS                        PERMANENT LOCAL      AUTO

15 rows selected.


6、建立对象的存储信息:


03:35:07 SQL> create table test (id int) tablespace users;

Table created.

03:35:22 SQL> insert into test values (1);

1 row created.

03:35:31 SQL> insert into test values (2);

1 row created.

03:35:33 SQL> insert into test values (3);

1 row created.

03:35:34 SQL> commit;

Commit complete.

03:40:42 SQL>  select SEGMENT_NAME,SEGMENT_TYPE ,TABLESPACE_NAME,bytes/1024/1024,BLOCKS,extents ,initial_extent/1024/1024 from user_segments
03:40:43   2   where segment_name='TEST';

SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                BYTES/1024/1024     BLOCKS    EXTENTS INITIAL_EXTENT/1024/1024
-------------------- ------------------ ------------------------------ --------------- ---------- ---------- ------------------------
TEST                 TABLE              USERS                                        4        512          1                        4

03:38:17 SQL> select  TABLESPACE_NAME ,INITIAL_EXTENT/1024/1024 ,NEXT_EXTENT/1024/1024, EXTENT_MANAGEMENT from dba_tablespaces
03:38:24   2    where TABLESPACE_NAME='USERS';

TABLESPACE_NAME                INITIAL_EXTENT/1024/1024 NEXT_EXTENT/1024/1024 EXTENT_MAN
------------------------------ ------------------------ --------------------- ----------
USERS                                                 4                     4 LOCAL

------------建立对象如果没有指定存储参数,则继承tablespace 的存储参数,test表分配了一个区,大小为4m
03:41:13 SQL> select segment_name,segment_type,TABLESPACE_NAME,EXTENT_ID,bytes/1024/1024,blocks from user_extents
03:42:04   2   where segment_name='TEST';

SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID BYTES/1024/1024     BLOCKS
-------------------- ------------------ ------------------------------ ---------- --------------- ----------
TEST                 TABLE              USERS                                   0               4        512

--------------EXTENT_ID 为extent的id号,从0 开始


03:43:53 SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
03:44:41   2    where table_name='TEST';

TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE  INI_TRANS  MAX_TRANS   NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
TEST                           USERS                                  10          1        255

03:45:17 SQL> analyze table test compute statistics;

Table analyzed.

----------对test 表进行分析,oracle通过分析的信息来建立计划进行优化

03:45:26 SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
03:45:28   2    where table_name='TEST';

TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE  INI_TRANS  MAX_TRANS   NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
TEST                           USERS                                  10          1        255          3         54          458

03:45:29 SQL>

---------------blocks 代表已经使用过的块,empty_blocks 代表从未使用过的块

HWM :High water Market 高水位线,在segment用来区分已经使用的块和从未使用的块(在做全表扫描时,会一直扫描到HWM)

03:51:19 SQL> begin
03:51:33   2   for i in 4..100000 loop
03:51:43   3   insert into test values (i);
03:51:50   4  end loop;
03:51:52   5  end;
03:51:53   6  /

PL/SQL procedure successfully completed.

03:52:12 SQL> analyze table test compute statistics;

Table analyzed.

03:52:29 SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
03:52:33   2   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

03:52:36 SQL>
 


建立autotrace ,跟踪sql语句

03:53:04 SQL> conn /as sysdba
Connected.
03:53:09 SQL> @?/rdbms/admin/utlxplan

Table created.

03:53:34 SQL> grant all on plan_table to public;

Grant succeeded.

03:53:43 SQL> @?/sqlplus/admin/plustrce
03:53:58 SQL>
03:53:58 SQL> drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist


03:53:58 SQL> create role plustrace;

Role created.

03:53:58 SQL>
03:53:58 SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

03:53:58 SQL> grant select on v_$statname to plustrace;

Grant succeeded.

03:53:58 SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

03:53:58 SQL> grant plustrace to dba with admin option;

Grant succeeded.


-------------SET autotrace on 打开自动跟踪
             set autotrace off 关闭自动跟踪
             set autotrace traceonly 只显示trace 信息,不显示对象的记录信息
            

03:53:58 SQL>
03:53:58 SQL> set echo off
03:53:58 SQL> grant plustrace to public;

Grant succeeded.

03:54:08 SQL>

------------对test 做全表扫描
03:54:53 SQL> set autotrace traceonly;
03:55:01 SQL> select * from test;

100000 rows selected.


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

                         
03:55:08 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
03:56:19 SQL> col file_name for a50
03:56:23 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

14 rows selected.

03:56:24 SQL> alter database datafile 12 autoextend on next 10m maxsize 500m;

Database altered.

-----------undo tablespace自动扩展

03:57:07 SQL> delete from test;

100000 rows deleted.

04:00:02 SQL> set autotrace off
04:00:08 SQL>  select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
04:00:11   2   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

04:00:14 SQL>

----------delete 删除未释放占用的数据块

04:00:14 SQL> insert into test values (1);

1 row created.

04:00:58 SQL> insert into test values (2);

1 row created.

04:00:59 SQL> commit;

Commit complete.

04:01:01 SQL> set autotrace on;
04:01:09 SQL> select * from test;

        ID
----------
         1
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |    43   (3)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |    43   (3)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        185  consistent gets
          0  physical reads
          0  redo size
        451  bytes sent via SQL*Net to client
        384  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)
 
04:01:15 SQL> set autotrace off
04:02:32 SQL> truncate table test;

Table truncated.

04:02:35 SQL> analyze table test compute statistics;

Table analyzed.

04:02:44 SQL>  select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
04:02:49   2    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

04:02:52 SQL> 04:00:14 SQL> insert into test values (1);
SP2-0734: unknown command beginning "04:00:14 S..." - rest of line ignored.
04:03:01 SQL> insert into test values (1);

1 row created.

04:03:03 SQL> insert into test values (2);

1 row created.

04:03:05 SQL> commit;

Commit complete.

04:03:33 SQL> analyze table test compute statistics;

Table analyzed.

04:05:36 SQL> select table_name, TABLESPACE_NAME ,PCT_FREE,INI_TRANS ,MAX_TRANS,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables
04:05:41   2    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

 

04:03:06 SQL> set autotrace on
04:03:10 SQL> select * from test;

        ID
----------
         1
         2


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        451  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

04:03:33 SQL>

------------truncate 释放高水位线

04:08:55 SQL> create table t01 (id int)
04:08:59   2    pctfree 20 pctused 40 initrans 3
04:09:01   3   storage( initial 128k next 1m pctincrease 50);

Table created.

04:10:28 SQL> select table_name,TABLESPACE_NAME,PCT_FREE,PCT_USED ,INI_TRANS,INITIAL_EXTENT/1024,NEXT_EXTENT/1024/1024,PCT_INCREASE from user_tables
04:10:53   2   where table_name='T01';

TABLE_NAME                     TABLESPACE_NAME                  PCT_FREE   PCT_USED  INI_TRANS INITIAL_EXTENT/1024 NEXT_EXTENT/1024/1024 PCT_INCREASE
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------- --------------------- ------------
T01                            USERS                                  20                     3                 128                     4    0

---------在local 管理的表空间上,存储参数设置意义不大

04:12:26 SQL> select owner, SEGMENT_NAME,TABLESPACE_NAME, BYTES   from dba_segments
04:12:44   2   where owner='SCOTT';

OWNER                          SEGMENT_NAME         TABLESPACE_NAME                     BYTES
------------------------------ -------------------- ------------------------------ ----------
SCOTT                          DEPT                 USERS                             4194304
SCOTT                          PK_DEPT              USERS                             4194304
SCOTT                          EMP                  USERS                             4194304
SCOTT                          PK_EMP               USERS                             4194304
SCOTT                          BONUS                USERS                             4194304
SCOTT                          SALGRADE             USERS                             4194304
SCOTT                          TEST                 USERS                             4194304
SCOTT                          T01                  USERS                             4194304

8 rows selected.