OBCP V4 数据库开发设计与优化实验笔记：从 OBD 部署到执行计划调优

这篇文章中，庆涛大佬会为大家详细分析一批 OBCP V4 的实验模拟题目，他还贴心地增加了很多批注和实验手册里没有的内容，分享了大量在实验考试题目以外的 OceanBase 数据库开发设计与优化方面的经验与思考。

希望大家都能把 OceanBase 数据库给运维的服服贴贴~ 

OBCP V4 考试有两场。第一场是在考点上机考理论题，第二场是在自己电脑上在线考实验题。OBCP V4 官方给出了 5 个 实验操作手册和实验环境。

实验环境是利用了阿里云 ECS 资源，每个实验独立的一套实验环境，每个实验环境的有效期是 3~4 小时。一旦激活了实验环境，就要尽快用完，否则到期了自动被释放了。

不过做这个五个实验，也不一定要用官方的实验环境。自己有 4C16G 的虚拟机 1-3 台 也可以做大部分实验。本文说的是其中的数据库开发设计与优化实验。是为了验证 OB 分布式特性、执行计划分析和调优、OUTLINE 和 SPM 技术、统计信息等技术。它只需要一个三节点的 OB 集群（2 节点其实也可以），不依赖 OCP。

1. OB 环境准备

1.1 服务器准备

这里我不用 OCP 部署 OB 集群，3 台虚拟机 4C16G 200G 空间足够了。实验环境的 ECS 也差不多。

注意：实验环境的 ECS 有公网和内网两个 IP， 部署 OB 集群用内网 IP。

下面是命令行下做一些 SSH 打通操作。以及清理前面的 OB 环境。

IPS="10.0.0.61
10.0.0.62
10.0.0.65"
for ip in $IPS;do echo $ip; ssh-copy-id admin@$ip; done
for ip in $IPS;do echo $ip; ssh admin@$ip "/bin/rm -rf /data/{1,log1}/* ; /bin/rm -rf /home/admin/{oceanbase,obproxy}/*" ; done
1.1.1 OBD 安装

不用 OCP，那么部署 OB 集群就用社区版工具 OBD。OB 单机版交付也依赖这个工具。下载地址在 OB 官网：

https://www.oceanbase.com/softwarecenter。

[root@server64 soft]# rpm -ivh ob-deploy-4.2.0-1.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:ob-deploy-4.2.0-1.el7            ################################# [100%]
Installation of obd finished successfully
Please source /etc/profile.d/obd.sh to enable it
[root@server64 soft]# sh /etc/profile.d/obd.sh

1.1 .2 OBD 配置文件

OBD 是自动化部署 OB 集群的软件，设计理念就是通过输入部署配置文件，自动化初始化相关服务器、部署目录和相关软件、初始化 OB 集群和相关参数等。下面这个参数文件是从 OBCP 实验环境复制出来的，稍微修改了一些。

## Only need to configure when remote login is required
user:
  username: admin
  password:
#   key_file: your ssh-key file path if need
  port: 22
#   timeout: ssh connection timeout (second), default 30
oceanbase:
  servers:
    - name: server1
      # Please don't use hostname, only IP can be supported
      ip: 10.0.0.61
    - name: server2
      ip: 10.0.0.62
    - name: server3
      ip: 10.0.0.65
  global:
    # Starting from observer version 6.2, the network selection for the observer is based on the 'local_ip' parameter, and the 'devname' parameter is no longer mandatory.
    # If the 'local_ip' parameter is set, the observer will first use this parameter for the configuration, regardless of the 'devname' parameter.
    # If only the 'devname' parameter is set, the observer will use the 'devname' parameter for the configuration.
    # If neither the 'devname' nor the 'local_ip' parameters are set, the 'local_ip' parameter will be automatically assigned the IP address configured above.
    # devname: eth0
    cluster_id: 1
    # please set memory limit to a suitable value which is matching resource.
    memory_limit: 14G # The maximum running memory for an observer
    system_memory: 2G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
    datafile_size: 2G # Size of the data file.
    datafile_next: 2G # the auto extend step. Please enter an capacity, such as 2G
    datafile_maxsize: 20G # the auto extend max size. Please enter an capacity, such as 20G
    log_disk_size: 30G # The size of disk space used by the clog files.
    cpu_count: 16
    production_mode: false
    enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
    max_syslog_file_count: 4 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
    # observer cluster name, consistent with obproxy's cluster_name
    appname: obcluster
    root_password: bbBB22__  #设置集群访问的密码
    # proxyro_password: # proxyro user pasword, consistent with obproxy's observer_sys_password, can be empty
  server1:
    mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started.
    rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
    obshell_port: 2886 # Operation and maintenance port for Oceanbase Database. The default value is 2886. This parameter is valid only when the version of oceanbase-ce is 4.2.2.0 or later.
    #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
    home_path: /home/admin/oceanbase
    # The directory for data storage. The default value is $home_path/store.
    data_dir: /data/1
    # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
    # redo_dir: /redo
    redo_dir: /data/log1
    zone: zone1
  server2:
    mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started.
    rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
    obshell_port: 2886 # Operation and maintenance port for Oceanbase Database. The default value is 2886. This parameter is valid only when the version of oceanbase-ce is 4.2.2.0 or later.
    #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
    home_path: /home/admin/oceanbase
    # The directory for data storage. The default value is $home_path/store.
    # data_dir: /data
    data_dir: /data/1
    # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
    # redo_dir: /redo
    redo_dir: /data/log1
    zone: zone2
  server3:
    mysql_port: 2881 # External port for OceanBase Database. The default value is 2881. DO NOT change this value after the cluster is started.
    rpc_port: 2882 # Internal port for OceanBase Database. The default value is 2882. DO NOT change this value after the cluster is started.
    obshell_port: 2886 # Operation and maintenance port for Oceanbase Database. The default value is 2886. This parameter is valid only when the version of oceanbase-ce is 4.2.2.0 or later.
    #  The working directory for OceanBase Database. OceanBase Database is started under this directory. This is a required field.
    home_path: /home/admin/oceanbase
    # The directory for data storage. The default value is $home_path/store.
    # data_dir: /data
    data_dir: /data/1
    # The directory for clog, ilog, and slog. The default value is the same as the data_dir value.
    # redo_dir: /redo
    redo_dir: /data/log1
    zone: zone3
