ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL 优化原理

SQL 优化原理

原创 Linux操作系统 作者:Devean 时间:2011-07-09 10:53:01 0 删除 编辑

=================================================================================

       PGA

=======================

在专有模式下,Oracle会为每个用户的连接请求分配一个服务进程(Server Process)为其服务,而为这个服务器进程分配的内存就叫做PGA

              ----------------------------------------------------------------------------------------------------------------------------

              PGA的官方名称是程序全局区(Program Global Area),也有叫做进程全局区(Process Global Area)

              叫什么并没有什么本质的区别,重要的是要知道:

              --------------------------------------------------------------

                            1.PGA是一块内存区域             

                            ------------------------------------------------------------------------------------------

                            2.这块内存区域包含的是和特定服务器进程相关的数据和控制信息。

                            ------------------------------------------------------------------------------------------

每个进程都拥有自己的PGA,并且是进程私有的。这也就是意味着,不同进程不会共享PGA内容。因此,和SGA不同,不需要Latch,Lock等保护机制,永远不会发生竞争。

              -----------------------------------------------------------------------------------------------------------------------------

              总的来说,PGA包含的是进程使用的操作系统信息和进程专有的私有状态信息。

              -----------------------------------------------------------------------------------------------------------------------------

              PGA主要包括两部分内容:

              -----------------------------------

                            1.Session Area

                            ----------------------

                                   Session Area这部分包括会话特有信息,比如说会话的跟踪信息,使用Alter Session

                                   命令修改的环境参数,打开的DB Links,会话拥有的角色等。

                                   -----------------------------------------------------------------------------------------------------------

                            2.Private SQL Area

Server Process每执行一个SQL语句时,都需要伸请一个Private SQL Area,这也就是通常所说的Cursor

                                   -----------------------------------------------------------------------------------------------------------

                                   这个区域又可分为两个部分:

                                   ------------------------------------

                                                 1.Persistent Area

                                                 -----------------------

                                                        这个区域存放的是如绑定变量,数据类型转换等Cursor的结构

                                                        信息,一旦Cursor被关闭时,这块区域就会被释放。

                                                        -----------------------------------------------------------------------------------

                                                 2.Run-Time-Area

                                                 -----------------------

                                                        这个区域在SQL运行过程中使用,其大小依赖于SQL语句的操作

                                                        方式(Sort或者Hash Join),要处理的数据行和每行记录的大小。

                                                        也就是要处理的数据越多,执行处理的步骤越多,这块区域的空

                                                        间需求就越大。   

                                                        如果是DML语句,SQL语句执行完毕这块区域就会释放;而如果

                                                        是查询语句,则只有在整个记录集都传递给用户后,或者用户   

                                                        取消查询后这部分空间才会被释放。

                                                        -------------------------------------------------------------------------------------

                                                        一旦SQL语句处理完毕后,Run-Time-Area就被释放,而Private

                                                        SQL Are部分就可以被其它SQL语句重用,每次对Private SQL

                                                        Area的重用都必须重新初始化。

                                                        -------------------------------------------------------------------------------------

                                   注意:不要把Private SQL AreaShared Pool中的Public SQL Area相混淆。

                                        ------------------------------------------------------------------------------------------------

                                        Public SQL Area中保存的是SQL语句可以共享的信息,如执行计划。

                                        ------------------------------------------------------------------------------------------------

Private SQL Area中保存的是每个Session特有的信息,比如当前获取的行数信息。

                                       ---------------------------------------------------------------------------------------------------

                                   Server Process在执行SQL语句前,必须在Share Pool中定位语句的Share SQL Area

                                   ---------------------------------------------------------------------------------------------------------

PGA中的活动也与这个过程类似,也必须在PGA中定位该SQL语句的Private SQL Area。如果定位失败,服务器必须为其分配一个Private SQL Area,并进行初始化。这个过程也是一个CPU密集型操作。

                                   ----------------------------------------------------------------------------------------------------------

