ハイウォーターマークを知る

Size: px

Start display at page:

Download "ハイウォーターマークを知る"

ゆたかこのえ
4 years ago
Views:

1 THE Database FOR Network Computing

2 Oracle Oracle Oracle7 Oracle8 Oracle8 Enterprise Edition R8.0.4 for Windows NTOracle7 Server R7.3.4 for Windows NT Oracle7Oracle8 Oracle,Oracle7,Oracle8

3 SQL*Loader DEALLOCATE UNUSED... 7

4 (HWM:High Water Mark) Oracle Oracle 10, Block HWM 90 DELETE -1-

5 1.3. USER_SEGMENTS USER_EXTENTS DBMS_SPACE DBMS_SPACE UNUSED_SPACE FREE_BLOCKS UNUSED_SPACE UNUSED_SPACE IN IN IN OUT OUT OUT OUT 'TABLE','INDEX','CLUSTER' total_blocks unused_blocks OUT HWM ID OUT HWM ID OUT HWM HWM IN -2-

6 UNUSED_SPACE SQL*Plus USER_DATA TEST UNUSED_SPACE TEST print -3-

7 TOTAL_BLOCKS,TOTAL_BYTESTEST 50 (=50 2K=100K) 49 TEST TOTAL_BLOCKS UNUSED_BLOCKS LAST_USED_EXTENT_FILE_ID 11 LAST_USED_EXTENT_BLOCK_ID ID= LAST_USED_BLOCK 1.4. TRUNCATE TRUNCATE DROP STORAGE INITIAL,MINEXTENTS REUSE STORAGE DROP STORAGE REUSE STORAGE DROP STORAGE DROP STORAGE REUSE STORAGE -4-

8 REUSE STORAGE DROP STORAGE( ) TRUNCATE DELETE DELETE DELETE DELETE WHERE DELETE ROLLBACK TRUNCATE DDL PL/SQL DBMS_SQL WHERE 1.5. Oracle SQL*Loader (DEALLOCATE UNUSED) -5-

9 Oracle Oracle HWM SQL*Loader SQL*Loader Oracle ( ) INSERT INSERT -6-

10 HWM DEALLOCATE UNUSED DEALLOCATE UNUSED Oracle7 R7.3 INITIAL MINEXTENTS DEALLOCATE UNUSED KEEP integer integer DEALLOCATE UNUSED INITIAL MINEXTENTS DEALLOCATE UNUSED -7-

11 DEALLOCATE UNUSED HWM HWM -8-

12 UNUSED_SPACE SQL 2.2. FULLSCAN SQL*Loader DEALLOCATE UNUSED CONVLDR DRCTLDR FULLSCAN 100,000 CONVLDR DRCTLDR 10,000 SQL*Plus ( )

13 CONVLOAD DRCTLOAD UNUSED_SPACE FULLSCAN ,400 1,433, ,352 20, CONVLOADDRCTLOAD , , ,912 20,

14 2.3. SELECT COUNT(*) FROM FULLSCAN SQL UNUSED_SPACE HWM 100,000 SQL 689 DELETE UNUSED_SPACE HWM DELETE HWM ,433,600 1,433, ,480 20,

15 SELECT COUNT(*) FROM FULLSCAN SQL 689 ( 128 ) TRUNCATE HWM HWM HWM ,400 1,433,600 1,433, , ,352 20,480 20, ,

16 DELETE HWM (7002K=1400K) 2.4. SQL*Loader HWM DELETE CONVLOAD,DRCTLOAD UNUSED_SPACE HWM HWM , , ,480 20, d10000.txt CONVLOAD Windows 95/NT Oracle SQL*Loader R7.3sqlldr73.exeR8.0sqlldr80.exe SQL*Loader (convload.ctl) CONVLOAD HWM TOTAL_BLOCKS , , , ,480 20,480 10,

17 DRCTLOAD SQL*Loader (drctload.ctl) DRCTLOAD HWM 70(=8010) 139(=1401) HWM , , , ,480 20, DEALLOCATE UNUSED SQL*Loader DRCTLOAD DEALLOCATE UNUSED DRCTLOAD DEALLOCATE UNUSED DRCTLOAD HWM HWM , , , ,912 2, DEALLOCATE UNUSED HWM DEALLOCATE UNUSED HWM -14-

18 TRUNCATE HWM HWM HWM Import/Export CREATE TABLE AS SELECT TRUNCATE STORAGE ,000 HWM 5,000 HWM SQL*Plus SHRKTEST

19 HWM , , , ,912 20,480 20, Import/Export Import/Export Export Export SHRKTEST Windows 95/NT Oracle Export R7.3exp73.exeR8.0exp80.exe Oracle7 R7.3 Export R7.3 direct=y exp help=y SHRKTEST Import Windows 95/NT Oracle Import R7.3imp73.exeR8.0imp80.exe HWM 70(=8010) HWM 36(=8044) , , , ,912 20,480 90,

20 COMPRESS=Y COMPRESS=Y INITIAL Export/Import COMPRESS=Y COMPRESS=Y COMPRESS=N K INITIAL 160K COMPRESS=Y COMPRESS=N INITIAL COMMPRESS=Y -17-

21 3.4. CREATE TABLE AS SELECT CREATE TABLE AS SELECT HWM CREATE TABLE AS SELECT CREATE TABLE AS SELECT Oracle7 R7.2 UNRECOVERABLE 2. 3.SQL*Plus RENAME 4.HWM 40 (2 ) 70(8010) HWM 36(=404) DELETE , ,840 81, ,912 20,480 8,

22 CREATE TABLE AS SELECT CREATE TABLE AS SELECT( CTAS) SQL UNRECOVERABLE REDO (COMMIT ) UNRECOVERABLE Oracle7 R7.2 UNRECOVERABLE CTAS Oracle8 UNRECOVERABLE NOLOGGING UNRECOVERABLE NOLOGGING Oracle8 LOGGING/NOLOGGING RECOVERABLE NOLOGGING LOGGING CTAS TABLESPACE STORAGE DEFAULT STORAGE CTAS STORAGE TABLESPACE NOLOGGING(UNRECOVERABLE) AS CTAS NOT NULL SHRKTEST 100,000 CTAS REDO NOLOGGING REDO 1440K 33K 1440K 33K 42K 42K 1450K 42K -19-

23 3.5. TRUNCATE CREATE TABLE AS SELECT TRUNCATE INSERT SELECT 1.CREATE TABLE AS SELECT temp PCTFREE=0 2.TRUNCATE 3. INSERT SELECT APPEND INSERT 4. 5.HWM 40 (2 ) 70(8010) HWM 36(=404) , ,840 81, ,912 20,480 8,

24 INSERT Oracle8 INSERT(DLI:Direct Load Insert) DLI SQL*Loader INSERT APPEND INSERT INSERT INSERT... SELECT NOLOGGING REDO INSERT SHRKTEST 100,000 REDO NOLOGGING APPEND REDO APPEND NOLOGGING REDO 1900K 28K 28K 1900K 1440K 28K 4. CREATE TABLE AS SELECT INSERT -21-

25 5. 1)Oracle8 Server R8.0,,1997 2)Oracle8 R8.0,,1997 3)Oracle8 R8.0,,1997 4) Oracle8,1998 5) Oracle7 7.3, Design & Migration Services Copyright ORACLE CORPORATION JAPAN

領域サイズの見積方法

領域サイズの見積方法 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