作者：
韓鋒
國產數據庫發展時間尚短、缺乏場景打磨，存在諸多不足也可理解。但比較欣喜的是，整個國產數據庫廠商都在努力追趕。
隨著數據庫國產化深入，越來越多的用戶開始使用國產數據庫；但在使用之后，大家難免會吐槽各種國產數據庫的種種不足。作為一種基礎軟件，數據庫軟件自身就很復雜。國產數據庫雖然經過二三十年的發展， 但相較于國外大型商業數據庫仍然存在不小差距。但與此同時我們也應該看到，國產數據庫正在奮起直追，不斷完善自身的產品功能。在數月前，筆者曾發表過一篇文章（參考），對比部分國內數據庫產品與Oracle在SQL管理方面的差距。文章閱讀量頗高，也受到多家廠商的關注。近期 OceanBase 在發布新版本后，也邀請筆者針對SQL管理部分做個小的測試。測試之余，也為我們國產數據庫的快速發展感到欣慰。也許，現在的產品仍然有很多的不完美，但相信未來是美好的。本文就針對 OceanBase 發布的新版本中SQL管理相關的部分功能進行測試及點評。受個人精力所限，未對完整功能做詳細測試，有興趣的伙伴可參考官方文檔。
1. OceanBase SQL 管理能力概覽
在正式展開之前，我們先回顧下之前對比的情況。之前是從SQL解析、執行計劃、SQL優化、執行過程及其他能力五個維度對比部分國產數據庫的能力。
此次，根據官方給予的指導，從下面這些維度總結下 OceanBase 的能力并與之前做對比。下面也將針對部分能力加以測試。
2. OceanBase SQL 管理能力：執行計劃
下面的測試環境，是采用 OceanBase V4.2.5 的 MySQL 兼容模式。
（1）固定執行計劃：Hint
Hint 是一種 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 |
================================================================================================（2）固定執行計劃：(Format) Outline
通過對某條 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
*/此外，在最新版本中還增加了 Format Outline 特性，提供了一種更為寬松的匹配規則。當用戶創建 Format Outline 時，在 Outline 原有流程之前，系統會先做一次忽略大小寫、空格等非語法定義符號的操作，歸一化為標準格式，這使得歸一化后得到同樣 Format SQL Text 或 Format SQL ID 的用戶請求都可以命中同一個 Format Outline。
（3）固定執行計劃：SPM
SQL 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 property（5）清除執行計劃：FLUSH PLAN CACHE
當執行計劃出現異常時，需要非常精準地清理某一個語句的執行計劃緩存。在 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 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+3. OceanBase SQL 管理能力：過程及優化
（1）ASH
Oracle 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 slot's 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(?)（2）SQL Stat
OceanBase 也提供了類似 Oracle AWR 中的基于快照的信息收集能力。其中，視圖 DBA_WR_SQLSTAT 就存儲用戶執行過的 SQL 的基本性能統計數據。其中，含 _DELTA 的列表示從上次采集 WR 快照到當前時間為止統計值的增量。
mysql> select snap_id,plan_type,executions_total,source_ip,source_port-> 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 |
+---------+-----------+------------------+--------------+-------------+（3）SQL Audit
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: 2（4）SQL Trace
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 |
+-------------------------------------------+----------------------------+------------+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 |
===============================================（2）統計信息
完整、準確的統計信息，是優化器工作的前提。作為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 |
+------------+--------------+-------------+--------+---------------+----------------------------+寫在最后
國產數據庫發展時間尚短、缺乏場景打磨，存在諸多不足也可理解。但比較欣喜的是，整個國產數據庫廠商都在努力追趕。近年已經多次受邀參加廠商的產品、用戶、生態大會，大家都希望更多聽聽來自外部的聲音。如此次也是OB官方聯系筆者聽取建議，并在新版本發布后第一時間聯系筆者進行評測。
從此次的評測來看，OceanBase在SQL 管理方面取得了長足的進步，達到比較完善的程度，可滿足日常SQL管理工作。在使用體驗上大量仿照了Oracle的做法，上手門檻很低。當然仍存在一些不足，如文檔偏重技術說明、缺少實操過程；不同兼容模式下產品能力尚未對齊等；但相信未來會越來越完善。
責任編輯：姜華
來源：
韓鋒頻道