PGA中可以同时包含多个的Private SQL Area,Server Process也会尽量减少对已有PrivateSQL Area的重新初始化,这一点和Share Pool的功能有异曲同工之处,其目的就是在Server Process这一侧尽量增加重用。一个大的PGA可以避免Private SQL Arear的置换,相应地就会减少CPU开销,因而可以提高性能。当然,在分配PGA内存和整体性能之间还需要一个折中的考虑。

                                   ----------------------------------------------------------------------------------------------------------

因此,对于PGA的调整实际就是对Private SQL Area中的Run-Time-Area区域的调整。

                                   ----------------------------------------------------------------------------------------------------------

Oracle9i之前,对于PGA的配置需要手工进行,DBA需要定义的参数包括SORT_AREA_SIZEHASH_AREA_SIZE

                                   -----------------------------------------------------------------------------------------------------------

Oracle9i开始,PGA实现了自动调整,这部分区域就有了一个新的名称:SQL工作区域(SQL WORK AREA),对应出现的PGA自动调整参数WORK_AREA_SIZE_POLICY也就因此得名。

                                   ----------------------------------------------------------------------------------------------------------

                     1:V$PROCESS视图查看PGA的总体分配情况:

                     --------------------------------------------------------------

                            SQL>select spid,program,

                                         pga_max_mem max,

                                        pga_alloc_mem alloc,

                                        pga_used_mem used,

                                        pga_freeable_mem free

                                        from v$process

                                        where spid in (

                                             select spid  from v$process where addr in(

                                          select paddr from v$session where sid in (

                                          select distinct sid from v$mystat

                                                 )

                                                 )

                                                 );

                            ---------------------------------------------------------------------------------------------------

                     2:V$PROCESS_MEMORY视图进一步查看PGA各个区域分配的大小信息:

                     ---------------------------------------------------------------------------------------------------------

                            SQL>select  p.program,p.spid,

                                          pm.allocated,pm.used,pm.max_allocated

                                          from v$process p,v$process_memory pm

                                          where p.pid = pm.pid

                                          and

                                          p.spid in(

                                          select spid from v$process where addr in(

                                          select paddr from v$session where sid in(

                                          select distinct sid from v$mystat

                                          )

                                          )

                                          );

                            ----------------------------------------------------------------------------------------------------

       SQL语句的执行过程

================================

              1.语法分析(Syncatic)

                     检查SQL语句的语法是否正确

              -----------------------------------------------------------------------------------------

              2.语义分析(Semantic)

                     检查SQL语句涉及的对象是否存在

              ------------------------------------------------------------------------------------------

              3.Library Cache中检查语句的Cursor是否存在。

              ------------------------------------------------------------------------------------------

如果存在可以重用的Cursor(包括Parent CursorChild Cursor),则把PGAPrivate SQL Area和其做关联,然后直接跳到第8步。这种解析叫做软解析(Soft Parse)

                --------------------------------------------------------------------------------------------------------------------------

                如果没有就申请一个Parent CursorParent Cursor就是用来保存SQL语句的。

                --------------------------------------------------------------------------------------------------------------------------

              4.View Merge

                     如果用户的语句中用到了视图,把视图语句合并到用户语句中

              ------------------------------------------------------------------------------------------

              5.Statement Transformation

                     Complex SQL转换成Simple SQL,比如展开子查询

              ------------------------------------------------------------------------------------------

              :某些资料也把(4)(5)这两步叫做逻辑优化(Logical Optimization)      

              -------------------------------------------------------------------------------------------

              6.Optimization

                     如果是CBO,则根据对象的统计信息确定最佳访问路径

              ------------------------------------------------------------------------------------------

              :某些资料把这个步骤叫做物理优化(Physical Optimization)

              -----------------------------------------------------------------------------------------

              7.QEP Generation

                     生成执行计划(Query Execution Plan);也就是产生Child Cursor

                     执行计划会保存在Child Cursor中,并和Parent Cursor相关联                         

                     Child Cursor中保存的是执行计划和执行环境信息,比如优化器模式

                  -------------------------------------------------------------------------------------------------------------------

                     和软解析相对应,如果一个语句完整的经历了第(1)到第(7)步,这个解析过程叫做硬解析

                     -------------------------------------------------------------------------------------------------------------------

              8.执行语句,生成结果

              --------------------------------------------------------------

              总结:以上(1)-(7)统称为解析(Parsing)。解析的最终结果就是生成执行计划。

                   ---------------------------------------------------------------------------------------------------------------------

