1, the optimization of the sql,sql with the Hotspot table execution time is too long to hold the latch of time. For statements that return fewer results, you should check the use of indexes and partitioning criteria. For example, we query tables that have been partitioned, and if we return a small amount of data, we can create an index (or partition index) and use the partitioning criteria, which is less efficient if the query partition table is not used.
2, for the need to return a large number of data query statements to minimize the number of executions, one-time reading data into the cache (such as in code logic, such as flexible use of data structures such as map as a cache layer, or using tools such as Memcach), the business layer as far as possible through the cache to read data, and update the cache;
3, for large tables (millions of rows or more) consider a combination of hash partition, the data evenly distributed to different partitions, to avoid hot spot block competition to improve IO;
4, if the table access is very large but rarely updated, often through the primary key query, you can create an index organization table IoT reduce query when the table action to improve query performance;
5, reduce the row migration of the table, create a new table according to the original table, and adjust the pctfree parameters of these tables (free space of the block), can reduce the row migration, reduce the query logic IO read;
6, consider enabling the keep buffer pool (currently we are inactive), the table is keep to the buffer pool;
7, create a temporary table space group, let the database assign different sessions to different temporary table space, at the session level of IO load balancing.
Vernacular Oracle: Hot Table Optimization Summary
