## OBD全新部署一个单节点 4.3.5.1 OBServer [root@10-186-56-43 ~]# obd demo -c oceanbase-ce Get local repositories ok Open ssh connection ok Get deployment connections ok Get standbys info ok Cluster status check ok oceanbase-ce work dir cleaning ok demo destroyed Cluster param config check ok Open ssh connection ok +--------------------------------------------------------------------------------------------+ | Packages | +--------------+---------+------------------------+------------------------------------------+ | Repository | Version | Release | Md5 | +--------------+---------+------------------------+------------------------------------------+ | oceanbase-ce | 4.3.5.1 | 101000042025031818.el7 | 8826bc816ae660198f9ca5fd7e96d93c1ce4fc84 | +--------------+---------+------------------------+------------------------------------------+ Repository integrity check ok Load param plugin ok Open ssh connection ok Initializes observer work home ok Parameter check ok Remote oceanbase-ce-4.3.5.1-101000042025031818.el7-8826bc816ae660198f9ca5fd7e96d93c1ce4fc84 repository install ok Remote oceanbase-ce-4.3.5.1-101000042025031818.el7-8826bc816ae660198f9ca5fd7e96d93c1ce4fc84 repository lib check ok demo deployed Get local repositories ok Load cluster param plugin ok Open ssh connection ok [WARN] OBD-1011: (127.0.0.1) The recommended value of fs.aio-max-nr is 1048576 (Current value: 65536) [WARN] OBD-1007: (127.0.0.1) The recommended number of stack size is unlimited (Current value: 10240) [WARN] OBD-1017: (127.0.0.1) The value of the "vm.max_map_count" must be within [327600, 1310720] (Current value: 65530, Recommended value: 655360) [WARN] OBD-1017: (127.0.0.1) The value of the "vm.overcommit_memory" must be 0 (Current value: 1, Recommended value: 0) [WARN] OBD-1017: (127.0.0.1) The value of the "fs.file-max" must be greater than 6573688 (Current value: 1024000, Recommended value: 6573688) [WARN] OBD-1012: (127.0.0.1) clog and data use the same disk (/) cluster scenario: express_oltp Start observer ok observer program health check ok Connect to observer 127.0.0.1:2881 ok Cluster bootstrap ok obshell start ok obshell program health check ok obshell bootstrap ok Connect to observer 127.0.0.1:2881 ok Wait for observer init ok +---------------------------------------------+ | oceanbase-ce | +-----------+---------+------+-------+--------+ | ip | version | port | zone | status | +-----------+---------+------+-------+--------+ | 127.0.0.1 | 4.3.5.1 | 2881 | zone1 | ACTIVE | +-----------+---------+------+-------+--------+ obclient -h127.0.0.1 -P2881 -uroot -p'6v7a2FbRQp0eGRNBBQNf' -Doceanbase -A cluster unique id: 79180c5b-c51d-5329-8348-0a014abda853-19643160875-01050304 demo running Trace ID: 8aa42094-1b6e-11f0-86d0-02000aba382b If you want to view detailed obd logs, please run: obd display-trace 8aa42094-1b6e-11f0-86d0-02000aba382b ## 登录系统主库查询information_schema.columns;，正常时间，符合预期 [root@10-186-56-43 ~]# obclient -h127.0.0.1 -P2881 -uroot -p'6v7a2FbRQp0eGRNBBQNf' -Doceanbase -A Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221489935 Server version: OceanBase_CE 4.3.5.1 (r101000042025031818-b6d5706eb3d2c5f501c7fa646ddbf32f3dc87069) (Built Mar 18 2025 18:12:30) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(root@(none))[oceanbase]> select count(*) from information_schema.columns; +----------+ | count(*) | +----------+ | 21239 | +----------+ 1 row in set (0.897 sec) obclient(root@(none))[oceanbase]> select count(*) from information_schema.columns; +----------+ | count(*) | +----------+ | 21239 | +----------+ 1 row in set (0.798 sec) obclient(root@(none))[oceanbase]> select count(*) from information_schema.columns; +----------+ | count(*) | +----------+ | 21239 | +----------+ 1 row in set (0.639 sec) obclient(root@(none))[oceanbase]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (0.535 sec) obclient(root@(none))[oceanbase]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (0.487 sec) ## 创建资源单元、资源池、租户 obclient(root@(none))[oceanbase]> CREATE RESOURCE UNIT mini MAX_CPU 2, MEMORY_SIZE '2G', LOG_DISK_SIZE '5G'; Query OK, 0 rows affected (0.032 sec) obclient(root@(none))[oceanbase]> create resource pool mini_pool unit=mini ,unit_num=1; Query OK, 0 rows affected (0.038 sec) obclient(root@(none))[oceanbase]> create tenant zhenxing -> charset='utf8mb4', -> replica_num=1, -> zone_list=('zone1'), -> primary_zone='zone1', -> resource_pool_list=('mini_pool') -> set ob_tcp_invited_nodes='%'; Query OK, 0 rows affected (22.481 sec) obclient(root@(none))[oceanbase]> exit Bye ## 登录普通租户查询information_schema.columns;，耗时8秒，性能较差 [root@10-186-56-43 ~]# mysql -h127.0.0.1 -uroot@zhenxing -P2881 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221502465 Server version: 5.7.25 OceanBase_CE 4.3.5.1 (r101000042025031818-b6d5706eb3d2c5f501c7fa646ddbf32f3dc87069) (Built Mar 18 2025 18:12:30) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> select count(*) from information_schema.columns; +----------+ | count(*) | +----------+ | 13161 | +----------+ 1 row in set (8.25 sec) ## 多次执行，时间均较长 MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (11.69 sec) MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (8.29 sec) ## 手工采集统计信息，因为是内存表，不支持 MySQL [(none)]> analyze table information_schema.columns; ERROR 1210 (HY000): Invalid argument ## 对基表做analyze，超时，调整查询超时参数 MySQL [(none)]> analyze table oceanbase.__all_virtual_information_columns; ERROR 4012 (HY000): Timeout MySQL [(none)]> set global ob_query_timeout=10000000*1000; Query OK, 0 rows affected (0.09 sec) MySQL [(none)]> set session ob_query_timeout=10000000*1000; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> analyze table oceanbase.__all_virtual_information_columns; Query OK, 0 rows affected (24.31 sec) ## 多次执行，时间均较长 MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (8.02 sec) MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (6.93 sec) ## 手工触发合并 MySQL [(none)]> alter system MAJOR FREEZE; Query OK, 0 rows affected (0.15 sec) MySQL [(none)]> SELECT * FROM DBA_OB_MAJOR_COMPACTION\G ERROR 1046 (3D000): No database selected MySQL [(none)]> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION\G *************************** 1. row *************************** FROZEN_SCN: 1744882680450520000 FROZEN_TIME: 2025-04-17 17:38:00.450520 GLOBAL_BROADCAST_SCN: 1744882680450520000 LAST_SCN: 1 LAST_FINISH_TIME: 1970-01-01 08:00:00.000000 START_TIME: 2025-04-17 17:38:00.514684 STATUS: COMPACTING IS_ERROR: NO IS_SUSPENDED: NO INFO: 1 row in set (0.08 sec) MySQL [(none)]> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION\G *************************** 1. row *************************** FROZEN_SCN: 1744882680450520000 FROZEN_TIME: 2025-04-17 17:38:00.450520 GLOBAL_BROADCAST_SCN: 1744882680450520000 LAST_SCN: 1 LAST_FINISH_TIME: 1970-01-01 08:00:00.000000 START_TIME: 2025-04-17 17:38:00.514684 STATUS: COMPACTING IS_ERROR: NO IS_SUSPENDED: NO INFO: 1 row in set (0.01 sec) MySQL [(none)]> select * from oceanbase.__all_virtual_compaction_diagnose_info; Empty set (0.07 sec) MySQL [(none)]> select * from oceanbase.__all_virtual_compaction_diagnose_info; Empty set (0.00 sec) ## 确认合并结束 STATUS: IDLE MySQL [(none)]> SELECT * FROM oceanbase.DBA_OB_MAJOR_COMPACTION\G *************************** 1. row *************************** FROZEN_SCN: 1744882680450520000 FROZEN_TIME: 2025-04-17 17:38:00.450520 GLOBAL_BROADCAST_SCN: 1744882680450520000 LAST_SCN: 1744882680450520000 LAST_FINISH_TIME: 2025-04-17 17:43:43.336863 START_TIME: 2025-04-17 17:38:00.514684 STATUS: IDLE IS_ERROR: NO IS_SUSPENDED: NO INFO: 1 row in set (0.00 sec) ## 多次执行，时间均较长 MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (9.61 sec) MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (9.14 sec) ## 跟踪命令trece MySQL [(none)]> exit Bye [root@10-186-56-43 ~]# mysql -h127.0.0.1 -uroot@zhenxing -P2881 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221597421 Server version: 5.7.25 OceanBase_CE 4.3.5.1 (r101000042025031818-b6d5706eb3d2c5f501c7fa646ddbf32f3dc87069) (Built Mar 18 2025 18:12:30) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> MySQL [(none)]> MySQL [(none)]> ## 开启trace记录 MySQL [(none)]> SET ob_enable_show_trace=on; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> select * from information_schema.columns limit 1\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: oceanbase TABLE_NAME: __all_core_table COLUMN_NAME: gmt_create ORDINAL_POSITION: 1 COLUMN_DEFAULT: CURRENT_TIMESTAMP IS_NULLABLE: YES DATA_TYPE: timestamp CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: 6 CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL COLUMN_TYPE: timestamp(6) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,reference COLUMN_COMMENT: GENERATION_EXPRESSION: SRS_ID: NULL 1 row in set (10.48 sec) ## 这里耗2分多钟，输出3万多行trace信息 MySQL [(none)]> show trace; | │ │ ├── close_das_task | 2025-04-17 17:48:45.460605 | 0.008 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.460642 | 0.016 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.462308 | 0.455 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.462333 | 0.111 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.462337 | 0.050 ms | | │ │ ├── open | 2025-04-17 17:48:45.462543 | 0.020 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.462621 | 0.074 ms | | │ ├── close | 2025-04-17 17:48:45.462798 | 0.059 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.462799 | 0.008 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.462840 | 0.002 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.464304 | 0.393 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.464327 | 0.088 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.464330 | 0.038 ms | | │ │ ├── open | 2025-04-17 17:48:45.464498 | 0.023 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.464584 | 0.042 ms | | │ ├── close | 2025-04-17 17:48:45.464720 | 0.086 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.464721 | 0.006 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.464783 | 0.004 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.466232 | 0.359 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.466255 | 0.095 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.466258 | 0.046 ms | | │ │ ├── open | 2025-04-17 17:48:45.466433 | 0.021 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.466497 | 0.044 ms | | │ ├── close | 2025-04-17 17:48:45.466622 | 0.051 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.466624 | 0.006 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.466658 | 0.003 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.468161 | 0.517 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.468185 | 0.085 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.468188 | 0.040 ms | | │ │ ├── open | 2025-04-17 17:48:45.468292 | 0.019 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.468336 | 0.119 ms | | │ ├── close | 2025-04-17 17:48:45.468776 | 0.090 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.468778 | 0.023 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.468839 | 0.003 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.470297 | 0.413 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.470322 | 0.083 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.470325 | 0.037 ms | | │ │ ├── open | 2025-04-17 17:48:45.470428 | 0.060 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.470516 | 0.071 ms | | │ ├── close | 2025-04-17 17:48:45.470774 | 0.057 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.470776 | 0.016 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.470817 | 0.003 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.472168 | 0.736 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.472195 | 0.097 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.472200 | 0.044 ms | | │ │ ├── open | 2025-04-17 17:48:45.472407 | 0.026 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.472477 | 0.119 ms | | │ ├── close | 2025-04-17 17:48:45.473232 | 0.076 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.473233 | 0.036 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.473292 | 0.005 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.474690 | 0.602 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.474715 | 0.127 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.474718 | 0.061 ms | | │ │ ├── open | 2025-04-17 17:48:45.474946 | 0.021 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.475012 | 0.120 ms | | │ ├── close | 2025-04-17 17:48:45.475863 | 0.217 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.475866 | 0.076 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.476024 | 0.011 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.478188 | 0.590 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.478242 | 0.145 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.478249 | 0.073 ms | | │ │ ├── open | 2025-04-17 17:48:45.478507 | 0.037 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.478599 | 0.097 ms | | │ ├── close | 2025-04-17 17:48:45.478820 | 0.066 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.478823 | 0.008 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.478859 | 0.003 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.480203 | 0.367 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.480224 | 0.103 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.480226 | 0.035 ms | | │ │ ├── open | 2025-04-17 17:48:45.480400 | 0.028 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.480484 | 0.040 ms | | │ ├── close | 2025-04-17 17:48:45.480602 | 0.059 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.480604 | 0.007 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.480629 | 0.002 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.482043 | 0.313 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.482066 | 0.078 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.482068 | 0.032 ms | | │ │ ├── open | 2025-04-17 17:48:45.482221 | 0.018 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.482278 | 0.032 ms | | │ ├── close | 2025-04-17 17:48:45.482386 | 0.040 ms | | │ │ ├── close_das_task | 2025-04-17 17:48:45.482387 | 0.006 ms | | │ │ └── end_transaction | 2025-04-17 17:48:45.482412 | 0.002 ms | | │ ├── inner_execute_read | 2025-04-17 17:48:45.483811 | 0.323 ms | | │ │ ├── sql_compile | 2025-04-17 17:48:45.483833 | 0.091 ms | | │ │ │ └── pc_get_plan | 2025-04-17 17:48:45.483836 | 0.033 ms | | │ │ ├── open | 2025-04-17 17:48:45.483989 | 0.017 ms | | │ │ └── do_local_das_task | 2025-04-17 17:48:45.484049 | 0.032 ms | | │ └── close | 2025-04-17 17:48:45.484169 | 0.081 ms | | │ ├── close_das_task | 2025-04-17 17:48:45.484172 | 0.008 ms | | │ └── end_transaction | 2025-04-17 17:48:45.484221 | 0.002 ms | | └── close | 2025-04-17 17:48:45.485515 | 0.475 ms | | ├── close_das_task | 2025-04-17 17:48:45.485518 | 0.385 ms | | └── end_transaction | 2025-04-17 17:48:45.485957 | 0.007 ms | +-------------------------------------------------+----------------------------+--------------+ 39019 rows in set (2 min 2.42 sec) ## 获取trace日志 MySQL [(none)]> select last_trace_id(); +-----------------------------------+ | last_trace_id() | +-----------------------------------+ | YB427F000001-000632F610B324E9-0-0 | +-----------------------------------+ 1 row in set (0.00 sec) ## 过滤trace日志 [root@10-186-56-43 log]# grep YB427F000001-000632F610B324E9-0-0 observer.log* >YB427F000001-000632F610B324E9-0-0.log MySQL [oceanbase]> select /*+query_timeout(3600000000) */ usec_to_time(request_time) as request_time, DATE_ADD( usec_to_time(request_time), INTERVAL elapsed_time / 1000000 second ) as execute_end_time, concat( 'select DBMS_XPLAN.DISPLAY_CURSOR(', char(39), plan_id, char(39), ',', char(39), 'ADVANCED', char(39), ',', char(39), svr_ip, char(39), ',', char(39), svr_port, char(39), ',', char(39), tenant_id, char(39), ')' ) sql_explain, svr_ip, query_sql, PARAMS_VALUE, plan_id, sql_id, ret_code, elapsed_time / 1000000 as "收到请求到结束总时间(S elapsed_time)", execute_time / 1000000 as "SQL执行时间(S execute_time)", get_plan_time / 1000000 as "get_plan_time(S)", round(request_memory_used / 1024 / 1024, 2) "request_memory_used(MB)", request_memory_used, TX_ID, plan_hash, trace_id, sid, tenant_id, tenant_name, DB_ID, DB_NAME, retry_cnt, RETURN_ROWS, AFFECTED_ROWS, PARTITION_CNT, rpc_count, case when PLAN_TYPE = 0 then '无执行计划' when PLAN_TYPE = 1 then '本地执行计划' when PLAN_TYPE = 2 then '远程执行计划' when PLAN_TYPE = 3 then '分布式执行计划' else NULL end as PLAN_TYPE, CASE WHEN REQUEST_TYPE = 0 THEN '非法请求' WHEN REQUEST_TYPE = 1 THEN '内部请求' WHEN REQUEST_TYPE = 2 THEN '本地请求' WHEN REQUEST_TYPE = 3 THEN '远程请求' WHEN REQUEST_TYPE = 4 THEN '分布式请求' WHEN REQUEST_TYPE = 5 THEN 'SQL prepare 请求' WHEN REQUEST_TYPE = 6 THEN 'SQL execute stmt 请求' WHEN REQUEST_TYPE = 7 THEN 'SQL 得到 Fetch 请求' WHEN REQUEST_TYPE = 8 THEN 'SQL 得到 send_piece 请求' WHEN REQUEST_TYPE = 9 THEN 'SQL 得到 get_piece 请求' WHEN REQUEST_TYPE = 10 THEN 'SQL 得到 send_long_data 请求' WHEN REQUEST_TYPE = 11 THEN '当前 SQL 是 PL 调用的内部 SQL' END AS REQUEST_TYPE, TABLE_SCAN, IS_HIT_PLAN, DISK_READS, CASE WHEN IS_INNER_SQL = 1 THEN '内部 SQL' WHEN IS_INNER_SQL = 0 THEN '非内部 SQL' ELSE NULL END AS IS_INNER_SQL, CASE WHEN IS_EXECUTOR_RPC = 1 THEN 'RPC调用' WHEN IS_EXECUTOR_RPC = 0 THEN '非RPC 调用' ELSE NULL END AS IS_EXECUTOR_RPC, EVENT, IS_BATCHED_MULTI_STMT -> from GV$OB_SQL_AUDIT t where 1 = 1 and query_sql like 'select * from information_schema.columns%' ORDER BY REQUEST_TIME DESC\G *************************** 1. row *************************** request_time: 2025-04-17 18:00:30.285151 execute_end_time: 2025-04-17 18:00:39.094651 sql_explain: select DBMS_XPLAN.DISPLAY_CURSOR('1435','ADVANCED','127.0.0.1','2882','1002') svr_ip: 127.0.0.1 query_sql: select * from information_schema.columns limit 1 PARAMS_VALUE: plan_id: 1435 sql_id: 64EC441CE236DFF9C1E7DA1558D1B411 ret_code: 0 收到请求到结束总时间(S elapsed_time): 8.8095 SQL执行时间(S execute_time): 8.7826 get_plan_time(S): 0.0267 request_memory_used(MB): 48956.01 request_memory_used: 51334098312 TX_ID: 0 plan_hash: 3435247239800877365 trace_id: YB427F000001-000632F610B331C6-0-0 sid: 3221597421 tenant_id: 1002 tenant_name: zhenxing DB_ID: 201001 DB_NAME: oceanbase retry_cnt: 0 RETURN_ROWS: 1 AFFECTED_ROWS: 0 PARTITION_CNT: 1 rpc_count: 0 PLAN_TYPE: 本地执行计划 REQUEST_TYPE: 本地请求 TABLE_SCAN: 1 IS_HIT_PLAN: 0 DISK_READS: 0 IS_INNER_SQL: 非内部 SQL IS_EXECUTOR_RPC: 非RPC 调用 EVENT: IS_BATCHED_MULTI_STMT: 0 1 row in set (0.02 sec) MySQL [oceanbase]> select * from gv$ob_plan_cache_plan_stat where plan_id=1435\G *************************** 1. row *************************** TENANT_ID: 1002 SVR_IP: 127.0.0.1 SVR_PORT: 2882 PLAN_ID: 1435 SQL_ID: 64EC441CE236DFF9C1E7DA1558D1B411 TYPE: 1 IS_BIND_SENSITIVE: 0 IS_BIND_AWARE: 0 DB_ID: 201001 STATEMENT: select * from information_schema.columns limit ? QUERY_SQL: select * from information_schema.columns limit 1 SPECIAL_PARAMS: PARAM_INFOS: {1,0,0,0,5} SYS_VARS: 45,45,281018368,+08:00,2,4,1,0,0,3,1,0,1,10485760,1,0,DD-MON-RR,DD-MON-RR HH.MI.SSXFF AM,DD-MON-RR HH.MI.SSXFF AM TZR,BINARY,BINARY,AL32UTF8,AL16UTF16,BYTE,FALSE,1,100,64,200,0,13,NULL,1,1,1,1,1,0,0,0,1000,BLOOM_FILTER,RANGE,IN,1,17180067073,17180067073,1,0,0,45,0,0, CONFIGS: 4,1,1,0,1,1,0,0,30,17180067073,1,0,0,1,3,0,10,1001,101,0, PLAN_HASH: 3435247239800877365 FIRST_LOAD_TIME: 2025-04-17 18:00:30.311941 SCHEMA_VERSION: 1744883111463464 LAST_ACTIVE_TIME: 2025-04-17 18:00:39.093623 AVG_EXE_USEC: 8809523 SLOWEST_EXE_TIME: 2025-04-17 18:00:39.093623 SLOWEST_EXE_USEC: 8809523 SLOW_COUNT: 1 HIT_COUNT: 0 PLAN_SIZE: 89352 EXECUTIONS: 1 DISK_READS: 0 DIRECT_WRITES: 0 BUFFER_GETS: 0 APPLICATION_WAIT_TIME: 0 CONCURRENCY_WAIT_TIME: 0 USER_IO_WAIT_TIME: 0 ROWS_PROCESSED: 1 ELAPSED_TIME: 8809523 CPU_TIME: 8809426 LARGE_QUERYS: 0 DELAYED_LARGE_QUERYS: 0 DELAYED_PX_QUERYS: 0 OUTLINE_VERSION: 0 OUTLINE_ID: -1 OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$5C40C8D3" "oceanbase"."__all_virtual_information_columns"@"SEL$2") MERGE(@"SEL$2" < "SEL$1") OPTIMIZER_FEATURES_ENABLE('4.3.5.1') END_OUTLINE_DATA*/ ACS_SEL_INFO: TABLE_SCAN: 1 EVOLUTION: 0 EVO_EXECUTIONS: 0 EVO_CPU_TIME: 0 TIMEOUT_COUNT: 0 PS_STMT_ID: -1 SESSID: 0 TEMP_TABLES: IS_USE_JIT: 0 OBJECT_TYPE: SQL_PLAN HINTS_INFO: /*+ */ HINTS_ALL_WORKED: 1 PL_SCHEMA_ID: 0 IS_BATCHED_MULTI_STMT: 0 RULE_NAME: PLAN_STATUS: INACTIVE ADAPTIVE_FEEDBACK_TIMES: NULL 1 row in set (0.00 sec)