每个SQL语句和它的执行计划都被保存在Library Cache中,而OracleLibrary Cache的访问也是基于HASH方式。每个SQL语句根据相同的HASH算法,得到一个HASH值,然后利用这个HASH值去检索Library Cahce

                   ----------------------------------------------------------------------------------------------------------------------

       Parent Cursor and Child Cursor

========================================

              1.Parent Cursor:

                     它保存的就是SQL语句的原始文本

              -----------------------------------------------------------------

              2.Child Cursor:

                     它保存的是SQL语句的执行计划和环境信息

              -----------------------------------------------------------------

Share Pool中,一个SQL语句的执行计划可以有多个,但每个执行计划都是和特定的条件相关联的。如检索条件,但即使检索条件相同,不同的环境(这里主要指优化器的参数设置)也会生成不同的执行计划。

对于Child Cursor,保存的是执行计划和环境信息,因此,即便执行计划一样,但是环境信息不同,仍然会产生多个Child Cursor

                     -----------------------------------------------------------------------------------------------------------------------

产生的这些执行计划都有可能被重用,因此也都会被保存。所以,Oracle对这些执行计划和匹配的环境作为Child Cursor保存,并和Parent Cursor相关联。

                     ----------------------------------------------------------------------------------------------------------------------

                     1:更改优化器模式

                     -------------------------------------------------

                            SQL>alter session set optimizer_mode=all rows/first_rows_10;

                            -----------------------------------------------------------------------------

                     2:当前系统中Parent Cursor的数量

                     ----------------------------------------------------

                            SQL>select sql_id,sql_text,executions from v$sqlarea;

                            -----------------------------------------------------------------------------

                     3:查看指定SQL语句的Child Cursor

                     ---------------------------------------------------

                            SQL>select sql_id,child_number,sql_text,optimizer_mode

                                      from v$sql

                                      where sql_text='select count(*) from a';

                            -----------------------------------------------------------------------------

                     4:V$SQL_SHARED_CURSOR中获取为什么会有多个Child Cursor存在

                     ----------------------------------------------------------------------------------------------------

                            SQL>select child_number,optimizer_mode_mismatch

                                      from v$sql_shared_cursor

                                      where sql_id='25xvwbgca91h5';

                            --------------------------------------------------------------------

                            注意:这里的child_number代表的不是child cursor的数量,而是child

                                 cursor的索引,0开始,child_number=0代表的是第一次产生的

                                 child cursor

                            ----------------------------------------------------------------------------------------------

              3.关于V$SQLAREAV$SQL

              ----------------------

                     1.V$SQLAREA是在Parent Cursor级别上统计语句执行信息

                     ---------------------------------------------------------------------------------

                     2.V$SQL    是在Child Cursor级别上统计语句执行信息

                     ----------------------------------------------------------------------------------

       Cursor有关的参数

==============================

              1.OPEN_CURSOR

              --------------------------------------

这个参数控制每个Session最多能同时打开的Cursor数量,如果超过这个数量,就会遇到ORA-1000错误

                     -----------------------------------------------------------------------------------------------------------------------

                     1:获得系统的参数设置以及系统曾经达到的最大值

                     ---------------------------------------------------------------------

                            SQL>select max(v$sesstat.value),

                                      v$parameter.value  as v$parameter_value

                                          from  v$sesstat,v$statname,v$parameter

                                          where v$sesstat.statistic#=v$statname.statistic#

                                          and   v$statname.name='opened cursors current'

                                          and   v$parameter.name='open_cursors'

                                          group by v$parameter.value;

                            -------------------------------------------------------------------------------

                     2:Cursor漏洞的解决方案-->

                                   确定一个业务逻辑需要的Cursor数量

                     ---------------------------------------------------------------------

                            SQL>select  max(a.value) as cumu_open_cur,

                                       p.value as max_open_cur

                                          from v$sesstat a,v$statname b,v$parameter p

                                          where a.statistic#=b.statistic#

                                          and b.name='opened cursors cumulative'

                                          and p.name='open_cursors'

                                          and a.sid=(

                                                    select max(sid) from v$mystat

                                                       )

                                          group by p.value;

                            -------------------------------------------------------------------------------

              2.SESSION_CACHED_CURSORS

              --------------------------------------------

                     这个参数用来控制CACHE缓存的CURSOR数量。

                     ------------------------------------------------------------------------------------

