• 存储过程调优之“10046”事件


    一、前言

        前段时间应需求,写存储过程,以满足避免在大数据量的原始表中进行直接的查询工作。从而生成一张中间表,用于以后各个维度的报表统计

      从而提高每张报表的查询效率。久而久之,随着存储过程越来越多,每天的任务耗时也越来越大,从而不得不考虑对存储过程进行优化。

    二、"10046"事件

        Oracle的10046事件,它可以跟踪应用程序所执行的SQL语句,从而得到每条SQL的解析次数,执行次数,CPU使用时间,每条SQL中每个部位的耗时等。这样,我们

      就可以根据这些信息来分析、定位数据库的性能问题。

        10046event是oracle用于系统性能分析时的一个最重要的事件,当激活这个事件后,将通知oracle kernel追踪会话的相关即时信息,并写入相应的trace文件中。这些 

      信息包括SQL的解析,绑定变量的使用情况,会话中发生的等待时间以及会罗列出主要耗时的部分等。

        10046event有不同的级别(level),分别追踪记录不同程度的信息。需要注意的是,级别是向下兼容的 即高一级别生成的trace所包含的信息包含低一级别的所有信息,

      追踪级别大致有:

         level 1:跟踪sql语句,包括解析、执行、提取、提交和回滚等。

         level 4:包括变量的详细信息。

         level 8:包括等待事件。

           level 12:包括绑定变量与等待事件。

           其中,level 1相当于打开了sql_trace。

         (Tips) 向下兼容。

    三、10046event 追踪最小化例子

        命令行/plsql 键入如下:

        alter session set tracefile_identifier='10046';-----------------------设置追踪标识符

        alter session set events '10046 trace name context forever, level 12';----------------开启追踪,并设置追踪级别

        select * from ALL_OBJECTS; -----------------所要追踪的SQL语句

        alter session set events '10046 trace name context off';------------关闭追踪

        执行完后,即所有会话都结束后,oracle将会生成一个orcl_ora_XXXX_10046.trc 的文件,根据个人目录不同,会生成在不同的

      目录当中,我们可以用locate命令来进行查询

        得到这个文件后,我们再用tkprof这个命令,对该文件进行格式化,便于我们的阅读。

        如:tkprof traceorcl_ora_xxxx_10046.trc  10046.txt sys=no sort=prsela,exeela,fchela

    四、解析trace文件

        我们打开10046.txt,对于初学者来说,并不能好好的去分析包含的信息,因为其中包含了许多“名词”,下面将会对此进行介绍

        1)摘录第一部分,SQL语句的执行情况总览
          select *
          from
          ALL_OBJECTS


        call     count       cpu    elapsed       disk      query    current        rows
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
        Parse        1      0.00       0.00          0          0          0           0
        Execute      1      0.00       0.00          0          0          0           0
        Fetch        2     10.94      10.68     222186     222957          0           1
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
        total        4     10.94      10.68     222186     222957          0           1

        关于统计表格的标题信息中count、cpu、elapsed、disk、query、current和rows的说明在该trace文件的最前端有一个简要的说明,这里再分别赘述一下。
        count   :查询在此阶段执行的次数;
        cpu     :该查询在此阶段的CPU时间量,以毫秒为单位;
        elapsed :花费在此阶段上的挂钟时间,该值比cpu值大的时候,表明存在等待事件;
        disk    :执行物理I/O次数;
        query   :在意一致性检索方式获得块时,执行逻辑I/O次数;
        current :逻辑I/O次数;
        rows    :此阶段,被处理或受影响的行数。

        关于第一列的赘述:
        Parse   :软编译和硬编译次数;
        Execute :在open和execute语句中完成的内容;
        Fetch   :select中会有数据显示,在update语句中不会有数据显示。

        2)摘录运行环境信息
        Misses in library cache during parse: 0
        Optimizer mode: ALL_ROWS
        Parsing user id: 51

        第一行的“0”表示查询使用的是软解析(soft parse)。
        优化模式是:ALL_ROWS
        使用最后一行的用户ID可以获得执行时的会话信息。获得用户信息可以通过下面的SQL语句完成。
        sys@ora10g> select * from all_users where user_id = 51;

        USERNAME                          USER_ID CREATED
        ------------------------------ ---------- -------------------
        SEC                                    51 2015-02-15 13:04:03

        3)摘录执行计划信息
        Rows     Row Source Operation
        -------  ---------------------------------------------------
              1  SORT AGGREGATE (cr=222957 pr=222186 pw=0 time=10686023 us)
     100000000   INDEX FAST FULL SCAN PK_T (cr=222957 pr=222186 pw=0 time=100000562 us)(object id 45619)

        有趣发现:通过第二行可以得到这个t表的数据量,这里显示结果是1亿。
        “解剖”上面出现的几个重要参数:
        cr=222957          -- 一致性读取
        pr=222186          -- 物理读取
        pw=0               -- 物理写
        time=100000562 us  -- 占用时间,单位:微妙 百万分之一秒


        4)摘录等待事件
        Elapsed times include waiting on following events:
          Event waited on                             Times   Max. Wait  Total Waited
          ----------------------------------------   Waited  ----------  ------------
          SQL*Net message to client                       3        0.00          0.00
          SQL*Net message from client                     3        0.00          0.00
          db file scattered read                      14249        0.00          1.10
          db file sequential read                        59        0.00          0.00
        
        通过这段等待事件的描述,可以清楚的得到在执行SQL语句的过程中都出现了哪些引人注目的等待事件。比如上面显示出的“db file scattered read”和“db file     sequential read”信息,如果此类信息在生产环境中大量出现,就需要重点深入分析和研究了。

    五、总结 

        对于10046事件,本身是非常消耗资源,对于存储过程,如果对此进行设置跟踪,会增加该存过的耗时,因此在使用过程中应避免一直启用追踪。但对于存储过程的调优,其包含的各种信息是非常有助于对整个存过进行有效的分析,对于10046扩展追踪,非常适合于对那些把很多业务逻辑写入到存储过程中的软件调优

      

      

  • 相关阅读:
    Spring Boot:自动配置原理
    centos7安装zookeeper
    spring集成RabbitMQ
    centos7安装RabbitMQ
    centos7安装MongoDB4.0(yum安装)
    nginx日志分割
    centos7 yum安装nginx
    centos7使用cronolog分割tomcat8.5的catalina.out日志
    ubuntu18安装微信
    idea打开dashboard
  • 原文地址:https://www.cnblogs.com/sicd/p/4378042.html
Copyright © 2020-2023  润新知