Oracle Execute to Parse 执行解析比分析

Execute to Parse%是AWR报告中Instance Efficiency Percentages部分中重要的一个性能指标,反应了数据库SQL解析和执行的比率。这个比率值同时也涉及到了与cursor相关的参数以及硬解析,软解析,软软解析等。本文是围绕这个比率进行展开及描述。

一、什么是Execute to Parse%

--下面是来自AWR报告的相关信息
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.60       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.99    In-memory Sort %:  100.00
            Library Hit   %:   99.96        Soft Parse %:   99.98
         Execute to Parse %:   -8.46         Latch Hit %:   98.39
Parse CPU to Parse Elapsd %:   90.79     % Non-Parse CPU:   97.35

Statistic                              Total     per Second     per Trans
-------------------------------- ----------- -------------- -------------
index crx upgrade (prefetch)               0            0.0           0.0
opened cursors cumulative          2,296,221           91.0         780.5
parse count (describe)                     3            0.0           0.0
parse count (failures)                     5            0.0           0.0
parse count (hard)                       512            0.0           0.2
parse count (total)                2,272,639           90.1         772.5
parse time cpu                        16,934            0.7           5.8
parse time elapsed                    18,651            0.7           6.3

Tom大师关于Execute to Parse的描述: the only way to influence that number is to either change a) the number of times you parse. b) the number of times you execute. The formula used: Execute to Parse %: dscr , round(100*(1-:prse/:exe),2) pctval Execute to Parse %: 一个语句执行和分析了多少次的度量。计算公式为:Execute to Parse =100 * (1 - Parses/Executions)。如果系统Parses > Executions,就可能出现该比率小于 0 的情况。该值<0 通常说明 shared pool 设置或者语 句效率存在问题,造成反复解析,reparse 可能较严重,或者是可能同 snapshot 有关,通常说明数据库性能存在一定问题。 If the number of parse calls is near the number of execute calls, then this ratio drifts towards zero (as yours is). As the number of execute calls increases (while holding parse calls constant), this number drifts towards 100%. That means you have parsed a statement ONCE and executed it MANY TIMES (that is good, that is best) cursor sharing = similar MIGHT change a hard parse into a soft parse (take a very very very bad thing and make it simply very very bad). cursor sharing similar CANNOT change the number of times parse is invoked however. There is precisely, exactly and only ONE person that can do that. That is the application developer. When they say “parse this”, we parse it - it matters not what the value of cursor sharing is (if you have a hard parse problem, if your soft parse percent is below 99%, you need to have the coders FIX that, you have (in addition to performance, memory, scalability issues) a HUGE security risk if you are not using binds). The developers must cache open cursors they know will be used over and over. The easiest way (to me) to accomplish this is to move all SQL into plsql, plsql automagically caches statements for us, it is the most efficient method to interface with the database. Alternatively, they can program it, or they can see if the API they are using can do it magically for them (search for jdbc statement caching on google for example if you are using jdbc) But it will have to be done in the application, there is nothing we can do outside of the application to influence how often it parses. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363

二、Execute to Parse相关参数描述

Google了一些关于这个问题的描述,大部分描述涉及到了以下2个参数 OPEN_CURSORS: specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed. SESSION_CACHED_CURSORS: specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed. If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can’t be completely avoided; a “softer” soft parse is done that’s faster and requires less CPU.) In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor. 当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object. 另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor. session_cached_cursor: 这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次, 那么这个cursor将会被加到session cursor cache list的MRU端. 当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU 端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能,也就是说连open cursor的动作都给省了。

三、分析及调整

