SQL优化过程：
1,捕获高负荷的SQL语句——>2得到SQL语句的执行计划和统计信息--->3分析SQL语句的执行计划和统计信息--->4采取措施，对SQL语句进行调整。
1找出高负荷SQL语句的方法：
性能检测器ADDM、自动化SQL调整（Automatic SQL Tuning)、自动工作负载库AWR、查看视图V$SQL、来自客户的报告、SQL跟踪工具（SQL Trace）
2.查看执行计划
一旦获得高负荷的SQL语句，就需要对该SQL语句进行调整，SQL语句的调整工具有：SQL Trace提供单条SQL语句的性能信息（包括统计信息）、AUTOTRACE是SQL*Plus的一个功能，也是用于产生某条SQL语句的性能信息、SQL Access Advisor（SQL访问指导）通过建议要创建，删除或保留的索引，物化视图，物化视图日志或分区来确定并帮助解决与SQL语句执行相关的性能问题。
SQL Tuning Advisor(SQL调整指导） 可以为SQL的优化提供建议，使SQL的调整简单化。
SQL Performance Analyzer（SQL性能分析器）可用于预测数据库环境改变对SQL执行造成的负面影响。
例子：
配置AUTOTRACE
--step1，创建执行计划表plan_table
SQL>conn item
SQL> @E:\app\dingqi\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql
SQL> create table PLAN_TABLE (
2 statement_id varchar2(30),
3 plan_id number,
4 timestamp date,
5 remarks varchar2(4000),
6 operation varchar2(30),
7 options varchar2(255),
8 object_node varchar2(128),
9 object_owner varchar2(30),
10 object_name varchar2(30),
11 object_alias varchar2(65),
12 object_instance numeric,
13 object_type varchar2(30),
14 optimizer varchar2(255),
15 search_columns number,
16 id numeric,
17 parent_id numeric,
18 depth numeric,
19 position numeric,
20 cost numeric,
21 cardinality numeric,
22 bytes numeric,
23 other_tag varchar2(255),
24 partition_start varchar2(255),
25 partition_stop varchar2(255),
26 partition_id numeric,
27 other long,
28 distribution varchar2(30),
29 cpu_cost numeric,
30 io_cost numeric,
31 temp_space numeric,
32 access_predicates varchar2(4000),
33 filter_predicates varchar2(4000),
34 projection varchar2(4000),
35 time numeric,
36 qblock_name varchar2(30),
37 other_xml clob
38 );
表已创建。
--step2，创建角色plustrace
SQL>conn / as sysdba
SQL> @E:\app\dingqi\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql
--step3 把角色plustrace授予用户item
grant plustrace to ITEM;
运行AUTOTRACE
--选项
set autot off 关闭autotrace；
set autot on 打开autotrace，显示sql语句的执行计划和 统计信息，还显示sql语句的执行结果；
set autot traceonly 打开autotrace，仅显示autotrace信息，不显示sql语句的执行结果；
set autot explain 打开autotrace，仅显示SQL语句的执行计划；
set autot on statistics 打开autotrace，仅显示SQL语句的统计信息。
查看用户所拥有的表：
select table_name from user_tables;
SQL> select * from myorder;
ORDER_NO ORDER_NAME ORDER_DESC
---------- -------------------- --------------------
1 光盘采购 采购公司需要的光盘
2 大米 要采购来自美国的大米
3 小米 小米公司生产的手机
已选择3行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 2858219380
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 87 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| MYORDER | 3 | 87 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
----------------------------------------------------------
275 recursive calls --递归调用的次数
0 db block gets --当前块被请求的次数，当前块指的是未被修改过的块
42 consistent gets --对块一致性读的请求次数，一致性读设计读回滚段
7 physical reads --从磁盘读的块的数量
0 redo size --产生的回滚数据的大小，单位是字节
683 bytes sent via SQL*Net to client --后台进程发送给客户端的信息量大小，单位字节
415 bytes received via SQL*Net from client --客户服务发给数据库服务器的信息量的大小
2 SQL*Net roundtrips to/from client –-oracle net发送和接受的信息数量，单位是条
4 sorts (memory) --内存排序的次数
0 sorts (disk) --磁盘排序的次数
3 rows processed --处理数据的行数
1> Plan hash Value
这一行是这一条语句的的hash值，我们知道ORACLE对每一条ORACLE语句产生的执行计划放在SHARE POOL里面，第一次要经过硬解析，产生hash值。下次再执行时比较hash值，如果相同就不会执行硬解析。
2> COST
COST没有单位，是一个相对值，是SQL以CBO方式解析执行计划时，供ORACLE来评估CBO成本，选择执行计划用的。没有明确的含义，但是在对比是就非常有用。
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
3> 对上面执行计划列字段的解释：
Id: 执行序列，但不是执行的先后顺序。执行的先后根据Operation缩进来判断（采用最右最上最先执行的原则看层次关系，在同一级如果某个动作没有子ID就最先执行。一般按缩进长度来判断，缩进最大的最先执行，如果有2行缩进一样，那么就先执行上面的。）
Operation：当前操作的内容。
Name：操作对象
Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集行数。
Bytes：表示执行该步骤后返回的字节数。
Cost（CPU）：表示执行到该步骤的一个执行成本，用于说明SQL执行的代价。
Time：Oracle 估计当前操作的时间。
4.谓词说明：
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."MGR" IS NOT NULL)
4 - access("A"."EMPNO" = "B"."MGR")
Access: 表示这个谓词条件的值将会影响数据的访问路劲（全表扫描还是索引）。
Filter：表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。
在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。
5、 动态分析
如果在执行计划中有如下提示：
Note
------------
-dynamic sampling used for the statement
这提示用户CBO当前使用的技术，需要用户在分析计划时考虑到这些因素。 当出现这个提示，说明当前表使用了动态采样。我们从而推断这个表可能没有做过分析。
这里会出现两种情况：
（1） 如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，也可以或者正确的执行计划。
（2） 如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而可能导致错误的执行计划。
表访问方式
1.Full Table Scan (FTS) 全表扫描
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
index unique scan --索引唯一扫描
通过唯一索引查找一个数值经常返回单个ROWID，如果存在UNIQUE或PRIMARY KEY约束（它保证了语句只存取单行的话），ORACLE
经常实现唯一性扫描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
index range scan --索引局部扫描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况是在谓词(WHERE 限制条件)中使用了范围操作符号（如>, < <>, >=, <=,BWTEEN)
index full scan --索引全局扫描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.
index fast full scan --索引快速全局扫描，不带order by情况下常发生
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
index skip scan --索引跳跃扫描，where条件列是非索引的前提情况下常发生
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据方式
连接方法（join）
连接是指从一个或者多个表中检索数据，oracle主要有3中连接方法:嵌套循环连接(Nested Loop Joins)、哈希连接（Hash Joins）和排序合并连接（Sort Merge Joins）。 在select语句中，连接方法至关重要，它直接影响查询的性能。
Nested Loop Joins：适合小量数据，oracle首先选择一张表作为连接的驱动表（Driving Table），这张表也被叫做外部表（outer table），其他表被当做内部表（inner table），内部表也叫做被驱动表，对于驱动表中的每行，oracle将访问内部表中的所有行。可以快速返回查询结果的前几行，因为是语句边执行边返回结果。
hash Joins：用于处理大数据量的数据集，特别是一个大表和一个小表的连接，oracle从两个表中选择一个较小的表，按照连接关键字（Join Key），在内存中建立哈希表，因此哈希表中较小的表适合放在内存中，然后oracle扫描大表，从中发现可以被连接的行。
排序合并连接（Sort Merge Joins),原理是先按照连接关键字进行排序，再把排序好的两个结果集合并在一起，所以，如果一个结果集已经排好序，此方式的速度将比‘哈希连接’的方法快。
SQL优化的目标
：减少一条sql语句的执行时间。
1.减少consistent gets的值；
2.减少db block gets 的值；
3.减少排序，尤其是磁盘排序（disk sorts）；
4.减少递归调用（recursive calls）；
5.减少执行代价（cost）。
SQL优化措施
1.收集统计信息；
2.重构索引；（删除多余的索引，删除组成索引列中无用的列、改变索引中列的书序，使之和where条件中列的顺序相同）
3.重构数据；（分区或聚簇）
4.禁用约束和触发器；
5.尽量使用相同的SQL语句；
6.改写SQL语句。
自动收集统计信息：
begin
dbms_auto_task_admin.enable(
client_name =>'auto optimizer stats collection',
operation =>NULL,
window_name =>NULL);
END;
关闭自动收集统计信息的命令：
begin
dbms_auto_task_admin.disable(
client_name =>'auto optimizer stats collection',
operation =>NULL,
window_name =>NULL);
END;
手动收集统计信息：
execute dbms_stats.gather_index_stats('ITEM','INDEX_ID');--收集索引的统计信息
execute dbms_stats.gather_table_stats('ITEM','Studentbook');--收集表的统计信息
execute dbms_stats.gather_schma_stats('ITEM');--收集用户拥有的所有对象的统计信息
execute dbms_stats.gather_dictionary_stats;--收集数据字典对象的统计信息
execute dbms_stats.gather_database_stats;--收集数据库中所有对象的统计信息
两个SQL相同必须满足的条件：
空格、大小写、换行、注释、表名、关键字、值相同；
引用的对象必须属于相同的模式；
如果使用绑定变量，绑定变量的名称、类型、长度相同。
改写SQL语句
- 要从执行计划中仔细查看、分析SQL语句中的每个表是否被高效地访问；
- 避免在where子句中的列上使用函数，这样会使列上的索引失效。如果要在列上使用函数，则需要创建函数索引。
- 尽量使用等价连接；
- 排序列上应该建立索引；
- 保持排序列和索引列次序一致；
- 尽量保持where子句中列的顺序和索引列的顺序一致；
- 尽量少用嵌套查询。如果非要使用，请用not exist 代替not in 子句；
- 用多表连接代替EXISTS子句；
- 选择合适的连接join；
- 在连接中，注意表的连接顺序。选择好驱动表和被驱动表；
- 大量的排序操作影响系统性能，所以尽量减少排序操作，group by、order by、rollup、distinct等都会产生排序。少用distinct,用exists代替distinct；
- 避免使用数据类型自动转换功能，自动数据类型转换会增加额外的系统开销，还会使索引无效；
- 启用并行执行，并行执行机制使多个服务并行的执行一条SQL语句，这是oracle的强大之处；
- 连接符OR、IN、AND以及=、<=、>=等前后都加一个空格，这是一个良好的书写习惯，防止产生两条不同的SQL语句；
- where子句中尽量使用变量，这样可以减少语句的解析时间。
- oracle的关键字要大写（内置函数名、SQL保留字大写，其他的字符都用小写。这样的目的也是为了防止产生两条不同的SQL语句；
- LIKE用于模糊查询，如果百分号在开头，将不会用到索引。
- %zhang 、%zhang% 这种情况下都不会用到索引；
- 要避免使用结构复杂的视图，结构复杂的视图隐含查询性能问题；
- 如果可以，把查询结果放在一个中间表中，程序需要时直接查询中间表，这是改善查询最有效的手段之一；
- 考虑使用物化视图（Materialized Views)代替结构复杂的查询；
- 减少对表的扫描，可以把多个扫描合并成一个扫描。
- 使用带returning子句的insert、update、delete语句完成选择数据和修改数据两个功能，减少对数据库的调用；
- 尽量让一条SQL语句完成更多的功能；
- 在where子句中使用IS NULL 或者NOT NULL将会使索引失效。
- 不要在where子句中使用连接符|| ，连接符将忽略索引。
Hint 提示是嵌入到SQL语句中的一些指令，用于改变SQL语句的执行计划。如果一条SQL语句执行的是索引扫描，我们可以使用“提示”强迫它进行全表扫描。（尽管没有必要）
1提示的分类
有关优化目标的提示
有关访问路径的提示
有关查询转换的提示
有关连接操作的提示
有关连接顺序的提示
有关并行执行的提示
2提示的语法
/*+hint(argument1,argument2)*/，提示以/*+开始，以*/结束，中间是提示指令，提示中不能出现模式名。
3常用提示
ALL_ROWS 表示以吞吐量为优化目标 SELECT /*+ALL_ROWS*/ * FROMtc;
FIRST_ROWS(n) 表示选择基于开销的优化方法，以响应时间为优化目标，n表示数据行数， SELECT /*+FIRST_ROWS*/ * FROM tc;
INDEX 该提示使oracle执行索引扫描
USE_MERGE 用于指示两个表使用“排序合并连接”方式进行连接JOIN:
1: SELECT /*+ USE_MERGE(a b) */ a.op_no,a.fnm,b.menu_id,b.code
2: FROM tb_user a, tb_user_grant b
3: WHERE a.op_no = b.op_no;
USE_HASH 用于提示两个表使用“哈希连接（hash joins）的方式进行连接：
USE_NL用于提示两个表使用“嵌套循环连接（Nested Loops Join）”的方式进行连接：
PARALLEL PARALLEL（表名，并行服务器进程的数量）。并行执行使Orace启动多个并行进程来执行同一个SQL语句。
1: SELECT /*+ FULL(a) PARALLEL(a,4) */ a.op_no,a.fnm
2: FROM tb_user a;
本例中启动4个并行服务器进程，对表tb_user 执行全表扫描。
PARALLEL_INDEX 语法是：PARALLEL_INDEX（表名，索引的名字，并行服务器进程的数量）。
1: SELECT /*+ PARALLEL_INDEX(t,kt,3) */ op_no
2: FROM tb_user_grant t
3: WHERE t.menu_id like '027%';
本例启动3个并行服务器进程来完成索引kt的扫描。
扫一扫
1276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
