ORACLE SQL优化:入门

听了王海波老师的课后的笔记

1.sql优化需要什么？

select ... from t1 where t1.xxx=:xxxx

2.为什么建索引可以优化

创建索引以后，oracle就有两个选择，当选择了更优秀的路径去执行，这个过程就叫做优化。

3.什么是访问路径

访问路径是用于从行源检索行的一种技术

（1）TABLE ACCESS FULL 全表扫描

（2）INDEX UNIQUE SCAN 索引唯一扫描

（3）INDEX RANGE SCAN 索引范围扫描

（4）INDEX SKIP SCAN 索引跳跃扫描

（5）INDEX  FULL SCAN 索引全扫描

（6）INDEX  FAST FULL SCAN 索引快速全扫描

（7）TABLE ACCESS BY INDEX ROWID 回表

4.为什么复杂的SQL更容易出问题

select .. from t1,t2 where t1.x=t2.x and t1.xxx=:xxxx

5.什么是连接方式

连接方式是用于两个集合通过某种关系残生一个新集合的方法

（1）嵌套循环（NESTED LOOPS)

（2）HASH连接 (HASH JOIN)

（3）排序合并连接 （SORT MERGE JOIN)

（4）笛卡尔连接（CARTESIAN JOIN)

（5）标量子查询（SCALAR SUBQUERY)

（6）半连接(SEMI JOIN)

（7）反连接（ANTI JOIN)

（8）FILTER

6.ORACLE是怎么判断某一条路是最优？

执行计划：是一条SQL语句中在Oracle中执行过程和访问路径的描述。

执行计划具体实施，可参考 https://blog.csdn.net/bisal/article/details/38919181

Oracle认为执行计划中cost值越小，执行效率越高，所以oracle会优先选择cost值小的执行计划

7.压力测试，CPU使用率不能超过70%

8.执行计划中cost值是依据什么计算出来的

RBO(基于规则的优化器）和CBO（基于成本的优化器)

RBO它有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说RBO对数据不“敏感”；它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。比如在规则中，索引的优先级大于全表扫描;RBO是根据可用的访问路径以及访问路径等级来选择执行计划，在RBO中，SQL的写法往往会影响执行计划，它要求开发人员非常了解RBO的各项细则，菜鸟写出来的SQL脚本性能可能非常差。随着RBO的被遗弃，渐渐不为人所知。

CBO是一种比RBO更加合理、可靠的优化器，它是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟，在ORACLE 10g中完全取代RBO， CBO是计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。如果对一次执行SQL时发现涉及对象（表、索引等）没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，动态的收集表和索引上的一些数据信息。

9.什么是统计信息

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

10.统计信息不准确怎么办

统计信息过期：(1).表中的数据变化达到30%，oracle会标记为过期状态  (2)从未进行统计信息收集活着长时间未收集

Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。

Hint:此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从 而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle 中，是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。
不建议在代码中使用hint，在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟 数据是不断变化的， 10g以后的CBO也越来越完善，大多数情况下我们该让Oracle自行决定采用什么执行计划。

Hint参考：https://www.iteye.com/blog/czmmiao-1478465

手动收集统计信息代码可参考：

https://blog.csdn.net/lin5450lin/article/details/89029008

11.是否执行计划最优，执行效率就好？

正常的开发过程中，会有多种sql语句写法，会导致优化器在解析的时候只能走某一种固定的计划执行。此时就算执行计划最优，执行效率也未必好

如下需要注意的几种情况：