obproxy:
  # Set dependent components for the component.
  # When the associated configurations are not done, OBD will automatically get the these configurations from the dependent components.
  depends:
    - oceanbase
  servers:
    - 10.0.0.61 #系统提供的机器1的ip地址，示例仅做展示
    - 10.0.0.62 #系统提供的机器1的ip地址，示例仅做展示
    - 10.0.0.65 #系统提供的机器1的ip地址，示例仅做展示
  global:
    listen_port: 2883 # External port. The default value is 2883.
    prometheus_listen_port: 2884 # The Prometheus port. The default value is 2884.
    home_path: /home/admin/obproxy
    # oceanbase root server list
    # format: ip:mysql_port;ip:mysql_port. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
    # rs_list: 192.168.1.2:2881;192.168.1.3:2881;192.168.1.4:2881
    enable_cluster_checkout: false
    # observer cluster name, consistent with oceanbase-ce's appname. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
    # cluster_name: obcluster
    skip_proxy_sys_private_check: true
    enable_strict_kernel_release: false
    # obproxy_sys_password: # obproxy sys user password, can be empty. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
    # observer_sys_password: # proxyro user pasword, consistent with oceanbase-ce's proxyro_password, can be empty. When a depends exists, OBD gets this value from the oceanbase-ce of the depends.
参数文件修改点简要说明：

  • 部署用户改到 admin 用户下，目录也调整了。我反对 OB 部署和运行在 root 用户下，不安全。

  • 相关参数基本不变，文件大小根据磁盘空间大小加大了一些。


1.2 OBD 部署企业版

1.2.1 配置 OBD 本地软件仓库
  • OBD 支持企业版部署的关键就是要把企业版软件放到本地的 YUM 仓库里。
obd mirror disable remote
obd mirror clone oceanbase-4.2.1.3-103030022024011313.el7.x86_64.rpm obproxy-4.3.1.0-602024090512.el7.x86_64.rpm

