目前,ob已经提供开发版供测试https://oceanbase.alipay.com/download,但只有mysql模式。

ob客户端可以从官方下载ODC、jdbc也可以从此下载,也就是IDE,oracle版本必须使用它连接。mysql版本可以使用navicat连接,为了体验一致的话,推荐用dbeaver。不过至今为止,navicat、sqlyog、mysql workbench都不支持自定义函数的只能提示,只支持内置函数。

oracle模式

1、支持自定义存储过程、函数

-- ob oracle版本函数
CREATE OR REPLACE FUNCTION demo_fun ( Name VARCHAR2 , vAge INTEGER , Sex VARCHAR2 DEFAULT '男' ) RETURN VARCHAR2 
IS V_var VARCHAR2 ( 32 ) ;
BEGIN V_var := name || ':' || TO_CHAR ( vAge ) || '岁,' || sex ;
RETURN v_var ;
END ;
-- 不要加"/"结束符
select demo_fun('1',1) from dual;


create or replace procedure procedure_name
 (
  v_empno in varchar2,
  v_sal out number
 ) is
 vsal number(7,2);
 begin
    select 1 into vsal from dual;
    v_sal:=vsal;
 end;

declare
 vsal number(7,2);
begin
    procedure_name('7935',vsal);
    dbms_output.put_line(vsal);
end;

2、支持自定义过程中执行动态SQL,如下:

create or replace procedure procedure_name
 (
  v_empno in varchar2,
  v_sal out number
 ) is
 vsal number(7,2);
 begin
    execute immediate 'create table t1(id number primary key,nam varchar2(100))';
 end;

declare
 vsal number(7,2);
begin
    procedure_name('7935',vsal);
    dbms_output.put_line(vsal);
end;
-- 执行后会创建表t1

 

-- ob mysql版本

-- 否则创建存储过程、函数会报错。mysql/mariadb必须启用,OB不支持该参数
set global log_bin_trust_function_creators  = on

delimiter $$
CREATE FUNCTION StuNameById()
    RETURNS VARCHAR(45)
    RETURN 'stuname';
$$

delimiter ;
select StuNameById()
-- ob mysql 创建存储过程
DELIMITER //
CREATE PROCEDURE ShowStuScore()
BEGIN
SELECT '1' as name1,'2' as id;
END //
delimiter ;

-- 但是ob mysql模式没有返回值,mysql/mariadb有返回值
call ShowStuScore()

3、支持自定义存储过程中执行ddl

-- ob mysql 创建存储过程
DELIMITER //
CREATE PROCEDURE exec_ddl(S_NAME_IN CHAR(30))
BEGIN
SET @SS = CONCAT('create table ',S_NAME_IN,'(id int primary key,nam varchar(100))');
  PREPARE STMT FROM @SS;
  EXECUTE STMT;
END //
delimiter ;

call exec_ddl('ob_t_2')

 

4、无论是oracle模式还是mysql模式,内部总归是基于mysql的,看系统参数就知道,show parameter报错,show variables like '%%'基本上是mysql参数,有一些ob、oracle的扩展参数,其中ob的很多抄了oracle隐含参数的特性,比如max_parallel_degree,完整的参数如下:

-- mysql模式
autocommit ON auto_increment_cache_size 1000000 auto_increment_increment 1 auto_increment_offset 1 binlog_row_image FULL block_encryption_mode aes-128-ecb character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8mb4 character_set_filesystem binary character_set_results utf8mb4 character_set_server utf8mb4 character_set_system utf8mb4 collation_connection utf8mb4_general_ci collation_database utf8mb4_general_ci collation_server utf8mb4_general_ci connect_timeout 10 datadir /usr/local/mysql/data/ div_precision_increment 4 error_on_overlap_time OFF explicit_defaults_for_timestamp ON foreign_key_checks OFF group_concat_max_len 1024 identity 0 init_connect interactive_timeout 28800 is_result_accurate ON last_insert_id 0 license lower_case_table_names 1 max_allowed_packet 4194304 max_sp_recursion_depth 0 max_user_connections 0 net_buffer_length 16384 net_read_timeout 30 net_write_timeout 60 ob_bnl_join_cache_size 10485760 ob_check_sys_variable ON ob_compatibility_mode MYSQL ob_create_table_strict_mode ON ob_default_replica_num 1 ob_early_lock_release OFF ob_enable_aggregation_pushdown ON ob_enable_blk_nestedloop_join OFF ob_enable_hash_group_by ON ob_enable_index_direct_select OFF ob_enable_jit OFF ob_enable_plan_cache ON ob_enable_sql_audit ON ob_enable_trace_log OFF ob_enable_transformation ON ob_enable_transmission_checksum ON ob_enable_truncate_flashback ON ob_interm_result_mem_limit 2147483648 ob_last_schema_version 1600655186941424 ob_log_level disabled ob_max_parallel_degree 32 ob_org_cluster_id 0 ob_plan_cache_evict_high_percentage 90 ob_plan_cache_evict_low_percentage 50 ob_plan_cache_percentage 5 ob_query_timeout 10000000 ob_read_consistency STRONG ob_reserved_meta_memory_percentage 10 ob_route_policy READONLY_ZONE_FIRST ob_sql_audit_percentage 3 ob_sql_work_area_percentage 5 ob_stmt_parallel_degree 1 ob_tcp_invited_nodes % ob_temp_tablespace_size_percentage 0 ob_timestamp_service GTS ob_trx_idle_timeout 120000000 ob_trx_timeout 100000000 optimizer_capture_sql_plan_baselines OFF optimizer_use_sql_plan_baselines OFF parallel_max_servers 10 parallel_servers_target 10 plugin_dir ./plugin_dir/ query_cache_size 1048576 query_cache_type OFF read_only OFF recyclebin ON sql_auto_is_null OFF sql_mode STRICT_ALL_TABLES sql_quote_show_create ON sql_select_limit 9223372036854775807 sql_throttle_cpu -1 sql_throttle_current_priority 100 sql_throttle_io -1 sql_throttle_logical_reads -1 sql_throttle_network -1 sql_throttle_priority -1 sql_throttle_rt -1 sql_warnings OFF system_time_zone +08:00 timestamp 0 time_zone +8:00 tracefile_identifier tx_isolation READ-COMMITTED tx_read_only OFF undo_retention 0 version 2.2.30 version_comment OceanBase 2.2.30 (r20200807074943-8db774b3cae3e41d7b06a2c9c82a2bee97eba0c0) (Built Aug 7 2020 08:06:34) wait_timeout 28800

下面是oracle模式:

oceanbase测试预研(持续更新)_oceanbase

oracle模式搞了一堆v$动态性能视图,v$parameter又没有,information_schema.session_variables也没有,就一不伦不类,关键两个模式无论数据字典、参数感觉都完全没有隔离,就一个试验品。

导出直接报错,真的是垃圾到家了。

oceanbase测试预研(持续更新)_oceanbase_02

5、执行计划和优化器提示

支持oracle模式的优化器提示,即使是在mysql模式。执行计划显示为oracle的执行计划,mysql虽然也是相同语法,但是执行计划出不来。如下:

oceanbase测试预研(持续更新)_oceanbase_03

 这个odc是难用的不行了,ob也一样,不是KPI要求,我是不会用的。

oceanbase测试预研(持续更新)_oceanbase_04 

看下obclient客户端工具:

[root@linux-dev ~]# rpm -ivh obclient-1.1.1-20190802211743.el7.alios7.x86_64.rpm 
准备中...                          ################################# [100%]
正在升级/安装...
   1:obclient-1.1.1-20190802211743.el7################################# [100%]
[root@linux-dev ~]# obclient -h10.19.1.23 -P2883 -usys@oracle_tenant0_1#obtest -p123456 -c -A sys
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 1652791
Server version: 5.6.25 OceanBase 2.2.30 (r20200807074943-8db774b3cae3e41d7b06a2c9c82a2bee97eba0c0) (Built Aug  7 2020 08:06:34)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> explain select * from ob_t_1 t,ob_t_2 t1 where t.id=t1.id
    -> ;
