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

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


		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


		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


		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


		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


		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
			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


		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.


		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.


	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