優(yōu)化器規(guī)則

優(yōu)化器根據(jù)統(tǒng)計(jì)信息和代價(jià)模型（[RBO] 什猖、[CBO]）為每個(gè)執(zhí)行計(jì)劃計(jì)算一個(gè)代價(jià)橡疼，代價(jià)是對(duì)執(zhí)行計(jì)劃的執(zhí)行時(shí)間舆吮，是CPU消耗揭朝、IO耗時(shí)和網(wǎng)絡(luò)傳輸耗時(shí)的綜合。
計(jì)劃選擇
通過(guò)選擇基表訪問(wèn)路徑色冀、連接算法和連接順序潭袱，最后綜合一些其他算子來(lái)計(jì)算代價(jià)，并選擇一個(gè)最小的執(zhí)行計(jì)劃

索引路徑選擇規(guī)則[RBO]

基于規(guī)則的路徑選擇主要用于索引選擇

• 正向規(guī)則锋恬，前置規(guī)則是強(qiáng)匹配規(guī)則屯换，決定了一個(gè)查詢(xún)使用什么樣的索引
• skyline如果一個(gè)索引在一些定義的維度上優(yōu)于另外一個(gè)索引，不優(yōu)的索引被剪掉与学，剩余的進(jìn)行代價(jià)比較
• OB優(yōu)先使用正向規(guī)則彤悔，無(wú)匹配規(guī)則時(shí)使用skyline
• rule_based 正向規(guī)則，cost_based代價(jià)選擇索守，
• unique_index_with_indexback唯一索引全匹配+回表+回表量低
• pruned_index_name剪掉的路徑,avaiable_index_name保留的訪問(wèn)路徑

前置規(guī)則

查詢(xún)條件是否能覆蓋所有索引鍵
使用該索引是否需要回表
唯一性索引全匹配+不需要回表
普通索引全匹配+不需要回表
唯一性索引全匹配+回表+回表數(shù)量少于一定的閾值
索引全匹配是指在索引鍵上都存在等值條件(get|multi-get)

Skyline剪枝規(guī)則

• 索引三個(gè)維度

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

基于代價(jià)的路徑選擇[CBO]

• 訪問(wèn)路徑的代價(jià)與很多因素有關(guān)晕窑，比如掃描的行數(shù)、回表的行數(shù)卵佛、投影的列數(shù)和謂詞的個(gè)數(shù)等
• 訪問(wèn)路徑的總代價(jià)是構(gòu)成訪問(wèn)路徑的每個(gè)算子的代價(jià)的總和
• 表掃描[table scan]是最基礎(chǔ)的算子杨赤，其代價(jià)由掃描訪問(wèn)路徑的代價(jià)和回表的代價(jià)兩部分組成
  • 掃描訪問(wèn)路徑的代價(jià)跟掃描的行數(shù)成正比。query range 決定了掃描的開(kāi)始和結(jié)束位置截汪，query range 的掃描是順序IO
  • 回表的代價(jià)跟回表的行數(shù)是正相關(guān)疾牲，回表的掃描機(jī)制是隨機(jī)IO
    普通索引的回表邏輯封裝在[table scan]算子中；全局索引的回表邏輯由[table lookup算子完成]
• 常見(jiàn)算子

sort sor的代價(jià)與記錄數(shù)挫鸽、記錄長(zhǎng)度说敏、排序列的長(zhǎng)度和類(lèi)型有關(guān)
group by 根據(jù)數(shù)組分組的算法可以將group by 分為兩種：hash group by和merge group by鸥跟。執(zhí)行計(jì)劃生成時(shí)根據(jù)SQL優(yōu)化器對(duì)于兩種算子的代價(jià)評(píng)估丢郊，來(lái)選擇使用哪種group by算子
material 當(dāng)需要下層算子把所有數(shù)據(jù)輸出時(shí)盔沫，使用material算子來(lái)保存下層算子的中間結(jié)果集
NLJ、HJ枫匾、MJ 兩個(gè)表進(jìn)行join的不同方法
subplan scan 類(lèi)似于table scan算子架诞，但它不從基表讀取數(shù)據(jù)，而是讀取孩子節(jié)點(diǎn)的輸出數(shù)據(jù)
EXchange 在分布式場(chǎng)景中干茉，用于線程間進(jìn)行數(shù)據(jù)交互的算子谴忧。一般成對(duì)出現(xiàn)的，數(shù)據(jù)源端有一個(gè)OUT算子角虫，目的端會(huì)有一個(gè)IN算子沾谓。該算子的代價(jià)主要為數(shù)據(jù)在網(wǎng)絡(luò)中的傳輸時(shí)間

