Oracle 區 EXTENT

pxbibm發表於2014-08-13
Oracle的基本概念extent,理解Oracle的基本知識原理,透過實驗加深的對基本概念的理解,
為後期學習資料最佳化打下堅實的基礎。
什麼是extent,一次分配的連續的oracle
建立表時分配新的區(extent)(10g),11g中不會立刻分配區,只有插入了第一條資料後才會分配區。注意版本的區別。
conn system/manager
grant select any dictionary to scott;
使scott使用者可以檢視資料字典。
SQL> conn scott/tiger
Connected.
徹底的清除表t1.
SQL> DROP TABLE t1 PURGE;
Table dropped.
 
建立新的表t1.
SQL>CREATE TABLE t1 AS SELECT * FROM EMP;
Table created.
 
SQL> SELECT SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    WHERE OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                           0          4         89          8
T1表建立的時候會分配新的空間,這個空間叫做初始區EXTENT,即使是一個空表也有初始區EXTENT(10g之前的版本,11g後只有插入第一條資料後才分配),初始區EXTENT和其它以後的區EXTENT都不同,因為初始區EXTENT中含有表頭塊,這個資料塊中沒有我們的資料。首先資料庫要找到在哪個表空間中分配空間,如果我們在建立表的時候沒有指明,那麼就從預設表空間中分配。我們scott使用者的預設表空間是users,users表空間的資料檔案是4號檔案.
在4號檔案中的第89個塊以後有連續8個空閒空間,為什麼要分派8個資料塊,而不是其它數量的資料塊呢?這要由users表空間的屬性來決定。這個屬性是在我們建立表空間時決定的。
 
SQL> SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS
    FROM DBA_TABLESPACES;
 
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
--------------- -------------- ----------- ----------- -----------
SYSTEM                   65536                       1 2147483645
UNDOTBS1                 65536                       1 2147483645
SYSAUX                   65536                       1 2147483645
TEMP                   1048576     1048576           1
USERS                    65536                       1 2147483645
TEMP2                  1048576     1048576           1
BIGTS                    65536                       1 2147483645
TP1                    1048576     1048576           1
TL                       65536                       1 2147483645
TS1                      65536                       1 2147483645
USERS表空間INITIAL_EXTENT初始區大小(65536),資料庫的塊大小為8192*8=INITIAL_EXTENT。
 
我們向表t1中新增新的資料,增長時分配新的區(extent)
SQL> insert into t1 select * from t1;
 
14 rows created.
增加一倍的資料。
SQL> SELECT SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS
    FROM DBA_EXTENTS
    WHERE OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4        89          8
我們的t1表沒有分配新的空間,因為8個塊可以存放所有的行。
SQL> insert into t1 select * from t1;
 
28 rows created.
增加一倍的資料。
 
SQL> SELECT SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    WHERE OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
我們的t1表沒有分配新的空間,因為8個塊可以存放所有的行。
 
SQL> insert into t1 select * from t1;
 
56 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
我們的t1表沒有分配新的空間,因為8個塊可以存放所有的行。
 
 
SQL> insert into t1 select * from t1;
 
112 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
我們的t1表沒有分配新的空間,因為8個塊可以存放所有的行。
 
SQL> insert into t1 select * from t1;
 
224 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
我們的t1表沒有分配新的空間,因為8個塊可以存放所有的行。
 
SQL> insert into t1 select * from t1;
 
448 rows created.
增加一倍的資料。
 
SQL> SELECT SEGMENT_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
我們的t1表分配新的空間,因為8個塊不能存放所有的行。必須要有新的空間來存放資料。
為什麼是521呢?因為在521資料塊前有其它表的資料。資料庫在521以後找到了連續的8個資料塊。
 
SQL> insert into t1 select * from t1;
 
896 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
我們的t1表沒有分配新的空間,因為16個塊可以存放所有的行。
SQL> insert into t1 select * from t1;
 
1792 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
T1                            2          4        537          8
T1                            3          4        545          8
我們的t1表分配新的空間,因為16個塊不能存放所有的行。必須要有新的空間來存放資料。
為什麼是537呢?因為在537資料塊前有其它表的資料。資料庫在537以後找到了連續的8個資料塊。
空間還是不夠,又分配了新的範圍。在545資料塊後又分配了8個塊。
 
 
SQL> insert into t1 select * from t1;
 
3584 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
 2 BLOCK_ID,BLOCKS from dba_extents
 3 where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
T1                            2          4        537          8
T1                            3          4        545          8
T1                            4          4        553          8
T1                            5          4        561          8
T1                            6          4        569          8
又分配了3個新的範圍。
SQL> insert into t1 select * from t1;
 
7168 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
T1                            2          4        537          8
T1                            3          4        545          8
T1                            4          4        553          8
T1                            5          4        561          8
T1                            6          4        569          8
T1                            7          4        577          8
T1                            8          4        585          8
T1                            9          4        593          8
T1                           10          4        601          8
T1                           11          4        609          8
又分配了4個新的範圍。
 
SQL> insert into t1 select * from t1;
 
14336 rows created.
增加一倍的資料。
 
SQL> select SEGMENT_NAME,EXTENT_ID,FILE_ID,
    BLOCK_ID,BLOCKS from dba_extents
    where OWNER='SCOTT' AND SEGMENT_NAME='T1';
 
SEGMENT_NAME          EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
-------------------- ---------- ---------- ---------- ----------
T1                            0          4         89          8
T1                            1          4        521          8
T1                            2          4        537          8
T1                            3          4        545          8
T1                            4          4        553          8
T1                            5          4        561          8
T1                            6          4        569          8
T1                            7          4        577          8
T1                            8          4        585          8
T1                            9          4        593          8
T1                           10          4        601          8
T1                           11          4        609          8
T1                           12          4        617          8
T1                           13          4        625          8
T1                           14          4        633          8
T1                           15          4        641          8
T1                           16          4        137        128
 
為什麼最後一個範圍的大小是128個資料塊,而不是8個了。因為資料庫已經分配了16個範圍,共分配了1M的空間,我們還要求分配新的空間,資料庫認為這個表很大,所以就一次分了128個資料塊。如果我們再增加表的大小,資料庫會分配多個1m,以後就8m為大小分配,再以後就64m大小分配。總之是一個原則,我們現有的資料越大,未來的範圍就越大。
上面的情況都是Oracle資料庫自動完成的,但是我們也可以手工分配區extent
alter table t1 allocate extent;
alter table t1 allocate extent(datafile 'D:\ORACLE\ORADATA\O10\USERS01.DBF size 9k);
 
手工回收未使用的範圍
alter table t1 deallocate unused;
檢視結果
select SEGMENT_NAME,EXTENT_ID,FILE_ID,
BLOCK_ID,BLOCKS from dba_extents
where OWNER='SCOTT' AND SEGMENT_NAME='T1';

總結:
根據該實驗我們加深對ORACLE工作原理機制。
1.我們可以在插入大量資料時,可以手動分配一下區,這樣在插入的時候節省了分配區的時間,能改善Oracle的效能,提高插入的速度。
2.我們可以對以下資料表進行統計分析,分析一些插入資料頻繁的表,估算出一個週期的增長量,根據增長量,我們預先分配好區,來提升效能。



2014.08.13 09:24
share you knowledge with the world. 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12798004/viewspace-1250248/,如需轉載,請註明出處,否則將追究法律責任。

相關文章