| |
USING INDEX
TABLESPACE TSI_BIO01 PCTFREE 20
STORAGE ( INITIAL 16384 NEXT 471556096 PCTINCREASE 80 ))
TABLESPACE TSD_BIO01
PCTFREE 20
PCTUSED 80
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1064960
NEXT 1048576
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
답변 ----------------------------------------
ㅇ TABLESPACE TSI_BIO01 PCTFREE 20 :
--> 어디서 가져오신 것인지 모르겠지만, 이런 문장이
연속되려면 OMF밖에 없을 듯 싶습니다. datafile ~ 절이
없네요.. pctfree 는 segment관리에서 있어야 하는 사항입니다. 9i의 경우엔
segment space management auto 명령으로 만들면 pctfree
pctused 를 사용해서 조절함이 필요없이 자동으로 segment
관리를 합니다.
--> 그러면 왜 pctfree, pctused 가 필요한지 알아볼까요?
row channing 현상과 row migration 현상에 대해서 알아야 합니다.
row channing은 db_block_size보다 너무 큰 데이터가 들어왔을 경우
인접한 다른 블록까지 데이터가 넘어가는 것을 말하죠. 이런 경우
만약에 큰 데이터의 특징을 가진다면 LOB type으로 다른 tablespace
에 저장하는 것이 옳긴 하겠죠. 하지만 row channing은 일반적인 현상입니다.
물론 9i 일 경우엔 파라미터에 db_?k_cache_size 를 주고
?k로 create tablespace ~~ blocksize ?k; 로 정하면 더 db_block_size
보다 더 큰 block_size를 가지는 tablespace를 만들고 그 안에
데이터를 넣는다면 rog channing을 방지할 수 있겠죠.
row channing은 어쩔 수 없는 현상이라고 할지라도.. row migration 은
꼭 막아야 하는 사항입니다. 이것은 예를들어 A라는 테이블에서
10번째 값이 insert되었습니다. 이어서 11번째 값이 insert되었구요.
그런데 10번째 값을 유저가 update시켰는데, update한 데이터가 예전의
데이터보다 크기가 큽니다. 그런데 db block에 11번째 값때문에 여유공간
이 없어서 다른 block으로 해당 데이터를 이동을 하는 것이죠.이런 것이 자주
일어날 경우 좋지 않습니다. 그래서 pctfree, pctused이라는 개념이
생긴 것이죠.. pctfree 10, pctused 40 이라고 한다면....
초기 빈 블록에 데이터가 들어가다가.. pctused 40%를 넘겼습니다. 그래도
계속해서 insert를 하지요. 그러다가 pctfree 10% 즉 데이터가 90%넘게
채워질 경우엔 더이상 데이터가 들어가지 않고 블록에 여유공간을 남겨둡니다.
file header 에서 freelist라는 것을 관리하는데 이것은 이 segment가
들어갈 빈공간이 있는지를 확인하는 것인데요. 위에서 90%를 넘기면
즉 pctfree공간이 10% 이하가 된다면... 그 블록을 freelist에서 제거를
하고 그 블록은 insert를 중단합니다. 다른 freelist에 등록된 빈
블록에 데이터를 넣겠죠.
나중에 데이터가 update가 될 경우를 블록의 10%만큼 미리 준비하는 것이죠.
그런데 만약에 그 블록의 데이터가 줄어든다면요? 90%보다 줄어들
경우 계속해서 0까지 줄어든다면 그 블록을 freelist에서 삭제된
상황이니 더 이상 insert가 안되겠죠. 이 경우 어떤 기준을
마련해두어야 할 것입니다. pctused는 이렇게 block에 데이터가
없어질경우 최소값을 말합니다.즉 최소한 이 만큼은 데이터가 들어가서
db block의 낭비를 최소화 하자는 것이죠. pctused가 필요한 이유입
니다. 우리가 90% 데이터에서 데이터가 삭제되거나 해서 90% 이하로
떨어지게 됩니다. 즉 pctfree값이 10% 보다 여유공간이 더 생기겠죠.
하지만 오라클은 pctfree 10%보다 더 큰 여유공간이 생겨도 freelist
에 그 블록을 추가하지 않습니다. 즉 데이터가 점점 줄다가 60%정도의
데이터가 되었을 지라도 freelist에 등록하지 않는 것이죠.
이제 pctused 40% 보다도 적어지는 상황이 생길 경우엔요? 이 경우에서
야 비로소 데이터를 넣을 수 있도록 freelist에 등록을 합니다.
하지만 9i에서는 pctfree, pctused 방법은 권하지 않습니다.
segment space management auto 절을 넣어서 auto로 관리하도록하죠.
참고로 auto방식의 경우엔 pctfree만 있습니다. 블록을 25%씩 4개로
나우어서 관리하는 방식입니다. auto를 쓰면 훨씬 데이터 블록
낭비를 막고 row migration 도 해결해줍니다. 수동은 권하지 않음
ㅇ STORAGE ( INITIAL 16384 NEXT 471556096 PCTINCREASE 80 )) :
위에서 storage 절은 extent할당 방식을 말합니다.
두가지 방식이 있는데 8i에서는 default 가 extent 를 할당할때에
dictionary extent managed tablespace를 사용했습니다. 물론 이때도
locally extent managed tablespace를 만들 수는 있었죠. 9i에서 와서는
locally extent managed tablespace가 default값입니다.
두개의 차이는 extent 할당하는 방식에 따릅니다.
간단히 말해서 dictionary extent tablespace를 생성할 경우엔 이 테이블
스페이스의 extent 할당시에 그 정보를 system tablespae의 base table
에서 가져옵니다. 하지만 extent할당을 여기저기서 하다보니 system
tablespace를 조회해서 extent를 어디를 할당하면 될 것인지 확인을
하고 extent를 할당후 할당정보를 system tablespace에 저장합니다.
병목현상이 엄청 나게 많아지겠죠.
locally extent manage tablespace의 경우엔 그렇지 않습니다. system tablespace
의 base table에서 extent 여유공간 정보를 가져오는 것이 아니라
해당 데이터파일 헤더에 직접 bitmap block으로 저장됩니다.
그래서 extent할당시에 system tablespac에서 찾아서 할당하는 것이 아니라
자신이 속한 테이터파일의 헤더에서 bitmap block에서 찾겠죠.
즉 경합현상이 거의 발생하지 않습니다. 오라클사에서는 dictionary
방식을 사용하지 말고 locally tablespace를 사용하도록 권합니다.
9i에서 system tablespace가 locally tablespace일 경우, 일반
tablespace생성시 default storage절(dictionary 방식)은 사용되지 않습니다. 에러남
하지만 system tablespace가 dictionary tablespace일 경우엔
default storage절(dictionary 방식)을 통해서 create tablespace를 사용할 수
있겠죠. 만약에 create table 절에 storage를 준다면 어떻게
될까요? 이런 경우 extenet management local 방식의 경우엔
질문사항인 storage 절을 주게 되면 모두 무시됩니다. 즉 tablespace
에서 정한 uniform size 대로 default 라면 1M 만큼의 extent할당을
합니다.
그렇다면 system tablespace가 dictionary tablesapce라면?
이 경우 일반 tablespace를 만들 경우에 default storage절을
지정할 수 있습니다. 하지만 create table 명령에서 질문과 같이
storage절을 주게 되면 tablespace의 설정은 모두 무시되고
create table 설정을 따릅니다. 맨 아래에 예제를 두니 참고하세요.
그러면 여기서 STORAGE ( INITIAL 16384 NEXT 471556096 PCTINCREASE 80 ))
는 무엇을 뜻하는가..
system tablespace가 locally extent managed tablespace에서는 위 절을 이용
하는 것이 불가능하니, dictionary extent managed tablesapce라고 하겠습니다.
또한 tablespace도 dictionary extent managed tablesapce 이구요.
create table test ( id number ) storage STORAGE ( INITIAL 16384 NEXT
471556096 PCTINCREASE 80 )); 이라고 주게되면 초기에 이 테이블에 extent는
16384 바이트만큼 미리 할당됩니다. 그리고 나서 extent안에 db block들에
데이터가 들어갔는데 데이터가 많이 들어가서 모두 데이터가 찼을 경우
새로운 extent를 할당합니다. 이때에 next 만큼을 할당하는 것이죠.
그런데 가장 문제가 있는 pctincrease 80 이라고 했네요. 그 다음 next의
경우엔 80% 만큼 더 크게 늘어납니다.
extent1 에서는 16384
extent2 에서는 16384 + 471556096
extent3 에서는 ( 16384 + 471556096 + 471556096 )*(1+0.8) =>
--> 계산법은 확실치 않음..
이렇든 pctincrease를 쓰게 되면 쓸데없이 한꺼번에 extent를 할당받으니
다른 table에서 그 만큼 이 공간을 쓰지 못하는 꼴이 되고 맙니다.
pctincrease는 쓰면 안됩니다.
그리고 dictionary 방식은 쓰지 말고 locally 방식으로 uniform size 를
지정해서 사용하세요.. uniform size 를 어느정도 주어야 하나가
고민이 된다면 10M 정도로 그냥 unique 하게 주세요. 단 너무 크면
안됩니다.
그런데 여러 테이블의 데이터가 들어가는데.. 너무 많이 extent
가 생겨서 다른 테이블의 데이터와 섞이면 안되는 것이 아닌가?
이것은 생각하지 마세요..~ 왜냐면 SMON이라는 프로세스가 알아서
합니다. 오라클이 알아서 하니 생각할 필요 없죠.
ㅇ PCTFREE 20 : -> 위에서 설명한 내용, 즉 update를 위해서 db block에
최소한 20% 정도의 공간을 남겨두겠다.
ㅇ PCTUSED 80 : -> 위에서 설명한 내용, 즉 pctfree로 인한 공간낭비를 막기
위해서 최소한 80% 만큼의 데이터는 꼭 넣도록 하겠다.
ㅇ INITRANS 1 : -> segment에 트랜젝션이 최소 한개 이상 걸릴 수있도록 한다.
ㅇ MAXTRANS 255 : -> segment에 트랜잭션이 최대 255개까지 걸릴 수 있도록 한다.
ㅇ INITIAL 1064960 : -> 위에서 설명 초기의 extent 크기
ㅇ NEXT 1048576 : -> 위에서 설명 초기 extent할당후 extent가 부족할 경우
다음의 extent 할당시 크기
ㅇ PCTINCREASE 0 : -> 0%씩 늘어나겠다는 이야기니.. 무시되겠죠.
ㅇ MINEXTENTS 1 : -> 최소한의 extent를 할당하겠다..
initial 10K 인데 minextents가 2 면요?
table을 생성하면서 20K를 미리 할당합니다.
20부터 시작하죠..~
ㅇ MAXEXTENTS 2147483645 : -> 최대한의 extent를 말합니다. 이거 썼다가는
데이터가 커지면 문제가 생길듯합니다.
ㅇ FREELISTS 1 FREELIST GROUPS 1 ) : -> 데이터블록의 segment관리에서
한개 freelist에서 free block을 찾고 데이터를 넣는 것에 많은 트랜잭션이 처리를 할 경우
성능이 나빠지겠죠? 그래서 freelist의 경우에 여러개가 있으면 분산이 되기에 좋겠죠. freelist group은 freelist를 몇개를 freelist group으로 사용하겠다는 뜻이겠죠.
ㅇ NOCACHE; 아래를 참고..
CACHE | NOCACHE | CACHE READS
Use the CACHE clauses to indicate how Oracle should store blocks in the buffer cache. If you specify neither CACHE nor NOCACHE:
In a CREATE TABLE statement, NOCACHE is the default
In an ALTER TABLE statement, the existing value is not changed.
CACHE Clause
For data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
As a parameter in the LOB_storage_clause, CACHE specifies that Oracle places LOB data values in the buffer cache for faster access.
Restriction on CACHE
You cannot specify CACHE for an index-organized table. However, index-organized tables implicitly provide CACHE behavior.
NOCACHE Clause
For data that is not accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.
As a parameter in the LOB_storage_clause, NOCACHE specifies that the LOB value is either not brought into the buffer cache or brought into the buffer cache and placed at the least recently used end of the LRU list. (The latter is the default behavior.)
Restriction on NOCACHE
You cannot specify NOCACHE for index-organized tables.
CACHE READS
CACHE READS applies only to LOB storage. It specifies that LOB values are brought into the buffer cache only during read operations, but not during write operations.
------------ 제가 테스트한 로그를 드립니다. --------------
SQL> create tablespace testdic
2 datafile "$DATA/u06/testdic.dbf" size 10M
3 extent management dictionary
4 default storage ( initial 20k next 20k );
Tablespace created.
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
SQL> select tablespace_name, initial_extent, next_extent, min_extents, max_extents
2 from dba_tablespaces where tablespace_name="TESTDIC";
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ -------------- ----------- -----------
MAX_EXTENTS
-----------
TESTDIC 20480 20480 1
249
SQL> create user userdic
2 identified by oracle
3 default tablespace testdic;
User created.
SQL> grant connect, resource to userdic;
Grant succeeded.
SQL> conn userdic/oracle
Connected.
SQL> create table dictable
2 (id number,
3 var varchar2(10))
4 storage ( initial 100k next 100k );
Table created.
SQL>
SQL> conn sys/oracle as sysdba
Connected.
SQL> select owner, segment_name, segment_type, tablespace_name, initial_extent, next_extent
2 from dba_segments where owner="USERDIC";
OWNER
------------------------------
SEGMENT_NAME
----------------------------------------------------------------------
----------
SEGMENT_TYPE TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------ ------------------------------ -------------- -----------
USERDIC
DICTABLE
TABLE TESTDIC 102400 102400
SQL> conn userdic/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DICTABLE TABLE
SQL> alter table dictable
2 storage ( initial 30k next 30k);
storage ( initial 30k next 30k)
*
ERROR at line 2:
ORA-02203: INITIAL storage options not allowed
SQL> alter table dictable
2 storage ( next 60k );
Table altered.
SQL> select owner, segment_name, segment_type, tablespace_name, initial_extent, next_extent
2 from user_segments ;
select owner, segment_name, segment_type, tablespace_name, initial_extent, next_extent
*
ERROR at line 1:
ORA-00904: "OWNER": invalid identifier
SQL> desc user_segments
Name Null? Type
----------------------------------------- -------- ----------------------------
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
BUFFER_POOL VARCHAR2(7)
SQL> select segment_name, segment_type, tablespace_name, initial_extent, next_extent
2 from user_segments ;
SEGMENT_NAME
----------------------------------------------------------------------
----------
SEGMENT_TYPE TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------ ------------------------------ -------------- -----------
DICTABLE
TABLE TESTDIC 102400 61440
SQL> conn sys/oracle as sysdba
Connected.
SQL> desc dba_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL>
SQL> create tablespace localtbs
2 datafile "$DATA/u06/localtbs01.dbf" size 10M
3 extent management local uniform size 20k;
Tablespace created.
SQL> create user localuser
2 identified by oracle
3 default tablespace localtbs;
User created.
SQL> grant connect, resource to localuser;
Grant succeeded.
SQL> desc dba_users
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
SQL> select username, default_tablespace
2 from dba_users where username="LOCALUSER";
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
LOCALUSER LOCALTBS
SQL> conn localuser/oracle
Connected.
SQL> create table localtable
2 (id number,
3 val varchar2(10))
4 storage ( initial 40k next 40k );
Table created.
SQL> conn sys/oracle as sysdba
Connected.
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
SQL> select tablespace_name, initial_extent, next_extent from dba_tablespaces
2 where tablespace_name="LOCALTBS";
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
LOCALTBS 20480 20480
SQL> desc dba_tables
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
SQL> select table_name, initial_extent, next_extent from dba_tables
2 where owner="LOCALUSER";
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
LOCALTABLE 40960 20480
SQL> conn localuser/oracle
Connected.
SQL> alter table localtable
2 storage ( initial 60k next 60k );
storage ( initial 60k next 60k )
*
ERROR at line 2:
ORA-02203: INITIAL storage options not allowed
SQL> alter table localtable
2 storage ( next 60k);
alter table localtable
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted
SQL> spool off;
dictionary 일 경우 table 생성시 storage절에서 initial, next를 맘대로 정할 수 있고 적용되나, 변경시에는 initial은 불가능 next는 가능, locally tablespace의 경우, table생성시 initial은 맘대로 변경해도 tablespace 설정을 따르며 테이블생성시 정한 것은 무시됨, next의 경우 table 생성시 설정을 따름며 적용됨
또한 locally의 경우 initial , next 변경 불가능
|