合 AWR报告中SQL统计部分的执行次数和每次执行时间为空的说明 (Doc ID 1522547.1)

 

0    291    1

Tags：

👉 本文共约895个字，系统预计阅读时间或需4分钟。

简介

Why "Executions" and "Elap per Exec(s)" are 'Blank' in AWR for Some SQL Statements (Doc ID 1522547.1)

从10.2开始，这是由于高版本游标数引起的，"Executions" 和 "Elap per Exec(s)" 这两项统计信息对于版本计数高于默认值（通常为200）的高版本游标将不再被收集。

收集这些统计信息需要和v$sql表关联以获取全部统计数据，而这可能导致性能问题，因为在扫描所有子游标时必须长时间持有‘库缓存锁（library cache lock）’。因此，自实施未公开Bug 4123514以来，仅显示版本计数小于等于200的游标。针对未公开Bug 4123514的修复已于Oracle 10.2及更高版本中包含。这就是为什么在10gR2之后的AWR中，高版本计数游标的“Executions”和“Elap per Exec(s)”字段可能为空的原因。

APPLIES TO:

Oracle Database - Standard Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

Some "Executions" and "Elap per Exec(s)" fields are blank in their AWR after 10gR2.

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

CHANGES

None.

CAUSE

This is due to the high version count of these SQLs:

SOLUTION

This is expected behavior.

Since 10.2, "Executions" and "Elap per Exec(s)" statistics of high version count cursor(> 200 by default) will no longer be collected.

Collecting these statistics needs to join back to v$sql to retrieve all the stats, and this might cause performance issues because the 'library cache lock' must be held for a long time when scanning all the child cursors.

Because of this, only cursors with version counts <=200 are displayed following the implementation of Unpublished Bug 4123514. The fix for Unpublished Bug 4123514 was included in Oracle 10.2 and above. This is why "Executions" and "Elap per Exec(s)" fields may be blank for high version count cursor in AWR after 10gR2.

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复