国产数据库也能打（写在OB新版本测试后）
随着数据库国产化深入，越来越多的用户开始使用国产数据库；但在使用之后，大家难免会吐槽各种国产数据库的种种不足。作为一种基础软件，数据库软件自身就很复杂。国产数据库虽然经过二三十年的发展， 但相较于国外大型商业数据库仍然存在不小差距。但与此同时我们也应该看到，国产数据库正在奋起直追，不断完善自身的产品功能。在数月前，笔者曾发表过一篇文章（参考），对比部分国内数据库产品与Oracle在SQL管理方面的差距。文章阅读量颇高，也受到多家厂商的关注。近期 OceanBase 在发布新版本后，也邀请笔者针对SQL管理部分做个小的测试。测试之余，也为我们国产数据库的快速发展感到欣慰。也许，现在的产品仍然有很多的不完美，但相信未来是美好的。本文就针对 OceanBase 发布的新版本中SQL管理相关的部分功能进行测试及点评。受个人精力所限，未对完整功能做详细测试，有兴趣的伙伴可参考官方文档。
1. OceanBase SQL 管理能力概览
在正式展开之前，我们先回顾下之前对比的情况。之前是从SQL解析、执行计划、SQL优化、执行过程及其他能力五个维度对比部分国产数据库的能力。
此次，根据官方给予的指导，从下面这些维度总结下 OceanBase 的能力并与之前做对比。下面也将针对部分能力加以测试。
2. OceanBase SQL 管理能力：执行计划
下面的测试环境，是采用 OceanBase V4.2.5 的 MySQL 兼容模式。
（1）固定执行计划：HintHint 是一种 SQL 语句注释，用于将指令传递给 OceanBase 数据库优化器。通过 Hint 可以使优化器生成指定的执行计划。一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用 Hint 指定，但在某些场景下，优化器生成的执行计划可能无法满足用户的要求，这时就需要用户使用 Hint 来主动指定并生成特殊的执行计划。Hint 可以说是 DBA 干预执行计划最为常用的手段之一。Hint 的丰富程度直接决定 DBA 能干预执行计划的程度。
OB Hint 仿照 Oracle Hint 的名称及用法，用起来比较简单。相较于 MySQL Hint，OB Hint 也丰富了很多。对于 Oracle DBA 来说是可以快速上手的，针对 MySQL DBA 来说则增加了很多调优的手段。
-- 测试使用 Hint 干预执行计划。
复制
mysql> select * from emp where emp_name=emp1234;
+--------+---------+----------+------------+---------+
|emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
|1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> select dbms_xplan.display_cursor() from dual;
==============================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
--------------------------------------------------------------------------------------------------------------
|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |1 |0 |0 |190 |
==============================================================================================================
mysql> select /*+ full(emp) */ * from emp where emp_name=emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> select dbms_xplan.display_cursor() from dual;
================================================================================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
------------------------------------------------------------------------------------------------
|0 |TABLE FULL SCAN|emp |2 |573 |1 |5258 |0 |5032 |
================================================================================================1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.
通过对某条 SQL 创建 Outline 可实现计划绑定。在系统上线前，可以直接在 SQL 语句中添加 Hint，控制优化器按 Hint 指定的行为进行计划生成。但对于已上线的业务，如果出现优化器选择的计划不够优化时，则需要在线进行计划绑定，即无需业务进行 SQL 更改，而是通过 DDL 操作将一组 Hint 加入到 SQL 中，从而使优化器根据指定的一组 Hint，对该 SQL 生成更优计划。该组 Hint 就称为 Outline。OceanBase Outline 也是仿照 Oracle Outline 的实现，使用体验也相差不大。特别是在验证 Outline 是否使用上，也可通过DBMS_XPLAN加以查看。
复制
-- 原始执行计划
mysql> select * from emp where emp_name=emp1234;
mysql> select dbms_xplan.display_cursor(0,all) from dual;
==============================================================================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
--------------------------------------------------------------------------------------------------------------
|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |1 |1169 |0 |118 |
==============================================================================================================
-- 使用 SQL Outline 固定新的执行计划
mysql> select sql_id ,statement from oceanbase.V$OB_PLAN_CACHE_PLAN_STAT
-> where statement like %emp_name%;
+----------------------------------+----------------------------------------+
| sql_id | statement |
+----------------------------------+----------------------------------------+
| 3A384EC9FBBF76DC073C209C7594BD62 | select * from emp where emp_name=? |
+----------------------------------+----------------------------------------+
mysql> CREATE OUTLINE ol_emp_name ON 3A384EC9FBBF76DC073C209C7594BD62
-> USING HINT /*+ full(emp) */ ;
mysql> select * from emp where emp_name=emp1234;
-- 查看是否使用 SQL Outline
mysql> select dbms_xplan.display_cursor(0,all) from dual;
================================================================================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|REAL.ROWS|REAL.TIME(us)|IO TIME(us)|CPU TIME(us)|
------------------------------------------------------------------------------------------------
|0 |TABLE FULL SCAN|emp |2 |573 |1 |5291 |0 |5153 |
================================================================================================
Used Hint:
-------------------------------------
/*+
FULL("emp")
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "default_database"."emp"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE(4.2.5.0)
END_OUTLINE_DATA
*/1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.
此外，在最新版本中还增加了 Format Outline 特性，提供了一种更为宽松的匹配规则。当用户创建 Format Outline 时，在 Outline 原有流程之前，系统会先做一次忽略大小写、空格等非语法定义符号的操作，归一化为标准格式，这使得归一化后得到同样 Format SQL Text 或 Format SQL ID 的用户请求都可以命中同一个 Format Outline。
（3）固定执行计划：SPMSQL Plan Management（SPM）是一种防止计划回退的机制，能够确保新生成的计划在经过验证后才被使用，以保证计划性能不断优化和更新。OceanBase 数据库支持在线 SPM 演进机制，即当发现新生成的计划不在基线中时，就会立即自动启动一个演进任务进行计划演进，这样就可以在用户无需手动干预的情况下自动完成计划演进。SPM 基于 SQL Plan Baseline 实现，SQL Plan Baseline 是执行计划的一个基线，用于持久化存储已经验证过的执行计划信息（Outline Data 等信息），每个执行计划可对应一个 Plan Baseline，通过该 Plan Baseline 可复现一个执行计划。
（4）查看执行计划：DBMS_XPLAN查看执行计划是所有优化的第一步，因此完善的执行计划查看手段非常必要。OceanBase 也提供了多种查看的方式，如典型的 Explain 命令；但这里重点介绍下通过 DBMS_XPlan 的方式来查看。相信 Oracle DBA 对这一能力尤为熟悉，其支持多种信息来源、丰富展示维度。在 OceanBase 中也做了类似的实现，并做了部分增强。下表是其支持的主要能力。
在展示内容的丰富程度上，可参考下面的测试。对比传统的 Explain 方式，无疑增强了很多。
复制
mysql> EXPLAIN SET STATEMENT_ID=testsql1 select * from emp where emp_id=100;
mysql> SELECT DBMS_XPLAN.DISPLAY(all,testsql1,plan_table);
=========================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------
|0 |TABLE GET|emp |1 |3 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([emp.emp_id], [emp.dept_id], [emp.emp_name], [emp.birthday], [emp.salary]), filter(nil), rowset=16
access([emp.emp_id], [emp.dept_id], [emp.emp_name], [emp.birthday], [emp.salary]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([emp.emp_id]), range[100 ; 100],
range_cond([emp.emp_id = 100])
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "default_database"."emp"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE(4.2.5.0)
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
emp:
table_rows:10000
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_emp_name, idx_emp_birthday, idx_emp_salary, emp]
pruned_index_name:[idx_emp_name, idx_emp_birthday, idx_emp_salary]
stats info:[versinotallow=2024-11-25 14:53:29.120791, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS]
Plan Type:
LOCAL
Parameters:
:0 => 100
:1 => testsql1
Note:
Degree of Parallelisim is 1 because of table property
mysql> EXPLAIN SET STATEMENT_ID=testsql1 select * from emp where emp_id=100;
mysql> SELECT DBMS_XPLAN.DISPLAY(all,testsql1,plan_table);
=========================================
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------
|0 |TABLE GET|emp |1 |3 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([emp.emp_id], [emp.dept_id], [emp.emp_name], [emp.birthday], [emp.salary]), filter(nil), rowset=16
access([emp.emp_id], [emp.dept_id], [emp.emp_name], [emp.birthday], [emp.salary]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([emp.emp_id]), range[100 ; 100],
range_cond([emp.emp_id = 100])
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "default_database"."emp"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE(4.2.5.0)
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
emp:
table_rows:10000
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_emp_name, idx_emp_birthday, idx_emp_salary, emp]
pruned_index_name:[idx_emp_name, idx_emp_birthday, idx_emp_salary]
stats info:[versinotallow=2024-11-25 14:53:29.120791, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[OPTIMIZER STATISTICS]
Plan Type:
LOCAL
Parameters:
:0 => 100
:1 => testsql1
Note:
Degree of Parallelisim is 1 because of table property1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.
当执行计划出现异常时，需要非常精准地清理某一个语句的执行计划缓存。在 OceanBase 中实现了语句级的清理能力。
复制
-- 查看执行计划缓存
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| sql_id | plan_hash | statement | last_active_time | executions |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| 3A384EC9FBBF76DC073C209C7594BD62 | 269335962286044871 | select * from emp where emp_name=? | 2024-11-26 14:28:38.374015 | 5 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
-- 清理执行计划缓存
mysql> ALTER SYSTEM FLUSH PLAN CACHE sql_id=3A384EC9FBBF76DC073C209C7594BD62
-> databases=default_database GLOBAL;
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
Empty set (0.02 sec)
-- 重新生成执行计划
mysql> select * from emp where emp_name=emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| sql_id | plan_hash | statement | last_active_time | executions |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| 3A384EC9FBBF76DC073C209C7594BD62 | 269335962286044871 | select * from emp where emp_name=? | 2024-11-26 14:32:24.447891 | 1 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
-- 查看执行计划缓存
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| sql_id | plan_hash | statement | last_active_time | executions |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| 3A384EC9FBBF76DC073C209C7594BD62 | 269335962286044871 | select * from emp where emp_name=? | 2024-11-26 14:28:38.374015 | 5 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
-- 清理执行计划缓存
mysql> ALTER SYSTEM FLUSH PLAN CACHE sql_id=3A384EC9FBBF76DC073C209C7594BD62
-> databases=default_database GLOBAL;
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
Empty set (0.02 sec)
-- 重新生成执行计划
mysql> select * from emp where emp_name=emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> select sql_id ,plan_hash,statement,last_active_time,executions
-> from oceanbase.gv$ob_plan_cache_plan_stat
-> where statement like %emp_name%;
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| sql_id | plan_hash | statement | last_active_time | executions |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
| 3A384EC9FBBF76DC073C209C7594BD62 | 269335962286044871 | select * from emp where emp_name=? | 2024-11-26 14:32:24.447891 | 1 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.
3. OceanBase SQL 管理能力：过程及优化
（1）ASHOracle DBA 对ASH/AWR，一定不陌生。它们是我们查看语句执行过程的好帮手。在 OceanBase 中也带来了同样的能力。ASH（Active Session History）是一种活动会话历史记录的诊断工具，用于记录数据库中所有活动会话的信息。ASH 报告（OceanBase Active Session History Report ）是一个能够提供定位瞬时发生异常的分析报告，与性能报告相比，能提供更加细粒度的诊断信息。一般的性能报告所覆盖的是小时级别的快照信息，诊断问题的粒度不能深入到 Session 级别。导致一些瞬时抖动信息很难从性能报告上得到详细的执行细节，因此，我们可以通过 ASH 报告这样一个会话级别的细粒度诊断信息来解决这种问题。
复制
-- 记录一个包含语句执行的时间段
mysql> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2024-11-25 21:50:44 |
+---------------------+
-- 对于执行时长短的SQL可能会记录不到，这里构造一个长SQL
mysql> select * from emp where emp_name=emp1234 and salary>sleep(3);
mysql> select now() from dual;
+---------------------+
| now() |
+---------------------+
| 2024-11-25 21:51:42 |
+---------------------+
-- 查看 SQL ID
mysql> select sql_id ,statement from oceanbase.V$OB_PLAN_CACHE_PLAN_STAT
-> where statement like %salary%;
+----------------------------------+--------------------------------------------------------+
| sql_id | statement |
+----------------------------------+--------------------------------------------------------+
| 3F5322F4E8E89841727D0313B5FBB7F9 | select * from emp where emp_name=? and salary>sleep(?) |
+----------------------------------+--------------------------------------------------------+
-- 生成 ASH Report(指定时间段及SQL ID)
mysql> call dbms_workload_repository.ash_report(
-> str_to_date(2024-11-25 21:50:00, %Y-%m-%d %H:%i%s),
-> str_to_date(2024-11-25 21:52:00, %Y-%m-%d %H:%i%s),
-> sql_id=>3F5322F4E8E89841727D0313B5FBB7F9);
ASH Report
Cluster Name: ob69oehg4nx4hs
Observer Version: OceanBase 4.2.5.0 (100010012024111110-19dd26fbb0ea8dc8a31ba208a90d58f9b67a4929)
Operation System Info: Linux(3.10.0-1160.119.1.el7.x86_64)_x86_64
User Input Begin Time: 2024-11-25 21:50:00
User Input End Time: 2024-11-25 21:52:00
Analysis Begin Time: 2024-11-25 21:51:05
Analysis End Time: 2024-11-25 21:51:15
Elapsed Time: 10
Num of Sample: 8
Average Active Sessions: 0.80
Top Active Tenants:
+---------------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
| Tenant Name|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|Equivalent Client Load|
+---------------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
| t69qw2ook3c2o| FOREGROUND| 8| 8| 0| 0.80| 100.00%| 1.00|
+---------------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
Top Node Load:
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
| IP| Port|Session Type| Total Count| Wait Event Count| On CPU Count| Avg Active Sessions| % Activity|Equivalent Client Load|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
| 10.104.56.87| 2882| FOREGROUND| 8| 8| 0| 0.80| 100.00%| 0.00|
+----------------+-------+------------+------------------+-----------------------+-------------------+--------------------+-----------+--------------------+
Top Groups:
- this section lists top resource consumer groups
- Group Name: resource consumer group name
- Group Samples: num of sampled session activity records in the current resource group
- % Activity: activity percentage for given resource group
- Avg Active Sessions: average active sessions during ash report analysis time period
+-----------------------------------+-------------+-----------+--------------------+----------------------------------------------------------------+-----------+--------------------------------+-----------+--------------------------------+-----------+
| Group Name|Group Samples| % Activity| Avg Active Sessions| Program| % Program| Module| % Module| Action| % Action|
+-----------------------------------+-------------+-----------+--------------------+----------------------------------------------------------------+-----------+--------------------------------+-----------+--------------------------------+-----------+
| cgroup//tenant_1002//OBCG_DEFAULT| 8| 100.00%| 0.80| T1002_SQL_CMD| 100.00%| UNDEFINED| 100.00%| UNDEFINED| 100.00%|
+-----------------------------------+-------------+-----------+--------------------+----------------------------------------------------------------+-----------+--------------------------------+-----------+--------------------------------+-----------+
Top Foreground DB Time:
- this section lists top foreground db time categorized by event
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+-------------+--------------------+-------------+--------------------+-----------+
| Event Name| Wait Class| Event Count| Avg Active Sessions| % Activity|
+-------------+--------------------+-------------+--------------------+-----------+
| sleep wait| IDLE| 8| 0.80| 100.00%|
+-------------+--------------------+-------------+--------------------+-----------+
Top Sessions:
- this section lists top Active Sessions with the largest wait event and SQL_ID
- Session ID: user session id
- % Activity: represents the load on the database caused by this session
- Avg Active Sessions: average active sessions during ash report analysis time period
- Event Name: comprise wait event and on cpu event
- % Event: represents the activity load of the event on the database
- % SQL ID: represents the activity load of the event on the database
- Sql Executions: represents the execution count of the SQL_ID
+--------------------+----------------+-----------+--------------------+----------------------------------------------------------------+--------------------+-----------+----------------------------------------+--------------------+-----------+-------------+
| Session ID| Program| % Activity| Avg Active Sessions| Event Name| Wait Class| % Event| SQL ID| Plan Hash| % SQL ID|Sql Executions|
+--------------------+----------------+-----------+--------------------+----------------------------------------------------------------+--------------------+-----------+----------------------------------------+--------------------+-----------+-------------+
| 3221643314| T1002_SQL_CMD| 100.00%| 0.80| sleep wait| IDLE| 100.00%| 3F5322F4E8E89841727D0313B5FBB7F9|14249117491818627721| 100.00%| 3|
+--------------------+----------------+-----------+--------------------+----------------------------------------------------------------+--------------------+-----------+----------------------------------------+--------------------+-----------+-------------+
Activity Over Time:
- this section lists time slot information during the analysis period.
- Slot Begin Time: current slots begin time. current slot end with next slot begin time.
- Event Name: comprise wait event and on cpu event
- Event Count: num of sampled session activity records
- % Activity: activity percentage for given event
- Avg Active Sessions: average active sessions during ash report analysis time period
+----------------------------+-------------+--------------------+-------------+-----------+--------------------+
| Slot Begin Time| Event Name| Wait Class| Event Count| % Activity| Avg Active Sessions|
+----------------------------+-------------+--------------------+-------------+-----------+--------------------+
| 2024-11-25 21:50:00.000000| sleep wait| IDLE| 8| 100.00%| 0.03|
+----------------------------+-------------+--------------------+-------------+-----------+--------------------+
Top Execution Phase:
- this section lists top phases of execution, such as SQL, PL/SQL, STORAGE, etc.
+------------+----------------------+----------+-----------+----------------------------------------+-----------+
|Session Type| Phase of Execution|Active Samples| % Activity| SQL_ID| % SQL_ID|
+------------+----------------------+----------+-----------+----------------------------------------+-----------+
| FOREGROUND| IN_SQL_EXECUTION| 8| 100.00%| 3F5322F4E8E89841727D0313B5FBB7F9| 100.00%|
| FOREGROUND| IN_PLSQL_EXECUTION| 8| 100.00%| 3F5322F4E8E89841727D0313B5FBB7F9| 100.00%|
| FOREGROUND| IN_STORAGE_READ| 8| 100.00%| 3F5322F4E8E89841727D0313B5FBB7F9| 100.00%|
+------------+----------------------+----------+-----------+----------------------------------------+-----------+
Top SQL with Top Events:
- This Section lists the SQL statements that accounted for the highest percentages event.
- Plan Hash: Numeric representation of the current SQL plan
- Active Samples: num of samples for top current SQL
- % Activity: activity percentage for given SQL ID
- Sampled Executions: represents the number of times the current SQL execution has been sampled
- Top Event: top event name for current SQL plan
- % Event: activity percentage for current SQL plan
- Top Operator/ExecPhase: top operator name or execution phase for current event
- % Operator/ExecPhase: activity percentage for given operator
+----------------------------------------+--------------------+--------------+--------------+--------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+
| SQL ID| Plan Hash|Active Samples| % Activity|Sampled Executions| Top Event| % Event| Top Operator/ExecPhase|% Operator/ExecPhase| SQL Text|
+----------------------------------------+--------------------+--------------+--------------+--------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+
| 3F5322F4E8E89841727D0313B5FBB7F9|14249117491818627721| 8| 100.00%| 3| sleep wait| 100.00%| TABLE RANGE SCAN| 100.00%| select * from emp where emp_name=? and salary>sleep(?)|
+----------------------------------------+--------------------+--------------+--------------+--------------+----------------------------------------------------------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+
Top SQL with Top Operator:
- This Section lists the SQL statements that accounted for the highest percentages of sampled session activity with sql operator
- Plan Hash: Numeric representation of the current SQL plan
- Active Samples: num of samples for top current SQL
- % Activity: activity percentage for given SQL ID
- Sampled Executions: represents the number of times the current SQL execution has been sampled
- Top Operator: top operator name for current SQL plan
- % Operator: activity percentage for given operator
- Top Event: top event name for current operator
- % Event: activity percentage for given event
+----------------------------------------+--------------------+--------------+--------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
| SQL ID| Plan Hash|Active Samples| % Activity|Sampled Executions| Top Operator| % Operator| Top Event| % Event| SQL Text|
+----------------------------------------+--------------------+--------------+--------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
| 3F5322F4E8E89841727D0313B5FBB7F9|14249117491818627721| 8| 100.00%| 3| TABLE RANGE SCAN| 100.00%| sleep wait| 100.00%| select * from emp where emp_name=? and salary>sleep(?)|
+----------------------------------------+--------------------+--------------+--------------+--------------+--------------------------------------------------------------------------------------------------------------------------------+--------------+----------------------------------------------------------------+--------------+----------------------------------------------------------------+
Complete List of SQL Text:
SQL ID: 3F5322F4E8E89841727D0313B5FBB7F9
SQL Text: select * from emp where emp_name=? and salary>sleep(?)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.133.134.135.136.137.138.139.140.141.142.143.144.145.146.147.148.149.150.151.152.153.154.155.156.157.
OceanBase 也提供了类似 Oracle AWR 中的基于快照的信息收集能力。其中，视图 DBA_WR_SQLSTAT 就存储用户执行过的 SQL 的基本性能统计数据。其中，含 _DELTA 的列表示从上次采集 WR 快照到当前时间为止统计值的增量。
复制
mysql> select snap_id,plan_type,executions_total,source_ip,source_port1.
复制
-> from oceanbase.DBA_WR_SQLSTAT
-> where sql_id=3A384EC9FBBF76DC073C209C7594BD62;
+---------+-----------+------------------+--------------+-------------+
| snap_id | plan_type | executions_total | source_ip | source_port |
+---------+-----------+------------------+--------------+-------------+
| 22 | 1 | 1 | 10.104.56.87 | 2882 |
+---------+-----------+------------------+--------------+-------------+1.2.3.4.5.6.7.
SQL Audit 可以提供详实的 SQL 执行情况，其中 GV$OB_SQL_AUDIT 就是最常用的 SQL 监控视图，能够记录每一次 SQL 请求的来源、执行状态、资源消耗及等待事件，除此之外还记录了 SQL 文本、执行计划等关键信息。该视图是诊断 SQL 问题的利器。GV$OB_SQL_AUDIT 视图的数据存放在一个可配置的内存空间中，每个租户在每个节点上都有一块独立的缓存，当内存使用或记录数达到淘汰上限时会触发自动淘汰，最久的数据优先淘汰。有经验的 DBA 在排查 SQL 问题时，往往第一件事就是关闭 SQL Audit 功能以保存现场，避免抖动现场的监控数据被淘汰。
复制
-- 开启会话级别的全链路追踪(记录所有语句的相关耗时等信息，采样频率为 50%
obclient> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,ALL);
mysql> SELECT request_id,usec_to_time(request_time),user_client_ip,user_name,db_name,
-> sql_id,query_sql ,ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,plan_id,is_hit_plan,disk_reads
-> FROM oceanbase.gv$OB_SQL_AUDIT
-> WHERE query_sql LIKE %emp_name% limit 1\G;
*************************** 1. row ***************************
request_id: 1669216
usec_to_time(request_time): 2024-11-26 14:54:24.977470
user_client_ip: 82.157.26.195
user_name: testuser
db_name: default_database
sql_id: 5650F89701DF0872BA2FCBD059EDBFC9
query_sql: select * from emp where emp_name =emp1234
ELAPSED_TIME: 13803
QUEUE_TIME: 18
EXECUTE_TIME: 271
plan_id: 4878
is_hit_plan: 0
DISK_READS: 21.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.
SQL Trace 能够交互式的提供上一次执行的 SQL 请求执行过程中调用链路情况，以及链路中各阶段耗时情况，以便进行性能分析或调优，快速找到性能瓶颈点。
复制
mysql> SET ob_enable_show_trace = 1;
mysql> select * from emp where emp_name =emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> show trace;
+-------------------------------------------+----------------------------+------------+
| Operation | StartTime | ElapseTime |
+-------------------------------------------+----------------------------+------------+
| com_query_process | 2024-11-26 14:57:02.179570 | 0.344 ms |
| └── mpquery_single_stmt | 2024-11-26 14:57:02.179576 | 0.327 ms |
| ├── sql_compile | 2024-11-26 14:57:02.179584 | 0.074 ms |
| │ └── pc_get_plan | 2024-11-26 14:57:02.179590 | 0.014 ms |
| └── sql_execute | 2024-11-26 14:57:02.179673 | 0.208 ms |
| ├── open | 2024-11-26 14:57:02.179674 | 0.020 ms |
| ├── response_result | 2024-11-26 14:57:02.179706 | 0.122 ms |
| │ └── do_local_das_task | 2024-11-26 14:57:02.179730 | 0.031 ms |
| └── close | 2024-11-26 14:57:02.179838 | 0.031 ms |
| ├── close_das_task | 2024-11-26 14:57:02.179839 | 0.009 ms |
| └── end_transaction | 2024-11-26 14:57:02.179857 | 0.002 ms |
+-------------------------------------------+----------------------------+------------+ CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,ALL);\n\nmysql> SELECT request_id,usec_to_time(request_time),user_client_ip,user_name,db_name,\n -> sql_id,query_sql ,ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,plan_id,is_hit_plan,disk_reads\n -> FROM oceanbase.gv$OB_SQL_AUDIT \n -> WHERE query_sql LIKE %emp_name% limit 1\\G;\n*************************** 1. row ***************************\n request_id: 1669216\nusec_to_time(request_time): 2024-11-26 14:54:24.977470\n user_client_ip: 82.157.26.195\n user_name: testuser\n db_name: default_database\n sql_id: 5650F89701DF0872BA2FCBD059EDBFC9\n query_sql: select * from emp where emp_name =emp1234\n ELAPSED_TIME: 13803\n QUEUE_TIME: 18\n EXECUTE_TIME: 271\n plan_id: 4878\n is_hit_plan: 0\n DISK_READS: 2"},"attribs":{"0":"*0|k+ql*0+t"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"92c4505b-2aa1-49f0-b48c-ff993801eb0d","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":225,"type":"text","selection":{"start":0,"end":986},"recordId":"FipSdXqTCo7yqIxHH6pcoi81nMg"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="" style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important;">
mysql> SET ob_enable_show_trace = 1;
mysql> select * from emp where emp_name =emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> show trace;
+-------------------------------------------+----------------------------+------------+
| Operation | StartTime | ElapseTime |
+-------------------------------------------+----------------------------+------------+
| com_query_process | 2024-11-26 14:57:02.179570 | 0.344 ms |
| └── mpquery_single_stmt | 2024-11-26 14:57:02.179576 | 0.327 ms |
| ├── sql_compile | 2024-11-26 14:57:02.179584 | 0.074 ms |
| │ └── pc_get_plan | 2024-11-26 14:57:02.179590 | 0.014 ms |
| └── sql_execute | 2024-11-26 14:57:02.179673 | 0.208 ms |
| ├── open | 2024-11-26 14:57:02.179674 | 0.020 ms |
| ├── response_result | 2024-11-26 14:57:02.179706 | 0.122 ms |
| │ └── do_local_das_task | 2024-11-26 14:57:02.179730 | 0.031 ms |
| └── close | 2024-11-26 14:57:02.179838 | 0.031 ms |
| ├── close_das_task | 2024-11-26 14:57:02.179839 | 0.009 ms |
| └── end_transaction | 2024-11-26 14:57:02.179857 | 0.002 ms |
+-------------------------------------------+----------------------------+------------+
mysql> SET ob_enable_show_trace = 1;
mysql> select * from emp where emp_name =emp1234;
+--------+---------+----------+------------+---------+
| emp_id | dept_id | emp_name | birthday | salary |
+--------+---------+----------+------------+---------+
| 1234 | 74 | emp1234 | 2001-12-30 | 1148.55 |
+--------+---------+----------+------------+---------+
mysql> show trace;
+-------------------------------------------+----------------------------+------------+
| Operation | StartTime | ElapseTime |
+-------------------------------------------+----------------------------+------------+
| com_query_process | 2024-11-26 14:57:02.179570 | 0.344 ms |
| └── mpquery_single_stmt | 2024-11-26 14:57:02.179576 | 0.327 ms |
| ├── sql_compile | 2024-11-26 14:57:02.179584 | 0.074 ms |
| │ └── pc_get_plan | 2024-11-26 14:57:02.179590 | 0.014 ms |
| └── sql_execute | 2024-11-26 14:57:02.179673 | 0.208 ms |
| ├── open | 2024-11-26 14:57:02.179674 | 0.020 ms |
| ├── response_result | 2024-11-26 14:57:02.179706 | 0.122 ms |
| │ └── do_local_das_task | 2024-11-26 14:57:02.179730 | 0.031 ms |
| └── close | 2024-11-26 14:57:02.179838 | 0.031 ms |
| ├── close_das_task | 2024-11-26 14:57:02.179839 | 0.009 ms |
| └── end_transaction | 2024-11-26 14:57:02.179857 | 0.002 ms |
+-------------------------------------------+----------------------------+------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.
4. OceanBase SQL 管理能力：其他
（1）调整对象：Invisible Index如何查看当前执行计划的异常或潜在可能得更优执行计划，常见的手段如统计信息修改、对象可见性等。OceanBase 这方面能力都具备。这里以不可见索引为示例，演示下。
复制
mysql> explain select * from emp where emp_name =emp1234;
=============================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
-------------------------------------------------------------
|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |
=============================================================
-- 修改索引可见性
mysql> alter table emp alter index idx_emp_name invisible;
mysql> explain select * from emp where emp_name =emp1234;
===============================================
ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|
-----------------------------------------------
|0 |TABLE FULL SCAN|emp |2 |573 |
===============================================1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.
完整、准确的统计信息，是优化器工作的前提。作为DBA日常优化的工作，统计信息是首要需要关注的。OceanBase 提供了多种统计信息的收集及查看手段。在测试中，发现一点小瑕疵，通过 Analyze 和 DBMS_STATS包的方式收集统计信息，能力上还不统一。期待未来统一起来。
复制
-- Analyze 方式收集（未收集索引信息）
mysql> analyze table emp;
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-25 21:39:58.833398 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-25 14:53:29.154282 |
+------------+--------------+-------------+--------+---------------+----------------------------+
-- DBMS_STATS 包方式收集
mysql> CALL DBMS_STATS.GATHER_TABLE_STATS (testuser, emp, method_opt=>FOR ALL COLUMNS SIZE AUTO, granularity=>ALL, degree=>4,cascade=>true);
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-26 14:37:24.010639 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-26 14:37:24.061085 |
+------------+--------------+-------------+--------+---------------+----------------------------+ explain select * from emp where emp_name =emp1234;\n============================================================= \n|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|\n-------------------------------------------------------------\n|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |\n============================================================= \n\n-- 修改索引可见性\nmysql> alter table emp alter index idx_emp_name invisible;\n\nmysql> explain select * from emp where emp_name =emp1234;\n=============================================== \nID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| \n----------------------------------------------- \n|0 |TABLE FULL SCAN|emp |2 |573 |\n=============================================== "},"attribs":{"0":"*0|f+ks*0+1i"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"a0126d51-3fd3-4644-a98b-0ceccafceea1","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":232,"type":"text","selection":{"start":0,"end":802},"recordId":"QDfNdrSDEoHoxoxLvTfc5WSbnDe"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="" style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important;"> CALL DBMS_MONITOR.OB_SESSION_TRACE_ENABLE(null,1,0.5,ALL);\n\nmysql> SELECT request_id,usec_to_time(request_time),user_client_ip,user_name,db_name,\n -> sql_id,query_sql ,ELAPSED_TIME,QUEUE_TIME,EXECUTE_TIME,plan_id,is_hit_plan,disk_reads\n -> FROM oceanbase.gv$OB_SQL_AUDIT \n -> WHERE query_sql LIKE %emp_name% limit 1\\G;\n*************************** 1. row ***************************\n request_id: 1669216\nusec_to_time(request_time): 2024-11-26 14:54:24.977470\n user_client_ip: 82.157.26.195\n user_name: testuser\n db_name: default_database\n sql_id: 5650F89701DF0872BA2FCBD059EDBFC9\n query_sql: select * from emp where emp_name =emp1234\n ELAPSED_TIME: 13803\n QUEUE_TIME: 18\n EXECUTE_TIME: 271\n plan_id: 4878\n is_hit_plan: 0\n DISK_READS: 2"},"attribs":{"0":"*0|k+ql*0+t"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"92c4505b-2aa1-49f0-b48c-ff993801eb0d","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":225,"type":"text","selection":{"start":0,"end":986},"recordId":"FipSdXqTCo7yqIxHH6pcoi81nMg"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="" style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important;">
-- Analyze 方式收集（未收集索引信息）
mysql> analyze table emp;
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-25 21:39:58.833398 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-25 14:53:29.154282 |
+------------+--------------+-------------+--------+---------------+----------------------------+
-- DBMS_STATS 包方式收集
mysql> CALL DBMS_STATS.GATHER_TABLE_STATS (testuser, emp, method_opt=>FOR ALL COLUMNS SIZE AUTO, granularity=>ALL, degree=>4,cascade=>true);
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-26 14:37:24.010639 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-26 14:37:24.061085 |
+------------+--------------+-------------+--------+---------------+----------------------------+ explain select * from emp where emp_name =emp1234;\n============================================================= \n|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|\n-------------------------------------------------------------\n|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |\n============================================================= \n\n-- 修改索引可见性\nmysql> alter table emp alter index idx_emp_name invisible;\n\nmysql> explain select * from emp where emp_name =emp1234;\n=============================================== \nID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| \n----------------------------------------------- \n|0 |TABLE FULL SCAN|emp |2 |573 |\n=============================================== "},"attribs":{"0":"*0|f+ks*0+1i"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"a0126d51-3fd3-4644-a98b-0ceccafceea1","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":232,"type":"text","selection":{"start":0,"end":802},"recordId":"QDfNdrSDEoHoxoxLvTfc5WSbnDe"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="" style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important;">
-- Analyze 方式收集（未收集索引信息）
mysql> analyze table emp;
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-25 21:39:58.833398 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-25 14:53:29.154282 |
+------------+--------------+-------------+--------+---------------+----------------------------+
-- DBMS_STATS 包方式收集
mysql> CALL DBMS_STATS.GATHER_TABLE_STATS (testuser, emp, method_opt=>FOR ALL COLUMNS SIZE AUTO, granularity=>ALL, degree=>4,cascade=>true);
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-26 14:37:24.010639 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-26 14:37:24.061085 |
+------------+--------------+-------------+--------+---------------+----------------------------+ explain select * from emp where emp_name =emp1234;\n============================================================= \n|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|\n-------------------------------------------------------------\n|0 |TABLE RANGE SCAN|emp(idx_emp_name)|1 |7 |\n============================================================= \n\n-- 修改索引可见性\nmysql> alter table emp alter index idx_emp_name invisible;\n\nmysql> explain select * from emp where emp_name =emp1234;\n=============================================== \nID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| \n----------------------------------------------- \n|0 |TABLE FULL SCAN|emp |2 |573 |\n=============================================== "},"attribs":{"0":"*0|f+ks*0+1i"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"a0126d51-3fd3-4644-a98b-0ceccafceea1","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":232,"type":"text","selection":{"start":0,"end":802},"recordId":"QDfNdrSDEoHoxoxLvTfc5WSbnDe"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="" style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 0px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important;">
-- Analyze 方式收集（未收集索引信息）
mysql> analyze table emp;
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-25 21:39:58.833398 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-25 14:53:29.154282 |
+------------+--------------+-------------+--------+---------------+----------------------------+
-- DBMS_STATS 包方式收集
mysql> CALL DBMS_STATS.GATHER_TABLE_STATS (testuser, emp, method_opt=>FOR ALL COLUMNS SIZE AUTO, granularity=>ALL, degree=>4,cascade=>true);
mysql> select table_name,object_type, num_rows,blocks,last_analyzed
-> from oceanbase.dba_tab_statistics where table_name=emp;
+------------+-------------+----------+--------+----------------------------+
| table_name | object_type | num_rows | blocks | last_analyzed |
+------------+-------------+----------+--------+----------------------------+
| emp | TABLE | 10000 | NULL | 2024-11-26 14:37:24.010639 |
+------------+-------------+----------+--------+----------------------------+
mysql> select table_name,index_name,object_type,blevel,distinct_keys,last_analyzed
-> from oceanbase. DBA_IND_STATISTICS where index_name=idx_emp_name;
+------------+--------------+-------------+--------+---------------+----------------------------+
| table_name | index_name | object_type | blevel | distinct_keys | last_analyzed |
+------------+--------------+-------------+--------+---------------+----------------------------+
| emp | idx_emp_name | INDEX | NULL | NULL | 2024-11-26 14:37:24.061085 |
+------------+--------------+-------------+--------+---------------+----------------------------+1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.133.134.135.136.137.138.139.140.141.142.143.144.145.146.147.148.
写在最后
国产数据库发展时间尚短、缺乏场景打磨，存在诸多不足也可理解。但比较欣喜的是，整个国产数据库厂商都在努力追赶。近年已经多次受邀参加厂商的产品、用户、生态大会，大家都希望更多听听来自外部的声音。如此次也是OB官方联系笔者听取建议，并在新版本发布后第一时间联系笔者进行评测。
从此次的评测来看，OceanBase在SQL 管理方面取得了长足的进步，达到比较完善的程度，可满足日常SQL管理工作。在使用体验上大量仿照了Oracle的做法，上手门槛很低。当然仍存在一些不足，如文档偏重技术说明、缺少实操过程；不同兼容模式下产品能力尚未对齐等；但相信未来会越来越完善。
THE END
