自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

國產數(shù)據庫也能打（寫在OB新版本測試后）

原創(chuàng) 精選
作者： 韓鋒
數(shù)據庫
國產數(shù)據庫發(fā)展時間尚短、缺乏場景打磨，存在諸多不足也可理解。但比較欣喜的是，整個國產數(shù)據庫廠商都在努力追趕。

隨著數(shù)據庫國產化深入，越來越多的用戶開始使用國產數(shù)據庫；但在使用之后，大家難免會吐槽各種國產數(shù)據庫的種種不足。作為一種基礎軟件，數(shù)據庫軟件自身就很復雜。國產數(shù)據庫雖然經過二三十年的發(fā)展， 但相較于國外大型商業(yè)數(shù)據庫仍然存在不小差距。但與此同時我們也應該看到，國產數(shù)據庫正在奮起直追，不斷完善自身的產品功能。在數(shù)月前，筆者曾發(fā)表過一篇文章（參考），對比部分國內數(shù)據庫產品與Oracle在SQL管理方面的差距。文章閱讀量頗高，也受到多家廠商的關注。近期 OceanBase 在發(fā)布新版本后，也邀請筆者針對SQL管理部分做個小的測試。測試之余，也為我們國產數(shù)據庫的快速發(fā)展感到欣慰。也許，現(xiàn)在的產品仍然有很多的不完美，但相信未來是美好的。本文就針對 OceanBase 發(fā)布的新版本中SQL管理相關的部分功能進行測試及點評。受個人精力所限，未對完整功能做詳細測試，有興趣的伙伴可參考官方文檔。

1. OceanBase SQL 管理能力概覽

在正式展開之前，我們先回顧下之前對比的情況。之前是從SQL解析、執(zhí)行計劃、SQL優(yōu)化、執(zhí)行過程及其他能力五個維度對比部分國產數(shù)據庫的能力。

此次，根據官方給予的指導，從下面這些維度總結下 OceanBase 的能力并與之前做對比。下面也將針對部分能力加以測試。

2. OceanBase SQL 管理能力：執(zhí)行計劃

下面的測試環(huán)境，是采用 OceanBase V4.2.5 的 MySQL 兼容模式。

（1）固定執(zhí)行計劃：Hint

Hint 是一種 SQL 語句注釋，用于將指令傳遞給 OceanBase 數(shù)據庫優(yōu)化器。通過 Hint 可以使優(yōu)化器生成指定的執(zhí)行計劃。一般情況下，優(yōu)化器會為用戶查詢選擇最佳的執(zhí)行計劃，不需要用戶使用 Hint 指定，但在某些場景下，優(yōu)化器生成的執(zhí)行計劃可能無法滿足用戶的要求，這時就需要用戶使用 Hint 來主動指定并生成特殊的執(zhí)行計劃。Hint 可以說是 DBA 干預執(zhí)行計劃最為常用的手段之一。Hint 的豐富程度直接決定 DBA 能干預執(zhí)行計劃的程度。

OB Hint 仿照 Oracle Hint 的名稱及用法，用起來比較簡單。相較于 MySQL Hint，OB Hint 也豐富了很多。對于 Oracle DBA 來說是可以快速上手的，針對 MySQL DBA 來說則增加了很多調優(yōu)的手段。

--  測試使用 Hint 干預執(zhí)行計劃。

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）固定執(zhí)行計劃：(Format) Outline

通過對某條 SQL 創(chuàng)建 Outline 可實現(xiàn)計劃綁定。在系統(tǒng)上線前，可以直接在 SQL 語句中添加 Hint，控制優(yōu)化器按 Hint 指定的行為進行計劃生成。但對于已上線的業(yè)務，如果出現(xiàn)優(yōu)化器選擇的計劃不夠優(yōu)化時，則需要在線進行計劃綁定，即無需業(yè)務進行 SQL 更改，而是通過 DDL 操作將一組 Hint 加入到 SQL 中，從而使優(yōu)化器根據指定的一組 Hint，對該 SQL 生成更優(yōu)計劃。該組 Hint 就稱為 Outline。OceanBase Outline 也是仿照 Oracle Outline 的實現(xiàn)，使用體驗也相差不大。特別是在驗證 Outline 是否使用上，也可通過DBMS_XPLAN加以查看。

