Nine Key Points of Oracle performance adjustment: 8. 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. When the segment is created, it is set to the starting position of the segment. When the record is inserted, it is added in increments of five blocks, 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. You can also use the alter table tanle_name move command to move the table's storage location (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 = 'nss' 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:
Use a larger block size, 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. Block Size 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.

5, pctfree, pctused

1) pctfree and pctused allow you to control the use of free space in all data blocks in a segment.
Pctfree: the minimum proportion of free space reserved by a data block that may be updated for existing records in the block to the block size.
Pctused: The minimum ratio of space occupied by row data and other information before the new record is inserted into the block.

2) use of these two parameters
If pctfree = 20% is specified during table creation, oracle reserves 20% of the space in each block of data segment in this table for updates to existing records. When the used space of a block is increased to 80% of the size of the entire block, the block is removed from the Free List. After the delete and update operations are submitted, the Oracle server processes this statement and checks whether the used space of the corresponding block is lower than pctused. If yes, the block is placed in the Free List.

3) pctfree and pctused settings
• Pctfree
-Default 10
-Zero if no update Activity
-Pctfree = 100 × UPD/(average row length)
• Pctused
-Default 40
-Set if rows deleted
-Pctused = 100-pctfree-100 × rows × (average row length)/blocksize
UPD: the average amount added by updates, in bytes. This is determined by subtracting the average row length of intercurrent average row length;
Average row length: After the analyize command is run, this value can be obtained from the avg_row_len column in dba_tables.
Rows: the number of rows to be deleted before free list maintenance occurs.

4) Delete and update can increase the free space of the block, but the released space may be discontinuous. Oracle will organize the fragments in the following circumstances: A block has enough free space to hold row piece, but since each shard is small, this row piece cannot be stored in a continuous section.

6. Migration and chaining

1) if a row of data is too large to accommodate a single block, two phenomena may occur:
A. chaining: the row data is too large to accommodate an empty block. Oracle will store the row data in a block chain composed of one or more blocks, insert and update may cause this problem. In some cases, row chaining cannot be avoided.
B. Migration, if such a block exists, the Oracle server moves the entire row to the new block and saves an image row pointing to the new storage location in the original location. The rowid of the image row is consistent with the original rowid.
Disadvantages of chaining and migration: the performance of insert and update is reduced, and index queries increase the number of I/O operations.

2) Check migration and chaining:
Analyize table table_name compute statistics;
Select num_rows, chain_cnt from dba_tables where table_name = '...';
Query image lines:
Analyize table table_name list chained rows;
Select owner_name, table_name, head_rowid from chained_rows where table_name = '...';
The reason for the generation of migration may be that the pctfree settings are too low and there is not enough space reserved for updates.
You can increase the pctfree value to avoid generating a row image.

3) Steps for eliminating the image line:
Run analyize table... list chained rows;
Copy the image row to the other table TMP;
Delete these rows from the source table;
Insert these rows from TMP to the source table.
Script:
/* Get the name of the table with migrated rows */
Accept table_name prompt 'Enter the name of the table with migrated rows :'
/* Clean up from last execution */
Set echo off
Drop table migrated_rows;
Drop table chained_rows;
/* Create the chained_rows table */
@? /Rdbms/admin/utlchain
Set echo on
Spool fix_mig
/* List the chained & migrated rows */
Analyze table & table_name list chained rows;
/* Copy the chained/migrated rows to another table */
Create Table migrated_rows
Select orig. * From & table_name orig, chained_rows cr
Where orig. rowid = Cr. head_rowid
And Cr. table_name = upper ('& table_name ');
/* Delete the chained/migrated rows from the original table */
Delete from & table_name
Where rowid in (select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
Insert into & table_name select * From migrated_rows;
Spool off
When using this script, you must remove the foreign key constraints involved.

7. Index Reorganization

Creating an index on an unstable table will affect performance. An index block can only enter the Free List if it is completely empty. Even if an index block contains only one entry, it must be maintained, therefore, indexes need to be rebuilt in stages.

1) check whether the index needs to be restructured
A. collect statistics on the use of an index
Analyze index acct_no_idx validate structure;
B. view collected statistics
Select name, (del_lf_rows_len/lf_rows_len) * 100 as index_usage from index_stats;

Column description
Lf_rows number of values currently in the Index
Lf_rows_len sum in bytes of the length of all values
Del_lf_rows number of values deleted from the index
Del_lf_rows_len length of all Deleted Values

C. If the waste exceeds 20%, the index needs to be rebuilt.
Alter index acct_no_idx rebuild;
D. Or sort the indexes.
Alter index acct_no_idx coalesce;

2) Mark unused Indexes
A. Start monitoring index usage
Alter index HR. emp_name_ix monitoring usage;
B. Stop using monitoring indexes.
Alter index HR. emp_name_ix nomonitoring usage;
C. query the index usage
Select index_name, used from V $ object_usage;
Deleting unused indexes can reduce the cost of DML operations and improve system performance.

In order to use blocks as economically as possible, tables with many empty blocks and image rows should be rebuilt, and indexes on unstable tables should be regularly created, create local-managed tablespaces as much as possible.