如果一个SQL语句被频繁执行(3次以上),那么CACHE就会缓存这个CURSOR,当下次再执行该SQL时,就省去了对CURSOR的重新初始化过程,从而提高了SQL执行效率。

                     -----------------------------------------------------------------------------------------------------------------------

                     1:评估该参数设置的效果

                     ------------------------------------

                            这个参数设置的效果可通过两个统计量来进行评估:

                            ---------------------------------------------------------------------

                                   1.session cursor cache hits:表示从CACHE CURSOR中得到的SQL语句数量

                                   ------------------------------------------------------------------------------------------------------

                                   2.Parse count(total):代表总的数量

                                   -------------------------------------------------------------------------------------------------------

                            通过这两个统计量的差就可得到发生了解析的数量

                            --------------------------------------------------------------------

                            SQL>select  cach.value cache_hits,

                                       prs.value all_parses,

                                        prs.value-cach.value sess_cur_cache_not_used

                                          from

                                          v$sesstat cach,

                                          v$sesstat prs,

                                          v$statname nm1,

                                          v$statname nm2

                                          where cach.statistics#=nm1.statistics#

                                          and nm1.name= 'session cursor cache hits'

                                          and prs.statistics#= nm2.statistics#

                                          and nm2.name= 'parse count(total)'

                                          and cach.sid= &sid and prs.sid= cach.sid;

                            --------------------------------------------------------------------

              3.CURSOR_SHARING

              -------------------------------

                     这个参数定义Cursor共享的模式,可以的取值包括:

                     --------------------------------------------------------------------

                            1.EXACT(精确)

缺省参数,这时系统不自动合并和共享Cursor,只有书写完全一致的Cursor才能共享

                            -----------------------------------------------------------------------------------------------------------------

                            2.SIMILAR(类似)

                            -----------------------------------------------------------------------------------------------------------------

                            3.FORCE(强制)

SIMILAR相似,会将非绑定变量转为绑定变量,和SIMILAR不同的是,PARSER强制认为共享是安全的,因此不会理会柱状图的信息,直接共享该Cursor

                            -----------------------------------------------------------------------------------------------------------------

              4.CURSOR_SPACE_FOR_TIME

              ---------------------------------------------

                     默认参数为FALSE,对性能提升不是很大,通常建议不要打开

                     -----------------------------------------------------------------------------------

       Explain之执行计划

=================================

              1.Explain plan for

              --------------------------------------------

                     注意:此执行计划并非Library Cache中真正的执行计划,只供分析使用。

                     -----------------------------------------------------------------------------------------------

                     1.生成指定语句的执行计划

                     --------------------------------------

                            SQL>explain plan for select * from dept;

                            -----------------------------------------------------------------------------------------

                     2.获得执行计划

                     --------------------------------------

                            SQL>select * from table(dbms_xplan.display);

                            -----------------------------------------------------------------------------------------

       SQL Trace and 10046 Event

=================================

二者的功能是一致的,都是跟踪某个Session的活动情况。既可以跟踪前台进程,也可以跟踪后台进程。

              ----------------------------------------------------------------------------------------------------------------------------

                     1.启动方法

                     --------------------------

                            1.10046事件分5个级别

                            -----------------------------------

                                   1.0级,相当于设置SQL_TRACE=FALSE,禁用跟踪功能

                                   -------------------------------------------------------------------------------

                                   2.1级,相当于设置SQL_TRACE=TRUE,启用标准跟踪

                                   -------------------------------------------------------------------------------

                                   3.4级,在1级的基础上还会跟踪绑定变量信息

                                   -------------------------------------------------------------------------------

                                   4.8级,在1级的基础上还会跟踪等待事件信息

                                   -------------------------------------------------------------------------------

                                   5.12级,相当于1+4+8

                                   -------------------------------------------------------------------------------

                            2.启用10046事件有两种方式:

                            ---------------------------------------

                                   1.静态修改初始化参数

                                   -------------------------------------------------------------

                                          [oracle@localhost ~]$ event="10046 trace name context forever,level 8"

                                          --------------------------------------------------------------------------------------------

                                   2.如果使用动态设置,则既可在系统级别设置,也可在会话级别设置

                                   ------------------------------------------------------------

                                          SQL>alter system set events='10046 trace name context forever,level 8';

                                          SQL>alter session set events='10046 trace name context forever,level 8';

                                          -------------------------------------------------------------------------------------------

                     2.关闭方式

                     ------------------------

                            SQL>alter session set events='10046 trace name context off';

                            ----------------------------------------------------------------------------

