Summary of Oracle hotspot table Optimization

Summary of Oracle hotspot table Optimization

1. Optimize the SQL related to the hotspot table. If the SQL Execution takes too long, it will hold latch for a long time. For statements with a small number of returned results, check whether indexes and partition conditions are used. For example, if we query a table that has already created a partition and return a small amount of data, we can create an index (or partition index) and use the partition conditions. If we do not use the query, the execution efficiency of the partition table is lower.

2. query statements that need to return a large amount of data should be executed as much as possible, and data should be read at a time in the cache (for example, the data structure such as map can be flexibly used as the cache layer in code logic, or use memcach or other tools), the business layer should try to read data through the cache, and update the cache according to certain logic;

3. For large tables (with more than one million rows), use HASH partitions to evenly distribute data to different partitions to avoid hotspot block competition and improve IO;

4. If a table has a large access volume but is rarely updated, it is often queried through the primary key. You can create an index to organize the table. iot reduces the return table action during query to improve query performance;

5. Reduce row migration of tables. Create new tables based on the original table and adjust the pctfree parameter of these tables (free space of blocks) to reduce row migration, reduces logical IO reads during queries;

6. Enable the keep buffer pool (which is disabled currently) and store the table keep in the buffer pool;

7. Create a temporary tablespace group so that the database can allocate different sessions to different temporary tablespaces and perform IO load balancing at the session level.