Locally Managed Tablespaces in Oracle 8 i Raghav

Locally Managed Tablespaces in Oracle 8 i Raghav Vinjamur

Questions • Extents – Size of Individual Extent in a Segment • Matters/Does Not Matter? – Number of Extents • Matters/Does Not Matter? 2

Questions ( AND Answers) • Extents – Size of Individual Extent in a Segment • Matters/Does Not Matter? – if small, can lead to numerous extents. – Number of Extents • Matters/does not matter? – Due to overheads of dictionary maintenance, May not be for full table scan performance. 3

Questions ( AND Answers) • Extent Management – Extents must therefore be • Fixed Size - Appropriately Large, • Fixed in Number • How to implement/manage Fixed Size Extents in an Oracle database? 4

Overview • Extent Management – Dictionary Managed Tablespaces - “DMT” – Locally Managed Tablespaces - “LMT” • Features/“Bugs”/“Usage Quirks” of LMTs in Oracle 8 i Rel 8. 1. 6. 0 • Benefits of LMTs over DMTs • Summary & References • Q&A 5

Extent Management • How to implement Fixed Size Extents in an Oracle database? – Pre-Oracle 8 – Oracle 8 i 6

Extent Management • Pre-Oracle 8 – DEFAULT STORAGE • INITIAL and NEXT are set equal, and • PCTINCREASE=0 • Caveats – Extent Sizes Rounded Up. • Nearest Multiple of 5. • MAYBE more (up to 4 additional blocks) if there are fewer than 5 blocks remaining in the unallocated portion. – Coalescing of Free Extents by SMON. – Extent Trimming After Parallel Operations. 7

Extent Management • Oracle 8 – DEFAULT STORAGE • INITIAL and NEXT are set equal, and • PCTINCREASE=0 – MINIMUM EXTENT integer • the minimum size of an extent in a tablespace. • controls free space fragmentation in tablespaces – ensures every used/free extent size in a tablespace is at least as large as, and is a multiple of integer – eliminates unwarranted extent size rounding/trimming 8

Extent Management • Dictionary Managed Tablespaces “DMT” – Free/Used Extent information of tablespaces tracked via Data Dictionary Tables (FET$ and UET$) • differentiates from Locally Managed Tablespaces in Oracle 8 i 9

Locally Managed Tablespaces • New in Oracle 8 i • EXTENT MANAGEMENT LOCAL clause of CREATE TABLESPACE statement – EXTENT MANAGEMENT DICTIONARY still is Default. • Allows Fixed Extent Size(s) – AUTOALLOCATE or “System Determined” Sizes – UNIFORM Size 10

Locally Managed Tablespaces • Create Tablespace TESTLMT datafile ‘lmt 01. dbf’ size 1000 M reuse EXTENT MANAGEMENT LOCAL AUTOALLOCATE; • Create Tablespace TESTLMT datafile ‘lmt 01. dbf’ size 1000 M reuse EXTENT MANAGEMENT LOCAL UNIFORM Size 10 M; 11

Locally Managed Tablespaces • AUTOALLOCATE – is default – can specify initial extent and Oracle determines the size of the rest, with a minimum of 64 k • UNIFORM – default is 1 MB 12

Locally Managed Tablespaces • Summary Notes for DBA’s 1. LMTs and DMTs can co-exist in same DB. 2. EXTENT MANAGEMENT DICTIONARY is default. 3. Currently, a locally managed SYSTEM tablespace (? ? For ROLLBACK SEGMENT usage) cannot be created (True in 8. 1. 7) 4. EXTENT MANAGEMENT LOCAL • CREATE TABLESPACE statement - for permanent tablespaces · CREATE TEMPORARY TABLESPACE statement - for temporary tablespaces 13

Locally Managed Tablespaces • Summary Notes for DBA’s … 2 · CREATE TEMPORARY TABLESPACE statement - for temporary tablespaces CREATE TEMPORARY TABLESPACE TEMPFILE 'temp. dbf' SIZE 256 M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256 K; 5. When creating an LMT, AUTOALLOCATE is default. 6. Temporary LMT’s can only use UNIFORM SIZE, and not AUTOALLOCATE. 14

Locally Managed Tablespaces • Summary Notes for DBA’s … 3 7. Parameters · TEMPORARY, · MINIMUM EXTENT, or · DEFAULT storage_clause, · NEXT, · PCTINCREASE, · MINEXTENTS, · MAXEXTENTS are not valid for locally managed tablespaces. Could get an error or be silently ignored!! 15

Locally Managed Tablespaces • Summary Notes for DBA’s … 4 8. DBMS_SPACE_ADMIN package provides • Defect Diagnosis and Repair Functionality for LMT • Migration of DMT to LMT or vice-versa 9. LMTs can be altered for many of the same reasons as a dictionary-managed tablespace. However, • altering storage parameters is not an option and • coalescing free extents is unnecessary • an LMT cannot be altered to a locally managed temporary tablespace. 16

Locally Managed Tablespaces • Summary Notes for DBA’s … 5 10. To find out which tablespaces are LMTs • SELECT FROM WHERE tablespace_name, extent_management dba_tablespaces extent_management = 'LOCAL'; 17

• These slides (and, a white paper!) will be posted on the NOCOUG web site, at http: //www. nocoug. org 18

Questions ? 19

Good Luck! 20