14、优化器_（执行计划、统计信息）_2

执行计划 execution plan

一个 SQL 语句select name from t where id=5;要能够执行的话，Oracle 需要解析，解析之后会生成执行计划
执行计划就是 Oracle 从 t 表里取数据的时候需要按哪个执行路径去走
第一种，全表扫描，使用 id=5 做filter
第二种，先访问索引，通过索引取出 rowid，然后访问表
哪种执行路径好，是解析要做的事情。最好的执行路径就是该条 SQL的执行计划

优化器 optimizer

Oracle 数据库软件最核心的一个组件就是优化器。安装 Oracle 软件后，优化器就会存在

优化器的作用：接到 SQL 文本以后，对 SQL 文本进行解析，生成执行计划

与优化器相关的参数 show parameter optimizer

optimizer_features_enable参数

假设优化器原来的版本是 11.1.0.7，现在是 11.2.0.4，一些 SQL语句使用原来的优化器解析时很好，但是用现在的优化器解析时执行计划好差
可以将 optimizer_features_enable改回 11.1.0.7，这时优化器在工作时会尽量模拟 11.1.0.7的特性去解析 SQL，但是已经升级到 11.2.0.4，有些特性模拟不出来，就会使用 11.2.0.4 的特性

RBO

  • RBO：rule-based optimizer 基于规则的优化器
  • Oracle 7、8i 使用
  • 优化器存储了好多规则，SQL需要解析成执行计划时，根据规则去卡，如果满足规则就走相应的执行路径
  • 规则示例：有 where 条件、where 条件使用的列上有索引、条件是列等于某个值，如果满足这三个条件，就走索引
  • 存在一个问题：执行一个 SQL select name from t where id = 5，满足上面的三个条件，会走索引，但是走索引一定好么。假设 t 表有 100万行，id = 5 有 90万行，走索引的执行性能差
  • 所以如果不考虑列的选择性、集群因子，只是根据规则去卡，可能会带来执行计划效率不高

CBO

  • Oracle 9i 可以选择 RBO和CBO
  • 从 Oracle 10g 开始只有CBO
  • CBO：cost-based optimizer 基于成本的优化器
  • 接收一条 SQL以后，列出所有的执行路径
  • 估算每一个执行路径需要的成本（CPU 和 IO，主要考虑IO成本、CPU考虑的比较少），选择成本少的执行路径
  • 成本的估算依据：表的行数、块数、索引列的选择性、集群因子等

统计信息 statistics

  • 主要是表和索引的统计信息
  • 表的行数、块数
  • 索引列的选择性、集群因子
  • 统计信息不是实时的，统计信息需要收集才会更新
  • 周一的时候 t 表有100万行，插入一行，但是统计信息不会马上更新。到了周六，表变成了 1000万行，然后对 t 表收集统计信息，统计信息被更新
  • 统计信息在变化不是很大的情况下对执行计划的生成没有太大的影响，可以不用频繁收集
  • 比如说表有100万行，过了几天后变成了110万行，从里面检索 id=5 的行（有10行）都会走索引，对执行计划影响不大，可以不用频繁收集统计信息
  • 因为收集统计信息需要消耗大量 IO，而且统计信息收集完成以后，Oracle 会把 shared pool 中原有的的执行计划变得失效，重新解析
  • 一般情况下，统计信息收集越准确，执行计划越好。但是也有别的情况（后期会学习什么时候收集）
  • 一个表发生过大批量的增删改之后需要及时收集统计信息**，执行计划才会变得准确一些
  • 收集统计信息 exec dbms_stats.gather_table_stats('U1','T1')

柱状图 histogram

  • 如果一个列上的值不多，但是一个值非常多，其他一个或几个值非常少，则该列有严重的数据倾斜
  • 例如一个表有 100万行，status列只有1和0两个值，0 只有10行，其他的都是 1，则 status列有严重的数据倾斜
  • select * from t where status=0 走索引好，select * from t where status=1 走全表扫描好
  • 如果一个表上的一个列上有严重的数据倾斜，并且该列有 where条件、有索引，此时有必要收集柱状图
  • 柱状图属于统计信息

桶 bucket

  • 桶越多，表达越准确，最多 254个桶
  • 桶的数量只要大于1就表示收集了柱状图
  • 在收集一个表的一个列的柱状图时，Oracle会自动判断需要多少桶，也可以自己指定桶的数量
  • select * from t where status=0 访问status = 0的列时，会发现 0 占不到 1%的数据，就走索引
  • select * from t where status=1 访问status = 1的列时，会发现 1 占 99%以上的数据，就走索引就走全表扫描

数据字典

  • Oracle 使用数据字典记录数据库自身的信息

  • 用户的信息

  • 表的信息

  • 索引的信息

  • 对象的信息

  • 其他信息

  • 数据字典的类型

  • 字典表: user$、tab$、col$、obj$。这些字典表很难看懂，所以 Oracle在字典表的基础上生成了视图

  • x$bh 反映数据库的实时运行状态，往往和性能相关，很难看懂，所以 Oracle 结合部分字典表和 x$bh 生成了v$bh 视图

  • 视图有三类（dba_、all_、user_）

  • dba_ 视图存储着所有用户的对象的信息（dba_users、dba_tables、dba_indexes、dba_views 。。。）

  • all_ 视图存储着用户有权限访问的对象的信息（all_tables、all_indexes 。。。）

  • user_ 视图存储着用户建立的对象的信息（user_tables、user_indexes 。。。）

