如何提高gv$sql_audit_sql查询速度

spider慢 · 2023 年12 月 27 日 20:39

如题，如何能提高按request_time查询v$ob_audit_sql一段时间范围的速度，能否__all_virtual_sql_audit加个索引
flt_trace_id varchar(1024) NOT NULL,
PRIMARY KEY (svr_ip, svr_port, tenant_id, request_id),
KEY all_virtual_sql_audit_i1 (tenant_id, request_id) STORING (trace_id, `client_ip

兹拉坦 · 2023 年12 月 28 日 09:48

这张表看上去是一张虚拟表，如果需要加索引的话得修改一下这张虚拟表的实现代码。

不过您可以看一下 src/share/inner_table/ob_inner_table_schema_def.py 这个文件里的虚拟表定义：

def_table_schema(
  owner = 'xiaoyi.xy',
  tablegroup_id = 'OB_INVALID_ID',
  table_name    = '__all_virtual_sql_audit',
  table_id      = '11031',
  table_type = 'VIRTUAL_TABLE',
  index_using_type = 'USING_BTREE',
  in_tenant_space = True,
  gm_columns    = [],
  rowkey_columns = [
    ('svr_ip', 'varchar:MAX_IP_ADDR_LENGTH'),
    ('svr_port', 'int'),
    ('tenant_id', 'int'),
    ('request_id', 'int'),
  ],
  normal_columns = [
    ('trace_id', 'varchar:OB_MAX_HOST_NAME_LENGTH'),
    ('client_ip', 'varchar:MAX_IP_ADDR_LENGTH'),
    ('client_port', 'int'),
    ('tenant_name', 'varchar:OB_MAX_TENANT_NAME_LENGTH'),
    ('effective_tenant_id', 'int'),
    ('user_id', 'int'),
    ('user_name', 'varchar:OB_MAX_USER_NAME_LENGTH'),
    ('db_id', 'uint'),
    ('db_name', 'varchar:OB_MAX_DATABASE_NAME_LENGTH'),
    ('sql_id', 'varchar:OB_MAX_SQL_ID_LENGTH'),
    ('query_sql', 'longtext'),
    ('plan_id', 'int'),
    ('affected_rows', 'int'),
    ('return_rows', 'int'),
    ('partition_cnt', 'int'),
    ('ret_code', 'int'),
    ('qc_id', 'uint'),
    ('dfo_id', 'int'),
    ('sqc_id', 'int'),
    ('worker_id', 'int'),

    ('event', 'varchar:OB_MAX_WAIT_EVENT_NAME_LENGTH', 'true'),
    ('p1text', 'varchar:OB_MAX_WAIT_EVENT_PARAM_LENGTH', 'true'),
    ('p1', 'uint', 'true'),
    ('p2text', 'varchar:OB_MAX_WAIT_EVENT_PARAM_LENGTH', 'true'),
    ('p2', 'uint', 'true'),
    ('p3text', 'varchar:OB_MAX_WAIT_EVENT_PARAM_LENGTH', 'true'),
    ('p3', 'uint', 'true'),
    ('level', 'int', 'true'),
    ('wait_class_id', 'int', 'true'),
    ('wait_class#', 'int', 'true'),
    ('wait_class', 'varchar:OB_MAX_WAIT_EVENT_PARAM_LENGTH', 'true'),
    ('state', 'varchar:19', 'true'),
    ('wait_time_micro', 'int', 'true'),
    ('total_wait_time_micro', 'int', 'true'),
    ('total_waits', 'int', 'true'),

    ('rpc_count', 'int', 'true'),
    ('plan_type', 'int'),

    ('is_inner_sql', 'bool'),
    ('is_executor_rpc', 'bool'),
    ('is_hit_plan', 'bool'),

    ('request_time', 'int'),
    ('elapsed_time', 'int'),
    ('net_time', 'int'),
    ('net_wait_time', 'int'),
    ('queue_time', 'int'),
    ('decode_time','int'),
    ('get_plan_time', 'int'),
    ('execute_time', 'int'),
    ('application_wait_time', 'uint', 'true'),
    ('concurrency_wait_time', 'uint', 'true'),
    ('user_io_wait_time', 'uint', 'true'),
    ('schedule_time', 'uint', 'true'),
    ('row_cache_hit', 'int', 'true'),
    ('bloom_filter_cache_hit', 'int', 'true'),
    ('block_cache_hit', 'int', 'true'),
    ('disk_reads', 'int', 'true'),
    ('execution_id', 'int'),
    ('session_id', 'uint'),
    ('retry_cnt', 'int'),
    ('table_scan', 'bool'),
    ('consistency_level', 'int'),
    ('memstore_read_row_count', 'int', 'true'),
    ('ssstore_read_row_count', 'int', 'true'),
    ('data_block_read_cnt', 'int', 'true'),
    ('data_block_cache_hit', 'int', 'true'),
    ('index_block_read_cnt', 'int', 'true'),
    ('index_block_cache_hit', 'int', 'true'),
    ('blockscan_block_cnt', 'int', 'true'),
    ('blockscan_row_cnt', 'int', 'true'),
    ('pushdown_storage_filter_row_cnt', 'int', 'true'),
    ('request_memory_used', 'bigint'),
    ('expected_worker_count', 'int'),
    ('used_worker_count', 'int'),
    ('sched_info', 'varchar:16384', 'true'),
    ('fuse_row_cache_hit', 'int', 'true'),

    ('user_client_ip', 'varchar:MAX_IP_ADDR_LENGTH'),
    ('ps_client_stmt_id', 'int'),
    ('ps_inner_stmt_id', 'int'),
    ('transaction_id', 'int'),
    ('snapshot_version', 'uint'),
    ('snapshot_source', 'varchar:16'),
    ('request_type', 'int'),
    ('is_batched_multi_stmt', 'bool'),
    ('ob_trace_info', 'varchar:4096'),
    ('plan_hash', 'uint'),
    ('user_group', 'int', 'true'),
    ('lock_for_read_time', 'bigint'),
    ('params_value', 'longtext'),
    ('rule_name', 'varchar:256'),
    ('proxy_session_id', 'uint'),
    ('tx_internal_route_flag', 'uint'),

    ('partition_hit', 'bool'),
    ('tx_internal_route_version', 'uint'),
    ('flt_trace_id', 'varchar:OB_MAX_SPAN_LENGTH')
  ],
  partition_columns = ['svr_ip', 'svr_port'],
  vtable_route_policy = 'distributed',
  index = {'all_virtual_sql_audit_i1' :  { 'index_columns' : ['tenant_id', 'request_id'],
                     'index_using_type' : 'USING_BTREE'}},
)

会发现这张虚拟表上其实已经有一个索引了，建在了 ‘tenant_id’, ‘request_id’ 这俩列上，所以感觉可以通过指定过滤条件为 where tenant_id = xxxxx and request_id > xxxxx and request_id < xxxxx 来取得相似的功能，request_id 应该是根据时间序递增的。