Transcript Document

Tuning Block Space Usage
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Tune segment space management
• Convert from dictionary-managed tablespaces
• Convert to Automatic Segment Space
Management
• Tune block space management
• Diagnose and correct row migration
• Diagnose table fragmentation
• Compare characteristics of bigfile and smallfile
tablespaces
14-2
Copyright © 2006, Oracle. All rights reserved.
Space Management
Space is managed at three levels:
• Files (OS, ASM, raw partitions) assign disk space
to tablespaces.
• Extents are used to allocate file space to
segments in a tablespace.
• Blocks are used to organize the space inside data
objects.
14-3
Copyright © 2006, Oracle. All rights reserved.
Extent Management
Extents are allocated in two ways:
• Dictionary managed
– Only supported for backward compatibility
– Extents managed in the EXT$ and FET$ dictionary
tables
– Recursive SQL
•
Locally managed
– Extents managed in the file header bitmap
– No undo created on extent operations
– Possible contention on file header blocks
14-4
Copyright © 2006, Oracle. All rights reserved.
Locally Managed Extents
•
Create a locally managed tablespace:
SQL>
2
3
4
5
6
•
14-5
CREATE TABLESPACE user_data_1
DATAFILE
‘/oracle9i/oradata/db1/lm_1.dbf’
SIZE 100M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 2M;
Default extent management is local.
Copyright © 2006, Oracle. All rights reserved.
Pros and Cons of Large Extents
•
Pros:
– Are less likely to extend dynamically
– Deliver a small performance benefit
– Enable the server process to read the entire extent
map with a single I/O operation
•
Cons:
– Free space may not be available
– May contain unused space
14-6
Copyright © 2006, Oracle. All rights reserved.
Migrating the SYSTEM Tablespace
to a Locally Managed Tablespace
•
Use the DBMS_SPACE_ADMIN package:
SQL> EXECUTE DBMS_SPACE_ADMIN. 2 TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
•
•
•
14-8
The locally managed SYSTEM tablespace
restrictions are enforced by the migration
procedure.
Migration is possible only when the system is in
RESTRICTED mode whereas all tablespaces other
than SYSTEM, UNDO, and TEMP are in READ ONLY
mode.
Convert other dictionary-managed tablespaces with
the same procedure before converting SYSTEM.
Copyright © 2006, Oracle. All rights reserved.
How Table Data Is Stored
Columns
Table A
Blocks
Table B
Rows
Segment
Segment
Table
Tablespace
Row piece
14-10
Copyright © 2006, Oracle. All rights reserved.
Extent
Anatomy of a Database Block
Block header
Growth
Free space
Row data
14-11
Copyright © 2006, Oracle. All rights reserved.
Minimize Block Visits
Minimize block visits by:
•
Using a larger block size
•
Packing rows tightly
•
Preventing row migration
Tablespace
Segments
Extents
Blocks
14-12
Copyright © 2006, Oracle. All rights reserved.
The DB_BLOCK_SIZE Parameter
The database block size:
• Is defined by the DB_BLOCK_SIZE parameter
•
•
•
•
•
•
•
14-13
Is set when the database is created
Becomes the default block size for tablespaces
and buffer cache
Is the minimum I/O unit for data file reads
Is 8 KB by default; up to 32 KB allowed on most
platforms
Cannot be changed easily
Should be an integer multiple of the operating
system (OS) block size
Should be less than or equal to the OS I/O size
Copyright © 2006, Oracle. All rights reserved.
Small Block Size: Considerations
•
Advantages:
– Reduces block contention
– Is good for small rows
– Is good for random access
•
Disadvantages:
– Has a relatively large space overhead
– Has a small number of rows per block
– Can cause more index blocks to be read
14-14
Copyright © 2006, Oracle. All rights reserved.
Large Block Size: Considerations
•
Advantages:
–
–
–
–
•
Less space overhead
Good for sequential access
Good for very large rows
Better performance of index reads
Disadvantages:
– Increases block contention
– Uses more space in the buffer cache
14-15
Copyright © 2006, Oracle. All rights reserved.
Block Allocation
•
•
When an INSERT or UPDATE operation requires
more space, a block must be found with adequate
space.
Two methods:
– Free lists
– Automatic Segment Space Management (ASSM)
14-16
Copyright © 2006, Oracle. All rights reserved.
Free Lists
Free list–managed space characteristics:
• Segment header blocks hold free lists.
• Blocks are added to and removed from the free
lists.
• Free lists are searched for available blocks.
• Segment headers are pinned for the search and
update of free lists.
14-17
Copyright © 2006, Oracle. All rights reserved.
Block Space Management
Each segment has parameters that control the space
usage inside a block. For a table:
• PCTFREE: Amount of space reserved for updates
• PCTUSED: A minimum level of free space in a block
before a block is placed on the free list
For an index:
• PCTFREE: Amount of space reserved for new index
entries at creation time
• PCTUSED: Always 0 for indexes
14-18
Copyright © 2006, Oracle. All rights reserved.
Block Space Management with Free Lists
PCTFREE
Inserts
1
2
Inserts
Inserts
3
14-19
Inserts
PCTUSED
Copyright © 2006, Oracle. All rights reserved.
4
Automatic Segment Space Management
Automatic Segment Space Management (ASSM)
characteristics:
• Space is managed with bitmap blocks (BMB).
• Multiple processes search different BMBs.
• Availability of block is shown with a full bit.
• The fullness is shown by a percentage full bit for
each of 25, 50, 75, and 100 percent used.
14-21
Copyright © 2006, Oracle. All rights reserved.
Automatic Segment Space Management
at Work
BMB
S
E
G
M
E
N
T
BMB
BMB
…
BMB
…
BMB
…
BMB
…
BMB
BMB
BMB
BMB
BMB
…
…
Block
…
…
…
DATA
…
…
Extent
14-22
Copyright © 2006, Oracle. All rights reserved.
BMB
Block Space Management with ASSM
Inserts
Inserts
75%
50%
1
25%
2
Inserts
Inserts
3
14-24
4
Copyright © 2006, Oracle. All rights reserved.
Creating an Automatic Segment Space
Management Segment
•
•
•
•
•
•
14-25
SEGMENT SPACE MANAGEMENT is the attribute used
for tablespace creation, which cannot be
subsequently altered.
Segment space management is declared at the
tablespace level.
Tablespace must be permanent and locally
managed.
Automatic space management segments are
specified through the AUTO keyword.
For free-list segments, use the default value of
MANUAL.
For ASSM, PCTUSED, FREELIST, and FREELIST
GROUPS are ignored at table creation.
Copyright © 2006, Oracle. All rights reserved.
Migration and Chaining
Migration
Chaining
Index
14-26
Table
Copyright © 2006, Oracle. All rights reserved.
Guidelines for PCTFREE and PCTUSED
•
PCTFREE
– Default: 10
– Zero if no UPDATE activity
•
– PCTFREE = 100 × UPD / (average row length)
PCTUSED
– Only with free lists
– Default: 40
– Set if rows are deleted
– PCTUSED = 100 – PCTFREE – (100 × rows × average
row length / block size)
14-28
Copyright © 2006, Oracle. All rights reserved.
Detecting Migration and Chaining
Use the ANALYZE command to detect migration
and chaining:
SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;
Table Analyzed.
SQL> SELECT num_rows, avg_row_len, chain_cnt
2
FROM DBA_TABLES
3
WHERE table_name='ORDERS';
NUM_ROWS AVG_ROW_LEN CHAIN_CNT
---------- ----------- ---------1171
67
83
Detect migration and chaining by using
Statspack/AWR:
Statistic
Total Per transaction ...
------------------------- ----- --------------- ...
table fetch continued row
495
.02 …
14-29
Copyright © 2006, Oracle. All rights reserved.
Selecting Migrated Rows
SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;
Table analyzed.
SQL> SELECT owner_name, table_name, head_rowid
2
FROM chained_rows
3
WHERE table_name = 'ORDERS';
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- ---------- -----------------SALES
ORDER_HIST AAAAluAAHAAAAA1AAA
SALES
ORDER_HIST AAAAluAAHAAAAA1AAB
...
14-30
Copyright © 2006, Oracle. All rights reserved.
Eliminating Migrated Rows
•
Export/import:
– Export the table.
– Drop or truncate the table.
– Import the table.
•
MOVE table command:
– ALTER TABLE EMPLOYEES MOVE
•
•
Online table redefinition
Copy migrated rows:
– Find migrated rows by using ANALYZE.
– Copy migrated rows to a new table.
– Delete migrated rows from the original table.
– Copy rows from the new table to the original table.
14-31
Copyright © 2006, Oracle. All rights reserved.
Shrinking Segments: Overview
Data
Data
Unused
space
Unused
space
HWM
Shrink
operation
Reclaimed space
HWM
14-33
Copyright © 2006, Oracle. All rights reserved.
Shrinking Segments: Considerations
•
•
•
A shrink operation is an online and in-place
operation.
It is applicable only to segments residing in ASSM
tablespaces.
Candidate segment types:
–
–
–
–
•
•
14-34
Heap-organized tables and index-organized tables
Indexes
Partitions and subpartitions
Materialized views and materialized view logs
Indexes are maintained.
Triggers are not fired.
Copyright © 2006, Oracle. All rights reserved.
Shrinking Segments by Using SQL
ALTER … SHRINK SPACE [COMPACT][CASCADE]
TABLE [OVERFLOW]
INDEX
MODIFY PARTITION
MATERIALIZED VIEW
MATERIALIZED VIEW LOG
MODIFY SUBPARTITION
MODIFY LOB
ALTER TABLE employees ENABLE ROW MOVEMENT;
1
ALTER TABLE employees SHRINK SPACE CASCADE;
2
ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);3
ALTER TABLE employees OVERFLOW SHRINK SPACE;
14-35
Copyright © 2006, Oracle. All rights reserved.
4
Segment Shrink: Basic Execution
ALTER TABLE employees SHRINK SPACE COMPACT;
HWM
1
HWM
ALTER TABLE employees SHRINK SPACE;
HWM
14-36
Copyright © 2006, Oracle. All rights reserved.
2
Segment Shrink:
Execution Considerations
•
Use compaction only:
– To avoid unnecessary cursor invalidation
– During peak hours
•
•
14-37
DML operations and queries can be issued during
compaction.
DML operations are blocked when HWM is
adjusted.
Copyright © 2006, Oracle. All rights reserved.
Using EM to Shrink Segments
14-38
Copyright © 2006, Oracle. All rights reserved.
Bigfile Tablespaces: Overview
•
•
•
A bigfile tablespace contains a single file.
Maximum file size ranges from 8 TB to 128 TB.
Tablespaces are logically equivalent to data files.
Database
Tablespace
Data
file
14-39
SMALLFILE
BIGFILE
Copyright © 2006, Oracle. All rights reserved.
Data
file
Bigfile Tablespaces: Benefits
•
•
Significantly increases the storage capacity
Simplifies data file management for large
databases by making tablespaces the main units
of disk space administration
BFT 1
…
BFT n
One-to-one
mapping
4 billion
blocks
14-41
8 EB
Copyright © 2006, Oracle. All rights reserved.
Using Bigfile Tablespaces
•
•
•
14-43
Supported only for locally managed tablespaces
using Automatic Segment Space Management
Use with logical volume managers or Automatic
Storage Management (ASM)
OMF used for complete data file transparency
Database Block Size
Recommended Maximum
Number of Extents
2 KB
100,000
4 KB
200,000
8 KB
400,000
16 KB
800,000
Copyright © 2006, Oracle. All rights reserved.
Practice Overview:
Tune Database Space Usage
This practice covers the following topics:
• Convert from dictionary-managed to locally
managed tablespaces
• Diagnose free-list contention
• Convert segments from Manual to Automatic
Segment Space Management
14-44
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Tune segment space management
• Convert from dictionary-managed tablespaces
• Convert to Automatic Segment Space
Management
• Tune block space management
• Diagnose and correct row migration
• Diagnose table fragmentation
• Compare characteristics of bigfile and smallfile
tablespaces
14-45
Copyright © 2006, Oracle. All rights reserved.