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 part 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 a large number of rows, but the table query is slow.

• Cause:

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

  • A suboptimal execution plan is used.

After you confirm that the performance bottleneck is due to SQL execution by using the V$OB_SQL_AUDIT view, if the suspicious SQL query has the characteristics of a range query, you can further confirm whether the table involved is a buffer table.

Buffer table determination logic

You can obtain the total number of rows of a table and the numbers of inserted, updated, and deleted rows by using internal views. If the table meets any of the following conditions, it is a buffer table:

• Large and roughly equal numbers of rows are inserted to and deleted from the primary table.

• A large proportion of rows of the index columns are updated.

You can use the following methods to solve this issue:

• Check whether a better execution plan is available and, if yes, manually bind it by executing the CREATE OUTLINE statement.

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

If no better execution plan is available, you can manually trigger a major compaction to reduce memory usage. However, to completely rectify this problem, use the following methods:

• Scale out the system.

• Increase the value of the system parameter cpu_quota_concurrency.

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

• Perform throttling for the problematic SQL query.