干掉蝴蝶效应中的蝴蝶。。。
分类： Oracle
2013-08-05 23:16:04
X$KFFXP
Column Name Description
ADDR table address/identifier
INDX row identifier
INST_ID instance number (RAC)
NUMBER_KFFXP ASM file number. Join with v$asm_file and v$asm_alias
COMPOUND_KFFXP File identifier. Join with compound_index in v$asm_file
INCARN_KFFXP File incarnation id. Join with incarnation in v$asm_file
PXN_KFFXP Extent number per file
XNUM_KFFXP Logical extent number per file (mirrored extents have the same value)
GROUP_KFFXP ASM disk group number. Join with v$asm_disk and v$asm_diskgroup
DISK_KFFXP Disk number where the extent is allocated. Join with v$asm_disk
AU_KFFXP Relative position of the allocation unit from the beginning of the disk. The allocation unit size (1 MB) in v$asm_diskgroup
LXN_KFFXP 0,1 used to identify primary/mirror extent, 2 identifies file header allocation unit (hypothesis)
FLAGS_KFFXP N.K.
CHK_KFFXP N.K.
结论1：au 1M，12个asm disk。10M的datafile（asm file）在DATADG里分到11个asm disk上，每个1au。
CREATE TABLESPACE andy_unfs1m DATAFILE '+DATADG' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE andy_unfs4m DATAFILE '+DATADG' SIZE 10M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;
SQL> select name,group_number,file_number,file_incarnation from v$asm_alias where name like 'ANDY%';
NAME GROUP_NUMBER FILE_NUMBER FILE_INCARNATION
---------------------------------------------------------------------- ------------ ----------- ----------------
ANDY_UNFS1M.274.813783641 2 274 813783641
ANDY_UNFS4M.275.813783743 2 275 813783743
SQL> select DISK_KFFXP,AU_KFFXP,PXN_KFFXP,XNUM_KFFXP,LXN_KFFXP from x$kffxp where GROUP_KFFXP=2 and NUMBER_KFFXP=274;
DISK_KFFXP AU_KFFXP PXN_KFFXP XNUM_KFFXP LXN_KFFXP
---------- ---------- ---------- ---------- ----------
5 5650 0 0 0
9 5648 1 1 0
1 5653 2 2 0
10 5653 3 3 0
8 5652 4 4 0
7 5652 5 5 0
6 5649 6 6 0
4 5648 7 7 0
3 5649 8 8 0
2 5650 9 9 0
0 5650 10 10 0
11 rows selected.
SQL> select DISK_KFFXP,AU_KFFXP,PXN_KFFXP,XNUM_KFFXP,LXN_KFFXP from x$kffxp where GROUP_KFFXP=2 and NUMBER_KFFXP=275;
DISK_KFFXP AU_KFFXP PXN_KFFXP XNUM_KFFXP LXN_KFFXP
---------- ---------- ---------- ---------- ----------
8 5653 0 0 0
9 5649 1 1 0
2 5651 2 2 0
7 5653 3 3 0
3 5650 4 4 0
5 5651 5 5 0
0 5651 6 6 0
10 5654 7 7 0
1 5654 8 8 0
11 5650 9 9 0
6 5650 10 10 0
11 rows selected.
SQL>
结论2：本地管理表空间uniform size用于段分配db extent的大小，跟asm AU和asm extent都无关。
CREATE USER andy_unfs1m IDENTIFIED BY andy1234 DEFAULT TABLESPACE andy_unfs1m;
CREATE USER andy_unfs4m IDENTIFIED BY andy1234 DEFAULT TABLESPACE andy_unfs4m;
create table andy_unfs1m.tab_unfs1m (id number,name varchar2(10));
create table andy_unfs4m.tab_unfs4m (id number,name varchar2(10));
SQL> select * from dba_extents where owner in (upper('andy_unfs1m'),upper('andy_unfs4m'));
OWNER SEGMENT_NAME PARTI SEGMENT_TYPE TABLESPACE_N EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------ -------------------- ----- ------------------ ------------ ---------- ---------- ---------- ---------- ---------- ------------
ANDY_UNFS1M TAB_UNFS1M TABLE ANDY_UNFS1M 0 13 128 1048576 128 13
ANDY_UNFS4M TAB_UNFS4M TABLE ANDY_UNFS4M 0 14 128 4194304 512 14
结论3：oracle 11gr2 asm可变大小区 asm extent 0-19999 1AU ;20000-39999 4AU ;>40000 16AU。只作用于asm file，而文件大小不能超过4194303 blocks，因此对于smallfile tablespace ，database实例在shared pool里维护的asm实例传过来的asm extent map blocks块还是很多。而且这个特性需要改DATADG的属性compatible.rdbms才能生效 。
SQL> select sum(b) numextents from (
2 select NUMBER_KFFXP a,MAX(PXN_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by NUMBER_KFFXP
3 ) c;
NUMEXTENTS
----------
260271个asm extent
SQL> select sum(b) numaus from (
2 select DISK_KFFXP a,max(AU_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by DISK_KFFXP
3 ) c;
NUMAUS
----------
260342个AU
CREATE TABLESPACE andy_30G8m DATAFILE '+DATADG' SIZE 30G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;
select sum(b) numextents from (
select NUMBER_KFFXP a,MAX(PXN_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by NUMBER_KFFXP
) c;
NUMEXTENTS
----------
290991比260271增30720个asm extent
select sum(b) numaus from (
select DISK_KFFXP a,max(AU_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by DISK_KFFXP
) c;
NUMAUS
----------
291064比260342 增30722个AU
以上显示建一个表空间里一个30G文件，分了30722个AU和30720个asm extent，每个asm extent一个AU。
SQL> select name,name,compatibility,database_compatibility from v$asm_diskgroup;
NAME NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------------------------ ------------------------------ ------------------------------ ------------------------------
ARCHDG ARCHDG 11.2.0.0.0 10.1.0.0.0
DATADG DATADG 11.2.0.0.0 10.1.0.0.0
GRIDDG GRIDDG 11.2.0.0.0 10.1.0.0.0
SQL>
alter diskgroup DATADG set attribute 'compatible.rdbms'='11.2.0.0.0';
SQL> select name,name,compatibility,database_compatibility from v$asm_diskgroup;
NAME NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------------------------ ------------------------------ ------------------------------ ------------------------------
ARCHDG ARCHDG 11.2.0.0.0 10.1.0.0.0
DATADG DATADG 11.2.0.0.0 11.2.0.0.0
GRIDDG GRIDDG 11.2.0.0.0 10.1.0.0.0
SQL>
CREATE TABLESPACE andy_30G8m112 DATAFILE '+DATADG' SIZE 30G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;
select sum(b) numextents from (
select NUMBER_KFFXP a,MAX(PXN_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by NUMBER_KFFXP
) c;
NUMEXTENTS
----------
313678-290991 增22687个asm extent，20000*1AU+2687*4AU=30748AU（M）
select sum(b) numaus from (
select DISK_KFFXP a,max(AU_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by DISK_KFFXP
) c;
NUMAUS
----------
321800 增30816个AU
以上显示使asm可变大小区生效，建一个表空间一个30G文件，分了22687个asm extent，30816个AU，其中2687个asm extent分了4AU。
CREATE TABLESPACE andy_16G8m112 DATAFILE '+DATADG' SIZE 16G AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M;
select sum(b) numextents from (
select NUMBER_KFFXP a,MAX(PXN_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by NUMBER_KFFXP
) c;
NUMEXTENTS
----------
330062-313678=16384
select sum(b) numaus from (
select DISK_KFFXP a,max(AU_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by DISK_KFFXP
) c;
NUMAUS
----------
338203-321800=16403
以上显示，再创建一个表空间一个16G文件，仍然分了16384个asm extent和16403个AU，说明这个特性不是作用于整库或整个DG。
alter TABLESPACE andy_30G8m112 add DATAFILE '+DATADG' SIZE 16G AUTOEXTEND OFF;
SQL> select sum(b) numextents from (
2 select NUMBER_KFFXP a,MAX(PXN_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by NUMBER_KFFXP
3 ) c;
NUMEXTENTS
----------
346446-330062=16384
SQL> select sum(b) numaus from (
2 select DISK_KFFXP a,max(AU_KFFXP) b from x$kffxp where GROUP_KFFXP=2 group by DISK_KFFXP
3 ) c;
NUMAUS
----------
354589-338203=16386
SQL>
以上显示，对andy_30G8m112表空间加一个16G文件，仍然分了16384个asm extent和16386个AU，说明这个特性不是作用于表空间，只作用于文件。
