Locate and analyze slow queries (1)

Sometimes a batch of slow queries can slow down the entire database system. Sometimes a single long-running query can significantly reduce the efficiency of the system. 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;

Identify slow SQL queries

Log in to the sys tenant and run the following command to accurately identify the slow SQL queries based on more detailed filtering 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\G

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

*************************** 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

Query information about slow SQL queries

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

1. Run the following command to retrieve the execution plans of the slow SQL queries.

   In the following query, set the tenant_id parameter based on your business analysis, and fill in the svr_ip, svr_port, and sql_id parameters based on the results of the preceding SQL query.

   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\G
   

   The execution result of the preceding command is as follows. The ID (plan_id) of the execution plan of the query, 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. 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 of the query.

   *************************** 1. row ***************************
       tenant_id: 1002
           svr_ip: 11.xxx.x.xx
           svr_port: 35046
           sql_id: 3310A1D1D81D4BA92CEEF42538136DD1
           plan_id: 220540
           last_active_time: 2023-11-30 09:58:49.206073
           first_load_time: 2023-11-30 09:54:47.119147
           outline_data: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$A0F2ABC8" "oceanbase"."__all_virtual_processlist"@"SEL$2") PROJECT_PRUNE(@"SEL$2") MERGE(@"SEL$2F8A4177" < "SEL$1") OPTIMIZER_FEATURES_ENABLE('4.0.0.0') END_OUTLINE_DATA*/
   

2. Obtain the shape of the execution plan of the slow SQL query.

   Run the following command to retrieve the tree-shaped execution plan of the query.

   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_SORT        | NULL                      |    1 |    7 |
   |  PHY_TABLE_SCAN | __all_virtual_processlist |    2 |    6 |
   +-----------------+---------------------------+------+------+
   

   You can also attempt to retrieve the logical plan of the query. The method is simple. Just execute the SQL statement with the EXPLAIN statement in front of it.

   explain select sleep(1000), rand() from t1;
   
   +------------------------------------------------------------------------------------+
   | Query Plan                                                                         |
   +------------------------------------------------------------------------------------+
   | ===============================================                                    |
   | |ID|OPERATOR       |NAME|EST.ROWS|EST.TIME(us)|                                    |
   | -----------------------------------------------                                    |
   | |0 |TABLE FULL SCAN|t101|1       |4           |                                    |
   | ===============================================                                    |
   | Outputs & filters:                                                                 |
   | -------------------------------------                                              |
   |   0 - output([sleep(cast(1000, DECIMAL(4, 0)))], [rand()]), filter(nil), rowset=16 |
   |       access(nil), partitions(p0)                                                  |
   |       is_index_back=false, is_global_index=false,                                  |
   |       range_key([t101.__pk_increment]), range(MIN ; MAX)always true                |
   +------------------------------------------------------------------------------------+