ERROR-00942: table or view 'SYS.OB_T_1' does not exist
obclient> explain select * from t,t1 where t.id=t1.id
    -> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST  |
--------------------------------------
|0 |HASH JOIN  |    |99000    |592998|
|1 | TABLE SCAN|T   |100000   |68478 |
|2 | TABLE SCAN|T1  |100000   |61860 |
======================================

Outputs & filters: 
-------------------------------------
  0 - output([T.ID], [T.NAM], [T1.ID], [T1.NAM]), filter(nil), 
      equal_conds([T.ID = T1.ID]), other_conds(nil)
  1 - output([T.ID], [T.NAM]), filter(nil), 
      access([T.ID], [T.NAM]), partitions(p0)
  2 - output([T1.ID], [T1.NAM]), filter(nil), 
      access([T1.ID], [T1.NAM]), partitions(p0)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

obclient> exit
Bye
[root@linux-dev ~]# ob
obclient  objcopy   objdump   
[root@linux-dev ~]# obclient -h10.19.1.23  -P2883 -uroot@mysql_teant0_2#obtest -p123456 -c -A oceanbase
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor.  Commands end with ; or \g.
Your OceanBase connection id is 1652810
Server version: 5.6.25/*这基本上就是基于mysql 5.6.25*/ OceanBase 2.2.30 (r20200807074943-8db774b3cae3e41d7b06a2c9c82a2bee97eba0c0) (Built Aug  7 2020 08:06:34)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient> explain select * from ob_t_1 t,ob_t_2 t1 where t.id=t1.id;
ERROR 1146 (42S02): Table 'oceanbase.ob_t_1' doesn't exist
obclient> use test;
Database changed
obclient> explain select * from ob_t_1 t,ob_t_2 t1 where t.id=t1.id;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =================================================================
|ID|OPERATOR                          |NAME    |EST. ROWS|COST  |
-----------------------------------------------------------------
|0 |EXCHANGE IN DISTR                 |        |100001   |402319|
|1 | EXCHANGE OUT DISTR               |:EX10001|100001   |274528|
|2 |  MERGE JOIN                      |        |100001   |274528|
|3 |   EXCHANGE IN MERGE SORT DISTR   |        |100000   |125756|
|4 |    EXCHANGE OUT DISTR (BROADCAST)|:EX10000|100000   |61860 |
|5 |     TABLE SCAN                   |t       |100000   |61860 |
|6 |   TABLE SCAN                     |t1      |100000   |61860 |
=================================================================

Outputs & filters: 
-------------------------------------
  0 - output([t.id], [t.nam], [t1.id], [t1.nam]), filter(nil)
  1 - output([t.id], [t.nam], [t1.id], [t1.nam]), filter(nil), dop=1
  2 - output([t.id], [t.nam], [t1.id], [t1.nam]), filter(nil), 
      equal_conds([t.id = t1.id]), other_conds(nil)
  3 - output([t.id], [t.nam]), filter(nil), sort_keys([t.id, ASC])
  4 - output([t.id], [t.nam]), filter(nil), dop=1
  5 - output([t.id], [t.nam]), filter(nil), 
      access([t.id], [t.nam]), partitions(p0)
  6 - output([t1.id], [t1.nam]), filter(nil), 
      access([t1.id], [t1.nam]), partitions(p0)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

也就oblient能用,问题是oracle、mysql执行计划还不通。也没官方文档说明执行计划的含义。

总体来说,非要用的话,推荐使用mysql模式。感觉oracle模式就是打补丁的。 

 

sql引起流程可以参考https://zhuanlan.zhihu.com/p/76950991,符合实际

ob高可用方案:https://oceanbase.alipay.com/community/articles/200123,其实引用到zk、kafka、redis集群、哨兵上是一个道理

ob的数据块组织方式:https://oceanbase.alipay.com/community/articles/200123

花若盛开,蝶自飞来,你若精彩,幸福开怀!2020年12月11日-18日