SQL执行详解.docx

SQL执行原理1.SQL执行过程1解析（判断对象是否存在，是否有权限查询, 语义解析（复杂的SQL简单化）检查缓存中是否有相同SQL等等）2优化（CBO确定优化模式，确定访问路径，联接顺序，过程中通过很多综合因素估算出各种方式的资源消耗，选取消耗最小为优）3生成QEP（Query Execution Plan：查询执行计划）4执行QEP，返回查询结果基本流程如下图：（常用到的性能视图：V$SQLAREA:SQL文本，SQL ID，内存使用信息，解析次数，磁盘读写情况，缓冲区数据读取信息，优化器消耗CPU时间消耗…，信息比较全面;V$SQL_PLAN:执行计划相关信息，通过上一视图中的SQL_ID在此视图中可以看出执行计划的信息，包括使用哪些优化模式，CPU消耗，IO消耗V$SQL，与v$sqlarea视图类似,此视图中可以看到详细的SQL执行资料V$SQLSTATS：此视图记录着sql的状态信息，解析次数，磁盘的读次数，直接写次数，等待时间等等信息）V$SQL_PLAN_STATISTICS_ALL:此视图是综合V_$SQL_PLAN_STATISTICS和V$SQL_PAN两个视图得来的关于执行计划的详细信息V$SQLSTATS：此视图记录了SQL的状态，当前的一些等待时间参数，及内存消耗参数V$SQL_OPTIMIZER_ENV：CBO（基于成本的优化器）的初始化参数，影响执行计划的重要参数V$SYS_OPTIMIZER_ENV：影响实例的系统参数，修改它也会影响到执行计划，相当于整个系统环境的变化优化SQL解析1．使用绑定变量，减少硬解析绑定变量，用用户的输入或参数作为一个占位符绑定进SQL语句，当值发生变化时，ORACLE把这些SQL语句当作一条相同的语句对待，只进行一次硬解析，然后再执行则进行解析，例：执行@1：select * from emp where empno=7698;上面结果可以看到，执行时有一次硬解析，接下来换个empno试下执行@2：select * from emp where empno=7782;上面两个可以看到两个SQL语句其他部分都相同，只有EMPNO值不同，生成的执行计划不同，造成两次硬解析，下面来看绑定变量例子执行@3：select * from emp where empno=:empno;先定义empno变量：Variable empno numberExec :empno:=值执行SQL：更换变量：exec :empno:=新值执行@：select * from emp where empno=:empno;从上面看到绑定变量可以减少硬解析的数量，效果如何，看下面的例子，比较一下执行的效率：上图为未绑定变量10000条查询使用了，4.172秒，看一下执行计划的trace文件截图这个汇总信息可以看到10000次都为硬解析，所以查询时间较长，每一次在解析的时候都在library cache(库缓存)中找不到，导致硬解析，下面看一下执行计划部分信息截图下面绑定变量看看是怎样的情况用时占了没绑定变量前的1/10不到，下面是TRACE文件信息这时的硬解析消失了，绑定变量后当作一个语句在执行，下面是执行计划的部分信息硬解析次数为0，绑定变量后CPU消耗，时间消耗远小于绑定之前，这是在小数据量表上查询的对比，性能相差不太大下面从客户端方式，如果一个用户在查询一个大表时，不使用绑定变量和使用绑定变量的区别（测试表：create table t as select object_name,object_id from dba_objects;）（上面表的有50317行数据，复制数据：insert into t select * from t,复制后有100634行数据）未绑定变量情况：绑定变量情况：在10W行数据表中，查询里面的5W条数据，未绑定时用时96.25秒，绑定后用时1.656秒，相差接近60倍优化解析二，尽量减少软解析，配置session_cached_cursors,高速缓存游标（父游标装SQL文本，子游标装环境信息，执行计划，查询结果等等），经常被用的sql会被缓存起来来减少软解析，找到相同游标则直接执行，这就是软软解析（DBA性能调整参数）优化访问路径索引扫描包括：1索引唯一扫描(index unique scan):只返回单行数据时用到此扫描（查询列有主键约束或者唯一约束时）例：create table t1 as select * from scott.emp;首先：创建普通索引时，图中可以看到创建普通索引，虽然只返回一行数据但没有主键约束也没有唯一约束时，oracle不使用索引唯一扫描下面看一下，重新建立唯一索引时图中看到创建唯一索引后，oracle使用了唯一索引扫描下面再来