Oracle调优---SQL调优

开发人员必备：

1.会看执行计划

2.懂得索引的优化方法

3.会抓取awr和分析awr

4.懂得数据库中的优化器

一、优化器模式

1.Rule

将忽略开销和统计数据并完全基于数据字典信息生成执行计划总是选择Oracle内部“最优的索引”，有可能选择并非理想的索引。

总是使用索引，总是从驱动表开始，在不可避免的情况下使用全表扫描，任何索引都可以。

2.Choose

选择最适合的优化器目标，根据统计数据有否，如果统计数据存在，将根据索引存在与否。这种选择模式存在很大的缺点。例如：如果将多个表总只有一个表通过分析有详细的统计资料，那么，数据库对其他表会自己进行估算，这样，产生的执行计划不一定是正确的执行计划。

3.first_rows

基于开销的优化模式，以最快的速度返回记录，但是会造成总体查询速度的下降或是消耗更多的资源，通常选择完全索引扫描，而不是全表扫描，倾向于索引扫描。

4.All_rows

确保哦总体查询的时间最短，但是可能受到第一条记录的时间更长，通常选择并行全表扫描，最适用于批量。

二、索引访问方式

1.索引范围扫描：对兼有索引的列进行范围的扫描。例如：

select * from table_name where col>n and col<b;

2.单个索引扫描：对建有索引的列选择唯一值

select * from table_name where col=n;

3.快速全索引扫描：对建有索引的列通过索引进行全扫描

select count(col)from table_name;col列建立了索引。

三、连接操作

1.嵌套循环连接（nl）

关系到两个表：一个小的内部表和一个外部表，Oracle比较内部数据的每一条记录和外部数据集的每一条记录，并返回符合条件的记录集，常用的内部表存在索引的情况，必须确保正确的驱动表和正确驱动数据集。在很多情况下反应最快，尤其中间结果集比较小的情况下。

2.散列连接（hash）

指两个表中较小的那个表（驱动表）中执行全表扫描，然后再在内存中创建一个散列表，散列表用来从比较大的那个表中读取记录（hash_multiblock_io_count）

3.排序拼合连接（merge）

是指从目标表中读取两个记录集，并使用连接字段将两个记录集分别排序。合并过程将来自每个数据集的每一条记录桶来自另一数据集中只相配的记录连接，并返回数据集的交集

四、对SQL结果进行排序

1.order by

2.union

3.group by

4.select distinct

5.select unique

6.create index

五、理解SQL执行

1.解析

①检查安全性：检验用户的权限同表进行比较

②检查SQL语法：书写符合正确的格式，语法是否正确

2.执行

①创建执行计划：产生器接受经过借些的sql，传递给sql优化器，优化器检查sql以及数据字典，然后产生执行计划

②捆绑执行计划：扫描捆绑变量，然后为每个变量赋值

③执行执行计划：将解析树放入缓存，然后执行所有的必要的磁盘输入输出

④读取结果集：读取记录

3.显示结果集和排序结果集

4.转换字段数据：对那些经过内置函数进行过转换的字段数据进行从新格式化处理和转换

六、SQL进行全表扫描

1.字段不平衡索引分布

2.使用choose或all_rows

3.带有like的条件查询，开始用%

4.内置函数使索引无效，to_char,substr,decode。

5.使用并行提示

6.没有收集统计信息

七、对表的处理

1.确定高平使用的表和索引

2.指定要存储的表

3.确定要进行记录重新排序的表

4.释放为被使用的索引

5.通过添加新索引来去掉全表扫描

八、SQL的调优过程

1.定位高平使用的SQL语句

2.调优sql

3.添加索引

4.更改优化器模式

5.添加提示

6.将调优持久化

--调优sql语句

1.添加提示：添加sql提示会修改执行计划，一般要尝试着集中都测试一下，寻找最合适的。

2.索引：添加索引能够去掉全表扫描，但是添加索引可能更改其他用到这个表的SQL语句，因此添加索引的时候要注意不要硬性其他sql

3.重新书写

--sql提示

优化器提示：对数据库的执行计划进行稳定

rule  First_rows  All_rows

1.表连接提示

①use_hash:where子句中的第一个表（最小的表），初始化参数，驱动表，分析统计资料，内存区。

②use_merge:通常与并行查询使用

③use_nl:很少用，因为一般的都是用这个

2.表反连接提示（not in ,not exists）

①merge_aj：在使用全表访问比索引访问更好的情况下

②hash_aj：散列反连接

③注意：只有在not in 子句中要求的字段由非空限制的时候，反连接merge_aj和hash_aj才起作用

3.index提示

注意：表名在提示中必须指定，如果指定表的别名，则在提示中使用别名，索引名称是可选的，如果不指定，则是用“最佳的”索引

①index

②and_equal

③index_asc

④index_desc

⑤index_combine：用来强制使用位图索引

⑥index_ffs:多块读取，但是没有按照顺序，通常和parallel联合使用

⑦use_concat

4.并行提示：parallel

①表访问提示

②Full

③Nocache

④Ordered：from中的第一个表作为驱动表

九、SQL语句中的表连接

等连接 外连接 自连接

十、sql资产讯的调优

关联子查询：（对于父查询中处理的记录来说，一个关联子查询是每行计算一次）

关联子查询的系统开销：对于返回到外层查询的记录来说，子查询是每行执行一次。因此，必须保证任何可能的时候子查询都要使用索引。

Exists:自动转换为嵌套循环

not exsits

非关联子查询：父查询中处理的记录来说，非关联子查询只会执行一次，而且结果集被保存在内存中（如果结果集比较小），或者放在一张Oracle临时数据段中（如果结果集比较大）

非关联子查询的系统开销：子查询只会执行一次，而且结果集通常是排好序的，并保存在临时数据段那种，其中每一个记录在返回时都会被父级查询引用，在子查询返回大量记录的情况下，将这些结果集排序回增大系统的开销

not in

in

----关联子查询和非关联子查询比较

1.如果父查询只返回较少的记录，那么在此执行子查询的开销不会非常大，关联子查询比非关联子查询执行的要更快。

2.如果子查询返回较少的记录，那么为内存中保存父查询的结果集的系统开销不会非常大，则非观澜子查询会比关联子查询执行的更快

----in和exsits比较

1.in是吧外表和内表作hash连接

2.exists是对外表作loop循环，每次loop循环再对内表进行查询

一直以来认为exsits比in效率高的说法是不准确的，当子查询返回结果集少的时候用in，自查询返回结果集多的时候用exsits

如果查询的两个表大小相当，那么用in和exists差别不大，如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

--not in和not exists比较

1.not exsits:做NL，对子查询先查，有个续表，有确定值，所以就算资产讯有NULL最终也有值返回

2.not in：做hash，对子查询表建立内存数组，用外表匹配，那子查询要是有NULL，那外表没匹配的最终无值返回

not in可以转换为in+minus

十一、sql书写规范

1.SQL语句中的所有表名、字段名全部小写，系统保留字、内置函数名、sql保留字大写

2.连接符or、in、and、以及=、<=、>=等前后加上一个空格

3.对较为复杂的sql语句加上注释，说明算法、功能

--注释分割：注释单独成行、放在语句前面

①应对不易理解的分支条件表达式加注释；

②对重要的计算应说明其功能

③过长的函数实现，应按其语句实现的功能分段甲乙概括性说明

④每条sql语句军营有注释说明（表名、字段名）

⑤常量及变量注释时，应注释被保存值的含义（必须），合法取值的范围（可选）

⑥可采用单行/多行注释。（--或/**/方式）