1.2.2 部署 OB 分布式目录这一步只是初始化相关软件目录。 
obd cluster deploy obcluster -c 3ecs-ob.yaml
[root@server64 soft]# obd cluster deploy obcluster -c 3ecs-ob.yaml
+----------------------------------------------------------------------------------------------+
|                                           Packages                                           |
+------------+-------------+------------------------+------------------------------------------+
| Repository | Version/Tag | Release                | Hash                                     |
+------------+-------------+------------------------+------------------------------------------+
| oceanbase  | 4.2.1.3     | 103030022024011313.el7 | 4c82d4a4f2d824038ece244cccad493b626e6afa |
| obproxy    | 4.2.1.0     | 20231220145047.el7     | 9b4e22543962ec00bdb15f59c0b1a9b2fb689584 |
+------------+-------------+------------------------+------------------------------------------+
Repository integrity check ok
Load param plugin ok
Open ssh connection ok
Initializes observer work home ok
Initializes obproxy work home ok
Parameter check ok
Remote oceanbase-4.2.1.3-103030022024011313.el7-4c82d4a4f2d824038ece244cccad493b626e6afa repository install ok
Remote oceanbase-4.2.1.3-103030022024011313.el7-4c82d4a4f2d824038ece244cccad493b626e6afa repository lib check ok
Remote obproxy-4.2.1.0-20231220145047.el7-9b4e22543962ec00bdb15f59c0b1a9b2fb689584 repository install ok
Remote obproxy-4.2.1.0-20231220145047.el7-9b4e22543962ec00bdb15f59c0b1a9b2fb689584 repository lib check ok
obcluster deployed
Please execute ` obd cluster start obcluster ` to start
Trace ID: 2ba1b4d0-049d-11f1-a480-005056be5b54
If you want to view detailed obd logs, please run: obd display-trace 2ba1b4d0-049d-11f1-a480-005056be5b54
1.2.3 初始化 OB 分布式集群
这一步初始化 OB 集群。包括安装软件、启动 OBSERVER 并初始化集群。 
obd cluster deploy obcluster -c 3ecs-ob.yaml
[root@server64 soft]# obd cluster deploy obcluster -c 3ecs-ob.yaml
+----------------------------------------------------------------------------------------------+
|                                           Packages                                           |
+------------+-------------+------------------------+------------------------------------------+
| Repository | Version/Tag | Release                | Hash                                     |
+------------+-------------+------------------------+------------------------------------------+
| oceanbase  | 4.2.1.3     | 103030022024011313.el7 | 4c82d4a4f2d824038ece244cccad493b626e6afa |
| obproxy    | 4.2.1.0     | 20231220145047.el7     | 9b4e22543962ec00bdb15f59c0b1a9b2fb689584 |
+------------+-------------+------------------------+------------------------------------------+
Repository integrity check ok
Load param plugin ok
Open ssh connection ok
Initializes observer work home ok
Initializes obproxy work home ok
Parameter check ok
Remote oceanbase-4.2.1.3-103030022024011313.el7-4c82d4a4f2d824038ece244cccad493b626e6afa repository install ok
Remote oceanbase-4.2.1.3-103030022024011313.el7-4c82d4a4f2d824038ece244cccad493b626e6afa repository lib check ok
Remote obproxy-4.2.1.0-20231220145047.el7-9b4e22543962ec00bdb15f59c0b1a9b2fb689584 repository install ok
Remote obproxy-4.2.1.0-20231220145047.el7-9b4e22543962ec00bdb15f59c0b1a9b2fb689584 repository lib check ok
obcluster deployed
Please execute ` obd cluster start obcluster ` to start
Trace ID: 2ba1b4d0-049d-11f1-a480-005056be5b54
If you want to view detailed obd logs, please run: obd display-trace 2ba1b4d0-049d-11f1-a480-005056be5b54
[root@server64 soft]#
[root@server64 soft]#
[root@server64 soft]#
[root@server64 soft]# obd cluster start obcluster
Get local repositories ok
Load cluster param plugin ok
Open ssh connection ok
[WARN] OBD-1007: (10.0.0.61) The recommended number of stack size is unlimited (Current value: 10240)
[WARN] OBD-1007: (10.0.0.62) The recommended number of stack size is unlimited (Current value: 10240)
[WARN] OBD-1007: (10.0.0.65) The recommended number of stack size is unlimited (Current value: 10240)
[WARN] OBD-2000: (10.0.0.61) not enough memory. (Free: 13G, Need: 14G)
[WARN] OBD-2000: (10.0.0.62) not enough memory. (Free: 13G, Need: 14G)
cluster scenario: None
Start observer ok
observer program health check ok
Connect to observer 10.0.0.61:2881 ok
oceanbase bootstrap ok
start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Connect to observer 10.0.0.61:2881 ok
Wait for observer init ok
+---------------------------------------------+
|                  oceanbase                  |
+-----------+---------+------+-------+--------+
| ip        | version | port | zone  | status |
+-----------+---------+------+-------+--------+
| 10.0.0.61 | 4.2.1.3 | 2881 | zone1 | ACTIVE |
| 10.0.0.62 | 4.2.1.3 | 2881 | zone2 | ACTIVE |
| 10.0.0.65 | 4.2.1.3 | 2881 | zone3 | ACTIVE |
+-----------+---------+------+-------+--------+
obclient -h10.0.0.61 -P2881 -uroot@sys -p'bbBB22__' -Doceanbase -A
cluster unique id: d623521d-4f9b-5591-b7f4-77136d0f19b2-19c3b493cd1-03010204
Connect to obproxy ok
+---------------------------------------------+
|                   obproxy                   |
+-----------+------+-----------------+--------+
| ip        | port | prometheus_port | status |
+-----------+------+-----------------+--------+
| 10.0.0.61 | 2883 | 2884            | active |
| 10.0.0.62 | 2883 | 2884            | active |
| 10.0.0.65 | 2883 | 2884            | active |
+-----------+------+-----------------+--------+
obclient -h10.0.0.61 -P2883 -uroot@proxysys -p'uYEWhByREG' -Doceanbase -A
obcluster running
Trace ID: c8e8669e-049d-11f1-a83b-005056be5b54
If you want to view detailed obd logs, please run: obd display-trace c8e8669e-049d-11f1-a83b-005056be5b54
根据上面连接信息，连接集群 SYS 租户。查看相关资源。 
obclient -h10.0.0.65 -P2883 -uroot@sys#obcluster -p'bbBB22__' -Doceanbase -c -A
obclient [oceanbase]> select zone,svr_ip,status, build_version from __all_server;
+-------+-----------+--------+-------------------------------------------------------------------------------------------+
| zone  | svr_ip    | status | build_version                                                                             |
+-------+-----------+--------+-------------------------------------------------------------------------------------------+
| zone1 | 10.0.0.61 | ACTIVE | 4.2.1.3_103030022024011313-c6afef2be5748fc54b1b0159b5224ae288491424(Jan 13 2024 13:36:43) |
| zone2 | 10.0.0.62 | ACTIVE | 4.2.1.3_103030022024011313-c6afef2be5748fc54b1b0159b5224ae288491424(Jan 13 2024 13:36:43) |
| zone3 | 10.0.0.65 | ACTIVE | 4.2.1.3_103030022024011313-c6afef2be5748fc54b1b0159b5224ae288491424(Jan 13 2024 13:36:43) |
+-------+-----------+--------+-------------------------------------------------------------------------------------------+
3 rows in set (0.008 sec)
SELECT SVR_IP, CPU_CAPACITY, CPU_CAPACITY_MAX, CONCAT(ROUND(MEMORY_LIMIT / 1024 / 1024 / 1024, 1), ' GB') AS MEMORY_LIMIT , CONCAT(ROUND(MEM_CAPACITY / 1024 / 1024 / 1024, 1), ' GB' ) AS MEN_CAPACITY
FROM GV$OB_SERVERS;
+-----------+--------------+------------------+--------------+--------------+
| SVR_IP    | CPU_CAPACITY | CPU_CAPACITY_MAX | MEMORY_LIMIT | MEN_CAPACITY |
+-----------+--------------+------------------+--------------+--------------+
| 10.0.0.61 |           16 |               16 | 14.0 GB      | 12.0 GB      |
| 10.0.0.62 |           16 |               16 | 14.0 GB      | 12.0 GB      |
| 10.0.0.65 |           16 |               16 | 14.0 GB      | 12.0 GB      |
+-----------+--------------+------------------+--------------+--------------+
3 rows in set (0.008 sec)
1.2.4 创建 OB 测试租户

手动创建业务租户。

#创建unit资源规格
CREATE RESOURCE UNIT unit1 MAX_CPU 4, MEMORY_SIZE '8G';
#创建resource pool资源池
CREATE RESOURCE POOL pool1 UNIT='unit1', UNIT_NUM=1, ZONE_LIST=('zone1','zone2','zone3');
#创建test租户
CREATE TENANT test PRIMARY_ZONE='zone1,zone2,zone3', RESOURCE_POOL_LIST=('pool1'), CHARSET='utf8mb4' SET ob_tcp_invited_nodes TO '%';
检查租户。
SELECT tenant_id, tenant_name, tenant_type, primary_zone, status ,tenant_role,COMPATIBILITY_MODE FROM dba_ob_tenants;
obclient [oceanbase]> SELECT tenant_id, tenant_name, tenant_type, primary_zone, status ,tenant_role FROM dba_ob_tenants;
+-----------+-------------+-------------+-------------------+--------+-------------+
| tenant_id | tenant_name | tenant_type | primary_zone      | status | tenant_role |
+-----------+-------------+-------------+-------------------+--------+-------------+
|         1 | sys         | SYS         | RANDOM            | NORMAL | PRIMARY     |
|      1003 | META$1004   | META        | zone1,zone2,zone3 | NORMAL | PRIMARY     |
|      1004 | test        | USER        | zone1,zone2,zone3 | NORMAL | PRIMARY     |
+-----------+-------------+-------------+-------------------+--------+-------------+
3 rows in set (0.022 sec)
设置测试租户的 root 用户密码和测试账户信息。 
obclient -h10.0.0.65 -P2883 -uroot@test#obcluster -p -Doceanbase -c -A
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 816
Server version: OceanBase 4.2.1.3 (r103030022024011313-c6afef2be5748fc54b1b0159b5224ae288491424) (Built Jan 13 2024 13:36:43)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [oceanbase]> set password=password('bbBB22__');
Query OK, 0 rows affected (0.081 sec)
obclient [oceanbase]> create user tuser identified by 'bbBB22__';
Query OK, 0 rows affected (0.064 sec)
obclient [oceanbase]> grant all privileges on *.* to tuser;
Query OK, 0 rows affected (0.088 sec)