連接算法join

• hash join 和 Merge join 只適用于等值的聯(lián)接條件
• Nested Loop Join 可用于任何連接條件，通常用于外表行數(shù)較少戳鹅，內(nèi)表有索引的場(chǎng)景
  CBO=小表的table_scan_cost+小表的記錄數(shù)×大表的table_scan_cost

內(nèi)表可進(jìn)行一次掃描并把結(jié)果物化在內(nèi)存中均驶，計(jì)劃算子-material
緩存塊嵌套循環(huán)聯(lián)接Blocked nested loop join，外表批量讀取默認(rèn)1000行枫虏，batch_join=true
Index Nested Loop Join 通過(guò)外層表匹配條件直接與內(nèi)層表索引進(jìn)行匹配妇穴，nl_param會(huì)有條件下壓
優(yōu)先選擇3，然后檢查2隶债，也可一起使用

• merge join 適合兩個(gè)輸入表有序腾它，大小相當(dāng)
  CBO=小表的table_scan_cost+大表的table_scan_cost+SORT的cost+merge_join_cost
• hash join 用手兩個(gè)表相對(duì)較小或者小表與大表的join，如果表太大死讹，OB將進(jìn)行切分多個(gè)分區(qū)
  CBO=小表的table_scan_cost+大表的table_scan_cost+hash_join_cost

執(zhí)行計(jì)劃管理

執(zhí)行計(jì)劃explain

• explain [basic|extended|partitions|format=format_name] explainable_stmt
• basic 最基本的計(jì)劃展示
• extended詳細(xì)計(jì)劃展示瞒滴，將表掃描的范圍段展示出來(lái)

is_index_back 表示該路徑是否需要回表
range_cond、range_key回俐、range 即為query range 訪問(wèn)路徑的掃描開(kāi)始和結(jié)束位置
filter_before_indexback逛腿、filter 那些謂詞在索引上計(jì)算，那些謂詞需要回表之后才能計(jì)算
table_rows 表的行數(shù)仅颇，優(yōu)化器靜態(tài)搜集的統(tǒng)計(jì)信息
physical_range_rows 存儲(chǔ)層進(jìn)行索引掃描的記錄數(shù)
output_rows 最終返回的記錄數(shù)

實(shí)時(shí)執(zhí)行計(jì)劃

• 查詢(xún)SQL在計(jì)劃緩存中的plan_id
  select * from v$plan_cache_plan_stat where tenant_id= 1001 and statment like ''
• 使用plan_id展示對(duì)應(yīng)執(zhí)行計(jì)劃
  select * from v$plan_cache_plan_explain where tenant_id= 1001 and plan_id=7

計(jì)劃緩存

• 計(jì)劃緩存是key-value,key為SQL字符串单默，value是執(zhí)行計(jì)劃
• 每個(gè)租戶(hù)在每一臺(tái)服務(wù)器上都有一個(gè)獨(dú)立的計(jì)劃緩存
• 同一條SQL不同參數(shù)值會(huì)保留多條執(zhí)行計(jì)劃
• 計(jì)劃支持自動(dòng)淘汰和手動(dòng)淘汰
  • plan_cache_evict_interval
  • ob_plan_cache_percentage 租戶(hù)內(nèi)存上限的百分比
  • ob_plan_cache_evict_high_percentage
  • ob_plan_cache_evict_low_percentage
  • alter system flush plan cache [tenant_list] [global]
  • 合并會(huì)導(dǎo)致統(tǒng)計(jì)信息更新，計(jì)劃緩存刷新
• ob_enable_plan_cache控制SQL是否使用計(jì)劃緩存
• /+use_plan_cache(none|default)/
• 計(jì)劃所占內(nèi)存大于20M不會(huì)加入緩存
• 分布式計(jì)劃且涉及多個(gè)表不會(huì)加入緩存
• 視圖
  • (g)v$plan_cache_stat 計(jì)劃緩存的狀態(tài)
  • (g)v$plan_cache_plan_stat 計(jì)劃執(zhí)行的統(tǒng)計(jì)信息
  • (g)v$plan_cache_plan_explain緩存中的執(zhí)行計(jì)劃

