sql的执行过程和优化

***********************************************************************************************************************************************************************************

运行一个语句的过程:

1、客户端输入sql语句，

2、sql语句通过网络到达数据库实例，然后server process 接受sql语句

3、server process对其进行一个hash运算，得到一个值，拿着这个值去到shared_pool中去找有没有。有--软解析直接拿到执行计划，没有--就硬解析（软解析和硬解析都是server process这个进程去做的）

4、得到执行计划后，server process根据执行计划去找（修改）对应的块，然后返给用户

***server process（前台进程）的一部分作用：将块从dbf中读到buffer_cache中，将buffer_cache中的数据读出来返给用户（select），修改buffer_cache中的块（update、insert、delete），并且写日志到redolog_buffer中，他不负责写回磁盘

-----也就是说server process主要负责对sga（shared_pool、buffer_cahce、redolog_buffer）的读写工作，还有就是把dbf中的块读到buffer_cache     -----这句话牢记吧

为什么server process不负责将数据写回磁盘呢？因为server process是和用户打交道了，所以要及时的响应用户，如果server process也负责去写磁盘的话，那用户的响应速度就会变慢，用户就会觉得数据库很慢；而且用户也不关心你什么时候把数据写回到了磁盘，所以这些事儿就交给后台进程去做了，这也是oracle设计的巧妙之处，所以oracle把一些后台能够做的事情尽力不给server process去做，提高用户的体验，这也是为什么oracle要设计后台进程的原因，server process很忙，用户就会感觉数据库很慢

****解析过程****

1、进行语法分析,看语法是否有错误

2、语意分析, 看对象是否村子, 权限是否符合

3. 将 SQL 语句经过hash 算法后得到一个值hash_value
4. 如果该值在内存中存在,那么叫命中执行软分析
5. 如果该值不存在,执行硬分析
6. 如果有视图,将视图的定义取出
7. 进行 SQL 语句的自动改写,如将子查询改写为连接
8. 优选最佳的执行计划   (这步是最消耗资源的，为什么要避免硬解析，就是因为这步耗资源)
9. 变量的帮定
10. 运行执行计划（ server process进程去执行执行计划去找对应的块）
11. 将结果返回给用户
如果是软分析,直接运行9 以后的步骤。从上面的过程看出,软分析比硬分析节约了很多的开

销。

sql大体执行过程：1、server process 拿着这条sql去解析，得到执行计划     2、根据执行计划去找对应对应数据（对应block）

************************************************************************************************************************************************************************************

优化SQL 语句的过程:
1. 找到消耗资源高的 SQL,或者运行时间大于6 秒的SQL。
2. 列出该语句的执行计划
最优计划的选择方式
列出执行计划集
估算每个计划的代价（cpu,io,网络）
选择代价(cost)最小的执行计划
3. 改变执行计划
4. 运行新的语句进行测试。

我们的如何查看执行计划呢?
我们主要关注四项:
1. COST 值的高低,一般说来COST 越高的语句运行越慢。Cost 是一个相对的值，只对本机，本数据库有可比性，如果不同主机，不同配置的数据库，不同版本的数据库之间的cost没有可比性。

2. 表之间连接的模式,如果连接模式不正确会引起灾难性的后果,比如大表之间的嵌套循环就会有问题。但也不是绝对的，因为hash 连接对不等连接是不可以的，如果你的语
句中含有not in 语法，有的时候循环嵌套可能会更好的完成任务。数据库是活的，不能一成不变的看待问题。

3. 是否使用了索引,索引和全表扫描是死对头,对立的,二者只能取其一。有的时候使用索引快，有的时候全表扫描快。具体情况具体分析。

4. 排序的使用,我们可以通过索引来避免排序。也可以改写 SQL 达到不排序的目的。

我们如何改变执行计划呢?
1. 改变数据库的版本,版本越高sql 的引擎越智能。执行计划越优秀。
2. 改变数据库的初始化参数,比如内存,IO,索引等参数
影响执行计划的参数有很多，下面只是部分。
OPTIMIZER_FEATURES_ENABLE=10.1.0
optimizer_mode=all_rows
PGA_AGGREGATE_TARGET
DB_FILE_MULTIBLOCK_READ_COUNT
CURSOR_SHARING
3. 收集统计信息，系统的，表的，索引的，列的统计信息都对执行计划有本质的影响。
4. 增加或减少索引，在不改应用的 SQL 前提下，索引对数据库的性能有巨大的影响。
5. 改写原来的 SQL，使用高级SQL 语法。达到同样的效果，但性能会有极大的提高。
6. 使用强制 HINT 来指定执行计划。一般用于执行计划的稳定和并行操作。

定位消耗资源多的sql语句

通过v$sqlstats字典我们可以定位物理读、执行次数、逻辑读、cpu消耗时间、消耗的时间、排序次数等一系列的指标：

select max(DISK_READS), max(EXECUTIONS), max(BUFFER_GETS), max(CPU_TIME), max(ELAPSED_TIME), max(SORTS) from v$sqlstats;

Select sql_id, sql_fulltext from v$sqlstats where BUFFER_GETS>####;

和sql有关的字典还有：v$sqltext   v$sqlarea---这张字典也可以

v$sqlstats和v$sqlarea有的数不太一样...

查看成本（cost）最高的sql：

select optimizer_cost, executions,sql_text from v$sqlarea 

where optimizer_cost > (select  max(optimizer_cost)/5 from v$sqlarea);

书写高效sql的基本原则：

在where 中用 = 关系运算时, 避免用函数在关系运算中，除非你使用函数建立索引
Where ename=‘king’
Where upper(ename)=‘KING’
尽量不要隐式转化数据类型，数据类型一定要匹配
尽量将一句SQL 分成多个语句完成，With 语句可以使语法更加可读，性能更好，更加智能。
使用视图的注意事项：复杂视图的连接要小心，尤其有外键的时候；考虑使用物化视图。

尽量减少访问数据的次数，使用高级分组rollup,cube。
使用case 语句。
使用存储过程，在程序中使用RETURNING 子句。
DELETE FROM employees WHERE job_id = 'SA_REP' AND hire_date + TO_YMINTERVAL('01-00')
< SYSDATE RETURNING salary INTO :bnd1;
避免再次访问原来的表来获得数据,减少了工作量。

基于成本（cost）的优化

要使SQL 语句的执行计划最优化，数据库必须知道关于数据库的详细统计数据
表（行数，块数，平均行长,hwm）
列（不同的值数，null 值的行数，柱状图）
索引（叶子数，索引深度，索引因子）
系统（IO 的性能和应用，CPU 的性能和应用）
Cost 是一个相对的值,和主机的环境有很大的关系,不同的数据库cost 没有可比性。