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.
Note
For more information about the V$OB_SQL_AUDIT view, see V$OB_SQL_AUDIT (Oracle mode) or V$OB_SQL_AUDIT (MySQL mode).
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_concurrencyspecifies 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 thecpu_quota_concurrencyparameter 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_concurrencyspecifies the maximum concurrency allowed for each CPU quota of a tenant. For more information, see cpu_quota_concurrency.Perform SQL throttling.
Notice
We recommend that you solve a capacity issue by increasing the CPU specification of the tenant or the value of cpu_quota_concurrency. More worker threads require more memory resources. Therefore, you also need to increase the memory specification of the tenant.