Collect and review the essence of oracle database performance optimization solutions

General rules for oracle database performance optimization:

1. Reduce Data Access (reduce the number of visits to the hard drive Room)

2. Return less data (reducing network transmission or disk access)

3. Reduce interactions (reduce network transmission)

4. Reduce server overhead (reduce cpu and memory overhead)

5. Use more resources (add resources)

==================================

1. Reduce Data Access (reduce the number of visits to the hard drive Room)

1. Reduce Data Access

1. Create and use the correct index

Indexes greatly increase the overhead of DML (add, delete, and modify). A reasonable index will greatly increase the efficiency by 100 times and 1000 times, but unreasonable indexes may even reduce the performance by 100 times]

A table can have multiple indexes, and an index can contain multiple fields.

Index usage:

Index_column>?

Index_column <?

Index_column =?

Index_column >=?

Index_column <=?

Index_columnbetween? And?

Index_columnin (?,?...?)

Index_columnlike? | % '(Suffix-directed fuzzy search)

T1.index _ column = t2.culunm2 (two tables are associated by index fields)

No index is used:

Index_column <>?

Index_columnnot in (?,?...?)

-------------- The index is not used

Function (index_column) =?

Index_column + 1 =?

Index_column | 'aaa' =?

-------------- Fields computed by common operations or functions do not use indexes.

Index_columnis null

---------------- The index does not save the null value, so is null does not use the index

Index_column = '20140901'

Index_column = 12345

-------------- Oracle converts the left and right sides to the same type during numerical comparison, which is equivalent to using a function. No index is used

A. index_column = a. column _!

-------- The index query value should be known, but the index will not be used if it is unknown.

Common Index considerations:

To add an index:

1. Primary Key

2. Foreign key

3. Fields with objects or identities

Use indexes with caution:

1. Date

2. year and month

3. Status ID

4. Region

5. Operators

6. Numeric Value

7. Long characters

Unsuitable for Indexing

1. Description of the remarks field

2. large fields

In addition:

You can create a composite index for several frequently used query fields.

For example, select id, name from company where type = '2 ';

If this feature is frequently used, you can create a composite index on id, name, and type;

Remember: there is no end to performance optimization. When the requirements are met, it is necessary to stop.

2. Less returned data

1. Data paging (client paging, server paging, and database paging)

2. Only the required fields are returned.

3. Reduce interactions (submit batch, increase fech_size, and use stored procedures)

Coming soon ------- learning and progress together