关于SQL_AUDIT视图中物理读指标的理解问题

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.2
【问题描述】如何理解DISK_READS和INDEX_BLOCK_READ_CNT指标含义?
测试语句:

select /*+ INDEX(a idx2) */ * from tcustomer a where C_ACCTBAL > 9000;

其执行计划:

obclient [tpch10]> explain extended pretty_color select /*+ INDEX(a idx2) */ * from customer a where C_ACCTBAL > 9000;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================                                                                                                                    |
| |ID|OPERATOR        |NAME   |EST.ROWS|EST.TIME(us)|                                                                                                                    |
| ---------------------------------------------------                                                                                                                    |
| |0 |TABLE RANGE SCAN|a(idx2)|136864  |421697      |                                                                                                                    |
| ===================================================                                                                                                                    |
| Outputs & filters:                                                                                                                                                     |
| -------------------------------------                                                                                                                                  |
|   0 - output([a.C_CUSTKEY(0x7f738c0404a0)], [a.C_NAME(0x7f738c040770)], [a.C_ADDRESS(0x7f738c040a40)], [a.C_NATIONKEY(0x7f738c040d10)], [a.C_PHONE(0x7f738c040fe0)],   |
|        [a.C_ACCTBAL(0x7f738c03f470)], [a.C_MKTSEGMENT(0x7f738c0412b0)], [a.C_COMMENT(0x7f738c041580)]), filter(nil), rowset=256                                        |
|       access([a.C_CUSTKEY(0x7f738c0404a0)], [a.C_ACCTBAL(0x7f738c03f470)], [a.C_NAME(0x7f738c040770)], [a.C_ADDRESS(0x7f738c040a40)], [a.C_NATIONKEY(0x7f738c040d10)], |
|        [a.C_PHONE(0x7f738c040fe0)], [a.C_MKTSEGMENT(0x7f738c0412b0)], [a.C_COMMENT(0x7f738c041580)]), partitions(p0)                                                   |
|       is_index_back=true, is_global_index=false,                                                                                                                       |
|       range_key([a.C_ACCTBAL(0x7f738c03f470)], [a.C_CUSTKEY(0x7f738c0404a0)]), range(9000,MAX ; MAX,MAX),                                                              |
|       range_cond([a.C_ACCTBAL(0x7f738c03f470) > cast(9000, DECIMAL(4, 0))(0x7f738c03f9c0)(0x7f738c03ed70)])                                                            |
| Used Hint:                                                                                                                                                             |
| -------------------------------------                                                                                                                                  |
|   /*+                                                                                                                                                                  |
|                                                                                                                                                                        |
|       INDEX("a" "idx2")                                                                                                                                                |
|   */                                                                                                                                                                   |
| Qb name trace:                                                                                                                                                         |
| -------------------------------------                                                                                                                                  |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                       |
|   stmt_id:1, SEL$1                                                                                                                                                     |
| Outline Data:                                                                                                                                                          |
| -------------------------------------                                                                                                                                  |
|   /*+                                                                                                                                                                  |
|       BEGIN_OUTLINE_DATA                                                                                                                                               |
|       INDEX(@"SEL$1" "a"@"SEL$1" "idx2")                                                                                                                               |
|       OPTIMIZER_FEATURES_ENABLE('4.0.0.0')                                                                                                                             |
|       END_OUTLINE_DATA                                                                                                                                                 |
|   */                                                                                                                                                                   |
| Optimization Info:                                                                                                                                                     |
| -------------------------------------                                                                                                                                  |
|   a:                                                                                                                                                                   |
|       table_rows:1500000                                                                                                                                               |
|       physical_range_rows:136864                                                                                                                                       |
|       logical_range_rows:136864                                                                                                                                        |
|       index_back_rows:136864                                                                                                                                           |
|       output_rows:136864                                                                                                                                               |
|       table_dop:1                                                                                                                                                      |
|       dop_method:Table DOP                                                                                                                                             |
|       avaiable_index_name:[idx1, idx2, customer]                                                                                                                       |
|       pruned_index_name:[idx1, customer]                                                                                                                               |
|       stats version:1694254801441111                                                                                                                                   |
|       dynamic sampling level:0                                                                                                                                         |
|   Plan Type:                                                                                                                                                           |
|       LOCAL                                                                                                                                                            |
|   Note:                                                                                                                                                                |
|       Degree of Parallelisim is 1 because of table property                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
50 rows in set (0.003 sec)

其audit_sql信息:

obclient [oceanbase]> select * from oceanbase.GV$ob_sql_audit where trace_id='YB42C0A81FC9-000604DBE69F658A-0-0' \G
*************************** 1. row ***************************
                         SVR_IP: 192.168.31.201
                       SVR_PORT: 2882
                     REQUEST_ID: 1179463
                    SQL_EXEC_ID: 9955581
                       TRACE_ID: YB42C0A81FC9-000604DBE69F658A-0-0
                            SID: 3221575926
                      CLIENT_IP: 192.168.31.192
                    CLIENT_PORT: 59626
                      TENANT_ID: 1004
                    TENANT_NAME: tpch
            EFFECTIVE_TENANT_ID: 1004
                        USER_ID: 200001
                      USER_NAME: root
                     USER_GROUP: 0
                 USER_CLIENT_IP: 192.168.31.192
                          DB_ID: 500523
                        DB_NAME: tpch10
                         SQL_ID: 5A3B2E7D76A40049D03CF6EDB4D5A292
                      QUERY_SQL: select /*+ INDEX(a idx2) */ * from customer a where C_ACCTBAL > 9000
                        PLAN_ID: 80174
                  AFFECTED_ROWS: 0
                    RETURN_ROWS: 1000
                  PARTITION_CNT: 2
                       RET_CODE: 0
                          QC_ID: 0
                         DFO_ID: 0
                         SQC_ID: 0
                      WORKER_ID: 0
                          EVENT: db file compact read
                         P1TEXT: fd
                             P1: 167
                         P2TEXT: offset
                             P2: 0
                         P3TEXT: size
                             P3: 0
                          LEVEL: 0
                  WAIT_CLASS_ID: 109
                    WAIT_CLASS#: 9
                     WAIT_CLASS: SYSTEM_IO
                          STATE: WAITED SHORT TIME
                WAIT_TIME_MICRO: 379
          TOTAL_WAIT_TIME_MICRO: 379
                    TOTAL_WAITS: 1
                      RPC_COUNT: 0
                      PLAN_TYPE: 1
                   IS_INNER_SQL: 0
                IS_EXECUTOR_RPC: 0
                    IS_HIT_PLAN: 0
                   REQUEST_TIME: 1694349304209518
                   ELAPSED_TIME: 10518
                       NET_TIME: 0
                  NET_WAIT_TIME: 1
                     QUEUE_TIME: 5
                    DECODE_TIME: 0
                  GET_PLAN_TIME: 1847
                   EXECUTE_TIME: 8658
          APPLICATION_WAIT_TIME: 0
          CONCURRENCY_WAIT_TIME: 0
              USER_IO_WAIT_TIME: 0
                  SCHEDULE_TIME: 0
                  ROW_CACHE_HIT: 0
         BLOOM_FILTER_CACHE_HIT: 0
                BLOCK_CACHE_HIT: 1499
                     DISK_READS: 1
                      RETRY_CNT: 0
                     TABLE_SCAN: 0
              CONSISTENCY_LEVEL: 3
        MEMSTORE_READ_ROW_COUNT: 0
         SSSTORE_READ_ROW_COUNT: 2000
            DATA_BLOCK_READ_CNT: 1030
           DATA_BLOCK_CACHE_HIT: 1030
           INDEX_BLOCK_READ_CNT: 4002
          INDEX_BLOCK_CACHE_HIT: 1001
            BLOCKSCAN_BLOCK_CNT: 0
              BLOCKSCAN_ROW_CNT: 0
PUSHDOWN_STORAGE_FILTER_ROW_CNT: 0
            REQUEST_MEMORY_USED: 2607064
          EXPECTED_WORKER_COUNT: 0
              USED_WORKER_COUNT: 0
                     SCHED_INFO: NULL
             FUSE_ROW_CACHE_HIT: 0
              PS_CLIENT_STMT_ID: -1
               PS_INNER_STMT_ID: -1
                          TX_ID: 0
               SNAPSHOT_VERSION: 1694349304011566925
                   REQUEST_TYPE: 2
          IS_BATCHED_MULTI_STMT: 0
                  OB_TRACE_INFO: NULL
                      PLAN_HASH: 6634794641901061603
             LOCK_FOR_READ_TIME: 0
                   PARAMS_VALUE: 
                      RULE_NAME: 
                  PARTITION_HIT: 1
            TX_INTERNAL_ROUTING: 0
               TX_STATE_VERSION: 0
1 row in set (0.204 sec)

从过SQL_AUDIT可以看到其中三个指标:
DISK_READS: 1
INDEX_BLOCK_READ_CNT: 4002
INDEX_BLOCK_CACHE_HIT: 1001
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000035692
DISK_READS:是物理读次数,
INDEX_BLOCK_READ_CNT: 访问的中间层微块数量,**如何理解这个中间层微块数量?**我理解是不是索引扫描的微块数量?
假设是索引扫描微块数量,那么INDEX_BLOCK_READ_CNT-INDEX_BLOCK_CACHE_HIT=3001个微块需要从磁盘中读取,一个微块一次IO,那么和DISK_READS的物理读次数对不上。
所以应该如何理解DISK_READS和INDEX_BLOCK_READ_CNT指标含义?

2 个赞

可以看下这个字段介绍能否有帮助
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000033851

2 个赞

文档中没有说明 INDEX_BLOCK_READ_CNT的用途。DISK_READS已经比较清楚,就是IO读取次数,但是 INDEX_BLOCK_READ_CNT怎么理解呢?

2 个赞

索引快的读取次数

帮忙看下,
如果是索引块的读取次数,那么发生物理读取的索引块次数是INDEX_BLOCK_READ_CNT-INDEX_BLOCK_CACHE_HIT=3001,且数据的读取每次是一个微块(写磁盘和分区调度是宏块,查询是微块),也就是索引块需要发生3001次读取IO,那么我想DISK_READS应该是包含索引物理读次数的,那为何DISK_READS=1会小于索引物理读次数=3001呢?

INDEX_BLOCK_READ_CNT和二级索引没有关系,是OB4.x内部针对sstable数据的中间层索引
这个表示读了多少次中间层微块,包含根节点+非跟节点次数,根节点可以理解为常驻内存的所以不会有IO,但不会统计到INDEX_BLOCK_CACHE_HIT,非跟节点可能从磁盘读取,这里和DISK_READS对不上就是因为读根节点的原因

非常感谢。另外想问下,disk_reads 每个IO是一个微块么?还是说随机读是读微块,顺序读(比如全表扫描)是多个微块?

这里如果不发生IO不应该统计在INDEX_BLOCK_CACHE_HIT中么? 还是说INDEX_BLOCK_CACHE是单独的一个cache,如果没有命中则去kvcache中(二级缓存)获取INDEX_BLOCK,如果缓存在kvcache中则不发生IO?