优化器规则

优化器根据统计信息和代价模型（[RBO] 、[CBO]）为每个执行计划计算一个代价，代价是对执行计划的执行时间，是CPU消耗、IO耗时和网络传输耗时的综合。
计划选择
通过选择基表访问路径、连接算法和连接顺序，最后综合一些其他算子来计算代价，并选择一个最小的执行计划

索引路径选择规则[RBO]

基于规则的路径选择主要用于索引选择

• 正向规则，前置规则是强匹配规则，决定了一个查询使用什么样的索引
• skyline如果一个索引在一些定义的维度上优于另外一个索引，不优的索引被剪掉，剩余的进行代价比较
• OB优先使用正向规则，无匹配规则时使用skyline
• rule_based 正向规则，cost_based代价选择，
• unique_index_with_indexback唯一索引全匹配+回表+回表量低
• pruned_index_name剪掉的路径,avaiable_index_name保留的访问路径

前置规则

查询条件是否能覆盖所有索引键
使用该索引是否需要回表
唯一性索引全匹配+不需要回表
普通索引全匹配+不需要回表
唯一性索引全匹配+回表+回表数量少于一定的阈值
索引全匹配是指在索引键上都存在等值条件(get|multi-get)

Skyline剪枝规则

• 索引三个维度

是否回表
是否存在interesting order
索引前缀能否抽取Query Range

基于代价的路径选择[CBO]

• 访问路径的代价与很多因素有关，比如扫描的行数、回表的行数、投影的列数和谓词的个数等
• 访问路径的总代价是构成访问路径的每个算子的代价的总和
• 表扫描[table scan]是最基础的算子，其代价由扫描访问路径的代价和回表的代价两部分组成
  • 扫描访问路径的代价跟扫描的行数成正比。query range 决定了扫描的开始和结束位置，query range 的扫描是顺序IO
  • 回表的代价跟回表的行数是正相关，回表的扫描机制是随机IO
    普通索引的回表逻辑封装在[table scan]算子中；全局索引的回表逻辑由[table lookup算子完成]
• 常见算子

sort sor的代价与记录数、记录长度、排序列的长度和类型有关
group by 根据数组分组的算法可以将group by 分为两种：hash group by和merge group by。执行计划生成时根据SQL优化器对于两种算子的代价评估，来选择使用哪种group by算子
material 当需要下层算子把所有数据输出时，使用material算子来保存下层算子的中间结果集
NLJ、HJ、MJ 两个表进行join的不同方法
subplan scan 类似于table scan算子，但它不从基表读取数据，而是读取孩子节点的输出数据
EXchange 在分布式场景中，用于线程间进行数据交互的算子。一般成对出现的，数据源端有一个OUT算子，目的端会有一个IN算子。该算子的代价主要为数据在网络中的传输时间

连接算法join

• hash join 和 Merge join 只适用于等值的联接条件
• Nested Loop Join 可用于任何连接条件，通常用于外表行数较少，内表有索引的场景
  CBO=小表的table_scan_cost+小表的记录数×大表的table_scan_cost

内表可进行一次扫描并把结果物化在内存中，计划算子-material
缓存块嵌套循环联接Blocked nested loop join，外表批量读取默认1000行，batch_join=true
Index Nested Loop Join 通过外层表匹配条件直接与内层表索引进行匹配，nl_param会有条件下压
优先选择3，然后检查2，也可一起使用

• merge join 适合两个输入表有序，大小相当
  CBO=小表的table_scan_cost+大表的table_scan_cost+SORT的cost+merge_join_cost
• hash join 用手两个表相对较小或者小表与大表的join，如果表太大，OB将进行切分多个分区
  CBO=小表的table_scan_cost+大表的table_scan_cost+hash_join_cost

执行计划管理

执行计划explain

• explain [basic|extended|partitions|format=format_name] explainable_stmt
• basic 最基本的计划展示
• extended详细计划展示，将表扫描的范围段展示出来

is_index_back 表示该路径是否需要回表
range_cond、range_key、range 即为query range 访问路径的扫描开始和结束位置
filter_before_indexback、filter 那些谓词在索引上计算，那些谓词需要回表之后才能计算
table_rows 表的行数，优化器静态搜集的统计信息
physical_range_rows 存储层进行索引扫描的记录数
output_rows 最终返回的记录数

实时执行计划

• 查询SQL在计划缓存中的plan_id
  select * from v$plan_cache_plan_stat where tenant_id= 1001 and statment like ''
• 使用plan_id展示对应执行计划
  select * from v$plan_cache_plan_explain where tenant_id= 1001 and plan_id=7

计划缓存

• 计划缓存是key-value,key为SQL字符串，value是执行计划
• 每个租户在每一台服务器上都有一个独立的计划缓存
• 同一条SQL不同参数值会保留多条执行计划
• 计划支持自动淘汰和手动淘汰
  • plan_cache_evict_interval
  • ob_plan_cache_percentage 租户内存上限的百分比
  • ob_plan_cache_evict_high_percentage
  • ob_plan_cache_evict_low_percentage
  • alter system flush plan cache [tenant_list] [global]
  • 合并会导致统计信息更新，计划缓存刷新
