Transcript Document

Database Tuning
Chap 22 : Segment Tuning
Nam, Kwang Hyun
Intelligent Database Systems Lab
School of Computer Science & Engineering
Seoul National University, Seoul, Korea
Center for E-Business Technology
Seoul National University
Seoul, Korea
Contents
 Object and Segment
 Segment Tuning

DB Block Size

DB Block Structure and Table Creation

Transaction Slot

Segment Header Block

PCTFREE

PCTUSED

PCTINCREASE
Copyright 2008 by CEBT
Database Tuning - 2
Object and Segment
 Object

Components in Database
 Segment

Object which has storage space.
Copyright 2008 by CEBT
Database Tuning - 3
Segment Tuning
 DB Block

The smallest unit of saving data in database and I/O unit

All I/O perform read/write as many as definite size by
DB_Block_Size at a time
 DB_Block_Size

The set value of DB_Block_Size Parameter in Oracle Initial
Parameter File

Disable to change a value after creating table
Copyright 2008 by CEBT
Database Tuning - 4
Segment Tuning
 Case 1 : DB_Block_Size is big


Pros
–
Many rows are extracted with one block access
–
Low frequency of I/O in table full scan
Cons
–
Many rows can be wasted in 1 row access
 Case 2 : DB_Block_Size is small

Pros
–

Pros and Cons
are reversed
each other
Good for 1 row access
Cons
–
Small rows are extracted with one block access
–
High frequency of I/O in table full scan
Copyright 2008 by CEBT
Database Tuning - 5
Segment Tuning
 General data block structure

Cache Layer
–

Transaction Layer
–

Information of transaction slot
Table Dictionary
–

Data block address, Block type,
Block format and System change
number
Information of table using
concerned block
Row Dictionary
–
Offset information for finding row
which is saved in block
Copyright 2008 by CEBT
Database Tuning - 6
Segment Tuning
 Table creation script
Related to Transaction Slot
Copyright 2008 by CEBT
Option
Database Tuning - 7
Transaction Slot

Definition

INITRANS, MAXTRANS
–


the number of assigned
transaction slot in transaction
layer
Role

Used for performing DML of
concerned Row saved in Block

Offer synchronism of DML
operation
 Examples
Consideration

Assign properly enough
transaction slot for operation
character

OLTP : many slots
–

Batch Table : few slots
-
Copyright 2008 by CEBT
Perform many process
simultaneously
Perform one DML at a
time
Database Tuning - 8
Segment Header Block - Freelist
Segment Header


Property

Indicate block which has
free space to save data

Maintain linked list to
manage free block

Used in case of ‘insert’
High Water Mark
The end line of used space
Usage method

To assign free block, freelist
should be acquired.

Freelist can receive only one
request. Therefore, other
requests should wait.
=> Freelist conflict
Copyright 2008 by CEBT
Database Tuning - 9
Segment Header Block - Freelist
 Solution of freelist conflict

Increase the number of freelist
 Example

Freelist Option = 3

Master freelist assign process
freelists when it receives
freelist request from process
operating ‘insert’.
Copyright 2008 by CEBT
Database Tuning - 10
Segment Header Block - Extent Map
 Located in the middle of Segment Header Block
 Manage all extent addresses of concerned segment.
 Extent is a group of successive blocks, but it doesn’t need to
be successive between extents.

Extent map is managed with first block address and number of
blocks in segment header block.
Copyright 2008 by CEBT
Database Tuning - 11
PCTFREE
 Row Chaining

In ‘update’ operate, if block doesn’t
have any free space, concerned row
will move another block

Row information remains original
block, but only real data are moved.
 PCTFREE

To eliminate row chaining, pre-assign
free space to block

PCTFREE is high => Row Chaining ↓,
Block usage ratio ↑

PCTFREE is low => Row Chaining ↑,
Block usage ratio ↓
Copyright 2008 by CEBT
Database Tuning - 12
PCTUSED
 Definition

Block storage parameter used to specify when Oracle should
consider a database block to be empty enough to be added to
the freelist.
 Consideration


Set PCTUSED big
–
Possibility goes up to be recognized as free block when small
number of rows are eliminated.
–
Block usage rate increase
Set PCTUSED small
–
Block usage rate decrease
–
Freelist reorganization decrease
Copyright 2008 by CEBT
Database Tuning - 13
PCTINCREASE
 Definition

Option to increase extent size with fixed percentage
 Consideration

It is hard to predict extent size of segment
–
Therefore, it is good to set PCTINCREASE ‘0’ for not changing extent
size.
Copyright 2008 by CEBT
Database Tuning - 14
References
 http://www.orafaq.com/wiki/Freelist
 http://www.orafaq.com/wiki/PCTFREE
 http://www.orafaq.com/wiki/PCTUSED
 http://www.orafaq.com/wiki/PCTINCREASE
Copyright 2008 by CEBT
Database Tuning - 15