Oracle 9i 数据库性能调优技术-les13

•
Pros
– Are less likely to extend dynamically – Deliver small performance benefit – Enable you to read the entire extent map with a single I/O operation
SQL> EXECUTE dbms_stats.gather_table_stats > ('HR','EMPLOYEES'); PL/SQL procedure successfully completed. SQL> SELECT num_rows, blocks, empty_blocks as empty, 2 avg_space, chain_cnt, avg_row_len 3 FROM dba_tables 4 WHERE owner = 'HR' 5 AND table_name = 'EMPLOYEES'; NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN -------- ------ ----- --------- --------- ----------13214 615 35 1753 0 184
13-14
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Block Size
Minimize block visits by: • Using a larger block size • Packing rows tightly • Preventing row migration
13-12 Copyright © Oracle Corporation, 2002. All rights reserved.
Recovering Space
Below the high-water mark: • Use the Export and Import utilities to:
– Export the table – Drop or truncate the table – Import the table
Allocation of Extents
To avoid the disadvantages of dynamic extent allocation: • Create locally managed tablespaces. • Size the segments appropriately. • Monitor segments ready to extend.
• • •
• • •
Is set when the database is created Is the minimum I/O unit for data file reads Is 2 KB or 4 KB by default, but up to 64 KB is allowed Cannot be changed easily Should be an integer multiple of the OS block size Should be less than or equal to the OS I/O size
Tablespace
Segments
Extents
Blocks
13-15 Copyright © Oracle Corporation, 2002. All rights reserved.
The DB_BLOCK_SIZE Parameter
The database block size: • Is defined by the DB_BLOCK_SIZE parameter
•
Cons
– Has a relatively large overhead – Has a small number of rows per block – Can cause more index blocks to be read
13-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Highwater mark
Segment header block
Empty blocks (never used)
13-9
Copyright © Oracle Corporation, 2002. All rights reserved.
The High-Water Mark
•
The high-water mark is:
13-18
Or use the Alter Table Employees Move command to move the table. Above the high-water mark, use the Alter Table Employees Deallocate Unused; command.
•
Large Block Size: Pros and Cons
•
Pros
– – – – Less overhead Good for sequential access Good for very large rows Better performance of index reads
•
Cons
– Increases block contention – Uses more space in the buffer cache
– Manage extents and Oracle blocks – Ensure effective use of space – Determine the high-water mark
• • • •
13-2
Describe the use of Oracle block parameters Recover space from sparsely populated segments Describe and detect chaining and migration of Oracle blocks Perform index reorganization
Using Oracle Blocks Efficiently
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Use automatic segment space management • Use manual segment space management
Locally Managed Extents
Create a locally managed tablespace:
SQL> 2 3 4 5 6 CREATE TABLESPACE user_data_1 DATAFILE ‘/oracle9i/oradata/db1/lm_1.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
– Recorded in the segment header block – Set to the beginning of the segment on creation – Incremented in five-block increments as rows are inserted – Reset by the TRUNCATE command
•
Never reset by using DELETE statements
13-10
Copyright © Oracle Corporation, 2002. All rights reserved.
Table Statistics
Populate the table statistics using the dbms_stats package and then query the values in dba_tables:
13-16
Copyright © Oracle Corporation, 2002. All rights reserved.
Small Block Size: Pros and Cons
•
Pros
– Reduces block contention – Is good for small rows – Is good for random access
Copyright © Oracle Corporation, 2002. All rights reserved.
Database Storage Hierarchy
Tablespace
Segments
Extents Extents
Extents
Blocks
13-3
Copyright © Oracle Corporation, 2002. All rights reserved.
•
Cons
– Free space may not be available – Unused space
13-7
Copyright © Oracle Corporation, 2002. All rights reserved.
The High-Water Mark
Extent 1 Empty blocks (rows deleted) Extent 2