博客如何查看已经完成创建的索引的耗时

如何查看已经完成创建的索引的耗时-c7电子娱乐

2025-01-09运维管理

问题背景

1736401759

oceanbase 数据库创建索引是 online ddl，执行期间需要扫描主表数据，完成排序，最后写入到索引表，所以执行的时间和对应表的数据量有关。平时我们可以通过 gv$session_longops 对应的视图在 ddl 执行期间查看进度，如果一条 ddl 已经执行完成了，比如这里的索引已经创建完成，如何确认创建索引花费了多长时间。

1、获取主表的 table_id

select
  table_id
from
  __all_virtual_table t1
join dba_ob_tenants t2
on
  t1.tenant_id = t2.tenant_id
join __all_virtual_database d
on
  t1.database_id = d.database_id
where
  t2.tenant_name = 'test1'
  and d.database_name = 'tpch'
  and t1.table_name = 'orders';
---------- 
| table_id |
---------- 
|   596757 |
---------- 
1 row in set (0.38 sec)

2、获取索引表对应的 table_id

select
  table_id
from
  __all_virtual_table t1
join dba_ob_tenants t2
on
  t1.tenant_id = t2.tenant_id
join __all_virtual_database d
on
  t1.database_id = d.database_id
where
  t2.tenant_name = 'test1'
  and d.database_name = 'tpch'
  and t1.data_table_id=596757;
---------- 
| table_id |
---------- 
|   596822 |
---------- 
1 row in set (0.35 sec)
-- 或者
select
  table_id
from
  __all_virtual_table t1
join dba_ob_tenants t2
on
  t1.tenant_id = t2.tenant_id
join __all_virtual_database d
on
  t1.database_id = d.database_id
where
  t2.tenant_name = 'test1'
  and d.database_name = 'tpch'
  and t1.table_name = '__idx_596757_idx_o_orderdate';
---------- 
| table_id |
---------- 
|   596822 |
---------- 
1 row in set (0.35 sec)

说明：

1、索引表对应的 data_table_id 是其主表的 table_id

2、索引表的命名是 __idx_主表id_索引名，比如这里的主表id是596757，索引名是o_orderdate。

3、查看 rootservice的历史事件

-- 耗时明细
select
  *
from
  dba_ob_rootservice_event_history
where
  value3 in (
  select
    distinct value3
  from
    dba_ob_rootservice_event_history
  where
    value5 = 'data_table_id:596757, dest_table_id:596822');
 ---------------------------- --------------- ---------------------------------------- ----------- -------- ------- -------- ---------- ------------------------------------ ----------- ---------- -------------- -------------------------------------------- ------------------ ------------------------ ------------------- ---------------- ------------- 
| timestamp                  | module        | event                                  | name1     | value1 | name2 | value2 | name3    | value3                             | name4     | value4   | name5        | value5                                     | name6            | value6                 | extra_info        | rs_svr_ip      | rs_svr_port |
 ---------------------------- --------------- ---------------------------------------- ----------- -------- ------- -------- ---------- ------------------------------------ ----------- ---------- -------------- -------------------------------------------- ------------------ ------------------------ ------------------- ---------------- ------------- 
| 2025-01-09 10:30:38.709015 | ddl scheduler | create index                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | table_id     | data_table_id:-1, index_table_id:-1        | schema_version   | 1736389837682120       |                   | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:30:38.755274 | ddl scheduler | switch_state                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | object_id    | object_id:596757, target_object_id:596822  | snapshot_version | 0                      | wait_trans_end    | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:30:39.440962 | ddl scheduler | ddl wait trans end ctx try_wait        | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | wait_type | 1        | tablet_count | 32                                         | snapshot_version | 1736389839233780000    | 1                 | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:30:39.554853 | ddl scheduler | switch_state                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | object_id    | object_id:596757, target_object_id:596822  | snapshot_version | 1736389839233780000    | redefinition      | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:30:40.273569 | ddl scheduler | index sstable build task send innersql | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | table_id     | data_table_id:596757, dest_table_id:596822 | sql_exec_addr    | "11.xxx.xxx.191:12882" |                   | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:30:40.515520 | ddl scheduler | index sstable build task send innersql | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | table_id     | data_table_id:596757, dest_table_id:596822 | sql_exec_addr    | "11.xxx.xxx.191:22882" |                   | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:32:09.359340 | ddl scheduler | index sstable build task finish        | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | table_id     | data_table_id:596757, dest_table_id:596822 | sql_exec_addr    | "11.xxx.xxx.191:22882" |                   | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:32:12.070107 | ddl scheduler | index sstable build task finish        | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | table_id     | data_table_id:596757, dest_table_id:596822 | sql_exec_addr    | "11.xxx.xxx.191:12882" |                   | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:32:13.994816 | ddl scheduler | switch_state                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | object_id    | object_id:596757, target_object_id:596822  | snapshot_version | 1736389839233780000    | validate_checksum | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:32:14.054413 | ddl scheduler | switch_state                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | object_id    | object_id:596757, target_object_id:596822  | snapshot_version | 1736389839233780000    | take_effect       | 11.xxx.xxx.191 |       22882 |
| 2025-01-09 10:32:19.436646 | ddl scheduler | switch_state                           | tenant_id | 1010   | ret   | 0      | trace_id | y59620ba2d9bf-0006272822d72c93-0-0 | task_id   | 49078172 | object_id    | object_id:596757, target_object_id:596822  | snapshot_version | 1736389839233780000    | success           | 11.xxx.xxx.191 |       22882 |
 ---------------------------- --------------- ---------------------------------------- ----------- -------- ------- -------- ---------- ------------------------------------ ----------- ---------- -------------- -------------------------------------------- ------------------ ------------------------ ------------------- ---------------- ------------- 
11 rows in set (0.03 sec)
-- 耗时
select
  timediff(max(timestamp),min(timestamp)) as used_time
from
  dba_ob_rootservice_event_history
where
  value3 in (
  select
    distinct value3
  from
    dba_ob_rootservice_event_history
  where
    value5 = 'data_table_id:596757, dest_table_id:596822');
 ----------------- 
| used_time       |
 ----------------- 
| 00:01:40.727631 |
 ----------------- 
1 row in set (0.01 sec)

说明：

1、其中：value5 = 'data_table_id:596757, dest_table_id:596822') ，data_table_id 表示是该索引对应的是那张主表id，dest_table_id:596822' 表示的是待确认的索引表的id

2、dba_ob_rootservice_event_history 视图默认保留 7 天的数据，如果没有查询到记录，需要确认一下对应的索引表的创建时间，确认截止当前时间是否已经超过了7天，通过配置项 ob_event_history_recycle_interval 进行设置数据保留时间。

通过以上三步就可以确认某个已经创建成功的索引的耗时情况了。

运维管理

和顺