Database Tuning Chap 22 : Segment Tuning

1
Database TuningChap 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
2
Contents

• Object and Segment
• Segment Tuning
• DB Block Size
• DB Block Structure and Table Creation
• Transaction Slot
• Segment Header Block
• PCTFREE
• PCTUSED
• PCTINCREASE

3
Object and Segment

• Object
• Components in Database
• Segment
• Object which has storage space.

4
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

5
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
• Good for 1 row access
• Cons
• Small rows are extracted with one block access
• High frequency of I/O in table full scan

Pros and Cons are reversed each other
6
Segment Tuning

• General data block structure
• Cache Layer
• Data block address, Block type, Block format and
  System change number
• Transaction Layer
• Information of transaction slot
• Table Dictionary
• Information of table using concerned block
• Row Dictionary
• Offset information for finding row which is saved
  in block

7
Segment Tuning

• Table creation script

Option
8
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
• Consideration
• Assign properly enough transaction slot for
  operation character

• Examples
• OLTP many slots
• Perform many process simultaneously
• Batch Table few slots
• Perform one DML at a time

9
Segment Header Block - Freelist

• Property
• Indicate block which has free space to save data
• Maintain linked list to manage free block
• Used in case of insert
• Usage method
• To assign free block, freelist should be
  acquired.
• Freelist can receive only one request. Therefore,
  other requests should wait.
• gt Freelist conflict

High Water Mark
The end line of used space
10
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.

11
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
  doesnt need to be successive between extents.
• Extent map is managed with first block address
  and number of blocks in segment header block.

12
PCTFREE

• Row Chaining
• In update operate, if block doesnt 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 gt Row Chaining ?, Block usage
  ratio ?
• PCTFREE is low gt Row Chaining ?,
• Block usage ratio ?

13
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

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

15
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