警告:设置了这个事件后,会在短时间内产生大量的trace文件,如果不及时关闭,很容易迅速耗尽磁盘空间。因此,除非必要,建议设置会话级别的跟踪就足够了,并且要及时关闭跟踪。

                        ------------------------------------------------------------------------------------------------------------------

                     3.跟踪文件存放目录和文件格式

                     ----------------------------------------

                            1.USER_DUMP_DEST

                            -------------------------------------------------

                            2.BACKGROUND_DUMP_DEST

                            --------------------------------------------------

                            3.文件名格式是:sid_ora_spid.trc

                            --------------------------------------------------

                            4.查找当前进程的SPID

                            --------------------------------

                                   SQL>select spid from v$process where addr in (

                                                 select paddr from v$session wehre sid in (

                                                 select distince sid from v$mystat

                                                 ));

                                   ---------------------------------------------------------------------

                            5.也可以通过定义跟踪文件名格式从而找到跟踪文件

                            ----------------------------------------------------------------------------

                                   SQL>alter session set tracefile_identifier='lisheng';

                                   ----------------------------------------------------------------------

                            6.跟踪文件转换

                            ------------------------------

                                   [oracle@localhost udump]$trprof orcl_ora_13258_lisheng.trc trace.txt

                                   -----------------------------------------------------------------------------------------

                                   需要注意的三列,所有的调整最终也是为了降低这3个统计量

                                   -----------------------------------------------------------------------------------------

                                          DISK:number of physical reads of buffers from disk

                                          ------------------------------------------------------------------------------------

                                          QUERY:number of buffers gotten for consistent read

                                          -------------------------------------------------------------------------------------

                                          CURRENT:number of buffers gotten in current mode (usually for update)

                                          -----------------------------------------------------------------------------------------

              如何跟踪其它会话的信息

              ---------------------------------

                     方法一:使用DBMS_SUPPORT

                     ---------------------------------------------

如果当前系统中没有,可以通过执行脚本$ORACLE_HOME/rdbms/admin/dbmssupp.sql手工创建

                            -----------------------------------------------------------------------------------------------------------------

                            1>打开跟踪

                            -----------------

                                   SQL>exec dbms_support.start_trace_in_session(

                                                               sid=>703,serial=>846,waits=>true,binds=>true);

                                   -------------------------------------------------------------------------------------------

                                   :WAITS,BINDS分别代表等待事件和绑定变量的信息

                                   -------------------------------------------------------------------------------------------

                            2>关闭跟踪

                            -----------------

                                   SQL>exec dbms_support.stop_trace_in_session(sid=>703,serial=>846);

                                   -------------------------------------------------------------------------------------------

                     方法二:oradebug

                     -------------------------

这是Oracle提供的在SQL*PLUS环境下使用的工具。使用这个工具需要提供进程的OS PID或者Oracle PID

*如果使用OS PID

       oradebug setospid 4600

*如果使用Oracle PID:

       oradebug setorapid 15

1、定义生成的跟踪文件是否有了限制,unlimit代表不限制:

       oradebug unlimit;

2、定义要跟踪的事件:

       oradebug event 10046 trace name context forever , level 12;

3、结束要跟踪的事件:

       oradebug event 10046 trace name context off;

DBMS_SUPPORTORADEBUG两种方法其实也是10046事件。

 

 

 

       Library Cache中获取

 

              1.先介绍几个视图:

              ---------------------------

                     1.V$SQL_PLAN

                     -------------------------------------------------

