Oracle sql优化核心思想学习笔记，记录体育生转行dba从0开始的学习历程
1.统计信息
1.1 什么是统计信息？
统计信息是为了让优化器选择最佳执行计划
1.2统计信息都有什么？
统计信息分为，表的统计信息，列的统计信息，索引的统计信息，系统的统计信息，数据字典的统计信息，以及动态性能试图基表的统计信息
表的统计信息主要包含，表的总行数（num_rows）、表的块数（blocks）以及平均长度（avg_low_len）,可以通过dba_tables获取表的统计信息
- 实验：
创建测试表t_stats
SQL> create table t_stats as select * from dba_objects;
Table created.查看t_stats统计信息
select owner, table_name, num_rows, blocks, avg_row_len
from dba_tables
where owner = 'SCOTT'
and table_name = 'T_STATS';
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT T_STATS新建表没有收集统计信息，内容为空
- 收集t_stats 统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'T_STATS',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/- 再次查看统计信息为：
OWNER TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ------ ------- -----------
SCOTT T_STATS 86262 1260 98一共有86262的数据，1260数据块，平均长度为98字节
列的统计信息主要包含列的基数，列中的空值数量，以及列的数据分布情况（直方图），我们可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息
- 查看表T_STATS统计信息
select column_name, num_distinct, num_nulls, num_buckets, histogram
from dba_tab_col_statistics
where owner = 'SCOTT'
and table_name = 'T_STATS';
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ----------- ---------------
OWNER 23 0 1 NONE
OBJECT_NAME 52003 0 1 NONE
SUBOBJECT_NAME 85 86005 1 NONE
OBJECT_ID 86262 0 1 NONE
DATA_OBJECT_ID 8577 77645 1 NONE
OBJECT_TYPE 44 0 1 NONE
CREATED 868 0 1 NONE
LAST_DDL_TIME 950 0 1 NONE
TIMESTAMP 990 0 1 NONE
STATUS 1 0 1 NONE
TEMPORARY 2 0 1 NONE
GENERATED 2 0 1 NONE
SECONDARY 2 0 1 NONE
NAMESPACE 20 0 1 NONE
EDITION_NAME 0 86262 0 NONE其中NUM_DISTINCT为列的基数 NUM_NULLS为列空值数量
- 查看表列统计信息（数据总数，空值，基数，选择性）
select a.column_name,
b.num_rows,
a.num_nulls,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'SCOTT'
and a.table_name = 'T_STATS';
COLUMN_NAME NUM_ROWS NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ---------- ----------- ----------- --------------- -----------
OWNER 86262 0 23 .03 NONE 1
OBJECT_NAME 86262 0 52003 60.28 NONE 1
SUBOBJECT_NAME 86262 86005 85 .1 NONE 1
OBJECT_ID 86262 0 86262 100 NONE 1
DATA_OBJECT_ID 86262 77645 8577 9.94 NONE 1
OBJECT_TYPE 86262 0 44 .05 NONE 1
CREATED 86262 0 868 1.01 NONE 1
LAST_DDL_TIME 86262 0 950 1.1 NONE 1
TIMESTAMP 86262 0 990 1.15 NONE 1
STATUS 86262 0 1 0 NONE 1
TEMPORARY 86262 0 2 0 NONE 1
GENERATED 86262 0 2 0 NONE 1
SECONDARY 86262 0 2 0 NONE 1
NAMESPACE 86262 0 20 .02 NONE 1
EDITION_NAME 86262 86262 0 0 NONE 0
- 创建索引会自动收集统计信息
Create index idx_t_stats_id on t_stats(object_id);
查看索引统计信息
select blevel, leaf_blocks, clustering_factor,status
from dba_indexes
where owner = 'SCOTT'
and index_name = 'IDX_T_STATS_ID';
BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- --------
1 191 1307 VALID
1.3 统计信息参数设置
- 如何收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TAB_OWNER',
tabname => 'TAB_NAME',
estimate_percent => 根据表大小设置,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 根据表大小，CPU资源和负载设置,
granularity => 'AUTO',
cascade => TRUE);
END;
/ownname表示表的拥有者，不区分大小写。
tabname表示表名字，不区分大小写。
granularity表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。对于该选项，我们一般采用AUTO方式，也就是数据库默认方式，因此，在后面的脚本中，省略该选项。
estimate_percent 表示采样率，范围是0.000 001～100。我们一般对小于1GB的表进行100%采样，因为表很小，即使100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。因此我们建议对小表100%采样。
我们一般对表大小在1GB～5GB的表采样50%，对大于5GB的表采样30%。如果表特别大，有几十甚至上百GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于30%。
- 查看表采样率：
SELECT owner,
table_name,
num_rows,
sample_size,
round(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='SCOTT' AND table_name='T_STATS';
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
SCOTT T_STATS 86262 86262 100
收集统计信息，设置采样为30%
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'T_STATS',
estimate_percent => 30,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ESTIMATE_PERCENT
------ ------------------------ ---------- -----------
SCOTT T_STATS 86450 25935 30当表采样率为30% 表总行数估计为86450 实际为：86262 ，一共分析了25935数据表的总行数等于 round（25935*100/30），也就是86450
Method_opt 用于空值收集直方图策略
- for all colums size skewonly 表示对表中所有列收集自动判断是否收集直方图
2.for all columns size auto 表示对出现在where 条件中的列自动判断是否收集直方图 mothed_opt 默认参数就是 for all columns size auto3.for all columns size repeat 表示当前有那些列收集了直方图，现在就收集那些列，当对一个性能稳定的表通常使用repeat 方式
4.for all columns object_type size skewonly 表示收集object_type的直方图，如果之前收集过其他列直方图，现在也收集直方图）
no_invalidate 表示在共享池中设计该表的游标是否失效，建议设置到立即失效，false，原因为：有些时候sql执行缓慢为统计信息失效过期。重新收集统计信息后并没有立即失效，是这个参数没有设置为false 导致
degree为收集统计信息时的并行
1.4 检查统计信息是否过期
表中有大量数据发生变化，这时表的统计信息就会过期。需要从新收集统计信息。不然可能会导致执行计划走偏
- 检查统计信息是否过期
select owner, table_name , object_type, stale_stats, last_analyzed
from dba_tab_statistics
where owner = 'SCOTT'
and table_name = 'T_STATS';
Stale_stat 为no没过期
- 查看过期原因：
select table_owner, table_name, inserts, updates, deletes, timestamp
from all_tab_modifications
where table_owner = 'SCOTT'
and table_name = 'T_STATS';
原因为更新数据9812条
扩展（在看执行计划时，有可能全表扫描能比索引扫描性能更高）
- 如何确定统计信息过期呢？
当表中有超过10%的数据发生变化时，就会引起统计信息过期
当进行sql优化时，我们需要检查表的统计信息是否过期。数据字典all_tab_modifications 还可以用来判断那些表可以定期降低高水位
- 例子：如果有sql在进行乱七八糟的表关联，嵌套试图，怎么快速检查sql中的表统计信息是否过期
如果sql如下：
Select * from emp e ,dept d where e.deptno = d.deptno;
先执行：SQL> explain plan for select * from emp e,dept d where e.deptno=d.deptno;
Explained
查看sql语句中的表是否过期
select owner, table_name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where (owner, table_name) in
(select object_owner, object_name
from plan_table
where object_type like '%TABLE%'
union
select table_owner, table_name
from dba_indexes
where (owner, index_name) in
(select object_owner, object_name
from plan_table
where object_type like '%INDEX%'));
- 查看统计信息过期原因：
select *
from all_tab_modifications
where (table_owner, table_name) in
(select object_owner, object_name
from plan_table
where object_type like '%TABLE%'
union
select table_owner, table_name
from dba_indexes
where (owner, index_name) in
(select object_owner, object_name
from plan_table
where object_type like '%INDEX%'));1.5 拓展统计信息
- 什么是拓展统计信息？
当where 条件中有多个谓词过滤条件，但是这些谓词过滤条件彼此是有关系的而不是独立的，这个时候需要收集拓展统计信息
注意：拓展统计信息只能用于等值查询，不能用于非等值查询
1.6 动态采样
- 动态采样的最终目的就是为了让优化器能过评估出较为准确的rows
动态采样级别分为11级
level 0：不启用动态采样。
level 1：当表（非分区表）没有收集过统计信息并且这个表要与另外的表进行关联（不能是单表访问），同时该表没有索引，表的数据块必须大于32个，满足这些条件的时候，Oracle会随机扫描表中32个数据块，然后评估返回的Rows。
level 2：对没有收集过统计信息的表启用动态采样，采样的块数为64个，如果表的块数小于64个，表有多少个块就会采样多少个块。
level 3：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是优化器不能准确地估算出返回的Rows，而是靠猜，比如WHERE SUBSTR(owner,1,3)，这时会随机扫描64个数据块进行采样。
level 4：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是表有两个或者两个以上过滤条件（AND/OR），这时会随机扫描64个数据块进行采样，相关列问题就必须启用至少level 4进行动态采样。level4采样包含了level 3的采样数据。
level 5：收集满足level 4采样条件的数据，采样的块数为128个。
level 6：收集满足level 4采样条件的数据，采样的块数为256个。
level 7：收集满足level 4采样条件的数据，采样的块数为512个。
level 8：收集满足level 4采样条件的数据，采样的块数为1 024个。
level 9：收集满足level 4采样条件的数据，采样的块数为4 086个。
level 10：收集满足level 4采样条件的数据，采样表中所有的数据块。
level 11：Oracle自动判断如何采样，采样的块数由Oracle自动决定。
默认动态采样等级为level2
- 如何设置动态采集level？
Alter session set optimizer_dynamic_sampling=3;
或者添加HINT启动动态采样
如：select /*+dynamic_sampling(3) */ from t_dyna where substr (owner,4,3) =’LIC’;
- 什么时候需要使用动态采样？
当系统中有全局临时表，就需要使用动态采样，因为全局临时表无法收集统计信息，我们建议对全局临时表至少启用level 4进行采样。
当执行计划中表的Rows估算有严重偏差的时候，例如相关列问题，或者两表关联有多个连接列，关联之后Rows算少，或者是where过滤条件中对列使用了substr、instr、like，又或者是where过滤条件中有非等值过滤，或者group by之后导致Rows估算错误，此时我们可以考虑使用动态采样，同样，我们建议动态采样至少设置为level 4。
1.7 定制统计信息收集策略
优化器在计算执行计划的成本时依赖于统计信息，如果没有收集统计信息，或者是统计信息过期了，那么优化器就会出现严重偏差，从而导致性能问题。因此要确保统计信息准确性。虽然数据库自带有JOB每天晚上会定时收集数据库中所有表的统计信息，但是如果数据库特别大，自带的JOB无法完成全库统计信息收集。一些资深的DBA会关闭数据库自带的统计信息收集JOB，根据实际情况自己定制收集统计信息策略。
- 收集使用全局临时表的sql
select b.object_owner, b.object_name, a.temporary, sql_text
from dba_tables a, v$sql_plan b, v$sql c
where a.owner = b.object_owner
and a.temporary = 'Y'
and a.table_name = b.object_name
and b.sql_id = c.sql_id;