设置租户参数和变量。 
下面这些租户参数和变量的设置是实验手册里没有，设置这些参数是为了提升后面初始化数据性能，同时也增加执行计划实验时成功概率。参数和变量的作用在 OBCP V4 课程里有介绍。
alter system set writing_throttling_trigger_percentage=90;
alter system set freeze_trigger_percentage=50;
set global ob_sql_audit_percentage=10;
set global ob_sql_work_area_percentage=10;
set global ob_plan_cache_percentage=10;
set global optimizer_dynamic_sampling=0;
至此，实验环境准备好了。






2. 实验1：分布式对象设计


第一个实验是研究分区表和表组的作用以及索引设计的。


2.1 分区表与表组


2.1 .1 初始化数据


首先用 sysbench 软件初始化 mysql 租户的测试数据。这跟 sysbench 测试 mysql 方法一样。


sysbench oltp_read_write --mysql-host=10.0.0.65 --mysql-port=2883 --mysql-user=tuser@test#obcluster --mysql-password=bbBB22__ --mysql-db=test --db-ps-mode=disable --report-interval=10 --mysql-ignore-errors=6002,6004,4012,2013,4016 --tables=3 --table_size=999999 --threads=10 --time=180 prepare
[root@server64 soft]# sysbench oltp_read_write --mysql-host=10.0.0.65 --mysql-port=2883 --mysql-user=tuser@test#obcluster --mysql-password=bbBB22__ --mysql-db=test --db-ps-mode=disable --report-interval=10 --mysql-ignore-errors=6002,6004,4012,2013,4016 --tables=3 --table_size=999999 --threads=10 --time=180 prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Inserting 999999 records into 'sbtest2'
Inserting 999999 records into 'sbtest3'
Inserting 999999 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest2'...


查看测试表的位置分布。


obclient -h10.0.0.65 -P2883 -uroot@test#obcluster -pbbBB22__ -Doceanbase -c -A
select table_name, partition_name, svr_ip, role from oceanbase.dba_ob_table_locations where database_name='test' and table_name like 'sbtest%' and role='leader' order by 2,1;
+------------+----------------+-----------+--------+
| table_name | partition_name | svr_ip    | role   |
+------------+----------------+-----------+--------+
| sbtest1    | NULL           | 10.0.0.61 | LEADER |
| sbtest2    | NULL           | 10.0.0.62 | LEADER |
| sbtest3    | NULL           | 10.0.0.65 | LEADER |
+------------+----------------+-----------+--------+
3 rows in set (0.121 sec)




2.1 .2 研究执行计划


查看分布式执行计划

实验主要就是看下面这个三表连接的执行计划。


obclient -h10.0.0.65 -P2883 --utuser@test#obcluster -pbbBB22__ -Dtest -c -A
EXPLAIN EXTENDED select count(*) from sbtest1 t1, sbtest2 t2, sbtest3 t3
where t1.id=t2.id and t1.id=t3.id and t1.k<>t2.k and t1.k<>t3.k;


这三个表连接，且连接条件都是主键。默认情况下，其执行计划类型是分布式执行计划，关键字：`DISTRIBUTED` .





上面看的是解析执行计划，我们也可以看实际执行计划。这里用到一些诀窍。


set @last_trace_id = '';
select count(*) from sbtest1 t1, sbtest2 t2, sbtest3 t3
where t1.id=t2.id and t1.id=t3.id and t1.k<>t2.k and t1.k<>t3.k;
select last_trace_id() into @last_trace_id ;
select svr_ip,svr_port,sid,sql_id,plan_id,plan_type,elapsed_time,query_sql,trace_id
from oceanbase.GV$OB_SQL_AUDIT
where request_type in (1,2,3) and db_name = 'test' and trace_id= @last_trace_id
order by request_time DESC \G


输出如下图：



第一次运行的输出会看到内部 SQL，后面反复运行时，输出结果就跟我这里一致。


2.1.3 配置负载均衡和表组

    

  • 这个执行计划是有优化空间的，因为三表连接字段都是主键 ID 。看是否能优化为本地执行计划。这里先用到表组技术。
    • 



#将负载均衡的调度时间间隔改为10秒：
alter system set partition_balance_schedule_interval='10s';
#创建sharding='NONE'的表组：
create tablegroup tbg1 sharding='NONE';
#将sbtest1,sbtest2,sbtest3加入到表组中：
alter tablegroup tbg1 add table sbtest1,sbtest2,sbtest3;
#等待20秒，让自动负载均衡完成分区均衡：
select /*+ query_timeout(60000000)*/ sleep(20);
SELECT job_id, create_time, finish_time, balance_strategy, job_type, target_unit_num, target_primary_zone_num, status
FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY
ORDER BY job_id DESC LIMIT 3;
#查看负载均衡的结果，三张表被放到了同一台OBServer上：
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like 'sbtest%' and role='leader' order by 2,1;


这里将分区负载均衡调度间隔调小为 10 秒，所以等了 20 秒让租户内分区均衡发生。


再看 SQL 的执行计划。





此时执行计划就变为本地执行计划。


其原因是表组 SHARDING=NONE 会尽可能把所有分区的主副本聚集在一个节点上，最终呈现一个本地执行计划。

具体执行计划是三个表在同一个节点上 先后 MERGE JOIN 。
2.1.4 单表变分区表改造接下来让情况稍微负载一点，把三个表变为分区表，目前 OB 4.2 已经支持非分区表变更为分区表。