查看当前系统session配置
SQL> Select 'session_cached_cursors' Parameter,
  2      Lpad(Value, 5) Value,
  3      Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
  4  From (Select Max(s.Value) Used
  5        From V$statname n, V$sesstat s
  6       Where n.Name = 'session cursor cache count'
  7        And s.Statistic# = n.Statistic#),
  8      (Select Value From V$parameter Where Name = 'session_cached_cursors')
  9  Union All
 10  Select 'open_cursors',
 11      Lpad(Value, 5),
 12      To_Char(100 * Used / Value, '990') || '%'
 13  From (Select Max(Sum(s.Value)) Used
 14        From V$statname n, V$sesstat s
 15       Where n.Name In
 16          ('opened cursors current', 'session cursor cache count')
 17        And s.Statistic# = n.Statistic#
 18       Group By s.Sid),
 19      (Select Value From V$parameter Where Name = 'open_cursors');

PARAMETER              VALUE                USAGE
---------------------- -------------------- -----
session_cached_cursors    50                  98%   --当前session_cached_cursors的使用率为98%,应考虑增加该参数值
open_cursors             300                  20%   --当前open_cursors仅为20%,说明当前够用

-- 也可以通过下面的脚步查看cursor的使用情况
SQL> SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  
  2   FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  
  3  WHERE A.STATISTIC# = B.STATISTIC#  
  4    AND B.NAME = 'opened cursors current'  
  5    AND P.NAME = 'open_cursors'  
  6  GROUP BY P.VALUE; 

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ---------------------------------------------
300              19

--查看cursor相关统计值,实例级别
SQL> select name,value from v$sysstat where name like '%cursor%';

NAME                                VALUE
----------------------------------- ----------
opened cursors cumulative            819271677
opened cursors current                     350
pinned cursors current                       6
session cursor cache hits            340959054
session cursor cache count           399411460
cursor authentications                   56465

SQL的执行包括几个步骤:打开、解析、绑定、执行、抓取、关闭。

硬解析:SQL语句在library cache无缓存
软解析:SQL语句在library cache找到了执行计划
软软解析:在pga内搜索session cursor cache list列表中找到对应的SQL,无论软解析、还是软软解析,都有解析这个操作。
要改善解析与执行的比率关系,就需要增加无解析的次数,无解析就是不再解析,为SQL绑定不同的变量,然后执行。
这样做的前提就是:1、Session不能断开;2、Session执行过解析过的SQL不要关闭;满足这两点就可以实现无解析。

根据上面的分析以及session_cached_cursors的使用率分析,将参数session_cached_cursors增加至300

alter system set session_cached_cursors=300 scope=spfile;

修改后要重启数据库方能生效。

SQL> @cursor_usage  --执行查询可以看到调整后session_cached_cursors usage完全充足

PARAMETER              VALUE                USAGE
---------------------- -------------------- -----
session_cached_cursors   300                  12%
open_cursors             300                  12%

四、sql_id az33m61ym46y4

通过调整之后跟踪,Execute to Parse为负值的情形依旧存在
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.96    In-memory Sort %:  100.00
            Library Hit   %:   99.88        Soft Parse %:   99.93
         Execute to Parse %:   -5.17         Latch Hit %:   98.47
Parse CPU to Parse Elapsd %:   90.85     % Non-Parse CPU:   98.40

进一步分析
SQL> set linesize 200;
SQL> set pagesize 1000;
SQL> col sql_text format a40;
SQL> SELECT st.sql_id,
  2    -- sq.sql_text,
  3           st.executions_total,
  4           st.parse_calls_total,
  5           ROUND (100 * (1 - (st.parse_calls_total / st.executions_total)), 2)
  6              execute_to_parse,
  7           st.executions_delta,
  8           st.parse_calls_delta,
  9           ROUND (100 * (1 - (st.parse_calls_delta / st.executions_delta)), 2)
 10              delta_ratio
 11      FROM DBA_HIST_SQLSTAT st, DBA_HIST_SQLTEXT sq, DBA_HIST_SNAPSHOT s
 12     WHERE     s.snap_id = st.snap_id
 13           AND s.begin_interval_time >=
 14                  TO_DATE ('2015-10-22 09:30:00', 'YYYY-MM-DD HH24:MI:SS')
 15           AND s.end_interval_time <=
 16                  TO_DATE ('2015-10-22 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
 17           AND st.sql_id = sq.sql_id
 18           AND st.parsing_schema_name in ('WX_USER','WX_XJW','XLKPORTALS','SCMONLINE')
 19           AND st.executions_total != 0
 20           AND st.executions_delta != 0
 21  ORDER BY delta_ratio;

SQL_ID        EXECUTIONS_TOTAL PARSE_CALLS_TOTAL EXECUTE_TO_PARSE EXECUTIONS_DELTA PARSE_CALLS_DELTA DELTA_RATIO
------------- ---------------- ----------------- ---------------- ---------------- ----------------- -----------
az33m61ym46y4            91316             91390             -.08            12530             12542         -.1
az33m61ym46y4            78786             78848             -.08            12504             12517         -.1
az33m61ym46y4            41137             41166             -.07            12388             12399        -.09
az33m61ym46y4            66282             66330             -.07            12550             12561        -.09
az33m61ym46y4            28749             28767             -.06            12589             12599        -.08
az33m61ym46y4            53732             53769             -.07            12595             12603        -.06

–从上面的查询可以看出sql_id az33m61ym46y4是罪魁祸首 Metalink上有唯一一篇关于这个sql的描述,不过也没有看到太多的建议。 同样的情形在Oracle 11.1.1.7上也存在,不过这条SQL在awr报告中没有当前11.2.1.0中突出 以下是metalink上的具体描述 Bug 12318969 : V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE IS RECYCLED Bug Attributes Type B - Defect Fixed in Product Version Severity 2 - Severe Loss of Service Product Version 2.2 Status 92 - Closed, Not a Bug Platform 226 - Linux x86-64 Created 02-Apr-2011 Platform Version NO DATA Updated 12-Oct-2011 Base Bug N/A Database Version N/A Affects Platforms Generic Product Source Oracle Knowledge, Patches and Bugs related to this bug Related Products Line More Applications & Technologies Family Industry Solutions Area Utilities Product 2245 - Oracle Utilities Framework Hdr: 12318969 N/A BATCH 2.2 BTJOBSUB PRODID-2245 PORTID-226 Abstract: V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE IS RECYCLED * 04/01/11 03:56 pm * Short Description: —————— instance is recycled Detailed Problem Statement: ————————– When database node crashes or when database node is recycled , there will error they, however do not re-connect to the database. way the online application reconnects after a database recycle. Workaround ———- groups. Impact on Business see attachments * 04/01/11 04:11 pm * * 04/05/11 03:39 pm * * 04/05/11 04:35 pm * (CHG: Sta->11 Asg->FJOCSON) * 04/06/11 08:56 am * (CHG: Sta->30 Asg->MZEEMAN) * 04/06/11 08:56 am * * 04/06/11 04:40 pm * * 04/08/11 12:34 pm * * 04/08/11 12:35 pm * (CHG: Sta->10 Asg->FJOCSON) * 04/08/11 12:35 pm * * 04/08/11 12:56 pm * (CHG: Sta->30 Asg->MZEEMAN) * 04/08/11 12:56 pm * * 04/15/11 05:38 pm * * 05/05/11 01:25 pm * * 05/05/11 03:29 pm * (CHG: Sta->10 Asg->ASHORTEN) * 05/05/11 03:29 pm * * 05/05/11 06:38 pm * * 05/05/11 06:38 pm * Updated the Batch Best Practices under “Threadpools and Database Recycling” * 05/06/11 02:42 pm * (CHG: Sta->30) * 05/06/11 02:42 pm * * 05/17/11 03:53 pm * (CHG: Sta->92) * 05/17/11 03:53 pm * (CHG: Sta->30) * 05/17/11 04:38 pm * * 05/24/11 03:55 pm * * 07/06/11 09:05 am * * 07/11/11 03:46 pm * * 07/11/11 03:49 pm * (CHG: Sta->11 Asg->ASHORTEN) * 07/11/11 03:49 pm * * 07/11/11 04:15 pm * (CHG: Sta->92 Asg->MZEEMAN) * 07/21/11 09:04 am * (CHG: Sta->10 Asg->ASHORTEN) * 07/21/11 09:04 am * When we changed the hibernate.c3p0.idle_test_period = 10 property per SPL recommendation, a side affect of this change is that a SQL is executed way too frequently. * 07/21/11 09:05 am * * 07/21/11 09:06 am * When PG&E changed the hibernate.c3p0.idle_test_period = 10 property per SPL recommendation, a side affect of this change is that a SQL is executed way too frequently. SQL_ID az33m61ym46y4 SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o WHERE o.owner LIKE :1 ESCAPE ‘/’ AND o.object_name LIKE :2 ESCAPE ‘/’ AND o.object_type IN (‘xxx’, ‘TABLE’) ORDER BY table_type, table_schem, table_name —————— Query is run 150,000+ per hour–apparently forever, on nodes 3,4,5, which totals 500,000 executions per hour * 07/21/11 09:06 am * * 08/02/11 04:08 pm * * 08/02/11 04:32 pm * The SQL shown in the example is really not from the product. It is not an SQL I think exists in the product as such (It is a database query and in fact CISADM should not really have access to ALL_TABLES. Please verify this is the ONLY SQL that is excessive. The settings do not get the SQL to execute more than they should. It only should affect reconnection checks. * 08/09/11 03:53 pm * * 08/09/11 03:54 pm * * 08/10/11 08:56 am * * 08/10/11 12:22 pm * (CHG: Sta->11 Asg->FJOCSON) * 08/10/11 02:32 pm * * 08/10/11 02:32 pm * (CHG: Sta->30 Asg->MZEEMAN) * 08/31/11 11:06 pm * * 08/31/11 11:14 pm * * 10/11/11 03:23 pm * (CHG: Sta->92) * 10/11/11 03:23 pm *

五、小结

a、Execute to Parse%是执行到解析的度量,最佳情况下,是一次解析多次执行,最佳的是使用软软解析; b、涉及到的参数主要是OPEN_CURSORS与session_cached_cursors,前者定义单个session可以打开游标数,后者定义游标可缓存长度 c、通常情况下上述两个参数的使用率应尽可能偏离80%,以确保性能及资源充足,注意,这2个参数增大应考虑是否pga以及sga需要进一步调整

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大内老A

谈谈基于SQL Server 的Exception Handling[中篇]

三、TRY CATCH & Return 在上面一节中,我通过RAISERROR重写了创建User的Stored procedure,实际上上面的Stored ...

19560
来自专栏恰童鞋骚年

轻量级ORM框架初探-Dapper与PetaPoco的基本使用

  EF是传统的ORM框架,也是一个比较重量级的ORM框架。这里仍然使用EF的原因在于为了突出轻量级ORM框架的性能,所谓有对比才有更优的选择。

28630
来自专栏数据库新发现

字符集问题的初步探讨(二)

原文发表于itpub技术丛书《Oracle数据库DBA专题技术精粹》,未经许可,严禁转载本文.

14120
来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

21010
来自专栏杨建荣的学习笔记

sed+awk模拟简单sql查询(26天)

经常需要用sqlplus去查询一些数据字典类型的数据,这些数据量不大,而且需要环境之间都是一样的,所以就想使用离线查询的方式,把那些数据以平面文件的形式存放,...

373120
来自专栏张善友的专栏

Sql Server 2005 ROW_NUMBER 函数实现分页

过去用SQL Server 2000分页的,大多都用到了临时表。SQL Server 2005 ROW_NUMBER 函数支持分页,性能据说也非常不错。 Pag...

22660
来自专栏数据和云

12c RMAN新特性之Recover Table

周玉其 云和恩墨南区技术顾问,曾在电信、物流等行业从事多年数据库开发、管理工作。

9020
来自专栏乐沙弥的世界

Oracle RAC环境下配置statspack

    Statspack是Oracle 9i时代的产物,对于监控与分析数据库性能有着跨里程碑的意义,是AWR的前身。在Oracle 10g后AWR取代了sta...

7530
来自专栏安恒网络空间安全讲武堂

Sqli_labs65关通关详解(上)

Less-1 这个题目是基于错误,单引号,字符型注入, http://127.0.0.1/sqli/Less-1/?id=1' //报错 http://...

73860
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 1.逻辑查询处理知多少

  T-SQL是ANSI和ISO SQL标准的MS SQL扩展,其正式名称为Transact-SQL,但一般程序员都称其为T-SQL。

9740

扫码关注云+社区

领取腾讯云代金券