Locate and analyze slow queries (1)

Sometimes a batch of slow queries can slow down the entire system. Other times, a single long-executing query can block resources and prevent other queries from being processed. This topic explains how to use the GV$OB_PROCESSLIST, GV$OB_PLAN_CACHE_PLAN_STAT, and GV$OB_PLAN_CACHE_PLAN_EXPLAIN views to locate slow queries and their execution plans.

Example scenario

In the following example, a slow query is initiated at the end. The procedure below can be used to locate the slow query and its execution plan.

CREATE TABLE T1 (C1 INT, C2 INT);
INSERT INTO T1 VALUES (1, 1);

SELECT SLEEP(1000) FROM T1;

Find slow SQL statements

Log in to the sys tenant and run the following command to accurately find the slow SQL statements based on the specified filter conditions. For example, you can specify the user or tenant parameter.

SELECT USER,
       tenant,
       sql_id,
       concat(time, 's') as time,
       info,
       svr_ip,
       svr_port,
       trace_id
FROM GV$OB_PROCESSLIST
WHERE STATE = 'ACTIVE'
ORDER BY time DESC LIMIT 1;

The following results are returned. The information about the slow queries is displayed in the results, including the username, tenant name, sql_id, execution duration, query statement, IP address and port number of the OBServer node where the query statement is executed, and trace_id of the query statement.

+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+
| USER | tenant   | sql_id                           | time | info                                                                                                            | svr_ip         | svr_port | trace_id                           |
+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+
| root | mysql001 | A585F887331EF7267F0C87B343C69D99 | 0s   | SELECT USER,
tenant,
sql_id,
concat(time, 's') as time,
info,
svr_ip,
svr_port,
trace_id
FROM GV$OB_PROCESSLIST | 11.xxx.x.xx |    28824 | Y70980BA1CCFB-0006062A332767A6-0-0 |
+------+----------+----------------------------------+------+-----------------------------------------------------------------------------------------------------------------+----------------+----------+------------------------------------+

Query information about slow queries

After you find the slow SQL statements, you can search for the execution plans of the queries.

1. Execute the following command to retrieve the execution plans of the slow SQL statements.

   In the following query, specify the tenant_id, svr_ip, svr_port, and sql_id based on the results of the slow SQL statement search.

   SELECT tenant_id,
      svr_ip,
      svr_port,
      sql_id,
      plan_id,
      last_active_time,
      first_load_time,
      outline_data
   FROM GV$OB_PLAN_CACHE_PLAN_STAT
   WHERE TENANT_ID = 1002
   AND SQL_ID = '3310A1D1D81D4BA92CEEF42538136DD1'
   AND SVR_IP = '11.xxx.x.xx'
   AND SVR_PORT = 35046;
   

   The preceding command returns the following results. The ID (plan_id) of the execution plan, the time when the execution plan was first generated (first_load_time), and the time when the execution plan was last used (last_active_time) are displayed in the results. For batch processing, last_active_time is very likely the time when the slow query was initiated.

   outline_data is a hint description of the execution plan.

   *************************** 1. row ***************************
       tenant_id: 1002
           svr_ip: 11.xxx.x.xx
           svr_port: 35046
           sql_id: 3310A1D1D81D4BA92CEEF42538136DD1
           plan_id: 741
   last_active_time: 2022-08-04 11:00:34.466037
   first_load_time: 2022-08-04 11:00:34.466037
       outline_data: /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test.t1"@"SEL$1") END_OUTLINE_DATA*/
   

2. Obtain the shape of the execution plan.

   Execute the following command to retrieve the tree-like structure of the execution plan.

   SELECT OPERATOR, NAME, ROWS, COST FROM GV$OB_PLAN_CACHE_PLAN_EXPLAIN
   WHERE TENANT_ID = 1002 AND
       SVR_IP = '11.xxx.x.xx' AND
       SVR_PORT = 35046 AND
       PLAN_ID = 741 ;
   

   Below is the physical plan returned by the preceding query.

   +----------------+------+------+------+
   | OPERATOR       | NAME | ROWS | COST |
   +----------------+------+------+------+
   | PHY_TABLE_SCAN | t1   |    1 |   45 |
   +----------------+------+------+------+
   

   In addition to the physical plan, you can also request the logical plan. The method is simple. Just execute the EXPLAIN statement for the SQL statement.

   explain select sleep(1000), rand() from t1;
   
   *************************** 1. row ***************************
   Query Plan: ===================================
   |ID|OPERATOR  |NAME|EST. ROWS|COST|
   -----------------------------------
   |0 |TABLE SCAN|t1  |1        |46  |
   ===================================
   
   Outputs & filters:
   -------------------------------------
   0 - output([sleep(?)], [rand()]), filter(nil),
       access([t1.__pk_increment]), partitions(p0)