e-works数字化企业网  »  文章频道  »  基础信息化  »  终端和服务器

基于CBO的SQL优化和Oracle实例优化

2017/12/13    来源:CSDN    作者:朱培      
关键字:CBO  SQL优化  
SQL优化是数据优化的重要方面,本文将分析Oracle自身的CBO优化,即基于成本的优化方法。Oracle为了自动的优化sql语句需要各种统计数据作为优化基础。
    SQL优化是数据优化的重要方面,本文将分析Oracle自身的CBO优化,即基于成本的优化方法。Oracle为了自动的优化sql语句需要各种统计数据作为优化基础。外面会通过sql的追踪来分析sql的执行过程,消耗的资源信息。对于数据库的性能问题往往是在系统部署一段时间之后出现的,即大量用户开始使用该系统,系统的数据处理量和各种计算复杂性增加的时候,这个时候往往会追溯到系统的初始设计阶段,所以我们还是要在编码阶段就编写高效的sql语句。我在网上看到了很多关于sql优化的文章,但是不尽人意,有的很笼统的描述有的根本还是错误的方法,所以我重新将我的学习过程分享出来。
 
    一、SQL查询处理过程详解
 
    查询处理与查询优化是两个相关联的概念,查询处理时执行SQL语句获取数据的过程,而查询优化是通过分析SQL语句以及其他资源获得最佳执行计划的过程。在这里最佳的执行计划。我指的是消耗资源最少的计划,例如包含有数据库服务器的CPU和系统I/O。一条SQL 的执行分为3个阶段:语法分析阶段、语句优化阶段、查询执行阶段。
 
    1.1 语法分析阶段
 
    语法分析是在SGA中完成的,(SGA是指系统全局区,包括数据库缓冲区、重做日志缓冲区、共享池、java池、大池、流池),在这里将sql语句分解为关系代数查询,也就是通过这些关系代数查询来验证这个sql的语法有没有写错,关键字是否正确等。
 
    1.2 语句优化阶段
 
    这是这3个步骤中最关键的一个地方了,oracle默认使用的是基于CBO来选择最好的执行计划,你可能会问,啥是CBO?,好吧!CBO其实就是基于成本的优化程序,也就是会将对成本消耗评估,将消耗的cpu执行周期、内存、I/O速率等资源转换为时间成本。时间最少的当然就是最好的了。例如Oracle的解析也分为硬解析和软解析, 对于不同的oracle版本,硬解析的次数也不同,在oracle12中,硬解析的次数为19次,在oracle11g中硬解析的次数为59次。
 
    在做这个阶段,Oracle会将语法分析树转换为一个逻辑查询,然后将逻辑查询转换为物理查询计划。而且这个物理查询计划还不止一种,因为优化器往往会生成好几个有效的查询计划,然后会根据这些计划来做出成本消耗评估。注意,这里只是做义工评估,并没有把每一种计划都去执行一遍。那么oracle是依据什么来评估的呢?一般会按照如下因素进行评估:a、查询中涉及的连接操作以及连接顺序  b、操作执行的算法  c、数据读取的方式,例如读内存还是磁盘  d、查询各操作之间的数据传递方式。
 
    一条sql语句进来,到最终对sql语句生成执行计划之前,需要经历一个过程,如下图所示(嗨呀,随手画的图, 画得比较丑呀!)
 
基于CBO的SQL优化和Oracle实例优化
 
    1.3 查询执行
 
    查询执行时最简单的一个步骤了,只需要将刚才步骤2的物理查询计划进行执行即可,然后将处理的数据返回给用户。
 
    二、基于成本的优化
 
    2.1 优化方式
 
    优化方式的含义是为满足SQL优化的目标而选择的优化方式,在默认情况下,是以SQL语句的吞吐量作为优化的目标。
 
    下面提供三种优化方式来满足不同的查询需求:
 
    1、All_Rows:默认方式,优化的目标是实现查询的最大吞吐量
 
    2、FIRST_ROWS_n:优化输出查询的前n行数据,目标是满足快速的响应需求
 
    3、FIRST_ROWS:使用CBO的成本优化尽快输出查询的前几行数据,满足最小响应时间的需求
 
    oracle提供了三种级别上的优化:实例级、会话级、语句级。
 
    查询当前数据库的CBO优化方式:
 

 查询当前数据库的CBO优化方式

 
    可以看出我当前的数据库的优化方式是实现查询的最大吞吐量。
 
    2.2 优化器工作过程
 
    CBO通过4个步骤步骤完成SQL的优化
 
    1、根据统计数据转换SQL语句  : 也就是指CBO认为转换后的语句查询会更高效,所以将你的sql语句转换为另外一种形式,例如你写的OR转换为 UNION ALL,将between转换为>=和<=等。
 
    2、根据资源情况选访问路径:指访问某个路径的数据所消耗的资源。
 
    3、根据统计数据选择连接方法: 如果涉及多个表,CBO会根据统计数据以及表的键的信息来选择连接的方法,在多个连接方法中选择计算成本最低的一个作为最佳连接方法。
 
    4、确定连接次序:指涉及的数据行的数目来确定最好的连接次序。
 
    2.3 统计数据
 
    --查看gather_stats_job的当前运行状态
 
 查询当前数据库的CBO优化方式
  
    --查询用户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语句,例如:
 
消除子查询:对于一些嵌套的子查询,将嵌套的sql语句
 
    这样的一条sql语句每次需要执行N*M次操作,具体数值你可以使用下文中是sql跟踪进行性能分析。
 
    优化后的语句为:
 
消除子查询:对于一些嵌套的子查询,将嵌套的sql语句
 
    优化后的这条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,执行命令如下:
 
    表已创建
 
    查看这个表结构:
 
基于CBO的SQL优化和Oracle实例优化
 
    然后我们通过这个命令来分析SQL语句的执行:
 
    SQL> explain plan for
  
    2  select count(*) from scott.emp;
 
    Explained
 
    我们来查看一下plan_table表中的sql语句执行计划信息:
 
基于CBO的SQL优化和Oracle实例优化
 
    我们可以看到,这是一个全表扫描的,表明是emp。
 
    如果我们想要更深入的对这条sql进行分析怎么办,例如想要知道这个的访问对象、消耗的CPU等信息。那么我们可以启用SQL追踪。
 
    1、使用autotrace指令
 
    使用该指令可以跟踪SQL语句并分析其执行步骤,统计信息如物理读数据量、磁盘和内存排序数据量。
 
    具体的操作命令如下:
 
基于CBO的SQL优化和Oracle实例优化
基于CBO的SQL优化和Oracle实例优化
 

责任编辑:李欢
本文为授权转载文章,任何人未经原授权方同意,不得复制、转载、摘编等任何方式进行使用,e-works不承担由此而产生的任何法律责任! 如有异议请及时告之,以便进行及时处理。联系方式:editor@e-works.net.cn tel:027-87592219/20/21。
e-works
官方微信
掌上
信息化
编辑推荐
新闻推荐
博客推荐
视频推荐