和之前的PLAN_TABLE相似,可以查看执行计划。所不同的是,件助这个视图查看到的是Library Cache中真实的执行计划。

                            -----------------------------------------------------------------------------------------------------------------

                     2.V$SQL_PLAN_STATISTICS

                     ------------------------------------------------

                            这个视图查看的是执行计划的每一步在运行时的统计信息,包括花费时间,处理的数据

行数。是对V$SQL_PLAN的有益补充。前一个视图虽然也有类似的数据,但那些数据都是在Parsing阶段预估出来的,供优化器选择最优的执行计划用的。而这个视图提供的是真正的性能消耗。但缺省这些数据并不会收集,要想收集这些数据,必须把初始化参数STATISTICS_LEVEL设置成ALL,或者对SQL语句添加GATHER_PLAN_STATISTICS提示。

                            -----------------------------------------------------------------------------------------------------------------

                     3.V$SQL_WORKAREA

                     -----------------------------------------------

                            该视图提供了SQL语句运行时内存的消耗。

                            -----------------------------------------------------------------------------------------------

                     4.V$SQL_PLAN_STATISTICS_ALL

                     -----------------------------------------------

                            这个视图是前三个视图的汇总,Oracle替用户完成了复杂的JOIN操作

                            -----------------------------------------------------------------------------------------------      

                     5.V$SESSION

                     ----------------------------------------------

                            在做性能调整时,V$SESSION是最初的入口。

                            Oracle10g中,这个视图的功能有了增强,如V$SESSION_WAIT

                            这个视图会记录两条SQL语句:

                            ------------------------------------------------------------------------------------------------

                                   1.SQL_ID,     SQL_CHILD_NUMBER(当前正在执行的)   

                                   ------------------------------------------------------------------------------------------

                                   2.PREV_SQL_ID,PREV_CHILD_NUMBER(刚刚执行完的)

                                   -------------------------------------------------------------------------------------------

:Oracle10g之前,要想定位一个Cursor,需要通过ADDRESSHASH_VALUE,CHILD_NUMBER三个变量。前两个用来定位Parent Cursor,后一个用来定位Child Cursor

                               -------------------------------------------------------------------------------------------------------------