-- 原始執(zhí)行計劃
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 固定新的執(zhí)行計劃
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 特性，提供了一種更為寬松的匹配規(guī)則。當用戶創(chuàng)建 Format Outline 時，在 Outline 原有流程之前，系統(tǒng)會先做一次忽略大小寫、空格等非語法定義符號的操作，歸一化為標準格式，這使得歸一化后得到同樣 Format SQL Text 或 Format SQL ID 的用戶請求都可以命中同一個 Format Outline。

（3）固定執(zhí)行計劃：SPM

SQL Plan Management（SPM）是一種防止計劃回退的機制，能夠確保新生成的計劃在經過驗證后才被使用，以保證計劃性能不斷優(yōu)化和更新。OceanBase 數(shù)據庫支持在線 SPM 演進機制，即當發(fā)現(xiàn)新生成的計劃不在基線中時，就會立即自動啟動一個演進任務進行計劃演進，這樣就可以在用戶無需手動干預的情況下自動完成計劃演進。SPM 基于 SQL Plan Baseline 實現(xiàn)，SQL Plan Baseline 是執(zhí)行計劃的一個基線，用于持久化存儲已經驗證過的執(zhí)行計劃信息（Outline Data 等信息），每個執(zhí)行計劃可對應一個 Plan Baseline，通過該 Plan Baseline 可復現(xiàn)一個執(zhí)行計劃。

（4）查看執(zhí)行計劃：DBMS_XPLAN

查看執(zhí)行計劃是所有優(yōu)化的第一步，因此完善的執(zhí)行計劃查看手段非常必要。OceanBase 也提供了多種查看的方式，如典型的 Explain 命令；但這里重點介紹下通過 DBMS_XPlan 的方式來查看。相信 Oracle DBA 對這一能力尤為熟悉，其支持多種信息來源、豐富展示維度。在 OceanBase 中也做了類似的實現(xiàn)，并做了部分增強。下表是其支持的主要能力。

在展示內容的豐富程度上，可參考下面的測試。對比傳統(tǒng)的 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）清除執(zhí)行計劃：FLUSH PLAN CACHE

當執(zhí)行計劃出現(xiàn)異常時，需要非常精準地清理某一個語句的執(zhí)行計劃緩存。在 OceanBase 中實現(xiàn)了語句級的清理能力。

-- 查看執(zhí)行計劃緩存
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 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+

-- 清理執(zhí)行計劃緩存
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)

-- 重新生成執(zhí)行計劃
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 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+
-- 查看執(zhí)行計劃緩存
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 |
+----------------------------------+--------------------+------------------------------------+----------------------------+------------+

-- 清理執(zhí)行計劃緩存
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)

-- 重新生成執(zhí)行計劃
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 管理能力：過程及優(yōu)化

（1）ASH

Oracle DBA 對ASH/AWR，一定不陌生。它們是我們查看語句執(zhí)行過程的好幫手。在 OceanBase 中也帶來了同樣的能力。ASH（Active Session History）是一種活動會話歷史記錄的診斷工具，用于記錄數(shù)據庫中所有活動會話的信息。ASH 報告（OceanBase Active Session History Report ）是一個能夠提供定位瞬時發(fā)生異常的分析報告，與性能報告相比，能提供更加細粒度的診斷信息。一般的性能報告所覆蓋的是小時級別的快照信息，診斷問題的粒度不能深入到 Session 級別。導致一些瞬時抖動信息很難從性能報告上得到詳細的執(zhí)行細節(jié)，因此，我們可以通過 ASH 報告這樣一個會話級別的細粒度診斷信息來解決這種問題。

-- 記錄一個包含語句執(zhí)行的時間段
mysql> select now() from dual;
+---------------------+
| now()               |
+---------------------+
| 2024-11-25 21:50:44 |
+---------------------+