快速參數(shù)化

• OB通過(guò)詞法分析對(duì)文本串直接參數(shù)化忘瓦，而不是語(yǔ)法樹(shù)參數(shù)化
• 不能參數(shù)化場(chǎng)景
  • order by | group by 后常量
  • limit 后常量
  • 格式串的字符串常量
  • 函數(shù)中的常量

SQL_audit

• SQL_audit可以用來(lái)查看每次請(qǐng)求客戶(hù)端來(lái)源搁廓，執(zhí)行服務(wù)器信息，執(zhí)行狀態(tài)信息耕皮，等待事件境蜕，執(zhí)行各階段耗時(shí)
• sql_audit參數(shù)設(shè)置
  • alter system set enable_sql_audit=true
  • alter system set sql_audit_memory_limit ='3G'
  • 每1s檢測(cè)是否淘汰轧粟，規(guī)則與avail_mem_limit相關(guān)(min(OBServer可使用內(nèi)存的10%,sql_audit_memory_limit))叠国，或者記錄多于900萬(wàn)條時(shí)

SQL Trace

• SQL trace 交互式的提供上一次執(zhí)行的sql請(qǐng)求執(zhí)行過(guò)程信息及各階段的耗時(shí)
• 開(kāi)關(guān) SET ob_enable_trace_log=1/0
• show trace查看信息
• trace_id 可快速查找相關(guān)的OBServer日志
• plan_id 可用于查詢(xún)計(jì)劃緩存
• phy_plan_type 計(jì)劃類(lèi)型

等待事件分析-SQL_audit

• 4大類(lèi)等待事件
  • APPlication_wait_time
  • concurrency_wait_time
  • user_io_wait_time
  • schedule_time
• 記錄了耗時(shí)最多的等待事件名稱(chēng)(event),等待事件耗時(shí)(WAIT_TIME_MICRO)
• 記錄了的所有等待事件的發(fā)生次數(shù)(TOTAL_WAITS)，總等待耗時(shí)(TOTAL_WAIT_TIME_MICRO)

TOP-N[SQL_audit]

• retry_cnt如果次數(shù)多豹芯，則可能有鎖沖突或切主等情況
• queue_time的值是否過(guò)大
• Get_plan_time的值過(guò)大可能是未命中計(jì)劃緩存is_hit_plan=0
• excute_time值過(guò)大可能是等待長(zhǎng)或者邏輯讀長(zhǎng)
• traCe日志查看慢查詢(xún)
• 查詢(xún)某段時(shí)間內(nèi)Qps罚拟，elapsed_time最多top-n
• plan_cache_plan_stat中avg_exe_usec最多top-n

流量分布統(tǒng)計(jì)

• SQL_audit中RPC_count台诗，elapsed_time,queue_time,QPS

執(zhí)行計(jì)劃類(lèi)型統(tǒng)計(jì)

• SQL_audit 中plan_type完箩，is_executor_rpc=0?為主計(jì)劃，1為子計(jì)劃

全表掃描統(tǒng)計(jì)

• sql_audit中table_scan=1

分布式計(jì)劃統(tǒng)計(jì)

• 通過(guò)trace_id 查詢(xún)SQL_audit中is_executor_rpc=0|1的所有記錄拉队，確以主和子計(jì)劃的情況

Hint

