Notes

Outline

Using Oracle Blocks Efficiently

Objectives After completing this lesson, you should be able to do the following: Determine an appropriate block size Optimize space usage within blocks Detect and resolve row migration Monitor and tune indexes

Database Storage Hierarchy

Allocating an Extent

Avoiding Dynamic Allocation

Avoiding Dynamic Allocation Disadvantages Create a locally managed tablespace:

Pros and Cons of Large Extents Pros: Are less likely to dynamically extend Deliver small performance benefit Can overcome OS limitations on file size Single read against extent map Cons: Free space may not be available Unused space

Database Block Size

DB_BLOCK_SIZE Is set when the database is created Is the minimum I/O unit for data file reads Default is 2 KB or 4 KB, but up to 64 KB is allowed Cannot be easily changed Should be a multiple of the OS block size OS I/O size is equal to or greater than DB_BLOCK_SIZE

Small Block Size Pros and Cons Pros: Reduces block contention Is good for small rows Is good for random access Cons: Has relatively large overhead Has small number of rows per block Can cause more index blocks to be read

Large Block Size Pros and Cons Pros: Less overhead Good for sequential access Good for very large rows Better performance of index reads Cons: Increases block contention Uses more space in the buffer cache

PCTFREE and PCTUSED

Guidelines PCTFREE Default 10 Zero if no UPDATE activity PCTFREE = 100 × upd / (upd  +  ins) PCTUSED Default 40 Set if rows deleted PCTUSED = 100 – PCTFREE – 100 × rows ×                         (ins + upd) /   blocksize

Migration and Chaining

Detecting Migration and Chaining

Selecting Migrated Rows

Eliminating Migrated Rows 1. Run ANALYZE TABLE ... LIST CHAINED ROWS; 2. Copy the rows to another table. 3. Delete the rows from the original table. 4. Insert the rows from step 2 back into the original table. Step 4 eliminates migrated rows because migration only occurs during an UPDATE operation.

The High-Water Mark

Table Statistics

The DBMS_SPACE Package

Index Reorganization Indexes on volatile tables cause a performance problem. Empty index blocks go to the free list. Even if a block contains only one entry, it must be maintained. You may need to rebuild indexes.

Monitoring Indexes

Summary In this lesson, you should have learned how to store blocks as economically as possible by: Using a larger block size Setting PCTFREE and PCTUSED Rebuilding tables with many empty blocks Rebuilding tables with migrated rows Rebuilding volatile indexes Using locally managed tablespaces