***********************************************************************************************************************************************************************************
运行一个语句的过程:
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_value4. 如果该值在内存中存在,那么叫命中执行软分析
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 没有可比性。