• ob_enable_plan_cache控制SQL是否使用计划缓存
• /+use_plan_cache(none|default)/
• 计划所占内存大于20M不会加入缓存
• 分布式计划且涉及多个表不会加入缓存
• 视图
  • (g)v$plan_cache_stat 计划缓存的状态
  • (g)v$plan_cache_plan_stat 计划执行的统计信息
  • (g)v$plan_cache_plan_explain缓存中的执行计划

快速参数化

• OB通过词法分析对文本串直接参数化，而不是语法树参数化
• 不能参数化场景
  • order by | group by 后常量
  • limit 后常量
  • 格式串的字符串常量
  • 函数中的常量

SQL_audit

• SQL_audit可以用来查看每次请求客户端来源，执行服务器信息，执行状态信息，等待事件，执行各阶段耗时
• sql_audit参数设置
  • alter system set enable_sql_audit=true
  • alter system set sql_audit_memory_limit ='3G'
  • 每1s检测是否淘汰，规则与avail_mem_limit相关(min(OBServer可使用内存的10%,sql_audit_memory_limit))，或者记录多于900万条时

SQL Trace

• SQL trace 交互式的提供上一次执行的sql请求执行过程信息及各阶段的耗时
• 开关 SET ob_enable_trace_log=1/0
• show trace查看信息
• trace_id 可快速查找相关的OBServer日志
• plan_id 可用于查询计划缓存
• phy_plan_type 计划类型

等待事件分析-SQL_audit

• 4大类等待事件
  • APPlication_wait_time
  • concurrency_wait_time
  • user_io_wait_time
  • schedule_time
• 记录了耗时最多的等待事件名称(event),等待事件耗时(WAIT_TIME_MICRO)
• 记录了的所有等待事件的发生次数(TOTAL_WAITS)，总等待耗时(TOTAL_WAIT_TIME_MICRO)

TOP-N[SQL_audit]

• retry_cnt如果次数多，则可能有锁冲突或切主等情况
• queue_time的值是否过大
• Get_plan_time的值过大可能是未命中计划缓存is_hit_plan=0
• excute_time值过大可能是等待长或者逻辑读长
• traCe日志查看慢查询
• 查询某段时间内Qps，elapsed_time最多top-n
• plan_cache_plan_stat中avg_exe_usec最多top-n

流量分布统计

• SQL_audit中RPC_count，elapsed_time,queue_time,QPS

执行计划类型统计

• SQL_audit 中plan_type，is_executor_rpc=0?为主计划，1为子计划

全表扫描统计

• sql_audit中table_scan=1

分布式计划统计

• 通过trace_id 查询SQL_audit中is_executor_rpc=0|1的所有记录，确以主和子计划的情况

Hint

• 语法/*+ [hint_text]... */
• QB_Name格式，TBL_NAME@SEL1|DEL$1，用于明确给指定表块hint，否则如果指代不清或冲突将失效
• explain extended 可以显示Outline Data

Outline

• outline视图:gv$outline
• create [OR Replace] OUTLINE <outline_name> ON <stmt> [TO <target_stmt>]
• create outline outline_name on sql_id using hint hint_text
• 如果gv$plan_cache_plan_stat和gv$outline中outline_id相同则表示按照绑定的outline生成执行计划
• gv$plan_cache_plan_explain 查看实际的执行计划

SQL调优策略

• 策略
  • 关注索引
  • 分区裁剪
  • 查询并行度
  • 联接顺序
  • 跨机或并行联接的数据再分布方式
  • 查询改写
  • 优化慢SQL
  • 关注请求流量均衡
  • 关注RPC请求均衡
• 调优步骤
  • 通过(g)v$sql_audit,SQL Trace和计划缓存视图查找待优化点
  • 查看某条SQL执行计划
  • 查询改写和联接调整，索引调整

统计信息

优化器的统计信息是一个描述数据库中表和列信息的数据集合，[CBO]依赖于查询中涉及的表、列、谓词等对象的统计信息来选取计划，OB的统计信息以普通数据的形式存储在内部表中，并且会在本地维护统计信息的缓存，以提高优化器对统计信息的访问速度。

统计信息类型

表级统计信息

表的统计信息主要存储在内部表 __all_table_stat_v2 中，主要包含以下信息：

• 表的基本信息（包括 tenant_id、table_id、partition_id 等）
• 表的统计信息类型（信息级别分为 GLOBAL、PARTITION 和 SUBPARTITION）
• 表的行数
• 表所占用的宏块数
• 表所占用的微块数
• 表的平均行长
• 表的收集统计信息时间
• 表的统计信息是否锁定

列级统计信息

列的统计信息存储在内部表 __all_column_stat_v2 中，主要包含以下信息：

