White Paper
1998 3 1998 7 NULL 1998 9 2 8.03 Design & Migration Services Oracle Corporation Japan 1998 Printed in Japan Oracle and SQL*Loader are registered trademarks. Oracle7 Oracle Corporation
Oracle I/O Oracle Oracle 2 1
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
PCTFREE PCTFREE 1 KCBH + UB4 + KTBBH + (INITRANS - 1) * KTBIT + KDBH KCBH UB4 KTBBH KTBIT KDBH V$TYPE_SIZE INITRANS INITRANS 1 255 1 KCBH + UB4 + KTBBH + (INITRANS - 1) * KTBIT + KDBH 20 + 4 + 48 + (1-1) * 24 + 14 86 (Byte) 3
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((2048 86) * (1 10 / 100)) - 4 CEIL(1962 * 0.9) - 4 1766-4 1962(Byte) 4
(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 --------- --------- --------- --------- --------- --------- --------- --------- 2.9285714 5 6.8571429 2.7857143 7 2.4285714.5 2 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 ---------------- 29.5 5
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
1 CHAR(15) column size overhead 15 1 16 (Byte) 2 DATE column size overhead 7 1 8 (Byte) 3 NUMBER(10) column size overhead 6 1 7 (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, 34 + 2 MAX 9, 34 + 2 34 2 36 Byte 7
/ / 1762 / 36 48 CEIL CEIL CEIL 20000 / 48) 417 417 * 2048 854016 (Byte) 0.81 (MB) 8
9 Oracle 3 1 10 20% l NULL l l l PCTFREE INITRANS l V$TYPE_SIZE 3 PCTFREE INITRANS
INITRANS 1 2 113 24 * INITRANS 113 24 * 2 161 (Byte) PCTFREE PCTFREE / 100 1 PCTFREE / 100 ( ) * (1 (PCTFREE / 100)) (2048 161) * (1 10 / 100) 1887 * 0.9 1698.3 1699 (Byte) 4 ROWID ROWID 10
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 15 1 16 (Byte) NUMBER(10) column size overhead 6 1 7 (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
MAX UB1*3 UB4 SB2, SB2 MAX 1*3 4 2, 26 + 2 MAX 9, 26 + 2 28 Byte ROWID ROWID F V ROWID 1 ROWID F V 2 6 127 * 1 128 * 2 ROWID F V 2 6 (2 * 1 0 * 2) 28 38 (Byte) FLOOR ( / ) FLOOR FLOOR ( / ) FLOOR (1699 / 38) 44 12
null 1.05 CEIL null CEIL1.05 null CEIL1.05 * 20000 / 44) 478 478 * 2048 978944Byte NOSORT CREATE INDEX 13
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 5 5 14
NEXT INITIAL 10% 5 OS INITIAL K M KB MB INITIAL NEXT ALTER TABLESPACE storage NEXT 5 5 MAXEXTENTS UNLIMITED OS 1 OS UNLIMITED 2048 121 4096 249 8192 505 Oracle7.3 UNLIMITED UNLIMITED 2G Oracle MAXEXTENTS NEXT ALTER TABLE EXAMPLE STORAGE(MAXEXTENTS 110, NEXT 5M); MINEXTENTS 1 1 OS PCTINCREASE 0 50 0 OS 3 % 0 15
872448 * 1.1 959693 (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
VARCHAR2 VARCHAR2(100) ABC 100 3 VARCHAR2 VARCHAR2 NUMBER VARCHAR2 NULL NULL NULL NULLTEST CHAR SQL NULL 17
byte NULL byte NULL SQL NULL NULL byte NULL SQL 18
byte NULL NULL byte NULL NULL OPTIMAL Oracle DELETE DROP TRUNCATE Oracle Oracle 900K 800K 1M 2 1.7M 19
Oracle I/O SQL> alter table example allocate extent 2 (size 500k datafile 'C: ORAWIN95 DATABASE example02.ora'); 500K 600K 1M IMPORT/EXPORT 20
21