Oracle performance adjustment to improve block Efficiency

In the key points of Oracle performance adjustment, it is also very important to improve the efficiency of the block. We also need to pay attention to some things in improving the efficiency of the block. The following is a detailed description of the issues that need to be paid attention to when Oracle performance is adjusted to improve block efficiency.

1. Avoid Dynamic Allocation Defects

Create a local table space;

Set the segment size properly;

Monitor the segment to be extended:

 
 

  
  
SELECT owner, table_name, blocks, empty_blocks 
FROM dba_tables WHERE empty_blocks / 
(blocks+empty_blocks) < .1; 
 
 

2. high water mark

The records are recorded in the segment header block, which is set to the starting position of the segment when the segment is created. When the records are inserted, the increment of the block is increased among the top 10 points of Oracle performance adjustment, truncate can reset the location of the high water mark, but delete cannot.

In full table scan, oracle reads all the data blocks below the high water mark, so the blocks above the high water mark may waste storage space, but will not reduce performance.

You can use the following methods to retrieve the blocks above the high water mark in the table:

 
 

  
  
Alter table_name deallocate unused； 
 
 

For blocks below high water mark:

Use the import/export tool: export data; drop or truncate table; import data. Or use the alter table tanle_name move command to move the table's storage location. At this time, you need to re-create the index ).

3. Table statistics

Use the analyize command to generate table statistics and query related information in dba_table.

 
 

  
  
ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;  
  
  
SELECT num_rows, blocks, empty_blocks as empty,avg_space, 
chain_cnt, avg_row_len FROM dba_tables WHERE owner =
'NDLS' AND table_name='T_WH_SHIPPING_BILL';  
  
  
Columns Description   
  
  
NUM_ROWS Number of rows in the table   
  
  
BLOCKS Number of blocks below the table high-water mark   
  
  
EMPTY_BLOCKS Number of blocks above the table high-water mark   
  
  
AVG_SPACE Average free space in bytes in the blocks 
below high-water mark  
  
  
AVG_ROW_LEN Average row length, including row overhead   
  
  
CHAIN_CNT Number of chained or migrated rows in the table 
 
 

4. block size

You can minimize the number of block accesses by using the following methods:

Improve the block size among the top 10 points of Oracle performance adjustment with larger values; compress rows closely; and block row images. There is a conflict between the two. The more lines are compressed into a block, the more likely the image is to be generated. The improvement Block size in the top ten points of Oracle performance adjustment is set during database creation and cannot be easily changed. It is the smallest IO unit for reading data files. The size range is 2 K-64 K, it should be set to an integer multiple of the OS block, which is smaller than or equal to the storage area that can be read at OS IO.

The advantage of small block size: Little block competition; conducive to small rows and random access. The disadvantage is that there is a high cost. Each block has fewer rows and may need to read more index blocks. The selection of Block size affects the system performance. In an OLTP environment, a smaller block size is more suitable. In a DSS environment, a larger block size is suitable.