• 語(yǔ)法/*+ [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生成執(zhí)行計(jì)劃
• gv$plan_cache_plan_explain 查看實(shí)際的執(zhí)行計(jì)劃

SQL調(diào)優(yōu)策略

• 策略
  • 關(guān)注索引
  • 分區(qū)裁剪
  • 查詢(xún)并行度
  • 聯(lián)接順序
  • 跨機(jī)或并行聯(lián)接的數(shù)據(jù)再分布方式
  • 查詢(xún)改寫(xiě)
  • 優(yōu)化慢SQL
  • 關(guān)注請(qǐng)求流量均衡
  • 關(guān)注RPC請(qǐng)求均衡
• 調(diào)優(yōu)步驟
  • 通過(guò)(g)v$sql_audit,SQL Trace和計(jì)劃緩存視圖查找待優(yōu)化點(diǎn)
  • 查看某條SQL執(zhí)行計(jì)劃
  • 查詢(xún)改寫(xiě)和聯(lián)接調(diào)整秩彤，索引調(diào)整

統(tǒng)計(jì)信息

優(yōu)化器的統(tǒng)計(jì)信息是一個(gè)描述數(shù)據(jù)庫(kù)中表和列信息的數(shù)據(jù)集合，[CBO]依賴(lài)于查詢(xún)中涉及的表事哭、列漫雷、謂詞等對(duì)象的統(tǒng)計(jì)信息來(lái)選取計(jì)劃，OB的統(tǒng)計(jì)信息以普通數(shù)據(jù)的形式存儲(chǔ)在內(nèi)部表中鳍咱，并且會(huì)在本地維護(hù)統(tǒng)計(jì)信息的緩存珊拼，以提高優(yōu)化器對(duì)統(tǒng)計(jì)信息的訪問(wèn)速度。

統(tǒng)計(jì)信息類(lèi)型

表級(jí)統(tǒng)計(jì)信息

表的統(tǒng)計(jì)信息主要存儲(chǔ)在內(nèi)部表 __all_table_stat_v2 中流炕，主要包含以下信息：

• 表的基本信息（包括 tenant_id澎现、table_id、partition_id 等）
• 表的統(tǒng)計(jì)信息類(lèi)型（信息級(jí)別分為 GLOBAL每辟、PARTITION 和 SUBPARTITION）
• 表的行數(shù)
• 表所占用的宏塊數(shù)
• 表所占用的微塊數(shù)
• 表的平均行長(zhǎng)
• 表的收集統(tǒng)計(jì)信息時(shí)間
• 表的統(tǒng)計(jì)信息是否鎖定

列級(jí)統(tǒng)計(jì)信息

列的統(tǒng)計(jì)信息存儲(chǔ)在內(nèi)部表 __all_column_stat_v2 中剑辫，主要包含以下信息：

• 列的基本信息（包括 tenant_id、table_id渠欺、partition_id妹蔽、column_id）
• 列的統(tǒng)計(jì)信息類(lèi)型（信息級(jí)別分為 GLOBAL、PARTITION 和 SUBPARTITION）
• 列中不同的值的數(shù)量 NDV（Number of Distinct Values）
• 列中 NULL 值的數(shù)量
• 列的最大值和最小值
• 列的采樣數(shù)據(jù)量大小
• 列的直方圖的稠密度
• 列的直方圖桶個(gè)數(shù)
• 直方圖類(lèi)型（頻率直方圖/ TopK 直方圖/混合直方圖）

存儲(chǔ)收集的統(tǒng)計(jì)信息

• 表的記錄數(shù)
• 列NDV
• 列中null值的數(shù)量
• 列的最大最小值
• 列的直方圖信息

收集方式

集群合并時(shí)自動(dòng)收集挠将、用戶(hù)手工收集胳岂、存儲(chǔ)層在本地自動(dòng)收集

直方圖

列的直方圖信息存儲(chǔ)在內(nèi)部表 __all_histogram_stat_v2 中，它包含了以下信息：

• 直方圖的基本信息（包括 tenant_id舔稀、table_id乳丰、partition_id、column_id）
• 直方圖的統(tǒng)計(jì)信息類(lèi)型（信息級(jí)別分為 GLOBAL内贮、PARTITION 和 SUBPARTITION）
• 直方圖中每個(gè)桶累積的數(shù)據(jù)量（包含當(dāng)前桶及其之前的桶的總和）
• 直方圖中每個(gè)桶里面的最大的 Value 值
• 直方圖中每個(gè)桶里面的最大 Value 值的頻次
• 頻率直方圖要求bucket的個(gè)數(shù)不小于NDV的個(gè)數(shù)
• topK直方圖只收集頻率最高的K個(gè)列值的統(tǒng)計(jì)

搜集統(tǒng)計(jì)信息

• oracle模式下产园，可以使用dbms_stats.gather.gather_table_stats收集
• oracle模式也支持 analyze table 語(yǔ)法
• 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)
• 默認(rèn)選擇率

表記錄數(shù) 100000
列NDV數(shù) 100

分布式與并行執(zhí)行

• ob先不考慮數(shù)據(jù)的物理分布，生成基于本地關(guān)系優(yōu)化的最優(yōu)執(zhí)行計(jì)劃夜郁，再根據(jù)訪問(wèn)的數(shù)據(jù)分布什燕，原始計(jì)劃樹(shù)上尋找恰當(dāng)位置插入exchange算子變成分布式計(jì)劃。
• 并行執(zhí)行分為并行查詢(xún)竞端、并行DDL屎即、并行DML
• OB支持分區(qū)間并行與分區(qū)內(nèi)并行
• 啟動(dòng)并行查詢(xún)的方式有兩種

通過(guò)parallel hint制定并行度[DOP],OB默認(rèn)不會(huì)開(kāi)啟分區(qū)內(nèi)并行
針對(duì)分區(qū)數(shù)大于1的分區(qū)表會(huì)自動(dòng)啟動(dòng)并行查詢(xún)；分區(qū)間并行事富，默認(rèn)的并行度為每個(gè)數(shù)據(jù)庫(kù)節(jié)點(diǎn)一個(gè)并行線程（explain 顯示dop=1）
GI算子用于并行執(zhí)行中的數(shù)據(jù)掃描迭代
px partition iterator 按分區(qū)迭代技俐，通常使用于dop=1的場(chǎng)景
px block iterator 按數(shù)據(jù)塊迭代埃撵，通常使用于dop>1的場(chǎng)景

并行執(zhí)行資源控制

• 通過(guò)parallel hint 指定并行度
• 針對(duì)查詢(xún)分區(qū)數(shù)大于1的分區(qū)表自動(dòng)啟動(dòng)并行查詢(xún)，dop=并行度
• g(v)$sql_audit中qc_id,dfo_id,sqc_id,worker_id
• parallel_max_servers控制每個(gè)服務(wù)器最大并行執(zhí)行線程個(gè)數(shù)(3.1以后不再使用)
• px_worker_per_cpu_quota 控制每顆邏輯CPU最多提供的并發(fā)線程數(shù)
• parallel_servers_target 控制租戶(hù)在每天OBserver上的最大并發(fā)線程數(shù)
• dtl_buffer_size數(shù)據(jù)交換緩存大小

數(shù)據(jù)重分布

• partition-wise join（PW）
  如果內(nèi)外表都是分區(qū)表方式相同虽另，物理分布一樣，并且jion的連接條件為分區(qū)鍵時(shí)饺谬，可以使用以分區(qū)為單位的連接方法
• partial partition-wise join（PKEY join）
  當(dāng)內(nèi)外表中的一個(gè)表為分區(qū)表捂刺，另一個(gè)表為非分區(qū)表，或者兩者皆為分區(qū)表但是連接鍵僅和其中一個(gè)分區(qū)表的分區(qū)鍵相同的情況下募寨，會(huì)以該分區(qū)分布為基準(zhǔn)族展，重新分布另一個(gè)表的數(shù)據(jù)
• hash,hash
  使用連接鍵上的hash函數(shù)，將每一表的行映射到查詢(xún)服務(wù)器拔鹰。映射完成后仪缸，每個(gè)查詢(xún)服務(wù)器都會(huì)在一對(duì)結(jié)果分區(qū)之間執(zhí)行連接
• broadcast
  將一個(gè)表的所有行都廣播到每個(gè)查詢(xún)服務(wù)器，與另一個(gè)表進(jìn)行并行地join操作列肢。
• random
  主要適用于union all場(chǎng)景恰画，將小表的數(shù)據(jù)隨機(jī)地發(fā)送到另一張表的分區(qū)所在的服務(wù)器

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執(zhí)行trace
• gv$plan_cache_plan_stat plan的執(zhí)行統(tǒng)計(jì)
• gv$sql_plan_statistics 算子的執(zhí)行統(tǒng)計(jì)
• gv$tenant_px_worker_stat 并行線程的執(zhí)行統(tǒng)計(jì)
• gv$sql_plan_monitor 算子在各個(gè)OBserver節(jié)點(diǎn)的執(zhí)行統(tǒng)計(jì)