#在改变分区规则前，先将sbtest1,sbtest2,sbtest3从原来的表组中删除：
alter table sbtest1 set tablegroup='';
alter table sbtest2 set tablegroup='';
alter table sbtest3 set tablegroup='';
#创建sharding='PARTITION'的表组：
create tablegroup tbg2 sharding='PARTITION';
#修改sbtest1,sbtest2,sbtest3为hash分区表：
alter table sbtest1 partition by hash(id) partitions 9;
alter table sbtest2 partition by hash(id) partitions 9;
alter table sbtest3 partition by hash(id) partitions 9;
#将sbtest1,sbtest2,sbtest3加入到新的表组：
alter tablegroup tbg2 add table sbtest1,sbtest2,sbtest3;
#等待20秒，让自动负载均衡完成分区均衡：
select /*+ query_timeout(60000000)*/ sleep(20);
#查看负载均衡的结果，三张表的相同分区汇聚在了一起，不同分区分散到3台OBServer上：
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like
'sbtest%' and role='leader' order by 2,1;


再次看 SQL 的执行计划。


set @last_trace_id = '';
select count(*) from sbtest1 t1, sbtest2 t2, sbtest3 t3
where t1.id=t2.id and t1.id=t3.id and t1.k<>t2.k and t1.k<>t3.k;
select last_trace_id() into @last_trace_id ;
select svr_ip,svr_port,sid,sql_id,plan_id,plan_type,elapsed_time,query_sql,trace_id
from oceanbase.GV$OB_SQL_AUDIT
where request_type in (1,2,3) and db_name = 'test' and trace_id= @last_trace_id
order by request_time DESC \G
select dbms_xplan.display_cursor(1224,'typical','10.0.0.65',2882);



此时执行计划又变为分布式执行计划。具体执行计划如下。不过跟之前不一样的是三个表的连接是在同一个节点上的，并且是分区间的并行连接，这是最好的执行计划，既规避了跨节点的 JOIN， 又发挥了分区表的分区并行能力。它有个名字叫： PARTITION WISE JOIN 。
2.1.5 分区表策略变更： HASH 变 RANGE

接下来要做的是将 HASH 分区表变更为 RANGE 分区表。


#在改变分区规则前，先将sbtest1,sbtest2,sbtest3从表组tbg2中删除：
alter table sbtest1 set tablegroup='';
alter table sbtest2 set tablegroup='';
alter table sbtest3 set tablegroup='';
#修改sbtest1,sbtest2,sbtest3为range分区表：
alter table sbtest1 partition by range(id)
(partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than (400000),
partition p4 values less than (500000),
partition p5 values less than (600000),
partition p6 values less than (700000),
partition p7 values less than (800000),
partition p8 values less than (900000),
partition p9 values less than (1000000)
);
alter table sbtest2 partition by range(id)
(partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than (400000),
partition p4 values less than (500000),
partition p5 values less than (600000),
partition p6 values less than (700000),
partition p7 values less than (800000),
partition p8 values less than (900000),
partition p9 values less than (1000000)
);
alter table sbtest3 partition by range(id)
(partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than (400000),
partition p4 values less than (500000),
partition p5 values less than (600000),
partition p6 values less than (700000),
partition p7 values less than (800000),
partition p8 values less than (900000),
partition p9 values less than (1000000)
);
#将sbtest1,sbtest2,sbtest3重新加回表组：
alter tablegroup tbg2 add table sbtest1,sbtest2,sbtest3;
#等待20秒，让自动负载均衡完成分区均衡：
select /*+ query_timeout(60000000)*/ sleep(20);
#查看负载均衡的结果，三张表的相同分区汇聚在了一起，不同分区分散到3台OBServer上：
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like
'sbtest%' and role='leader' order by 2,1;


同号分区的主副本分布在同一个节点上。只不过节点 65 上的 LEADER 分区数量会比 61 和 62 多 3 个。不算特别均衡，但符合预期（10 个均衡组，3个节点，自然无法均衡）。





2.1.6 分区维护。先测试清理分区数据的方法，最快的是 truncate partition 。此外，测试删除和新增分区，人为导致 三个分区表的分区数量不一致。




alter table sbtest2 truncate partition p0;
#在修改前，查看分区的位置分布：
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like
'sbtest%' and role='leader' order by 2,1;
#对sbtest1、sbtest2删除分区p0，对sbtest2、sbtest3增加分区p10：
alter table sbtest1 drop partition p0;
alter table sbtest2 drop partition p0;
alter table sbtest2 add partition (partition p10 values less than(1100000));
alter table sbtest3 add partition (partition p10 values less than(1100000));
select /*+ query_timeout(60000000)*/ sleep(20);
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like
'sbtest%' and role='leader' order by 2,1;


此时三个分区表的分区数分别为 9，10，11 个。11 个均衡组三个节点分，最佳布局是 4-4-3 。

观察三个节点上的 LEADER 副本数量和均衡组分布，61 62 65 分别有 4个、4个、5个。p10 所在的分区没有约束在一个节点内部。

把表 sbtest1 补一个分区，表 sbtest3 删除一个分区后。


#对sbtest3删除分区p0，对sbtest1增加分区p10，让3个表的分区定义再次一致：
alter table sbtest3 drop partition p0;
alter table sbtest1 add partition (partition p10 values less than(1100000));
select /*+ query_timeout(60000000)*/ sleep(20);
select table_name, partition_name, svr_ip, role from
oceanbase.dba_ob_table_locations where database_name='test' and table_name like
'sbtest%' and role='leader' order by 2,1;

此时，3个分区表的同号分区再次在一个节点上，均衡组分布符合预期。



2.2 索引对执行计划影响研究


2.2.1 初始化数据

下面给表 sbtest1 在中间插入两列，并初始化数据。