Oracle10g开始,可以用SQL_ID来定位Parent CursorSQL_ID就是根据SQL语句计算出来的HASH值。对于一个SQL来说,SQL ID永远不变,ADDRESS只是一个内存指针,可能变化。                                            ------------------------------------------------------------------------------------------------------------

              2.如何获取信息

              ----------------------

                     这个方法也是利用的DBMS_XPLAN包,这个包从Oracle9i就有了,10g又增加了新的方法。

                     -----------------------------------------------------------------------------------------------------------------------

                            DISPLAY                               |Oracle9i

                            ------------------------------------------------------------------

                            DISPLAY_CURSOR                  |Oracle10g

                            ------------------------------------------------------------------

                            DISPLAY_AWR                     |Oracle10g

                            ------------------------------------------------------------------

                            DISPLAY_SQLSET                   |Oracle10g

                            ------------------------------------------------------------------

                            DISPLAY_SQL_PLAN_BASELINE |Oracle11g

                            ------------------------------------------------------------------

              3.实例

              --------------------

                     1.DISPLAY_CURSOR(Oracle10g)

                     ---------------------------------------------

                            格式声明:

                            ----------------------

                            DBMS_XPLAN.DISPLAY_CURSOR(

                                          sql_id            in varchar2 default null,

                                          child_number   in number   default null,

                                          format           in varchar2 default 'TYPICAL'

                                                                                           );

                            ------------------------------------------------------------------------------------

                            :1.child_number 如不指定值,会列出所有子游标的执行计划

                               -------------------------------------------------------------------------------

                               2.format       用来控制输出内容

                               -------------------------------------------------------------------------------

                            FORMAT常见4种格式:

                            ------------------------------------------------------

                                   1.BASIC 只需要最基本的执行计划

                                   ------------------------------------------------

                                   2.TYPICAL 缺省值

                                   ------------------------------------------------

                                   3.SERIAL TYPICAL基本相同,只是没有任何并行相关信息

                                   ----------------------------------------------------------------------------------

                                4.ALL    TYPICAL增加了语句过滤信息

                                   ----------------------------------------------------------------------------------

                            如果收集了SQL运行信息,还可以使用如下选项

                            -----------------------------------------------------------------

                                   要使用如下选项,还需如下其一操作:

                                   -----------------------------------------------------------

                                          1.执行SQL语句时,使用/*+ gather_plan_statistics */提示

                                          -----------------------------------------------------------------------------------------------------

                                          2.在系统范围或者至少在执行该语句的会话内,设置参数 statistics_level='ALL'

                                          -----------------------------------------------------------------------------------------------------

                                   1.IOSTATS     显示IO数据

                                   ---------------------------------------------------------------------------

                                   2.MEMSTATS       如果PGA手动管理,会出现相关内容

                                   ---------------------------------------------------------------------------

                                   3.ALLSTATS  同时显示以上两个内容

                                   ---------------------------------------------------------------------------

                     1:

                     -----------------------

                            1.执行测试语句

                            ------------------------------------

                                   SQL>select /*+ lisheng */ /*+ gather_plan_statistics */ count(*)

                                                                      from scott.emp where sal <1000;

                                   -------------------------------------------------------------------------------------------

                            2.确认这个语句的SQL_ID

                            ------------------------------------

                                   SQL>select sql_text,sql_id from v$sql

                                                 where sql_text like '%lisheng%';

                                   --------------------------------------------------------------------------------------------

                            3.查看执行计划

                            -----------------------------------

                                   SQL>select * from table(

                                                 dbms_xplan.display_cursor('d4ky9n1kfu0gk',null,'TYPICAL'));

                                   ---------------------------------------------------------------------------------------------

                                   SQL>select * from table(

                                                 dbms_xplan.display_cursor(

                                                 'd4ky9n1kfu0gk',null,'TYPICAL,+IOSTATS,+MEMSTATS'));

                                   ----------------------------------------------------------------------------------------------

                     2.DISPLAY_AWR(Oracle10g)

                     ----------------------------------------

这个方法是作为Oracle10g诊断包的一部分发布的,用户借助这个方法可以获得AWR中的执行计划,

                            -----------------------------------------------------------------------------------------------------------------

                            其信息来自于DBA_HITS_SQL_PLAN,DBA_HIST_SQLTEXT

                            -----------------------------------------------------------------------------------------------------------------

                            Select * from table(dbms_xplan.display_awr(‘awr_sql_id’));

       阅读执行计划

              一个完整的执行计划应该包含以下五部分内容:

              1.SQL概要信息

              ----------------------------

                     *SQL_ID,即SQL语句的Parent CursorOracle使用SQL_ID来区分每一个SQL语句

                     *Child Cursor ,即SQL语句的Child Cursor

                     *完整的SQL语句

              2.执行计划

              ----------------------------

                     *执行计划的HASH

                     *以表格形式列出的执行计划

              3.QB

              ----------------------------

                     OracleSQL语句分为single sql statementcomplex sql statement。所谓的简单SQL语句就

没有任何SQL嵌套的一个SQL语句,而复杂SQL语句就是使用了如视图,子查询,或者带有集合操作符的SQL语句(UNION,UNIONALL,MINUS,INTERCEPT)

Query Block命名:

1、  系统自动生成

格式为:Prefix$n

根据操作类型的不同,前缀也不同:

CRI$        Create Index Action

DEL$       Delete Action

INS$        Insert Action

MEG$      Merge Action

SEL$              Select Action

SET$              用于集合操作

UPD$      Update Action

MISC$     Other Action ,for example “Lock table”

2、  自定义

通过 /*+ qb_name(…) */指定

如:select  /*+ qb_name(stone)  */ * from test;

                            子查询块的名称格式:

                                   Table_name@ qb_name

                     复杂语句的处理办法:

Oracle优化器在遇到复杂SQL语句时,首先要做的就是转换(transformation,也就是没法把复杂的SQL语句转换成简单的SQL语句。对于视图则是合并(Merge),对于子

查询则是展开(Unnset Subquery)。10053事件可跟踪SQL语句的Parse全过程。

语句转换的理想结果就是去掉所有的ViewSubquery,整个语句改写成只有一层SELECT语句,所有的表都合并到一个FROM子句中。早期,Oracle认为这样的性能会更好。但有时也是得不偿失,所以才有了CBO

语句转换结束后,原始的复杂SQL语句不复存在,取而代之的是一个新的简单的SQL语句,这个新的SQL语句也对应着一个Query Block,对于这个Query BlockOracle采用了一种不同的命名方式,其格式是SET$XXXXXXXX8 个字符)。这8 个字符和SQL_ID异曲同工,也是对新的SQL语句进行某种HASH计算的值,可以认为是不变的。对于这个Query Block名称,用户是无法通过/*+ qb_name */提示命名的。

                     Query Block的作用

Query Block主要用于提示(Hint)。OracleHint是一个非常强大的性能调整工具,件助于它DBA可以随心所欲的控制SQL语句的执行计划。

但要特别注意的是,所有的控制访问路径、连接方式的Hint都是在Singe SQL语句内起作用,或者说在一个Query Block范围内起作用。

如,要实现对B表全表扫描(错误):

Select  /* full(a)  full(b)  */ a.id,v_b.id

       From a,(select *  from  b) v_b

       Where ………………..

 

要实现对B表全表扫描(正确):

                            Select /* full(a) */ a.id,v_b.id

                            From a,(select /* full(b) */ from b ) v_b

                            Where ……………………………

DBA通常希望能把一个SQL语句的所有Hint集中放到一个地方。有两种方法可以实现这个目的:

1、  Global Hint

2、  Query Block

(1)       Global Hint

要想使用Global Hint,需要为子查询创建一个别名,然后通过这个别名引用子查询中的对象。

如:

SQL> select /* full(a) full(v_b.b) */ a.id,v_b.id

  2  from a,(select * from b) v_b

  3  where .....

对于视图、Inline-view这两种Query Block来说,Global Hint 是有效的。但是对于子查询,也就是放在WHERE子句中的子查询是无法定义别名的,这时Global Hint这种方法就不起作用了。

                     2QB_NAME

这种方法只在Oracle10g以后的版本有效

Oracle10g开始,每个Hint都可以跟一个参数,也就是Query Block Name,其目的

就是定义这个Hint的作用范围。有了这个扩展之后,对于嵌套子查询也可以完成上

的要求了。

如:

SQL> select /* qb_name(main) full(@main t_a) full(@sub t_b) */ ...

  2  from t_a

  3  where ... exist (select /* qb_name(sub) */ from t_b...)

引用方式:@qb_name table_name

用这种方式时,建议使用有意义的名字。当然也可以使用系统产生的名字,但需要

获得执行计划,区分出每个Query Block的名字,尤其是发生TransformationZ时生

成的名字。

              4.谓词(predicate)(过滤条件)

              ----------------------------             

                            关系数学术语,俗称过滤条件,包括选择条件,连接条件等

            1 - access("E"."DEPTNO"="D"."DEPTNO")

              2 – filter (“E”.”SAL”>300)

              每一行都是一步数据过滤,记录了过滤是执行计划的哪一行起作用,以及如何起作用的。

              *filter     代表对原始数据的过滤条件

              *access    代表利用某种结构过滤数据,比如利用索引、hash连接等

              执行计划中每个使用数据过滤的行前,也会带有一个*号,和这里的代表行号的数字相对应。

 

              5.列投影(projection)

              ----------------------------

                            也是关系数学的术语,含义就是只选择部分列而非所有列

                            这一部分显示了每一个操作返回了哪些列,以及这些列的数据类型、数据长度。

                            从这里,我们可以判断是否发生了隐式数据类型转换,而隐式数据类型转换正是

                            阻止使用索引的罪魁祸首之一。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25963200/viewspace-701743/,如需转载,请注明出处,否则将追究法律责任。

下一篇: SQL 执行计划
请登录后发表评论 登录
全部评论

注册时间:2011-06-20

  • 博文量
    21
  • 访问量
    16244