14 copyright © 2006, oracle. all rights reserved. tuning block space usage
TRANSCRIPT
14Copyright © 2006, Oracle. All rights reserved.
Tuning Block Space Usage
14-2 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-3 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-4 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-5 Copyright © 2006, Oracle. All rights reserved.
Locally Managed Extents
• Create a locally managed tablespace:
• Default extent management is local.
SQL> CREATE TABLESPACE user_data_1
2 DATAFILE
3 ‘/oracle9i/oradata/db1/lm_1.dbf’
4 SIZE 100M
5 EXTENT MANAGEMENT LOCAL
6 UNIFORM SIZE 2M;
14-6 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-8 Copyright © 2006, Oracle. All rights reserved.
Migrating the SYSTEM Tablespaceto a Locally Managed Tablespace
• Use the DBMS_SPACE_ADMIN package:
• 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.
SQL> EXECUTE DBMS_SPACE_ADMIN. - 2 TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');
14-10 Copyright © 2006, Oracle. All rights reserved.
How Table Data Is Stored
Tablespace
Table A Table B
SegmentSegment
Rows
Columns
Table
Blocks
Row piece Extent
14-11 Copyright © 2006, Oracle. All rights reserved.
Anatomy of a Database Block
Block header
Free space
Row data
Growth
14-12 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
Blocks
Segments
Extents
14-13 Copyright © 2006, Oracle. All rights reserved.
The DB_BLOCK_SIZE Parameter
The database block size:
• Is defined by the DB_BLOCK_SIZE parameter
• 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
14-14 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-15 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-16 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-17 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-18 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-19 Copyright © 2006, Oracle. All rights reserved.
Block Space Management with Free Lists
Inserts
Inserts
Inserts
1
3
2
4
Inserts
PCTFREE
PCTUSED
14-21 Copyright © 2006, Oracle. All rights reserved.
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-22 Copyright © 2006, Oracle. All rights reserved.
Automatic Segment Space Managementat Work
Extent
BMB
BMB BMB
BMB BMB
DATA
BMB
BMB
BMB BMB BMB
{SEGMENT
Block
… … … …
…
…
…
…
…
…
…BMB
BMB
14-24 Copyright © 2006, Oracle. All rights reserved.
Block Space Management with ASSM
Inserts
Inserts
Inserts
1
3
2
4
Inserts
25%
50%
75%
14-25 Copyright © 2006, Oracle. All rights reserved.
Creating an Automatic Segment SpaceManagement Segment
• 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.
14-26 Copyright © 2006, Oracle. All rights reserved.
Migration and Chaining
Index Table
Migration
Chaining
14-28 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-29 Copyright © 2006, Oracle. All rights reserved.
Detecting Migration and Chaining
Use the ANALYZE command to detect migrationand chaining:
Statistic Total Per transaction ... ------------------------- ----- --------------- ...table fetch continued row 495 .02 …
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:
14-30 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-31 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-33 Copyright © 2006, Oracle. All rights reserved.
Shrinking Segments: Overview
HWM
HWM
Shrinkoperation
DataUnusedspace
Data
Unusedspace
Reclaimed space
14-34 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:– Heap-organized tables and index-organized tables– Indexes– Partitions and subpartitions– Materialized views and materialized view logs
• Indexes are maintained.
• Triggers are not fired.
14-35 Copyright © 2006, Oracle. All rights reserved.
Shrinking Segments by Using SQL
ALTER … SHRINK SPACE [COMPACT][CASCADE]
TABLE [OVERFLOW] INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG
MODIFY PARTITION MODIFY SUBPARTITION MODIFY LOB
ALTER TABLE employees SHRINK SPACE CASCADE;
ALTER TABLE employees ENABLE ROW MOVEMENT; 1
2
ALTER TABLE employees MODIFY LOB(resume) (SHRINK SPACE);3
ALTER TABLE employees OVERFLOW SHRINK SPACE; 4
14-36 Copyright © 2006, Oracle. All rights reserved.
Segment Shrink: Basic Execution
HWM
ALTER TABLE employees SHRINK SPACE COMPACT;
HWM
HWM
ALTER TABLE employees SHRINK SPACE;
1
2
14-37 Copyright © 2006, Oracle. All rights reserved.
Segment Shrink:Execution Considerations
• Use compaction only:– To avoid unnecessary cursor invalidation– During peak hours
• DML operations and queries can be issued during compaction.
• DML operations are blocked when HWM is adjusted.
14-38 Copyright © 2006, Oracle. All rights reserved.
Using EM to Shrink Segments
14-39 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
SMALLFILEDatafile
BIGFILEDatafile
14-41 Copyright © 2006, Oracle. All rights reserved.
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 …
8 EB4 billionblocks
One-to-one mapping
BFT n
14-43 Copyright © 2006, Oracle. All rights reserved.
Using Bigfile Tablespaces
• 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
800,00016 KB
400,0008 KB
200,0004 KB
100,0002 KB
Recommended Maximum Number of Extents
Database Block Size
14-44 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-45 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