alter table sbtest1 add column (name char(30) before k,age int before k) ;
show create table sbtest1\G
DROP PROCEDURE IF EXISTS UPDATE_NEW_COLUMNS ;
DELIMITER //
CREATE PROCEDURE UPDATE_NEW_COLUMNS( IN tableName VARCHAR(255) )
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE name_value CHAR(30) DEFAULT ' ';
DECLARE age_value INT DEFAULT 1;
DECLARE names_array VARCHAR(255) DEFAULT
'John,Mary,David,Linda,James,Susan,Robert,Karen,Michael,Lisa,William,Nancy,Richard,Betty,Charles,Margaret,Joseph,Dorothy,Thomas,Sandra';
DECLARE name_index INT;
WHILE i <= 40 DO
SET name_index = FLOOR(RAND() * 20) + 1; -- 生成 1 到 20 之间的随机索引
SET name_value = SUBSTRING_INDEX(SUBSTRING_INDEX(names_array, ',',name_index), ',', -1);
SET age_value = FLOOR(RAND() * 50) + 18; -- 生成 18 到 67 之间的随机年龄
SET @sql = CONCAT('UPDATE ', tableName, ' SET name="', name_value, '", age
	= ', age_value, ' WHERE id%40 = ', i-1);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
COMMIT;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
#调用存储过程，执行批量更新
CALL UPDATE_NEW_COLUMNS('sbtest1');
 SELECT NAME,COUNT(*) FROM sbtest1 GROUP BY NAME;
+----------+----------+
| NAME     | COUNT(*) |
+----------+----------+
| Michael  |    22500 |
| William  |    67500 |
| Susan    |   112500 |
| Dorothy  |   112500 |
| Nancy    |    67500 |
| Betty    |    45000 |
| Thomas   |    45000 |
| Margaret |    22500 |
| Mary     |    67500 |
| Lisa     |    45000 |
| Linda    |    67500 |
| Karen    |    45000 |
| Robert   |    22500 |
| Joseph   |    45000 |
| Sandra   |    22500 |
| James    |    45000 |
| Richard  |    45000 |
+----------+----------+
17 rows in set (0.492 sec)
EXPLAIN select id,name,age from sbtest1 where id<100000 and name ='Margaret' and age<18;
EXPLAIN select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
EXPLAIN select id,name,age from sbtest1 where name ='Margaret' and age<18;


    

  • 然后查看三个 SQL 的执行计划。三个 SQL 前两个 SQL 只是传值不同，第三个少一个 ID 条件。理论上前两个 SQL 解析后 SQL_ID 是一样的。
    • 


第一个 SQL 居然是远程的执行计划，这跟当前会话的路由节点有关。退出重新登录再看看。果然此时变为本地执行计划，扫描一个分区。并且是主键扫描。第二个 SQL 是分布式执行计划，扫描的是 0 和 1 号分区。前面我们知道这两个分区在同一个节点，但这个执行计划依然是分布式执行计划，因为用了分区间的并行。第三个 SQL 没有做分区裁剪，也是分布式执行计划，扫描了所有分区。2.2.2 本地索引测试加三个本地索引，分别使用不同的前缀列。

create index ix1 on sbtest1 (name,age) LOCAL;
create index ix2 on sbtest1 (age,name) LOCAL;
create index ix3 on sbtest1 (id,name,age) LOCAL;




第一个 SQL 执行计划不变，依然走主键扫描。因为这个条件下去没有数据返回，主键扫描成本更低。

第二个 SQL 执行计划也没变，这个跟实验材料有些区别。可能跟数据分布和统计信息有关。

第三个 SQL 执行计划发生变化，选了本地索引。依然有并行，还是分布式执行计划。is_global_index=false, 表示是本地索引；is_index_back=false，表示没有索引回表，因为索引列包含了主键 id 列。
2.2.3 全局索引测试
再加一个全局索引。注意：MySQL 租户允许列上有重复的索引，ORACLE 租户不允许。create index ix1_g on sbtest1 (name,age) GLOBAL;


再看三个 SQL 的执行计划。


第一个 SQL 执行计划不变，因为有分区裁剪，且没有数据，所以不需要全局索引。

第二个 SQL 执行计划变为走全局索引了，看起来是本地执行计划，实际上是用了 DAS ，此时屏蔽了存储层的取数据行为，所以看不出来是分布式执行计划了。is_global_index=true 表示是全局索引，is_index_back=false 表示没有索引回表。



第三个 SQL 执行计划也变为走全局索引，也用了 DAS。






3. 实验 2：SQL 执行优化


3.1 执行计划管理


3.1 .1 Outline 测试


obclient -h10.0.0.61 -uroot@test#obcluster -P2883 -pbbBB22__ -c -A test
EXPLAIN EXTENDED select/*+ index(sbtest1 ix1_g)*/ id,name,age from sbtest1 where id<100000 and name ='Margaret' and age<18;
EXPLAIN EXTENDED select/*+ index(sbtest1 ix1_g)*/ id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
EXPLAIN EXTENDED select/*+ index(sbtest1 ix1_g)*/ id,name,age from sbtest1 where name ='Margaret' and age<18;

首先查看三个 SQL 的 Hint 执行计划中的 OUTLINE DATA 。

use test;
EXPLAIN EXTENDED select/*+ index(sbtest1 ix1_g)*/ id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test"."sbtest1"@"SEL$1" "ix1_g")
      USE_DAS(@"SEL$1" "test"."sbtest1"@"SEL$1")
      OPTIMIZER_FEATURES_ENABLE('4.2.1.0')
      END_OUTLINE_DATA
  */

然后根据 SQL 文本创建 OUTLINE 。注意，HINT 要紧跟在 SELECT 后面，不要有空格。

create outline otl1 on select/*+ index(sbtest1 ix1_g)*/ id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select * from oceanbase.DBA_OB_OUTLINES where outline_name = 'otl1'\G



OUTLINE 创建成功后，再看看 SQL 是否使用到 OUTLINE 。


select id,name,age from sbtest1 where id<100000 and name ='Margaret' and age<18;
select @sql1_host_id := host_ip(), @sql1_trace_id := last_trace_id(),connection_id();
select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select @sql2_host_id := host_ip(), @sql2_trace_id := last_trace_id(),connection_id();
select USEC_TO_TIME(request_time) req_time, svr_ip,sid,sql_id,plan_id,plan_type,elapsed_time,query_sql
from oceanbase.GV$OB_SQL_AUDIT
where db_name = 'test' and trace_id in(@sql1_trace_id, @sql2_trace_id)
order by request_time desc limit 50;
SELECT SVR_IP, SQL_ID, PLAN_ID, TYPE, STATEMENT, FIRST_LOAD_TIME, LAST_ACTIVE_TIME, OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, HINTS_ALL_WORKED
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE SVR_IP='10.0.0.61' AND PLAN_ID=1824 \G

查看 SQL 审计试图，两个 SQL 文本不一样，SQL_ID 一样，执行计划 PLAN_ID 也一样。

查看执行计划缓存统计信息，可以看到这个执行计划用到了 OUTLINE 。通过 SQL 文本创建 OUTLINE 成功概率不高，更多的时候是通过 SQL_ID 去创建 OUTLINE 。

接下来是演示当前这个版本的 OB 如何生成 SQL_ID 的。

# STATEMENT_DIGEST_TEXT获得参数化的文本，MD5生成SQL_ID，UPPER转化为大写
select UPPER(MD5(STATEMENT_DIGEST_TEXT("select id,name,age from sbtest1 where name ='Margaret' and age<18")));
# or
select id,name,age from sbtest1 where name ='Margaret' and age<18;
select @sql3_host_id := host_ip(), @sql3_trace_id := last_trace_id(),connection_id();
select svr_ip,svr_port,sid,sql_id,plan_id,plan_type,elapsed_time,query_sql from oceanbase.GV$OB_SQL_AUDIT where trace_id= @sql3_trace_id ;

注：我这个图的 SQL 跟上面 SQL 不一致，所以结果跟实验材料不一致。

  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "test"."sbtest1"@"SEL$1" "ix1_g")
      USE_DAS(@"SEL$1" "test"."sbtest1"@"SEL$1")
      OPTIMIZER_FEATURES_ENABLE('4.2.1.0')
      END_OUTLINE_DATA
  */ ;
