主页 / dba / 问题 / 61044
Accepted
Jon Heller
Asked: 2014-03-17 21:05:15 +0800 CST

历史 SQL 监控

  • 772

是否有将实时 SQL 监控扩展到历史活动的内置 Oracle 功能？我对在 SQL操作级别聚合等待事件特别感兴趣，而不仅仅是每个语句。

操作级别信息可在DBA_HIST_ACTIVE_SESS_HISTORY. 许多 AWR 报告都使用该表，但它们中的任何一个都显示操作级别的信息吗？目前我通过加入DBA_HIST_ACTIVE_SESS_HISTORYand来获取这些数据DBMS_XPLAN.DISPLAY_AWR，但似乎应该有一个内置功能来为我做这件事。在我构建自己的工具之前，我想确保其他人尚未构建它。

这个缺失的功能可能听起来有点不便，但使用实际的执行统计数据，甚至是其中的一个样本，会使调优变得容易得多。例如，下面是最近出现的问题的真实 SQL 监控报告，经过修改以适合此处。有了操作级信息，就无需猜测哪个步骤慢，甚至无需检查等待。问题很快就被发现了——索引在大INSERT.

SQL Monitoring Report


SQL Text
------------------------------
<SQL statement...>


Global Information
------------------------------
<metadata like run status, session, sql_id, duration, etc...>

Parallel Execution Details (DOP=64 , Servers Allocated=128)
...

SQL Plan Monitoring Details (Plan Hash Value=2062479242)

=================================================================================
| Id |     Operation          | Activity |          Activity Detail             |
|    |                        |   (%)    |            (# samples)               |
=================================================================================
...
|  1 |   PX COORDINATOR       |     0.19 | enq: KO - fast object checkpoint (21)|
|  2 |    PX SEND QC (RANDOM) |          |                                      |
|  3 |     INDEX MAINTENANCE  |    84.70 | buffer busy waits (2801)             |
|    |                        |          | enq: HW - contention (29)            |
|    |                        |          | enq: TX - allocate ITL entry (25)    |
|    |                        |          | free buffer waits (61734)            |
|    |                        |          | write complete waits (10365)         |
|    |                        |          | Cpu (341)                            |
|    |                        |          | enq: FB - contention (1632)          |
|    |                        |          | enq: TX - contention (53)            |
|    |                        |          | db file sequential read (3717)       |
|    |                        |          | read by other session (641)          |
|  4 |      PX RECEIVE        |     0.02 | Cpu (18)                             |
|    |                        |          | resmgr:cpu quantum (1)               |
...
=================================================================================

还有一些其他方法可以产生类似的信息，例如跟踪/*+ gather_plan_statistics */、 或alter session set statistics_level=all;。但是这些方法只在开发环境中有用。我没有能力重新运行具有不同设置的语句。

oracle

1 个回答

  1. Best Answer

    Oracle 12c 有一个性能中心，其中包括历史模式下的受监控 SQL。虽然我没有测试过它，并且基于一些开放的错误，我怀疑它是否能 100% 工作。

    对于 11g，或者当 Performance Hub 不可用或不工作时，我创建了开源程序Historical SQL Monitoring (hist_sql_mon)：

    以下是如何使用它的示例：

    select hist_sql_mon.hist_sql_mon(
    p_sql_id            => '2ssrz4j1m39wx',
    p_start_time_filter => date '2014-09-25',
    p_end_time_filter   => sysdate - interval '1' day)
from dual;

    主要输出是一个 CLOB，其中包含一个执行计划，其中包含事件计数和不同计数。

    -------------------------------------------------------------
|Id  | Operation              | Name   | Rows  | Cost (%CPU)| Event (count|distinct count)
-------------------------------------------------------------
|  0 | SELECT STATEMENT       |        |       | 83031 (100)|
|  1 |  SORT AGGREGATE        |        |     1 |            |
|  2 |   HASH JOIN RIGHT OUTER|        |  8116K| 83031   (4)| Cpu (25|25)
|  3 |    INDEX FULL SCAN     | I_USER2|   155 |     1   (0)|
|  4 |    NESTED LOOPS OUTER  |        |  8116K| 82993   (4)| Cpu (1|1)
|  5 |     NESTED LOOPS OUTER |        |  8116K| 10702  (24)| Cpu (2|2)
...

    这些函数还将 SQL 语句打印到 DBMS_OUTPUT。绑定变量被硬编码值替换，因此查询可以在任何地方运行。这有助于调试或创建您自己的查询。

    ----------------------------------
--Historical SQL Monitoring Report
----------------------------------
--Execution plans and ASH data, where there are some samples for a plan_hash_value.
select
    --Add execution metadata.
    case
        when plan_table_output like 'Plan hash value: %' then
...
    • 0

相关问题