本帖最后由 sunyunyi 于 2017-9-7 17:35 编辑
目前服务于电力行业,致力于帮助客户解决生产过程中出现的问题,提高生产效率, 爱好书法,周易!愿结交志同道合之士!共同进步! 微信号:sunyunyi_sun
version: 12.2.0.1.0
需要测试的表空间信息:
DBMS_METADATA.GET_DDL('TABLESPACE','DATA_01')
--------------------------------------------------------------------------------
CREATE TABLESPACE "DATA_01" DATAFILE
SIZE 5368709120
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT ---LMT 管理
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO ---ASSM 管理
ALTER DATABASE DATAFILE
'+DATA/test/datafile/data_01.400.952770489' RESIZE 32212254720
建立测试表
drop table user_sun.text_ext;
create table user_sun.test_ext tablespace data_01 as select * from dba_objects;
table meta info:
@meta:
CREATE TABLE "USER_SUN"."TEST_EXT"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 --PCTFREE 10 PCTUSED 40
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 --INITIAL 65536 NEXT 1048576 初始化64K next1M
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_01"
note:block size=8k
set lines 1200 pages 5000;
col owner for a12
col segment_name for a12
col segment_type for a10
select owner,segment_name,segment_type,file_Id,blocks,count(extent_id)
from dba_extents where segment_name='TEST_EXT'
group by owner,segment_name,segment_type,file_Id,blocks
order by blocks
OWNER SEGMENT_NAME SEGMENT_TY FILE_ID BLOCKS COUNT(EXTENT_ID)
------------ ------------ ---------- ---------- ---------- ----------------
USER_SUN TEST_EXT TABLE 6 8 16
USER_SUN TEST_EXT TABLE 6 128 63
USER_SUN TEST_EXT TABLE 6 8192 4
USER_SUN TEST_EXT TABLE 6 1024 120
USER_SUN TEST_EXT TABLE 6 256 1
USER_SUN TEST_EXT TABLE 6 1920 1
前 16 个 extent 每个区大小为 8*8k
接下来 63 个 extent 每个区大小为 128*8k
接下来 120 个 extent 每个区大小为 1024*8k
接下来 n 个 extent 每个区大小为 1024*8k
偶尔也有例外哦!!
again:
drop table user_sun.test_ext;
create table user_sun.test_ext tablespace data_01 as select * from dba_objects;
insert into user_sun.test_ext select * from user_sun.test_ext;
/
...OWNER SEGMENT_NAME SEGMENT_TY FILE_ID BLOCKS COUNT(EXTENT_ID)
------------ ------------ ---------- ---------- ---------- ----------------
USER_SUN TEST_EXT TABLE 6 8 16
USER_SUN TEST_EXT TABLE 6 128 63
USER_SUN TEST_EXT TABLE 6 1024 120
USER_SUN TEST_EXT TABLE 6 8192 4
新建表"USER_SUN"."TEST_EXT3"
修改
PCTFREE 90 PCTUSED 1
STORAGE(INITIAL 500k NEXT 10m
CREATE TABLE "USER_SUN"."TEST_EXT3"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30)
) SEGMENT CREATION IMMEDIATE
PCTFREE 90 PCTUSED 1 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 500k NEXT 10m MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA_01"
insert into user_sun.test_ext3 select * from user_sun.test_ext;
OWNER SEGMENT_ NAME SEGMENT_TY FILE_ID BLOCKS COUNT(EXTENT_ID)
------------ ------------ ---------- ---------- ---------- ----------------
USER_SUN TEST_EXT3 TABLE 6 8 16
USER_SUN TEST_EXT3 TABLE 6 128 80
USER_SUN TEST_EXT3 TABLE 6 256 24
USER_SUN TEST_EXT3 TABLE 6 512 1
USER_SUN TEST_EXT3 TABLE 6 1024 148
USER_SUN TEST_EXT3 TABLE 6 2048 45
USER_SUN TEST_EXT3 TABLE 6 6016 1
USER_SUN TEST_EXT3 TABLE 6 8192 155
看看上面的结果出乎意料!
结果:
STORAGE(INITIAL 500k NEXT 10m ) 参数不起作用,LMT 下自动管理extent的分配策略
总结:
PCTFREE :为将来更新已有行操作预留的空间比例。当剩余空间小于该值时block标记为非空闲,只能操作DEL和upd操作
PCTUSED :用于为插入新行预留的最小百分比,注意是最小!
在ASSM三级位图替换了8版本的freelist
三级位图原理:
L1 中记录有多少个extent,每个块的状态是free还是full
L2 中有多少个L1,每个L1的地址和L1的状态,其中状态分为以下7中情况:
0 : unformatted
1 : logically full
2 : 0-25% full
3 : 25-50% full
4 : 50-75% full
5 : 75-100% full
L3 中记录有多少个区,多少个块,高水位,第一个三级BMB地址,最后一个L1地址,最后一个L2地址,最后一个L3地址,
extent map,Auxillary Map,Second Level Bitmap block DBAS.
L1 dump:
assm01.jpg (76.18 KB, 下载次数: 40)
2017-9-6 17:02 上传
L2 dump
assml2.png (12.77 KB, 下载次数: 34)
2017-9-6 17:03 上传
L3 dump
assml3.png (17.12 KB, 下载次数: 31)
2017-9-6 17:06 上传
另外提供行链接和行迁移的检查方法:
SQL> select name,value from v$sysstat where name='table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 25874
SQL> SQL> @?/rdbms/admin/utlchain
SQL> select count(*) from chained_rows;
SQL>analyze table USER_SUN.TEST_EXT3 list chained rows into chained_rows;
SQL> select count(*) from chained_rows; --是否有记录