Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

 

Code Block
languagesql
-- Search for long run queries with exeuction or time to fetch result set more than 1 second
SELECT A.SESSION_ID,
       A.CLIENT_PID,
       B.QUERY,
       B.EXECUTE_TIME,
       B.FETCH_TIME
  FROM V$SESSION A,
       V$STATEMENT B
 WHERE A.ID = B.SESSION_ID
   AND (B.EXECUTE_TIME > 1000000 OR B.FETCH_TIME > 1000000);

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.

Panel

Altibase Monitoring Guide Document >> 3. Monitoring Elements and Monitoring Query >> 2.Statement (Shortcut: http://aid.altibase.com/x/o4KZ 2. Statement)