00 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