スロークエリの特定と分析（1）

稼働中のデータベースシステムでは、時には一連のスロークエリがシステム全体のパフォーマンスを低下させることがあります。また、特定の種類のクエリが非常に長い時間をかけて実行され、終わらないこともあります。本記事では、GV$OB_PROCESSLIST、GV$OB_PLAN_CACHE_PLAN_STAT、GV$OB_PLAN_CACHE_PLAN_EXPLAIN を使用して、スロークエリと対応する実行計画を特定する方法を紹介します。

例

以下の例では、最後にスロークエリを実行しています。以下のプロセスを通じて、このスロークエリとその実行計画を特定できます。

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

SELECT SLEEP(1000) FROM T1;

スローSQLの検索

sysテナントにログインし、以下のコマンドを実行して、より詳細なフィルター条件を指定することで、検索対象のスローSQLを正確に取得します。例えば、user または tenant などを指定します。

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;

以下の結果が得られます。ユーザー名、テナント名、sql_id、実行時間、クエリ、配置されているOBServerノード情報（IPアドレス + ポート）、およびこのクエリのtrace_idを含むスロークエリの情報が表示されます。

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

スロークエリ情報の照会

上記でスローSQLを検索した結果に基づいて、そのクエリの実行計画情報を検索することができます。

1. 以下のコマンドを実行して、スローSQLの実行計画情報を取得します。

   以下のクエリでは、tenant_id は実際に分析する業務に応じて決定し、svr_ip、svr_port、sql_id はスローSQLを検索した結果に基づいて入力します。

   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;
   

   上記のコマンドを実行すると、次のような結果が返されます。このクエリの実行計画の番号（plan_id）、この実行計画が最初に生成された時間（first_load_time）、および最後に使用された時間（last_active_time）が得られます。バッチ処理において、last_active_time はスロークエリが開始された時間である可能性が高いです。

   outline_data は、このクエリの実行計画に対するヒントの説明です。

   *************************** 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. スローSQLの計画形式を取得します。

   以下のクエリを実行すると、実行計画のツリー形式を取得できます。

   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 ;
   

   以下は上記クエリの物理計画です。

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

   物理計画に加えて、論理計画の収集も試すことができます。論理計画を収集する方法は非常に簡単で、SQLを直接EXPLAINするだけです。

   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)