-- 對于執(zhí)行時長短的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 就存儲用戶執(zhí)行過的 SQL 的基本性能統(tǒng)計數(shù)據。其中，含 _DELTA 的列表示從上次采集 WR 快照到當前時間為止統(tǒng)計值的增量。

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 執(zhí)行情況，其中 GV$OB_SQL_AUDIT 就是最常用的 SQL 監(jiān)控視圖，能夠記錄每一次 SQL 請求的來源、執(zhí)行狀態(tài)、資源消耗及等待事件，除此之外還記錄了 SQL 文本、執(zhí)行計劃等關鍵信息。該視圖是診斷 SQL 問題的利器。GV$OB_SQL_AUDIT 視圖的數(shù)據存放在一個可配置的內存空間中，每個租戶在每個節(jié)點上都有一塊獨立的緩存，當內存使用或記錄數(shù)達到淘汰上限時會觸發(fā)自動淘汰，最久的數(shù)據優(yōu)先淘汰。有經驗的 DBA 在排查 SQL 問題時，往往第一件事就是關閉 SQL Audit 功能以保存現(xiàn)場，避免抖動現(xiàn)場的監(jiān)控數(shù)據被淘汰。

-- 開啟會話級別的全鏈路追蹤(記錄所有語句的相關耗時等信息，采樣頻率為 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 能夠交互式的提供上一次執(zhí)行的 SQL 請求執(zhí)行過程中調用鏈路情況，以及鏈路中各階段耗時情況，以便進行性能分析或調優(yōu)，快速找到性能瓶頸點。

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

如何查看當前執(zhí)行計劃的異?；驖撛诳赡艿酶鼉?yōu)執(zhí)行計劃，常見的手段如統(tǒng)計信息修改、對象可見性等。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）統(tǒng)計信息

完整、準確的統(tǒng)計信息，是優(yōu)化器工作的前提。作為DBA日常優(yōu)化的工作，統(tǒng)計信息是首要需要關注的。OceanBase 提供了多種統(tǒng)計信息的收集及查看手段。在測試中，發(fā)現(xiàn)一點小瑕疵，通過 Analyze 和 DBMS_STATS包的方式收集統(tǒng)計信息，能力上還不統(tǒng)一。期待未來統(tǒng)一起來。

-- 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 |
+------------+--------------+-------------+--------+---------------+----------------------------+

寫在最后

國產數(shù)據庫發(fā)展時間尚短、缺乏場景打磨，存在諸多不足也可理解。但比較欣喜的是，整個國產數(shù)據庫廠商都在努力追趕。近年已經多次受邀參加廠商的產品、用戶、生態(tài)大會，大家都希望更多聽聽來自外部的聲音。如此次也是OB官方聯(lián)系筆者聽取建議，并在新版本發(fā)布后第一時間聯(lián)系筆者進行評測。

從此次的評測來看，OceanBase在SQL 管理方面取得了長足的進步，達到比較完善的程度，可滿足日常SQL管理工作。在使用體驗上大量仿照了Oracle的做法，上手門檻很低。當然仍存在一些不足，如文檔偏重技術說明、缺少實操過程；不同兼容模式下產品能力尚未對齊等；但相信未來會越來越完善。

責任編輯：姜華 來源： 韓鋒頻道
國產數(shù)據庫SQLOracle
相關推薦

2023-11-08 07:31:51

國產數(shù)據庫YashanDB

2024-04-26 09:37:43

國產數(shù)據庫開發(fā)者

2023-08-03 08:42:24

2021-08-02 09:01:29

PythonMySQL 數(shù)據庫

2021-08-04 09:00:53

Python數(shù)據庫Python基礎

2011-08-01 15:35:51

GlassFishJava 7

2013-03-28 15:59:34

為知筆記

2009-07-30 18:22:14

OracleTimesTenIn-Memory D

2020-08-24 19:23:29

Pythonpipenv開發(fā)工具

2015-03-13 15:30:26

編程數(shù)據庫創(chuàng)建表單

2010-02-23 17:44:48

Python 3.0

2009-06-17 09:24:34

學習strutsStruts新版本

2015-02-05 16:59:36

平安WiFiiOS

2011-11-04 14:07:40

存儲

2011-03-11 09:14:18

國產數(shù)據庫

2011-03-11 09:26:13

2021-08-10 15:32:12

Redis緩存數(shù)據庫

2021-03-03 11:15:05

Linux 系統(tǒng) 數(shù)據

2009-12-31 11:09:36

Ubuntu wine
點贊
收藏

51CTO技術棧公眾號