select * from oceanbase.DBA_OB_OUTLINES where outline_name='otl2'\G;



查看 SQL_ID 对应的执行计划缓存，也看到了使用了这个 OUTLINE_ID 。



实验做完后，清理相关索引和 OUTLINE 。


drop index ix1 on sbtest1;
drop index ix2 on sbtest1;
drop index ix3 on sbtest1;
drop index ix1_g on sbtest1;
drop outline otl1;
drop outline otl2;
SELECT * FROM oceanbase.DBA_OB_OUTLINES;
SELECT * FROM oceanbase.DBA_SQL_MANAGEMENT_CONFIG ;




3.1 .2 SPM 测试


在做 SPM 测试之前，再次清理之前的索引和 BASELINE （如果有的话）。


obclient -h10.0.0.61 -uroot@test#obcluster -P2883 -pbbBB22__ -c -A test
alter table sbtest1 drop index ix1;
alter system flush plan cache global;
select DBMS_SPM.DROP_SQL_PLAN_BASELINE('test','9D827857E23201D96198DB6944874F35','16326534133740390936');
select DBMS_SPM.DROP_SQL_PLAN_BASELINE('test','9D827857E23201D96198DB6944874F35','1645389932264426918');


不开 BASELINE，跑下面测试 SQL。


show variables like '%baseline%';
select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);
select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);
select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
SELECT usec_to_time(request_time) req_time, svr_ip,sid, plan_hash, concat( plan_id, ':', plan_type) plan_id, elapsed_time ela_time, query_sql
FROM oceanbase.GV$OB_SQL_AUDIT
WHERE request_type IN (1, 2, 3) AND sql_id='9D827857E23201D96198DB6944874F35'
AND db_name = 'test' AND request_time >= time_to_usec('2026-2-12 13:15:00')
ORDER BY request_time DESC ;
SELECT CREATED, CREATOR, ORIGIN, SQL_HANDLE,PLAN_NAME, SQL_TEXT,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC\G
SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, query_sql
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G


这个 SQL 会将每个 SQL 重跑两次，以消除第一次 SQL 解析时耗时偏大问题。此外，加上 SLEEP(1) 是为了将各个 SQL 执行时间错开。


多执行几次 SQL 1，平均耗时还会继续下降。





BASELINE 视图是空的。



当 SQL 2 执行后，会多出一个执行计划，SQL_ID 是一样的。区别是一个是本地执行计划，一个是分布式执行计划。


注意：PLAN_NAME 不一样，后面有用。PLAN_ID 也不一样，后面每次重新生成执行计划，这个 PLAN_ID 都会变，但是 PLAN_NAME 就这两个。


会话级别打开 BASELINE 设置。


    

  • 

    optimizer_capture_sql_plan_baselines：表示当新的计划优于基线计划时，是否捕获新的计划作为基线，默认值为 false 。
    

    • 

  • 

    optimizer_use_sql_plan_baselines：表示当SQL生成新的计划时是否使用基线计划进行演进，默认值为 false 。
    

    • 



SET optimizer_use_sql_plan_baselines = on;
SET optimizer_capture_sql_plan_baselines = on;
show variables like '%baseline%';


select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);
SELECT concat(CREATOR, ' : ', ORIGIN, ' : ', CREATED) CREATE_INFO,  SQL_HANDLE,PLAN_NAME,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED, SQL_TEXT
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC \G
SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, QUERY_SQL
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G

跑第一个 SQL，捕捉到执行计划存入 BASELINE ，PLAN_NAME 918 结尾，本地执行计划。仔细看的话，开启 SPM 后，执行计划又重新生成了（PLAN_ID 变了）。

select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);

SQL 重复跑，第二次直接使用缓存的执行计划，耗时更短。跑第二个 SQL，条件不一样，应该是分布式执行计划。

select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);

 BASELINE 捕捉到新的执行计划，PLAN_NAME 936 结尾，分布式执行i计划。此时，执行计划缓存里会有两个执行计划并存，一个本地，一个分布式。

下面开始执行计划演进测试。


回到原会话。新建索引，从而影响执行计划。


create index ix1 on sbtest1 (name,age) LOCAL;


重跑第一个 SQL，根据前面经验，它不会用这个索引。


