領域サイズの見積方法

Size: px

Start display at page:

Download "領域サイズの見積方法"

あきみみやまる
6 years ago
Views:

1 White Paper

2 NULL Design & Migration Services Oracle Corporation Japan 1998 Printed in Japan Oracle and SQL*Loader are registered trademarks. Oracle7 Oracle Corporation

3 Oracle I/O Oracle Oracle 2 1

4 l l l l PCTFREE INITRANS l V$TYPE_SIZE V$TYPE_SIZE SQL> select type, type_size from v$type_size 2 where type in ('KCBH','UB4','KTBBH','KTBIT','KDBH','KDBT','UB1','SB2'); TYPE TYPE_SIZE UB1 1 UB4 4 SB2 2 KCBH 20 KTBIT 24 KTBBH 48 KDBH 14 KDBT 4 8 ; 2

5 PCTFREE PCTFREE 1 KCBH + UB4 + KTBBH + (INITRANS - 1) * KTBIT + KDBH KCBH UB4 KTBBH KTBIT KDBH V$TYPE_SIZE INITRANS INITRANS KCBH + UB4 + KTBBH + (INITRANS - 1) * KTBIT + KDBH (1-1) * (Byte) 3

6 PCTFREE DB_BLOCK_SIZE PCTFREE A * PCTFREE / 100 CEIL * PCTFREE / 100 KDBT CEIL * 1 PCTFREE / 100 KDBT CEIL PCTFREE KDBT V$TYPE_SIZE SQL> select name, value from v$parameter where name='db_block_size'; NAME VALUE db_block_size 2048 CEIL(( - ) *(1 - PCTFREE / 100))- KDBT CEIL(( ) * (1 10 / 100)) - 4 CEIL(1962 * 0.9) (Byte) 4

7 (vsize(colname)) ANALYZE USER_TABLES AVG_ROW_LEN visize(colname) SQL> desc emp; NULL? EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select avg(vsize(empno)) empno, 2 avg(nvl(vsize(ename), 0)) ename, 3 avg(nvl(vsize(job), 0)) job, 4 avg(nvl(vsize(mgr), 0)) mgr, 5 avg(nvl(vsize(hiredate), 0)) hiredate, 6 avg(nvl(vsize(sal), 0)) sal, 7 avg(nvl(vsize(comm), 0)) comm, 8 avg(nvl(vsize(deptno), 0)) deptno 9 from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SQL> select (avg(vsize(empno)) + avg(nvl(vsize(ename), 0)) 2 + avg(nvl(vsize(job), 0)) + avg(nvl(vsize(mgr), 0)) 3 + avg(nvl(vsize(hiredate), 0)) + avg(nvl(vsize(sal), 0)) 4 + avg(nvl(vsize(comm), 0)) + avg(nvl(vsize(deptno), 0)) ) 5 average_row_size 6 from emp; AVERAGE_ROW_SIZE

8 2 column size overhead column size CHAR VARCHAR2 NUMBER p 1 CEIL(p / 2) 1 38 DATE 7Byte CEIL overhead column size 250Byte 1 column size 250Byte 3 3 * UB1 UB1 V$TYPE_SIZE 6

9 1 CHAR(15) column size overhead (Byte) 2 DATE column size overhead (Byte) 3 NUMBER(10) column size overhead (Byte) 3 * UB1 3 * 1 (16 7 8) 34 (Byte) MAX UB1*3 UB4 SB2, SB2 MAX A, B UB1 UB4 SB2 A B V$TYPE_SIZE MAX UB1*3 UB4 SB2, SB2 MAX 1*3 4 2, MAX 9, Byte 7

10 / / 1762 / CEIL CEIL CEIL / 48) * (Byte) 0.81 (MB) 8

11 9 Oracle % l NULL l l l PCTFREE INITRANS l V$TYPE_SIZE 3 PCTFREE INITRANS

