高頻度の短時間クエリの特定と分析

CREATE TABLE T1 (C1 INT, C2 INT);
CREATE INDEX IDX_C1 ON T1 (C1);

-- 10K行のデータを挿入し、C1値はほぼ一意である
INSERT INTO T1 VALUES (1, 1), (2, 2), ... (10000, 10000);

-- 高頻度クエリ：単一実行時間は短いが、読み取る行数には大きな差が生じる可能性がある
SELECT * FROM T1 WHERE C1 = 1;

SELECT /*+READ_CONSISTENCY(WEAK), QUERY_TIMEOUT(100000000)*/  svr_ip, sql_id,
    tenant_id, tenant_name, user_name, db_name, plan_id,
    count(*) exections,
    max(event) event,
    max(table_scan) table_scan,
    sum(case when ret_code = 0 then 0 else 1 end) fail_times,
    sum(rpc_count) rpc_count,
    sum(retry_cnt) retry_cnt,
    sum(case when plan_type = 2 then 1 else 0 end)
    remote_plans,
    sum(case when is_hit_plan = 1 then 0 else 1 end) miss_plans,
    round(avg(elapsed_time)) elapsed_time,
    round(max(elapsed_time)) max_elapsed_time,
    round(avg(execute_time)) execute_time,
    round(avg(( execute_time - total_wait_time_micro + get_plan_time ))) cpu_time,
    round(max(( execute_time - total_wait_time_micro + get_plan_time ))) max_cpu_time,
    round(avg(queue_time)) queue_time,
    round(avg(net_wait_time)) netwait_time,
    round(avg(user_io_wait_time)) iowait_time,
    round(avg(get_plan_time)) getplan_time,
    round(avg(decode_time)) decode_time,
    round(avg(total_wait_time_micro)) total_wait_time,
    round(avg(application_wait_time)) app_wait_time,
    round(avg(concurrency_wait_time)) concurrency_wait_time,
    round(avg(schedule_time)) schedule_time,
    round(avg(return_rows)) return_rows,
    round(avg(affected_rows)) affected_rows,
    round(avg(row_cache_hit * 2 + bloom_filter_cache_hit * 2 + block_cache_hit +
    disk_reads)) logical_reads,
    round(avg(row_cache_hit)) row_cache_hit,
    round(avg(bloom_filter_cache_hit)) bloom_filter_cache_hit,
    round(avg(block_cache_hit)) block_cache_hit,
    round(avg(disk_reads)) disk_reads,
    round(avg(memstore_read_row_count)) memstore_read_row_count,
    round(avg(ssstore_read_row_count)) ssstore_read_row_count,
    sum(memstore_read_row_count + ssstore_read_row_count) as total_row_count
    from   gv$ob_sql_audit
    where  is_inner_sql = 0
    group  by svr_ip, sql_id order by total_row_count desc limit 10;

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;

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

SELECT OPERATOR,  -- 演算子タイプ
       NAME,      -- 操作対象
       ROWS,      -- 予想される行数
       COST       -- 予想されるコスト
FROM GV$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 |
+----------------+------+------+------+

EXPLAIN SELECT * FROM T1 WHERE C1 = 1;

Query Plan: ===================================
|ID|OPERATOR  |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1  |10000     |46  |  -- インデックスヒットしない場合、予想される行数は実際よりもはるかに高くなる可能性があります。
==================================

Outputs & filters:
-------------------------------------
0 - output([t1.*]), filter([t1.c1 = 1]),
    access([t1.__pk_increment]), partitions(p0)