• 列的基本信息（包括 tenant_id、table_id、partition_id、column_id）
• 列的统计信息类型（信息级别分为 GLOBAL、PARTITION 和 SUBPARTITION）
• 列中不同的值的数量 NDV（Number of Distinct Values）
• 列中 NULL 值的数量
• 列的最大值和最小值
• 列的采样数据量大小
• 列的直方图的稠密度
• 列的直方图桶个数
• 直方图类型（频率直方图/ TopK 直方图/混合直方图）

存储收集的统计信息

• 表的记录数
• 列NDV
• 列中null值的数量
• 列的最大最小值
• 列的直方图信息

收集方式

集群合并时自动收集、用户手工收集、存储层在本地自动收集

直方图

列的直方图信息存储在内部表 __all_histogram_stat_v2 中，它包含了以下信息：

• 直方图的基本信息（包括 tenant_id、table_id、partition_id、column_id）
• 直方图的统计信息类型（信息级别分为 GLOBAL、PARTITION 和 SUBPARTITION）
• 直方图中每个桶累积的数据量（包含当前桶及其之前的桶的总和）
• 直方图中每个桶里面的最大的 Value 值
• 直方图中每个桶里面的最大 Value 值的频次
• 频率直方图要求bucket的个数不小于NDV的个数
• topK直方图只收集频率最高的K个列值的统计

搜集统计信息

• oracle模式下，可以使用dbms_stats.gather.gather_table_stats收集
• oracle模式也支持 analyze table 语法
• mysql模式下

analyze table table_name update histogram on column_name_list with number buckets

谓词选择率

• eq_selectivity =1/ndv | (num_rows-num_null)/num_rows/ndv
• range_selectivity = {length([max(start,min_value), min(end,max_value)]) /length([min_value,max_value])} + N*eq_selectivity
• AND a_and_b_selectivity = a_selectivity * b_selectivity
• 互斥OR a_or_b_mutex_selectivity=a_selectivity+b_selectivity
• 非互斥OR a_or_b_independent_selectivity =a_selectivity + b_selectivity −a_and_b_selecitivity
• != ne_selectivity=1−eq_selectivity−null_selectivity
• join a_join_b_selectivity=min(a_eq_selectivity,b_eq_selectivity)
• 默认选择率

表记录数 100000
列NDV数 100

分布式与并行执行

• ob先不考虑数据的物理分布，生成基于本地关系优化的最优执行计划，再根据访问的数据分布，原始计划树上寻找恰当位置插入exchange算子变成分布式计划。
• 并行执行分为并行查询、并行DDL、并行DML
• OB支持分区间并行与分区内并行
• 启动并行查询的方式有两种

通过parallel hint制定并行度[DOP],OB默认不会开启分区内并行
针对分区数大于1的分区表会自动启动并行查询；分区间并行，默认的并行度为每个数据库节点一个并行线程（explain 显示dop=1）
GI算子用于并行执行中的数据扫描迭代
px partition iterator 按分区迭代，通常使用于dop=1的场景
px block iterator 按数据块迭代，通常使用于dop>1的场景

并行执行资源控制

• 通过parallel hint 指定并行度
• 针对查询分区数大于1的分区表自动启动并行查询，dop=并行度
• g(v)$sql_audit中qc_id,dfo_id,sqc_id,worker_id
• parallel_max_servers控制每个服务器最大并行执行线程个数(3.1以后不再使用)
• px_worker_per_cpu_quota 控制每颗逻辑CPU最多提供的并发线程数
• parallel_servers_target 控制租户在每天OBserver上的最大并发线程数
• dtl_buffer_size数据交换缓存大小

数据重分布

• partition-wise join（PW）
  如果内外表都是分区表方式相同，物理分布一样，并且jion的连接条件为分区键时，可以使用以分区为单位的连接方法
• partial partition-wise join（PKEY join）
  当内外表中的一个表为分区表，另一个表为非分区表，或者两者皆为分区表但是连接键仅和其中一个分区表的分区键相同的情况下，会以该分区分布为基准，重新分布另一个表的数据
• hash,hash
  使用连接键上的hash函数，将每一表的行映射到查询服务器。映射完成后，每个查询服务器都会在一对结果分区之间执行连接
• broadcast
  将一个表的所有行都广播到每个查询服务器，与另一个表进行并行地join操作。
• random
  主要适用于union all场景，将小表的数据随机地发送到另一张表的分区所在的服务器

hint

• use_px /*+ use_px parallel(4) */
• no_use_px /*+no_use_px */
• parallel /*+ parallel(4) */
• ordered /*+ ordered */
• leading /*+ leading(T1 T2) */
• use_nl/use_hash/use_merge /*+ use_nl(T_inner) */
• pq_distribute /*+ pd_distribute(t_inner broadacast,none) use_hash(t1 t2) */

视图

• gv$sql_audit sql执行trace
• gv$plan_cache_plan_stat plan的执行统计
• gv$sql_plan_statistics 算子的执行统计
• gv$tenant_px_worker_stat 并行线程的执行统计
• gv$sql_plan_monitor 算子在各个OBserver节点的执行统计