12 INITRANS * INITRANS * (Byte) PCTFREE PCTFREE / PCTFREE / 100 ( ) * (1 (PCTFREE / 100)) ( ) * (1 10 / 100) 1887 * (Byte) 4 ROWID ROWID 10

13 column size overhead column size overhead column size 127Byte 1 column size 127Byte 2 3 * UB1 UB1 V$TYPE_SIZE CHAR(15) column size overhead (Byte) NUMBER(10) column size overhead (Byte) 3 * UB1 3 * 1 (16 7) 26 (Byte) MAX UB1*3 UB4 SB2, SB2 MAX A, B UB1 UB4 SB2 A B V$TYPE_SIZE 11

14 MAX UB1*3 UB4 SB2, SB2 MAX 1*3 4 2, MAX 9, Byte ROWID ROWID F V ROWID 1 ROWID F V * * 2 ROWID F V 2 6 (2 * 1 0 * 2) (Byte) FLOOR ( / ) FLOOR FLOOR ( / ) FLOOR (1699 / 38) 44 12

15 null 1.05 CEIL null CEIL1.05 null CEIL1.05 * / 44) * Byte NOSORT CREATE INDEX 13

16 STORAGE STORAGE STORAGE l INITIAL l NEXT l MAXEXTENTS l MINEXTENTS l PCTINCREASE l INITIAL l l INITIAL 5 OS K M KB MB ALTER TABLE storage INITIAL

17 NEXT INITIAL 10% 5 OS INITIAL K M KB MB INITIAL NEXT ALTER TABLESPACE storage NEXT 5 5 MAXEXTENTS UNLIMITED OS 1 OS UNLIMITED Oracle7.3 UNLIMITED UNLIMITED 2G Oracle MAXEXTENTS NEXT ALTER TABLE EXAMPLE STORAGE(MAXEXTENTS 110, NEXT 5M); MINEXTENTS 1 1 OS PCTINCREASE OS 3 % 0 15

18 * (Byte) 1 (MB) CREATE TABLE example( name CHAR(15), today DATE, id NUMBER(10) )STORAGE( INITIAL 1M, NEXT 100K, MAXEXTENTS UNLIMITED, MINEXTENTS 1, PCTINCREASE 0 ); INITIAL SQL*Loader INITIAL INITIAL %sqlldr DATA=example1.dat DIRECT=true PARALELL=TRUE FILE=/dev/D1 %sqlldr DATA=example2.dat DIRECT=true PARALELL=TRUE FILE=/dev/D2... %sqlldr DATA=example10.dat DIRECT=true PARALELL=TRUE FILE=/dev/D10 CREATE TABLE example( name CHAR(15), today DATE, id NUMBER(10) )STORAGE( INITIAL 64K, NEXT 100MB, MAXEXTENTS UNLIMITED, MINEXTENTS 1, PCTINCREASE 0 )PARALELL (DEGREE 10); 16

19 VARCHAR2 VARCHAR2(100) ABC VARCHAR2 VARCHAR2 NUMBER VARCHAR2 NULL NULL NULL NULLTEST CHAR SQL NULL 17

20 byte NULL byte NULL SQL NULL NULL byte NULL SQL 18

21 byte NULL NULL byte NULL NULL OPTIMAL Oracle DELETE DROP TRUNCATE Oracle Oracle 900K 800K 1M 2 1.7M 19

22 Oracle I/O SQL> alter table example allocate extent 2 (size 500k datafile 'C: ORAWIN95 DATABASE example02.ora'); 500K 600K 1M IMPORT/EXPORT 20

23 21

Oracleの領域管理～トラブル防止のテクニック～

Oracleの領域管理～トラブル防止のテクニック～ THE Database FOR Internet Computing Oracle ... 3... 4... 4... 5... 5... 7... 8... 9... 12... 13... 13... 16... 17... 18... 20... 22... 23... 23... 24... 25... 25... 26... 28... 31... 31... 31... 33...