Geeky Internal Stuff: Block

1
Geeky Internal StuffBlock Index Internals

• Dan HotkaDirector of Database Field Operations

2
(No Transcript)
3
New BookISBN 0-7897-2369-7www.amazon.com
4
Essential for Business
Quest Software

• Development Deployment
• SQL Development
• Deployment Change Management
• Production Support
• High Availability
• Enterprise Monitoring
• Database Management
• Report Management

Managing the applications and databases that keep
businesses running.
5
Agenda

• Introduction
• Block Size
• Block Layout
• Row Layout
• Extent Mngt
• Space Mngt
• Free List Mngt
• Direct Path Loading

6
Agenda

• BTree Indexes
• What is
• Structure
• Tips Techniques
• Unique Indexes
• Non-unique Indexes
• Reverse-key Indexes
• IOTs
• Bitmapped Indexes

7
Block Size

• Oracle Supports 2K to 64K Blocks
• Block size multiple of OS file system block size
• Do NOT make Oracle block size smaller than OS
  block size!!!
• Block size vs Performance

8
Block Layout
9
Dump Commands

• Unix
• dd bs2k if/ora8/data/users01.dbf skip200 count
  5 od -x gt pg
• VMS
• DUMP /BLOCKS (STARTltos block gt, END,os block
  gt) /OUT blockdmp.out users01.dbf
• NT
• HEDIT
• SVRMGR
• alter system dump datafile 7 block 201

10
Space Utilization in a Segment
11
Free Lists

• Master Free Lists (MFLs)
• Stored in Segment Header
• Contention if multiple INSERTS by multiple
  processes
• Process Free Lists (PFLs)
• defined by storage parameter freelists n
• set equal to number of processes doing inserts
  simultaneously
• Increases INSERT performance
• virtual list

12
Free Lists (continued)

• Transaction Free Lists (TFLs)
• Minimum of 16, implicitly created in Segment
  Header
• Stored in separate area, limited only by block
  size
• Used by Active Processes
• Needed when releasing space to a block due to a
  DELETE or UPDATE
• Faster rollbacks, chains blocks together by
  transaction
• If not enough, committing transactions will wait
• Check VWAIT, VTRANSACTIONS (TX enqueue)

13
Things in the Segment Header

• Number of Blocks
• Number of Extents
• HWM
• Free List Flag (1 or 0)
• MFLs
• Free List Begin (rdba)
• Free List End (rdba)
• Blocks on Free List

14
Things in the Segment Header (cont)

• Map Header
• has rdba if UNLIMITED extents
• of Extents in this block
• DBA of Last Block in Map
• of Extent Map Blocks
• Extent Map
• PFLs (controlled by FREELISTS n)

• Free List Group Flag rdbas (controlled by
  MAXINSTANCES)

15
Finding Free Space
Trans Slide
16
Dynamic Extent Allocation
17
Data Block Sections
18
Cache Layer Layout
19
Transaction Layer Layout
Flowchart
20
PCTFREE/PCTUSED
21
Row Layout
22
Putting it all together
23
Chained Row Layout
24
Row Chaining How it works
25
Migrated Row Layout
26
Row Migration How it works
27
Max Extents vs Unlimited Extents

• Unlimited extents WILL cause chaining at the
  segment level!
• Max extents does not.
• 2K block can hold 121 extents
• Monitor extent usage and adjust storage
  parameters as needed.

28
Table Truncate
29
Table Truncate
30
Table Truncate
31
BTree History

• Balanced-Tree Index
• Originally conceived by Bayer and McCreight in
  1972
• Performs well with a consistent number of reads
  to locate a row/series of rows

32
BTree Index Structure
33
BTree Index Split
34
Index Block Layout
35
Index Common Header
36
Branch Block Header
37
Index Leaf Block Header
38
Index Rows Non-Unique
39
Index Rows Unique
40
BTree Tips Techniques

• BTree not best suited for sequential key inserts
• Deletes leave gaps to the left of the index
• Leaves are maintained for future possible inserts
• Possible Index Height change (additional I/Os)
• REVERSE KEY Indexes solves this problem
• Unique Indexes
• Better Individual Row Performance - stores just
  ROWID

41
BTree Tips Techniques

• Non-Unique Indexes
• Better Range-Scan Performance - stores ROWID
  pairs
• Reverse Key Indexes
• Setup like Non-Unique
• Oracle reads Index blocks from left to right
• Oracle will NOT use a reverse-key Index on a
  range scan!

42
BTree Space Management

• Empty Blocks not removed from index
• Makes for longer Index searches
• May increase the height of the index
• High PCTFREE
• Assists Oracle by doing most of the block
  splitting during the initial data load
• Low PCTFREE
• Helps Oracle minimize block splits due to
  inserts/updates by leaving some room in each
  index block behind

43
BTree Space Management

• PCTFREE 0
• Where index key is sequentially always increasing
• NOT for reverse-key indexes
• Oracle 8.1.6
• Recognizes types of rows being added
• if random in nature - loads blocks 70 full
• if sequential in nature - loads blocks 95 full
• Index Height Change
• Candidate for reorg/rebuild

44
Bitmap Indexes
45
Bitmap Indexes Tips Techniques

• Resembles non-unique Index structure
• DML will add leaves if bitmap at its limit OR
  outside the range of the keys
• Can be slow on DML
• May have to reshuffle bitmap to accommodate a
  change
• Searches are efficient
• Boolean operations are efficient

46
Bitmap Indexes Tips Techniques

• The fewer the Unique values, the faster this will
  perform
• NO row-level locking!
• DML locks entire bitmap which in turn, locks
  entire underlying table
• Rule-based Optimizer ignores Bitmap Indexes

47
What have we learned?

• Data Block Layout
• Free Space Mngt
• Extent Mngt
• Row Layout
• Row Chaining/Migration
• Table Truncate
• Direct Path Loading

48
What have we learned?

• BTree Index
• Structure
• Layout
• Unique
• Non-Unique
• Reverse Key
• Tips Techniques
• Bitmap Indexes
• Tips Techniques

49
(No Transcript)
50
w w w . q u e s t . c o m