Capacity issues

If you confirm that the performance bottleneck is not caused by the execution plan, it is likely caused by capacity issues. You can query the V$OB_SQL_AUDIT view. If the value of the QUEUE_TIME field increases significantly, and no SQL query involves a large number of logical reads or results in a large value for the EXECUTE_TIME field, the performance bottleneck is caused by capacity issues. If the performance bottleneck is caused by suboptimal plans, you can find a large number of logical reads and time-consuming SQL queries, and the execution duration is prolonged mainly due to the increase of CPU time.

Capacity issues may occur in the following scenarios:

• Increase of application traffic

  If the execution plans of top SQL statements are not changed, but the CPU utilization of the tenant and the number of SQL executions increase, the performance issue is likely caused by the increase of application traffic.

• Changes of application workload

  Changes of application workload, such as the increase in the number of large queries, also cause performance issues.

• Contention of computing resources at the infrastructure layer.

You can solve the capacity issues by using the following methods:

• Adjust the maximum number of CPU cores for a tenant.

• Adjust cluster parameters.

  The cluster parameter cpu_quota_concurrency specifies the number of worker threads provided by the CPU cores of a tenant. The minimum number of worker threads for a tenant is calculated by using the following formula: cpu_quota_concurrency × MIN_CPU. When a capacity issue occurs in a tenant, if the CPU workload of the physical server is not high, it indicates that most worker threads do not use CPU resources. You can set the cpu_quota_concurrency parameter is a proper larger value, so that more computing resources of the physical server are available for the worker threads. Note that an excessively large value leads to frequent context switching and frequent creation and termination of threads, which result in system issues.

  Note

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

• Perform SQL throttling.