Buffer tables

Data is frequently inserted to or deleted from a buffer table. An index table is also a buffer table. When the indexed column of a primary table is updated, data is inserted to or deleted from the index table. Buffer table exceptions are likely for storage engines based on the log-structured merge-tree (LSM-tree) architecture. In an LSM-tree-based storage engine, data is divided into baseline data and incremental data. The incremental data is stored in MemTables in the memory and is written to minor compaction SSTables on the disk through minor compactions, and then written to the baseline SSTables on the disk through a daily major compaction. During the execution of a query, the MemTables, minor compaction SSTables, and baseline SSTables are all queried to return the final data. In an LSM-tree architecture, deleted data is first labelled as deleted but is physically deleted only after the daily major compaction. If a large amount of incremental data is labelled as deleted, few rows are actually available for upper-layer applications. In addition, the labelled data may be processed during range queries, which is time-consuming and results in long execution time of SQL queries. In the presence of buffer tables, the optimizer is prone to generate suboptimal execution plans.

A buffer table has the following characteristics:

• Trigger condition:

  A large proportion of data in the table is frequently updated.

• Scenario:

  • The application logic involves a large number of insert and delete operations.

  • The application logic involves a large number of index column updates.

• Symptom:

  The table does not have many rows, but queries are slow.

• Cause:

  • A large amount of data labelled as deleted is processed during range queries.

  • A non-optimal execution plan is used.

When you determine from the V$OB_SQL_AUDIT view that it is an SQL issue, if the suspicious SQL has range query characteristics, you can further confirm whether the table is a buffer table.

Buffer table determination logic

You can use internal views to count the total rows and the increments of inserted, updated, and deleted rows at the table level. A table is a buffer table if it meets any of the following conditions:

• The primary table has both a large number of inserts and deletes: the insert and delete row increments are similar, and the numbers of inserted and deleted rows are large.

• A large number of index column updates: the update row increment accounts for a large proportion of total rows, and the number of updated rows is large.

You can resolve this issue by using the following methods:

• Analyze whether a better execution plan exists and manually bind it by using the CREATE OUTLINE statement.

• Manually trigger a major compaction to physically delete the data that is labelled as deleted.

If no better execution plan exists, you need to resolve through merge, but recovery should be as soon as possible. You can try the following:

• Scale out.

• Increase the system parameter cpu_quota_concurrency.

  cpu_quota_concurrency specifies the maximum concurrency allowed for each CPU quota of a tenant. For more information, see cpu_quota_concurrency.

• Throttle the problematic SQL (minimize traffic as much as possible, or even stop it).