基于CBO的SQL优化和Oracle实例优化
1.2 语句优化阶段
这是这3个步骤中最关键的一个地方了,oracle默认使用的是基于CBO来选择最好的执行计划,你可能会问,啥是CBO?,好吧!CBO其实就是基于成本的优化程序,也就是会将对成本消耗评估,将消耗的cpu执行周期、内存、I/O速率等资源转换为时间成本。时间最少的当然就是最好的了。例如Oracle的解析也分为硬解析和软解析, 对于不同的oracle版本,硬解析的次数也不同,在oracle12中,硬解析的次数为19次,在oracle11g中硬解析的次数为59次。
在做这个阶段,Oracle会将语法分析树转换为一个逻辑查询,然后将逻辑查询转换为物理查询计划。而且这个物理查询计划还不止一种,因为优化器往往会生成好几个有效的查询计划,然后会根据这些计划来做出成本消耗评估。注意,这里只是做义工评估,并没有把每一种计划都去执行一遍。那么oracle是依据什么来评估的呢?一般会按照如下因素进行评估:a、查询中涉及的连接操作以及连接顺序 b、操作执行的算法 c、数据读取的方式,例如读内存还是磁盘 d、查询各操作之间的数据传递方式。
一条sql语句进来,到最终对sql语句生成执行计划之前,需要经历一个过程,如下图所示(嗨呀,随手画的图, 画得比较丑呀!)
1.3 查询执行
查询执行时最简单的一个步骤了,只需要将刚才步骤2的物理查询计划进行执行即可,然后将处理的数据返回给用户。
二、基于成本的优化
2.1 优化方式
优化方式的含义是为满足SQL优化的目标而选择的优化方式,在默认情况下,是以SQL语句的吞吐量作为优化的目标。
下面提供三种优化方式来满足不同的查询需求:
1、All_Rows:默认方式,优化的目标是实现查询的最大吞吐量
2、FIRST_ROWS_n:优化输出查询的前n行数据,目标是满足快速的响应需求
3、FIRST_ROWS:使用CBO的成本优化尽快输出查询的前几行数据,满足最小响应时间的需求
oracle提供了三种级别上的优化:实例级、会话级、语句级。
查询当前数据库的CBO优化方式:
SQL> show parameter optimizer_mode;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
可以看出我当前的数据库的优化方式是实现查询的最大吞吐量。
2.2 优化器工作过程
CBO通过4个步骤步骤完成SQL的优化
1、根据统计数据转换SQL语句 : 也就是指CBO认为转换后的语句查询会更高效,所以将你的sql语句转换为另外一种形式,例如你写的OR转换为 UNION ALL,将between转换为>=和<=等。
2、根据资源情况选访问路径:指访问某个路径的数据所消耗的资源。
3、根据统计数据选择连接方法: 如果涉及多个表,CBO会根据统计数据以及表的键的信息来选择连接的方法,在多个连接方法中选择计算成本最低的一个作为最佳连接方法。
4、确定连接次序:指涉及的数据行的数目来确定最好的连接次序。
2.3 统计数据
--查看gather_stats_job的当前运行状态
-
- select job_name,state,owner from dba_scheduler_jobs;
--查询用户scott拥有表的统计分析情况:sample_size表示采样行数
select last_analyzed,table_name,owner,num_rows,sample_size from dba_tables where owner='SCOTT';
--为模式scott的所有表统计数据(手工收集)
execute dbms_stats.gather_schema_stats(ownname => 'scott');三、主动优化SQL语句
3.1 优化查询
1、优化查询:explain,对于使用索引查询,使用like的时候只有%不在第一个位置才会有效,使用多列查询的时候,只有查询条件中使用了这些字段中的第一个字段时,索引才会被引用,or查询条件时,前后两个条件中的列都是索引时,查询中才会使用索引。
2,优化数据库结构,将字段很多的表分解为多个表,增加中间表,增加冗余字段,优化插入速度,禁用唯一性检查,使用批量插入,禁止外键检查,禁止自动提交,优化表optimize
3,优化数据库的服务器,硬件:内存,io, 优化参数。
4、使用绑定变量:我们都知道,在Oracle中是分为了硬解析和软解析的,在SGA中,共享池就是存放解析后的SQL语句,此时的共享池包含SQL语句的最终执行计划。如果有相同的是SQL查询语句,就不需要再次解析SQL语句了,而是直接从共享池中执行SQL语句的执行计划。使用共享池就是为了避免硬解析的发生,因为每次去进行硬解析的时候都需要重新去分析语句的语法语义,然后通过CBO优化生成的最终执行计划,这样就很消耗CPU的资源。使用绑定变量,也就是我们在java开发中常见的给一个sql语句加一个?来执行,然后再传入参数。
例如: select ename,job,sal from scott.emp where deptno=?
然后我们再把参数传入,这样不仅可以防止SQL注入,而且可以对SQL进行优化。
5、消除子查询:对于一些嵌套的子查询,将嵌套的sql语句,例如:
select * from scott.emp e1 where e1.sal>
(select avg(sal) from scott.emp e2 where e2.deptno=e1.deptno);
这样的一条sql语句每次需要执行N*M次操作,具体数值你可以使用下文中是sql跟踪进行性能分析。
优化后的语句为:
select *
from scott.emp e1,(select e2.deptno ,avg(e2.sal) avg_sal from scott.emp e2 group by deptno) d
where e1.deptno=d.deptno and e1.sal >d.avg_sal
优化后的这条sql只需要进行N+M此操作即可,其伸缩性更强,计算结果也不会呈指数增长。虽然初步看起来优化后的sql语句似乎更长一点,如果你在质疑到底对不对,你可以使用我们接下来讲到的SQL语句分析工具来进行对比,大家可以通过其执行计划来验证。
3.2 SQL语句优化工具
使用explain plan for 指令来获得SQL语句的执行计划,所以我们先来创建一个执行这个指令所需要的表,在oracle的安装目录中,我们需要找到utlxplan.sql这个文件,然后执行。我这里的这个文件的路径位于E:\oracle\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql,执行命令如下:
来源: http://blog.csdn.net/sdksdk0/article/details/78745507