select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);
SELECT concat(CREATOR, ' : ', ORIGIN, ' : ', CREATED) CREATE_INFO,  SQL_HANDLE,PLAN_NAME,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED, SQL_TEXT
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC \G
SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, QUERY_SQL
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G



BASELINE 视图不变，执行计划缓存被清空重新生成了。


select id,name,age from sbtest1 where id<100000 and name ='margaret' and age<18;
select sleep(1);



SQL 1 执行计划没有变。


再跑第二个 SQL。


select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
SELECT concat(CREATOR, ' : ', ORIGIN, ' : ', CREATED) CREATE_INFO,  SQL_HANDLE,PLAN_NAME,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED, SQL_TEXT
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC \G
SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, QUERY_SQL
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G



SQL 2 执行后，出现新的执行计划（PLAN_NAME 287 结尾），并且都在演进中。注意，这个新的执行计划不在 BASELINE 视图中。


分别查看这三个执行计划的内容。


select dbms_xplan.display_cursor(4576, 'typical', '10.0.0.61', 2882)\G
select dbms_xplan.display_cursor(4584, 'typical', '10.0.0.61', 2882)\G
select dbms_xplan.display_cursor(4587, 'typical', '10.0.0.61', 2882)\G

计划 1 （对应 PLAN_NAME 936) 是主键扫描（也是全表扫描）。计划 2 （对应 PLAN_NAME 287）是走了本地索引扫描。计划 3 （对应PLAN_NAME 918）是主键扫描（也是全表扫描）再跑一次 SQL 2 。

select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
SELECT concat(CREATOR, ' : ', ORIGIN, ' : ', CREATED) CREATE_INFO,  SQL_HANDLE,PLAN_NAME,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED, SQL_TEXT
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC \G
SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, QUERY_SQL
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G

看 LAST_ACTIVE_TIME， 知这次  SQL 又走了老的执行计划（计划 1 ）。再多跑几次 SQL 2 。

select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);
select id,name,age from sbtest1 where id<300000 and name ='Margaret' and age<18;
select sleep(1);

SQL2 又走了新的执行计划（计划 2 ） 。所以 SQL2 多次执行的时候，会出现 2 种执行计划在用。实际上新的执行计划用的次数更多。这就是执行计划演进机制。

FROM oceanbase.GV$OB_SQL_AUDIT
WHERE request_type IN (1, 2, 3) AND sql_id='9D827857E23201D96198DB6944874F35'
AND db_name = 'test' AND request_time >= time_to_usec('2026-2-12 13:15:00')
ORDER BY request_time DESC ;



通过 SQL 审计视图也能看出，两个执行计划都有使用


最后，接受这个新的执行计划到 BASELINE 。




CALL DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE('test', '9D827857E23201D96198DB6944874F35','8344383507633235287');
SELECT concat(CREATOR, ' : ', ORIGIN, ' : ', CREATED) CREATE_INFO,  SQL_HANDLE,PLAN_NAME,  ENABLED, ACCEPTED, FIXED, REPRODUCED, AUTOPURGE
, EXECUTIONS, ELAPSED_TIME, CPU_TIME ,scn_to_timestamp((1+last_executed)*1000) LAST_EXECUTED, SQL_TEXT
, concat('SELECT DBMS_SPM.DROP_SQL_PLAN_BASELINE(''', creator, ''', ''', sql_handle, ''', ''', plan_name , ''');') drop_spm_sql
FROM oceanbase.DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'select id,name,age from sbtest1%'
ORDER BY created DESC \G



SELECT SVR_IP, PLAN_ID, PLAN_HASH, TYPE, FIRST_LOAD_TIME, LAST_ACTIVE_TIME,concat(EXECUTIONS, ' : ', AVG_EXE_USEC, ' us') EXEC_INFO
,  OUTLINE_ID, OUTLINE_DATA, HINTS_INFO, EVOLUTION, EVO_CPU_TIME, EVO_EXECUTIONS, QUERY_SQL
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT WHERE sql_id='9D827857E23201D96198DB6944874F35' ORDER BY first_load_time DESC \G



SPM 接受 BASELINE 后，执行计划被清空了。BASELINE 里多了一笔记录。


总结

当业务大量使用分区表并且租户的 PRIMARY_ZONE 是多 ZONE 或者 UNIT_NUM 大于 1 的时候，表组功能可以适当根据业务特点减少一些跨节点的表连接，从而提升性能。 4.2 版本的表组相比 V4 之前的版本更加的灵活，有多个 SHARDING MODE 可以设置。这个功能目前看比较稳定，也很容易掌握。


SPM 是 4.2.1 新出的功能，是为了避免特殊的业务场景（如大小账户、分页查询）下出现的缓存的执行计划出现性能回退问题。这个实验测试的 SPM 还只是其中一部分场景，已经能看出开启 SPM 后，执行计划管理会变得更复杂一些。


SPM 并不一定能消除执行计划反复横跳的现象。在 SPM 之前，OB 的 SQL 执行也有反馈机制，如果连续几次执行性能下降，就会重新生成一次执行i计划缓存。OCP 里经常有这种 SQL 执行性能衰退或者执行计划性能衰退的告警。SPM 目前看可能会让这个情形的分析更加复杂。4.2.5 版本的 SPM 在策略上又有所改进，还需要继续测试研究。此外，如果是会话级别的 SPM，会导致 SQL 执行计划被清空重新生成。如果是并行的会话，一个开启 SPM 一个不开启 SPM，都跑同样的 SQL，那么 SQL 的执行计划缓存很有可能会反复被清空并重新生成，即会有很多硬解析。本文还没有测试开启租户全局的 SPM 会怎么样，预计会有大量的 BASELINE 记录生成。分析起来更复杂了。所以，个人建议，除非客户业务正遭遇严重的大小账户带来的性能问题，否则不要无脑地去生产环境开启 SPM 。


图片


庆涛大佬干货推荐



立即试用 OceanBase 企业版，体验国产数据库能力立即试用







            

            
posted @ 
2026-09-10 11:30 
OceanBase数据库 
阅读(6) 
评论(0) 
 
收藏 
举报