表的其他的重要物理参数

表的其他的重要物理参数

1、MSSM(Manual segment space management)上的freelists
2、maxtrans
3、logging


CREATE TABLESPACE NOASSM DATAFILE 
  'D:\ORA1101G\ORADATA\GX\USERS_NOASSM1.DBF' SIZE 100M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON

create table t_freelist
(
a integer,
b varchar2(10)
) tablespace noassm

四个session
declare 
  i integer;
begin
i:=dbms_utility.get_time;
  -- Test statements here
for j in 1..100000 loop
insert into t_freelist values(j,'xx');
end loop;
commit;
dbms_output.put_line((dbms_utility.get_time-i)/100);
end;

alter table t_freelist storage (freelists 5)

select e.TOTAL_WAITs,e.TIME_WAITED,e.WAIT_CLASS from v$system_event e
where event = 'buffer busy waits'

0   1   49	    261	Concurrency
1   10  4426	300	Concurrency
2   1   4428	300	Concurrency
3   5   8892	375	Concurrency
4   1   9072	414	Concurrency

freelist	TOTAL_WAITs	TIME_WAITED	WAIT_CLASS
1			
5			
10			
1			


CREATE TABLESPACE USERS DATAFILE 
  'D:\ORA1101G\ORADATA\GX\USERS01.DBF' SIZE 1868M AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO

select dbms_metadata.get_ddl('TABLE','T_FREELIST') from dual

  CREATE TABLE "SCOTT"."T_FREELIST" 
   (	"A" NUMBER(38,0), 
	"B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


declare 
  -- Local variables here
  i integer;
begin
i:=dbms_utility.get_time;
  -- Test statements here
for j in 1..100000 loop
insert into t_freelist values(j,'xx');
end loop;
commit;
dbms_output.put_line((dbms_utility.get_time-i)/100);
end;

SQL> alter table t_freelist storage (freelists 5);
alter table t_freelist storage (freelists 5)
*
第 1 行出现错误:
ORA-10620: Operation not allowed on this segment


drop table t_freelist purge
create table t_freelist
(
a integer,
b varchar2(10)
) tablespace users 
STORAGE(FREELISTS 10)

9416	427	Concurrency
10195	455	Concurrency

尽管提高freelists设置较高的值，可以提高并发处理的效率，但是可能会导致使用较多的磁盘空间。

多个freelists的工作机制

1、	每一个freelist有较少的free block
2、	当存在多个freelist时候，会多出一个主freelist
3、	每一个freelist只能从主freelist申请新的free block,而不会从其他的freelist申请新的free blocks。


并行加载大量数据的时候，为了提高效率，可能采用并行加载方法。这时候，可以暂时将表的freelist设定一个较高的值，等任务结束后，再将表的freelist设定一个较合理的值。


Inittrans和maxtrans

在块的首部有一个事务表，这个事务表建立一些数据行记录哪些事务将块上的哪些行的数据锁定，这个事务表的初始化行数由Inittrans设定，其默认值为2。随着这个块上的事务数增加，事务表的条目也开始增加，增加到最大的条目数maxtrans（假定这个块有足够的自由空间），每一个事务条目占用23左右字节数。

ORA9IR2> create table t1 ( x int ) maxtrans 2;
ORA9IR2> create table t2 ( x int ) maxtrans 2;

ORA9IR2> insert into t1 values ( 1 );
ORA9IR2> insert into t1 values ( 2 );
ORA9IR2> insert into t1 values ( 3 );

ORA9IR2> insert into t2 values ( 1 );
ORA9IR2> insert into t2 values ( 2 );
ORA9IR2> insert into t2 values ( 3 );
显然，t1,t2的数据在同一个块上。
ORA9IR2> commit;

Session 1
ORA9IR2> update t1 set x=x where x=1;
ORA9IR2> update t2 set x=x where x=1;

session 2
ORA9IR2> update t1 set x=x where x=2;

session 3
ORA9IR2> update t2 set x=x where x=3;

session 2
ORA9IR2> update t2 set x=x where x=2;


that'll block.  T2 has a lock by session 1 and session 3 -- maxtrans (max conccurrent  transactions) on that block is limited to two, so session 2 must wait


back to session 3

ORA9IR2> update t1 set x=x where x=3;


that'll block as well.  then session 2 will recieve


ORA9IR2> update t2 set x=x where x=2;
update t2 set x=x where x=2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

在10g以后的版本maxtrans是255


logging 与nologging
通常对象(主要是表、索引)采用logging的方式被创建的，这说明在创建对象的时候，能够生成redo的时候，就会生成redo。 而nologging则允许某一些操作，可以不生成redo。

Nologging则只影响几个特定的操作。如对象创建，直接路径加载。

archive log list
数据库日志模式             非存档模式
自动存档                   禁用
存档终点                   USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列         380
当前日志序列               382


drop table t_logging purge;
drop table t_nologging purge;


select a.sid,a.statistic#,a.value 
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
      b.NAME like 'redo size' and
      sid=( select sid from v$mystat where rownum=1)
--1954800

create table t_logging logging
as
select * from all_objects


select a.sid,a.statistic#,a.value 
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
      b.NAME like 'redo size' and
      sid=( select sid from v$mystat where rownum=1)
--2072888

select 2072888-1954800 from dual

118088

select a.sid,a.statistic#,a.value 
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
      b.NAME like 'redo size' and
      sid=( select sid from v$mystat where rownum=1)
--2072888
create table t_nologging nologging
as
select * from all_objects

select a.sid,a.statistic#,a.value 
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
      b.NAME like 'redo size' and
      sid=( select sid from v$mystat where rownum=1)
--2186732

select 2186732-2072888 from dual

113844


select table_name,logging from user_tables where table_name in   ('T_LOGGING','T_NOLOGGING')

T_LOGGING	YES
T_NOLOGGING	NO

select * from v$sesstat
    where statistic#=140
    and sid = (select sid from v$session where username=USER);

select * from v$statname where lower(name) like '%redo%'
--140


select * from v$session where audsid=userenv('sessionid');
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
select * from v$session where sid = ( select sid from v$mystat where rownum=1);

select a.sid,a.statistic#,a.value 
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
      b.NAME like 'redo size' and
      sid=( select sid from v$mystat where rownum=1)



create table as select
insert /*+ APPEND */
direct path loads

will already skip logging in a noarchivelog mode database as they do direct writes to the datafiles
other operations such as insert/update/delete/merge will always generate redo regardless of the 
mode