Início / dba / Perguntas / 61044
Accepted
Jon Heller
Asked: 2014-03-17 21:05:15 +0800 CST

Monitoramento histórico de SQL

  • 772

Existe um recurso integrado do Oracle que estende o monitoramento de SQL em tempo real para atividades históricas? Estou especificamente interessado em obter eventos de espera agregados no nível de operação SQL , não apenas por instrução.

As informações do nível de operação estão disponíveis em DBA_HIST_ACTIVE_SESS_HISTORY. Muitos relatórios AWR usam essa tabela, mas algum deles exibe informações no nível da operação? Atualmente, estou obtendo esses dados juntando DBA_HIST_ACTIVE_SESS_HISTORYe DBMS_XPLAN.DISPLAY_AWR, mas parece que deve haver um recurso interno para fazer isso por mim. Antes de construir minha própria ferramenta, quero garantir que outra pessoa ainda não a tenha construído.

Esse recurso ausente pode parecer um pequeno inconveniente, mas usar as estatísticas de execução reais , ou mesmo uma amostra delas, torna o ajuste muito mais fácil. Por exemplo, abaixo está um Relatório de Monitoramento SQL real para um problema recente, modificado para caber aqui. Com informações de nível de operação, não havia necessidade de adivinhar qual etapa estava lenta ou mesmo examinar as esperas. O problema foi encontrado rapidamente - os índices não foram desabilitados antes de um grande arquivo 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)               |
...
=================================================================================

Existem alguns outros métodos que produzem informações semelhantes, como rastreamento /*+ gather_plan_statistics */, ou alter session set statistics_level=all;. Mas esses métodos são úteis apenas em um ambiente de desenvolvimento. Não tenho o luxo de poder executar novamente instruções com configurações diferentes.

oracle

1 respostas

  1. Best Answer

    O Oracle 12c possui um Performance Hub que inclui SQL monitorado no modo histórico. Embora eu não tenha testado, e com base em alguns bugs abertos, duvido que funcione 100% do tempo.

    Para 11g, ou quando o Performance Hub não está disponível ou não está funcionando, criei o programa de código aberto Historical SQL Monitoring (hist_sql_mon) :

    Aqui está um exemplo de como usá-lo:

    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;

    A saída primária é um CLOB contendo um plano de execução com uma contagem e uma contagem distinta de eventos.

    -------------------------------------------------------------
|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)
...

    As funções também imprimem a instrução SQL em DBMS_OUTPUT. As variáveis ​​de ligação são substituídas por valores embutidos em código para que a consulta possa ser executada em qualquer lugar. Isso pode ajudar na depuração ou na criação de suas próprias consultas.

    ----------------------------------
--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

relate perguntas