Seven Key Points of Oracle performance adjustment: application optimization

1. Concepts
To improve performance, you can use the following data access methods:
A. Clusters
B. Indexes
-B-tree (normal or reverse key)
-Bitmap
-Function-based
C. Index-organized tables
D. materialized views

The more layers the index has, the lower the efficiency. If the index contains many deleted rows, the index will become inefficient. If 15% of the index data has been deleted, you should consider re-indexing.

2. Application Problems
A. Use declarative constraints instead of passingCodeRestrictions
B. Code sharing
C. Use bind variables instead of text to optimize shared SQL
D. Adjust the value of cursor_sharing (exact/similar/force)

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.