`
itspace
  • 浏览: 960949 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle表空间用统一区大小注意点

OracleSQLCache 
阅读更多
今天在做测试用EXTENT MANAGEMENT LOCAL UNIFORM管理时，遇到看起来比较奇怪的问题，于是研究了一下。
首先创建3个表空间，注意其初始大小和UNIFORM SIZE。
SQL> create tablespace testblock datafile '/oradata/mcstar/testblock01.dbf' SIZE 200M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create tablespace testblock2 datafile '/oradata/mcstar/testblock02.dbf' SIZE 10M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create tablespace testblock3 datafile '/oradata/mcstar/testblock03.dbf' SIZE 10M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
需要注意的是Oracle创建表空间时，自动扩展为关闭
SQL> select file_id,file_name,blocks-user_blocks,AUTOEXTENSIBLE from  dba_data_files order by 1;

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
      1 /oradata/mcstar/system01.dbf                              8 YES
      2 /oradata/mcstar/undotbs01.dbf                             8 NO
      3 /oradata/mcstar/sysaux01.dbf                              8 YES
      4 /oradata/mcstar/users01.dbf                               8 YES
      5 /oradata/mcstar/mcstar01.dbf                              8 NO
      6 /oradata/mcstar/xu01.dbf                                  8 NO
      7 /oradata/mcstar/zhoul01.dbf                               8 YES
      8 /oradata/mcstar/zhoul201.dbf                              8 YES
      9 /oradata/mcstar/testblock01.dbf                         256 NO
     10 /oradata/mcstar/undotbs02.dbf                             8 YES
     11 /oradata/mcstar/testblock02.dbf                         128 NO

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
     12 /oradata/mcstar/testblock03.dbf                         256 NO

12 rows selected.
问题来了，可以看到数据文件block和用户可用的block想减，值竟然不一样。
9号文件差值为256*8k/1024/1024=2M,11号文件差值为128*8k/1024/1024=1M,12号文件差值为256*8k/1024/1024=2M。
由前面知道9号文件的uniform size为2M,11号文件的uniform_size为1M，似乎和差值对的上，但12号文件的差值和uniform_size完全对不上
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
  2  order by 1;

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                         256

12 rows selected.

继续研究，在表空间testblock3上创建表格，并插入一定的数据量。
SQL> create table testblock3  tablespace testblock3 as select * from obj$;

Table created.

SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;

EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024
        
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'TESTBLOCK3',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>  select AVG_ROW_LEN,NUM_ROWS,SAMPLE_SIZE from dba_tables where lower(table_name)='testblock3';

AVG_ROW_LEN   NUM_ROWS SAMPLE_SIZE
----------- ---------- -----------
         78      60759       60759

SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16m;

Database altered.

SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3

根据平均行长度为78，数据文件存放业务数据行数可近似计算为((16*1024/8-8)*(8192-819-60))/78=2040*7313/78=191263，但现在目前只存有88252。
两者相差较大。
SQL> select count(*) from testblock3;

  COUNT(*)
----------
     88252

由于目前数据文件只有16M，减去1个数据文件头，7个extent map block，插入数据时并足以进行第二个extent扩展，所以目前表格中只有一个extent，
业务表格实际值，远小于理论值，也就解释的通了。
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;

EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024

通过bbed查看，可以看到业务数据好像写到了1033个块，但理论上Oracle业务数据只要写到1032个block即可。
        
BBED> dump block 1032
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1032             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
06a20000 07040003 7a259c0e 000a0106 83bc0000 01000000 22c00400 4e0e9c0e

BBED> dump block 1033
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1033             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
06a20000 08040003 7a259c0e 000a0106 c4420000 01b60000 22c00400 4e0e9c0e


BBED> dump block 1034
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1034             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
00a20000 09040000 00000000 00000105 09a30000 00000000 00000000 00000000

于是采用10046进行跟踪，可以看到业务数据存储到block 11032个为止。
SQL> alter system flush buffer_cache;

System altered.


SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';

Session altered.

SQL> select count(*) from testblock3;

  COUNT(*)
----------
     88252

SQL>  ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1030 blocks=1 obj#=311330 tim=1273413313089160
WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1031 blocks=1 obj#=311330 tim=1273413313089194
WAIT #19: nam='db file sequential read' ela= 7 file#=12 block#=1032 blocks=1 obj#=311330 tim=1273413313089226
通过以上分析。12号文件不能被用户使用的256个块，似乎和extent不能扩展有关。
对dba_extent视图查询可知，Oracle开始extent扩展是从第9号block开始（1号block为datafile_head，2-7号block为extent map）。
由于extent扩展至少需要向数据文件一次性申请1个extent大小（本例12号文件一开始为10M，第二次extent扩展时，由于不能获得8M，所以将浪费2M空间，需要注意的是这2M空间包含了datafile_head和extent map）
为验证上述猜想，继续测试，将数据文件resize至刚好能满足2个extent大小的大小。8M*2*1024*1024+8*8K*1024=16777216+65536=16842752
将数据文件扩展至16842752大小
SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16842752;

Database altered.
继续插入业务数据
SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3

可以看到表格testblock3扩展出第二个分区
SQL>  select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;


EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024
         1       1033       1024
        
SQL>  select count(*) from testblock3;

  COUNT(*)
----------
    204172
采用10046跟踪，可以看到业务数据已经存放至2056为止，该数据文件得到了充分利用。
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';

Session altered.

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL>  alter system flush buffer_cache;

System altered.

SQL>  select count(*) from testblock3;

  COUNT(*)
----------
    204172

SQL>  ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

WAIT #1: nam='db file sequential read' ela= 11 file#=12 block#=2054 blocks=1 obj#=311330 tim=1273414177770633
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2055 blocks=1 obj#=311330 tim=1273414177770673
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2056 blocks=1 obj#=311330 tim=1273414177770715
12号数据文件不可用空间也降至8个数据块。
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
  2 
FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                           8
    
12 rows selected.

通过以上测试表明，我们创建数据文件设置的大小只要能满足extent扩展的需要，就能最大程度的节省空间。如果不加考虑就设置大小，在数据文件自动扩展关闭的情况下，那最大程度将浪费(uniform size-8*block_size)的空间大小。
SQL> create tablespace testblock4 datafile '/oradata/mcstar/testblock04.dbf' SIZE 16842752
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.


SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
  2 
FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                           8
     13 /oradata/mcstar/testblock04.dbf                           8

13 rows selected.
分享到：
| 谈谈Oracle dba_free_space
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    查询Oracle数据库表空间和数据文件方法

    火龙果软件工程技术中心 表空间是oracle数据库中最大的逻辑单位与...一、查看Oracle数据库中表空间信息的方法1、查看Oracle数据库中表空间信息的工具方法：使用oracleenterprisemanagerconsole工具，这是oracle的客户

    Oracle自学（学习）材料 (共18章 偏理论一点)

    8-14 缺省临时表空间 8-15 缺省临时表空间的限制 8-16 脱机状态 8-17 只读表空间 8-19 删除表空间 8-20 改变表空间的大小 8-21 允许数据文件的自动增长 8-22 手工改变数据文件的大小 8-23 给表空间添加数据文件 8-24...

    Oracle SQL Develop简介

    Oracle SQL Developer 使用简要说明 ...默认使用 JDBC 瘦驱动程序意味着无需安装 Oracle 客户端，从而将配置和占用空间大小降至最低。SQL Developer 的安装只需进行解压和双击操作即可，简单易行。

    oracle10g课堂练习I（2）

    调整还原表空间的大小 9-14 使用还原指导 9-15 小结 9-16 练习概览：管理还原段 9-17 10 实施 Oracle 数据库安全性 课程目标 10-2 业界安全性要求 10-3 责任划分 10-5 数据库安全性 10-6 最少权限原则 10...

    数据库设计规范-编码规范.docx

    c) 编写数据库建数据库、建数据库对象、初始化数据脚本文件 4.3 设计原则 a) 采用多数据文件 b) 禁止使用过大的数据文件，unix系统不大于2GB,window系统不超过500MB c) oracle数据库中必须将索引建立在索引表空间里...

    数据库审计系统需求说明.docx

    支持分布式部署，管理中心可实现统一配置、统一报表、 统一查询。 管理中心和探测器都可存储审计数据，实现大数据环境下磁盘空间的有效利用和 扩展；管理中心和探测器直接的数据传输速率、时间、端口都可自定义。 3 ...

    图片管理系统（桃源相册管理）

    34.用户等级制，可设每个等级的空间、上传大小、相册和照片个数及其它条数限制。 35.为每个等级设置开启外链、外链地址、开启二级域名及等级费用。 36.注册审核、邮件验证、防重复IP注册、时长注册功能。 37.来访IP...

    盛世桃源通用文件管理系统 v2.5 for .net 2.0/3.0/3.5.zip

    8.可以按名称、时间、大小、类型、注释、子目录等元素组合搜索文件。 9.文件和目录可进行压缩解压ZIP操作，使得可以批量上传及批量下载文件。 10.文件转发功能，可将多个文件下载地址通过邮件或短信发送给他人下载...

    桃源相册管理系统v2.3

    34.用户等级制，可设每个等级的空间、上传大小、相册和照片个数及其它条数限制。 35.为每个等级设置开启外链、外链地址、开启二级域名及等级费用。 36.注册审核、邮件验证、防重复IP注册、时长注册功能。 37.来访IP...

    动软.Net代码生成器 v2

    1.新建项目中，还有命名空间Maticsoft的问题。 2.修正了Nvarchar类型大小翻倍问题。 3.完善了ORACLE 主键和字段注释问题。 4.Model中值类型的字段改为可空类型。具体设置：工具-选项-代码参数-字段类型映射中...

    数据库系统概论第四版答案

    维护时对数据库进行统一控制，以保证数据的完整性、安全性，并在多用户同时使用数据库 时进行并发控制，在发生故障后对系统进行恢复。数据库系统的出现使信息系统从以加工数 据的程序为中心转向围绕共享的数据库为...

    学生成绩信息管理系统论文 JSP 完整版

    数据库技术是信息系统的核心和基础，它的出现极大地促进了计算机应用向各行各业的渗透数据库的建设规模、数据库信息量的大小和使用频度已成为衡量一个国家信息化程度的重要标志。 MySQL作为一种开放源码数据库，以其...

    桃源相册管理系统源码 v2.3

    34.用户等级制，可设每个等级的空间、上传大小、相册和照片个数及其它条数限制。 35.为每个等级设置开启外链、外链地址、开启二级域名及等级费用。 36.注册审核、邮件验证、防重复IP注册、时长注册功能。 37.来访IP...

    C#编程经验技巧宝典

    104 <br>0171　如何只允许输入指定图片格式　105 <br>0172　如何设置录入图片统一图片大小　105 <br>5.4　数组处理技巧　105 <br>0173　如何转换数组类型　105 <br>0174　如何复制数组中一系列元素的...

    工程硕士学位论文 基于Android+HTML5的移动Web项目高效开发探究

    Viewport不局限于浏览器可视区域的大小，可能比浏览器的可视区域要大，也可能比浏览器的可视区域要小。 媒体查询 CSS媒体查询允许开发者基于浏览网站的设备的特性来应用不同的样式申明，最常用的特性是视口宽度。 ...

    JAVA上百实例源码以及开源项目

    1个目标文件，JNDI的使用例子，有源代码，可以下载参考，JNDI的使用，初始化Context,它是连接JNDI树的起始点，查找你要的对象，打印找到的对象，关闭Context…… ftp文件传输 2个目标文件，FTP的目标是：（1）提高...

    JAVA上百实例源码以及开源项目源代码

    日历表格面板 [ConfigLine.java] 控制条类 [RoundBox.java] 限定选择控件 [MonthMaker.java] 月份表算法类 [Pallet.java] 调色板，统一配色类 Java扫雷源码 Java生成自定义控件源代码 2个目标文件 Java实现HTTP连接...

Magicbox
Global site tag (gtag.js) - Google Analytics