arraysize 参数

全表扫描最大的内存读取量不会超过表的总的block的数量

oracle有一个参数：arraysize
默认是：arraysize = 15（一次返给用户的数据量是15行）

  • 一般情况下，全表扫描最大的内存读取量不会超过表的总的 block 的数量
  • 实际上，Oracle 在工作时有一个小瑕疵
SQL> show arraysize
arraysize 15    // 表示一次返给用户的行数是 15行

arraysize 是 sqlplus 的参数
set arraysize 5000   //最大可以设置为 5000

  • 使用下图解释 arraysize 的原理（由于 arraysize 的限制，导致一个数据块被读了多次）

  • 假设一个数据块有100行，每次访问这个块时，返给用户15行。即读一次返回15行，发生一次内存读（全部读完需要7次内存读）

  • 所以可能会出现一个表有1000多个块，全表扫描时会出现6000多内存读（表访问方式的操作2）

  • roundtrip：数据库返给用户一次数据，用户给予一个响应

  • 修改 arraysize 参数 set arraysize 5000

  • 再次执行 SQL select * from t2 发现内存读正常

查看执行计划

  • 第一种，使用 plsql developer 的解释计划

  • 第二种，set autotrace trace

  • 前两种都不够准确（往往和真实的执行计划不一样）

  • 第三种，dbms_xplan

  • 第一步，select * from v$sql where sql_text like 'select * from t2%'; 获取 SQL_ID 和 CHILD_NUMBER

  • 第二步
    set linesize 150
    set pagesize 0
    select * from table(dbms_xplan.display_cursor('741xfy549bxbz',0));

  • Rows：这一步预计返回的行数

  • Bytes：这一步预计返回的字节数

  • Cost(%CPU)：估计成本，是一个累积值。括号内表示CPU成本所占的百分比

  • Time：预计时间，是一个累积值

感知优化器

SQL> show parameter optimizer
optimizer_capture_sql_plan_baselines   boolean	 FALSE
optimizer_dynamic_sampling	         integer	 2
optimizer_features_enable	          string	 11.2.0.4
optimizer_index_caching 	         integer	 0
optimizer_index_cost_adj	         integer	 100
optimizer_mode		        	    string	 ALL_ROWS
optimizer_secure_view_merging	         boolean	 TRUE
optimizer_use_invisible_indexes        boolean	 FALSE
optimizer_use_pending_statistics       boolean	 FALSE
optimizer_use_sql_plan_baselines       boolean	 TRUE
  • optimizer_mode 参数
  • 优化器在解析 SQL前，先看一下 optimizer_mode参数
  • 一个SQL 语句解析完了以后有多条执行路径，其中一条成本比较低，但是需要将所有的数据都处理完了再返给用户，optimizer_mode 参数是 ALL_ROWS就会选择这条执行路径
  • 另外一条成本相对高一些，但是可以先把一部分数据返给用户，optimizer_mode 参数是 FIRST_ROWS_10可能会选择这条执行路径
  • 如果该参数是 FIRST_ROWS_1或FIRST_ROWS_10时，Oracle 在选择执行路径时会倾向于走索引

操作

  • optimizer_mode参数是 ALL_ROWS时， 访问 t2 表 select * from t2 where object_id between 1 and 1000 order by object_id;`

  • 更改optimizer_mode 参数 alter session set optimizer_mode=first_rows_10;

  • 再次执行 select * from t2 where object_id between 1 and 1000 order by object_id; 发现走索引

  • 走索引不需要再进行排序，走索引取出来的数据是按照索引排好序的

  • 原因：first_rows_10 表示选择执行路径时，优先考虑以最快速度先把一部分数据返给用户的执行路径。所以会走索引，因为走索引能够用最快的速度把第一批数据返给用户

RBO

  • optimizer_mode 参数是 ALL_ROWS

  • select * from t2 where object_id between 1 and 8000;

  • 强制走索引 select /*+ index(t2,i_t2_id) */ * from t2 where object_id between 1 and 8000;

  • 使用 RBO select /*+ rule */ * from t2 where object_id between 1 and 8000;

统计信息收集

  • 第一步，查看 t1 表有多少行

  • 第二步，查看列的唯一值的数量

  • 第三步，修改数据并提交，修改之后该列出现了数据倾斜

  • 不收集统计信息，执行SQL select * from t1 where object_id=100; 发现走索引

  • 收集统计信息exec dbms_stats.gather_table_stats('U1','T1');

  • 查询柱状图是否收集 select * from dba_tab_columns t where t.TABLE_NAME='T1';

  • NUM_BUCKETS：只要大于1就表示收集了柱状图

  • 查询柱状图的信息 select * from dba_tab_histograms where OWNER='U1' and TABLE_NAME='T1';

  • 访问 object_id = 100的列，走全表扫描

  • 访问 object_id = 13500 的列，走索引

posted @ 2024-11-20 15:43  一只c小凶许  阅读(0)  评论(0)  编辑  收藏  举报