Generally, there are types of causes that are often analyzed as a cause of increasing CPU utilization. Therefore, a step-by-step check to see if it corresponds to a well-known type of problem can be effective.
2.1 Has more transaction processing occurred than usual?
If the items of 'Session', 'Service Thread', and 'Prepare/Execute' corresponding to the 'routine check' items all show increasing values and the CPU usage increases, this is a normal increase pattern.
Be cautious when distinguishing the following from a normal pattern. The number of service threads may increase due to unverified queries (performed for a long time or causing a large amount of change operation) and services from users.
This is a type that increases by the service thread's own load-balancing function.
The pattern itself is a normal case, and it is necessary to find the cause of the query that is increased in this way, however, it is difficult to find out if query. that is executed by the user. As a result, it is necessary to observe the server with the following query.
2.2 Has a newly added service (application) occurred?
As it is most likely to be a direct cause, the execution plan for newly added queries should be analyzed first, and appropriate tuning operation should be performed for queries with high execution time and access cost. Periodic observation is necessary with the method of searching the malignant substances mentioned above.
As a result, both of the above two items are cases in which the structure of the applications that execute the serve or the query to be executed are implemented incorrectly. (Please refer to the CPU usage of Altibase's query process described below)
However, if the users check the items in 2.1, 2.2, most users would answer "No". Then, if there is no change in service configuration and system, and there is not change in the version of Altibase, what is the cause of CPU overload?
In order to find a problem, it is necessary to analyze the currently running program and DB.
2.3 Problem analysis of the application in service
If a CPU overload occurs even though there is no change in the configuration of any service operation, it can be assumed that the problem that the existing query originally had is revealed as the cause of the increase in data.
For example, the index used in the past performed properly in a situation where the data size was small, but the data size increased over time. In this situation, if an existing index was not properly used, it means it may be the cause of the increase in CPU utilization.
2.4 Problem caused by query execution cost increase due to query plan change
Altibase version 4, Altibase version 5, and Altibase version 6(version 6.1.1.6.1 or later) or later are versions in which the statistical information collection operation used by the optimizer is automatically executed according to specific conditions.
Statistical information is often updated mainly due to data increase and DDL execution (index change). In this case, the query optimizer may occasionally select the wrong plan with a higher cost than before while selecting a new query plan.
Queries executed with such an inefficient plan can significantly increase the access-cost and cause an increase in CPU usage. Generally, in this case, the problem query is often identified with the query that retrieves the long-run query described above.
Therefore, when an abnormal CPU usage pattern occurs, it is effective to check the long-run query first with the monitoring query.
The long-run query can be checked if there is a problem with the plan by executing the statement showing the plan when the query is executed and then checking the execution plan of the query in question, or by retrieving the runtime plan stored in V$PLANTEXT.
To check the status of a query, execute the following query to check the status of a query from the "Altibase Monitoring Guide" document.
Altibase Monitoring Guide Document >> 3. Monitoring Elements and Monitoring Query >> 2.Statement (Shortcut: 2. Statement)