Locate and analyze slow queries I

The efficiency of a running database system can downgrade when a batch of queries are concurrently executed or when a type of queries are executed for a long time. This topic describes how to locate a slow query and obtain its execution plan by using __all_virtual_processlist, gv$plan_cache_plan_stat, and gv$plan_cache_plan_explain.

Sample scenario

The following example initiates a slow query. You can locate the slow query and obtain its execution plan by taking the following steps.

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

SELECT SLEEP(1000) FROM T1;

Locate a slow SQL query

Log on to the sys tenant and execute the following statement to accurately locate the slow SQL query with detailed filter conditions. For example, you can specify user or tenant.

SELECT user,
   tenant,
   sql_id,
   concat(time, 's') as time,
   info,
   svr_ip,
   svr_port,
   trace_id
FROM __all_virtual_processlist
WHERE STATE = 'ACTIVE'
ORDER BY time DESC LIMIT 1

The following result is returned, which displays information about the slow SQL query, including the username, tenant name, SQL ID, execution time, SQL text, IP address and port of the OBServer node where the query is executed, and trace ID of the query.

*************************** 1. row ***************************
       USER: admin
    tenant: mysql
    sql_id: 3310A1D1D81D4BA92CEEF42538136DD1
        time: 13s
        info: select sleep(1000), rand() from t1
    svr_ip: 11.xxx.x.xx
    svr_port: 35046
    trace_id: Y88E60BA20011-0005E5453393493A

Obtain the execution plan for a slow query

You can obtain information about the execution plan for the preceding slow SQL query based on the query information returned.

1. Execute the following statement to obtain information about the execution plan for the slow SQL query.

   Use the actual tenant ID for tenant_id and specify svr_ip, svr_port, and sql_id based on the previously returned information about the slow SQL query.

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

   The following result is returned, which displays information about the execution plan for the query, including the plan ID (plan_id), time when the plan was first generated (first_load_time), and time when the plan was last used (last_active_time). For batch processing, last_active_time is very likely the time when the slow query was initiated.

   Here, outline_data is the 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 tree-shaped execution plan for the slow SQL query.

   Execute the following statement:

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

   The physical plan for the query is as follows:

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

   You can also try to obtain the logical plan by using the EXPLAIN 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)