段空间管理:
手动段空间管理manual segment space management (MSSM).
MSSM是Oracle的遗留实现,原先需要对控制空间分配和提供高并发性的参数数不胜数,并且需要对这些参数进行细致地调整,而大家并不希望继续这么做。当使用MSSM表空间时,
oracle会使用自由列表(FREELIST)来维护他们的HWM之下的块。
自动段空间管理automatic segment space management (ASSM).
ASSM在Oracle 9i才首次被引入。你只需要控制与空间使用相关的一个参数:PCTFREE。
FREELIST:
一个对象可以有多个FREELIST。使用块时,可能会根据需要把块放在FREELIST上或者从FREELIST上去除。只有位于HWM以下的对象块才会出现在FREELIST中,只有当FREELIST为空才会使用HWM之上的块,此时Oracle会推进HWM,并把这些块增加到FREELIST中。如果预料到会有很多并发用户在一个对象上执行大量的INSERT或UPDATE,就可以去配置多个FREELIST,这对性能提升很有好处(但是可能要以额外的存储空间为代价)。
--使用5个并发会话进行疯狂插入查看statspack
--实验环境准备
EODA@PROD1> create tablespace mssm datafile '/u01/app/oracle/oradata/PROD1/mssm.dbf' size 1m autoextend on next 1m segment space management manual;
Tablespace created.
EODA@PROD1> create tablespace assm datafile '/u01/app/oracle/oradata/PROD1/assm.dbf' size 1m autoextend on next 1m segment space management auto;
Tablespace created.
EODA@PROD1> drop table t purge;
Table dropped.
EODA@PROD1> set echo off
--EODA@PROD1> !echo begin for i in 1 .. 100000 loop insert into t values \(i,\'x\'\)\; end loop\; commit\; end\; > test.sql
EODA@PROD1> !echo / >> test.sql
EODA@PROD1> !echo exit >> test.sql
EODA@PROD1> !echo \#\!/bin/bash > test.sh
EODA@PROD1> !echo sqlplus eoda/foo @test.sql \&>> test.sh
EODA@PROD1> !echo sqlplus eoda/foo @test.sql \&>> test.sh
EODA@PROD1> !echo sqlplus eoda/foo @test.sql \&>> test.sh
EODA@PROD1> !echo sqlplus eoda/foo @test.sql \&>> test.sh
EODA@PROD1> !echo sqlplus eoda/foo @test.sql \&>> test.sh
EODA@PROD1> !echo wait >> test.sh
EODA@PROD1> set echo on
EODA@PROD1> !chmod 755 test.sh
--第一个实验assm
EODA@PROD1> create table t ( x int, y char(50) ) tablespace assm;
Table created.
EODA@PROD1> exec statspack.snap
PL/SQL procedure successfully completed.
EODA@PROD1> !/bin/bash ./test.sh
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 18:29:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 18:29:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 18:29:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 18:29:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 18:29:53 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing