Preparando o Oracle Database para o Exadata Machine – Parte 1: Utilizando o Exadata Simulation
June 24, 2016

Oracle Exadata Logo Mini - Flavio Soares

É possível estimar os benefícios do Exadata, sem estar no Exadata?

O Exadata conta com uma série de recursos (físicos e lógicos) que fazem com que a máquina trabalhe com notável desempenho, capacidade e mestria, executando atividades de maneira como nenhuma outra existente hoje no mercado para banco de dados.

O próprio Infiniband, que é um padrão de comunicação interconnect (db nodes e storage cells) que desempenha um excelente throughput de dados entre os servidores. Podemos listar outras features como o Smart Scan, Smart Flash, Storage Index, Column Projection, Offloading De Dados e Predicate Filtering, todas elas mudam completamente a forma como uma instrução é tratada no Exadata.

A abordagem ideal em uma migração não Exadata para Exadata, seria trabalhar em um cenário real: executar todas as cargas e processos do seu database no próprio hardware Exadata e comparar os resultados obtidos. Infelizmente, nem todos tem o privilégio de ter uma máquina dessas encostada no DataCenter, só para realizar esse tipo de teste. Muita das vezes, o esforço, tempo, planejamento e principalmente o investimento para se obter um teste desse, acaba não sendo justificado.

Pensando em tudo isso, a Oracle a partir do Oracle Database 11g Release 2, adicionou no SQL Performance Analyzers (SPA) a possibilidade de processar sua carga de trabalho real em um “simulador do storage cell”.

A ideia basicamente consiste em coletar a sua carga de trabalho e deixar armazenado em um SQL Tuning Set (SQLTSET). O SPA então executa o SQLTSET duas vezes, a primeira execução é processada normalmente sem nenhum alteração no banco de dados, já a segunda ele ativa o modo “cell storage simulation” e registra as informações obtidas. Após as duas execuções, o SPA avalia a mudança de comportamento através da coluna io_interconnect_bytes (v$SQL), apresentando no final um relatório com a comparação das duas execuções.

Com esse tipo de relatório em mãos, fica fácil de responder algumas perguntas como:

  • Como será o desempenho do Exadata com a minha carga de trabalho atual?
  • Será que o Exadata, é a máquina certa para o meu ambiente?
  • Os ganhos da performance do Exadata serão grandes o bastante para justificar o custo dessa máquina?

O Exadata simulation tem a capacidade de suportar apenas cargas de trabalhos de DW e DSS. O segredo do molho do Exadata nos ambientes OLTP é o acesso ao Smart Flash Cache. Como o Exadata Simulation não consegue calcular/reproduzir os acessos de blocos via Flash, torna a sua utilização nesse caso para cargas OLTP inviável de ser testado e não costumam apresentar grande mudanças de comportamento. Já cargas do estilo DW/DSS, que contém um alto número de acessos, grande retorno de linhas e um elevados full scans nas tabelas/indexes, torna o melhor candidato para ser reproduzido no Exadata Simulation.

 

Capturando os dados

É possível capturar os dados para o SQLSET, de duas formas:

1) Snapshot do AWR.

Nesse caso, você captura os dados de uma carga que aconteceu no passado via snapshot do AWR:

sys@sl01$1 SQL> COLUMN snaphist_begin_time      HEADING BEGIN_TIME    FORMAT a28 
sys@sl01$1 SQL> COLUMN snaphist_end_time        HEADING END_TIME      FORMAT a28 
sys@sl01$1 SQL> COLUMN snaphist_snap_id         HEADING SNAP_ID       FORMAT 999999
sys@sl01$1 SQL> COLUMN snaphist_dbid            HEADING DBID          FORMAT 99999999999
sys@sl01$1 SQL> COLUMN snaphist_inst_id         HEADING INST_ID       FORMAT 999999
sys@sl01$1 SQL> SELECT DISTINCT
  2    dbid snaphist_dbid,
  3    snap_id snaphist_snap_id, 
  4    TO_CHAR(begin_interval_time, 'DD/MM/YYYY HH24:MI') snaphist_begin_time,
  5    TO_CHAR(end_interval_time, 'DD/MM/YYYY HH24:MI') snaphist_end_time,
  6    error_count
  7    FROM dba_hist_snapshot
  8    WHERE end_interval_time 
  9    BETWEEN TO_TIMESTAMP('18-06-2016 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND TO_TIMESTAMP('20-06-2016 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
 10    ORDER BY snaphist_dbid, snaphist_snap_id, 2;

        DBID SNAP_ID BEGIN_TIME                   END_TIME                     ERROR_COUNT
------------ ------- ---------------------------- ---------------------------- -----------
  3642494801   19504 18/06/2016 00:00             18/06/2016 00:15                       0
               19505 18/06/2016 00:15             18/06/2016 00:30                       0
               19506 18/06/2016 00:30             18/06/2016 00:45                       0
               19507 18/06/2016 00:45             18/06/2016 01:00                       0
               19508 18/06/2016 01:00             18/06/2016 01:15                       0
               19509 18/06/2016 01:15             18/06/2016 01:30                       0
               19510 18/06/2016 01:30             18/06/2016 01:45                       0
               19511 18/06/2016 01:45             18/06/2016 02:00                       0
               19512 18/06/2016 02:00             18/06/2016 02:15                       0
               19513 18/06/2016 02:15             18/06/2016 02:30                       0
               19514 18/06/2016 02:30             18/06/2016 02:45                       0
               19515 18/06/2016 02:45             18/06/2016 03:00                       0
               19516 18/06/2016 03:00             18/06/2016 03:15                       0
               19517 18/06/2016 03:15             18/06/2016 03:30                       0
               19518 18/06/2016 03:30             18/06/2016 03:45                       0
               19519 18/06/2016 03:45             18/06/2016 04:00                       0
               19520 18/06/2016 04:00             18/06/2016 04:15                       0
               19521 18/06/2016 04:15             18/06/2016 04:30                       0
               19522 18/06/2016 04:30             18/06/2016 04:45                       0
               19523 18/06/2016 04:45             18/06/2016 05:00                       0
               19524 18/06/2016 05:00             18/06/2016 05:15                       0
               19525 18/06/2016 05:15             18/06/2016 05:30                       0
               19526 18/06/2016 05:30             18/06/2016 05:45                       0
               19527 18/06/2016 05:45             18/06/2016 06:00                       0
               19528 18/06/2016 06:00             18/06/2016 06:15                       0
               19529 18/06/2016 06:15             18/06/2016 06:31                       0
               19530 18/06/2016 06:31             18/06/2016 06:45                       0
               19531 18/06/2016 06:45             18/06/2016 07:00                       0
               19532 18/06/2016 07:00             18/06/2016 07:15                       0
               19533 18/06/2016 07:15             18/06/2016 07:30                       0
               19534 18/06/2016 07:30             18/06/2016 07:45                       0
               19535 18/06/2016 07:45             18/06/2016 08:00                       0
               19536 18/06/2016 08:00             18/06/2016 08:15                       0
               19537 18/06/2016 08:15             18/06/2016 08:30                       0
               19538 18/06/2016 08:30             18/06/2016 08:45                       0
               19539 18/06/2016 08:45             18/06/2016 09:00                       0
               19540 18/06/2016 09:00             18/06/2016 09:15                       0
               19541 18/06/2016 09:15             18/06/2016 09:30                       0
               19542 18/06/2016 09:30             18/06/2016 09:45                       0
               19543 18/06/2016 09:45             18/06/2016 10:00                       0
               19544 18/06/2016 10:00             18/06/2016 10:15                       0
               19545 18/06/2016 10:15             18/06/2016 10:30                       0
               19546 18/06/2016 10:30             18/06/2016 10:45                       0
               19547 18/06/2016 10:45             18/06/2016 11:00                       0
               19548 18/06/2016 11:00             18/06/2016 11:15                       0
               19549 18/06/2016 11:15             18/06/2016 11:30                       0
               19550 18/06/2016 11:30             18/06/2016 11:45                       0
               19551 18/06/2016 11:45             18/06/2016 12:00                       0
               19552 18/06/2016 12:00             18/06/2016 12:15                       0
               19553 18/06/2016 12:15             18/06/2016 12:30                       0
               19554 18/06/2016 12:30             18/06/2016 12:45                       0
               19555 18/06/2016 12:45             18/06/2016 13:00                       0
               19556 18/06/2016 13:00             18/06/2016 13:15                       0
               19557 18/06/2016 13:15             18/06/2016 13:30                       0
               19558 18/06/2016 13:30             18/06/2016 13:45                       0
               19559 18/06/2016 13:45             18/06/2016 14:00                       0
               19560 18/06/2016 14:00             18/06/2016 14:15                       0
               19561 18/06/2016 14:15             18/06/2016 14:30                       0
               19562 18/06/2016 14:30             18/06/2016 14:45                       0
               19563 18/06/2016 14:45             18/06/2016 15:00                       0
               19564 18/06/2016 15:00             18/06/2016 15:15                       0
               19565 18/06/2016 15:15             18/06/2016 15:30                       0
               19566 18/06/2016 15:30             18/06/2016 15:45                       0
               19567 18/06/2016 15:45             18/06/2016 16:00                       0
               19568 18/06/2016 16:00             18/06/2016 16:15                       0
               19569 18/06/2016 16:15             18/06/2016 16:30                       0
               19570 18/06/2016 16:30             18/06/2016 16:45                       0
               19571 18/06/2016 16:45             18/06/2016 17:00                       0
               19572 18/06/2016 17:00             18/06/2016 17:15                       0
               19573 18/06/2016 17:15             18/06/2016 17:30                       0
               19574 18/06/2016 17:30             18/06/2016 17:45                       0
               19575 18/06/2016 17:45             18/06/2016 18:00                       0
               19576 18/06/2016 18:00             18/06/2016 18:15                       0
               19577 18/06/2016 18:15             18/06/2016 18:30                       0
               19578 18/06/2016 18:30             18/06/2016 18:45                       0
               19579 18/06/2016 18:45             18/06/2016 19:00                       0
               19580 18/06/2016 19:00             18/06/2016 19:15                       0
               19581 18/06/2016 19:15             18/06/2016 19:30                       0
               19582 18/06/2016 19:30             18/06/2016 19:45                       0
               19583 18/06/2016 19:45             18/06/2016 20:00                       0
               19584 18/06/2016 20:00             18/06/2016 20:15                       0
               19585 18/06/2016 20:15             18/06/2016 20:30                       0
               19586 18/06/2016 20:30             18/06/2016 20:45                       0
               19587 18/06/2016 20:45             18/06/2016 21:00                       0
               19588 18/06/2016 21:00             18/06/2016 21:15                       0
               19589 18/06/2016 21:15             18/06/2016 21:30                       0
               19590 18/06/2016 21:30             18/06/2016 21:45                       0
               19591 18/06/2016 21:45             18/06/2016 22:00                       0
               19592 18/06/2016 22:00             18/06/2016 22:15                       0
               19593 18/06/2016 22:15             18/06/2016 22:30                       0
               19594 18/06/2016 22:30             18/06/2016 22:45                       0
               19595 18/06/2016 22:45             18/06/2016 23:00                       0
               19596 18/06/2016 23:00             18/06/2016 23:15                       0
               19597 18/06/2016 23:15             18/06/2016 23:30                       0
               19598 18/06/2016 23:30             18/06/2016 23:45                       0
               19599 18/06/2016 23:45             19/06/2016 00:00                       0
               19600 19/06/2016 00:00             19/06/2016 00:15                       0
               19601 19/06/2016 00:15             19/06/2016 00:30                       0
               19602 19/06/2016 00:30             19/06/2016 00:45                       0
               19603 19/06/2016 00:45             19/06/2016 01:00                       0
               19604 19/06/2016 01:00             19/06/2016 01:15                       0
               19605 19/06/2016 01:15             19/06/2016 01:30                       0
               19606 19/06/2016 01:30             19/06/2016 01:45                       0
               19607 19/06/2016 01:45             19/06/2016 02:00                       0
               19608 19/06/2016 02:00             19/06/2016 02:15                       0
               19609 19/06/2016 02:15             19/06/2016 02:30                       0
               19610 19/06/2016 02:30             19/06/2016 02:45                       0
               19611 19/06/2016 02:45             19/06/2016 03:00                       0
               19612 19/06/2016 03:00             19/06/2016 03:15                       0
               19613 19/06/2016 03:15             19/06/2016 03:30                       0
               19614 19/06/2016 03:30             19/06/2016 03:45                       0
               19615 19/06/2016 03:45             19/06/2016 04:00                       0
               19616 19/06/2016 04:00             19/06/2016 04:15                       0
               19617 19/06/2016 04:15             19/06/2016 04:30                       0
               19618 19/06/2016 04:30             19/06/2016 04:45                       0
               19619 19/06/2016 04:45             19/06/2016 05:00                       0
               19620 19/06/2016 05:00             19/06/2016 05:15                       0
               19621 19/06/2016 05:15             19/06/2016 05:30                       0
               19622 19/06/2016 05:30             19/06/2016 05:45                       0
               19623 19/06/2016 05:45             19/06/2016 06:00                       0
               19624 19/06/2016 06:00             19/06/2016 06:15                       0
               19625 19/06/2016 06:15             19/06/2016 06:30                       0
               19626 19/06/2016 06:30             19/06/2016 06:45                       0
               19627 19/06/2016 06:45             19/06/2016 07:00                       0
               19628 19/06/2016 07:00             19/06/2016 07:15                       0
               19629 19/06/2016 07:15             19/06/2016 07:30                       0
               19630 19/06/2016 07:30             19/06/2016 07:45                       0
               19631 19/06/2016 07:45             19/06/2016 08:00                       0
               19632 19/06/2016 08:00             19/06/2016 08:15                       0
               19633 19/06/2016 08:15             19/06/2016 08:30                       0
               19634 19/06/2016 08:30             19/06/2016 08:45                       0
               19635 19/06/2016 08:45             19/06/2016 09:00                       0
               19636 19/06/2016 09:00             19/06/2016 09:15                       0
               19637 19/06/2016 09:15             19/06/2016 09:30                       0
               19638 19/06/2016 09:30             19/06/2016 09:45                       0
               19639 19/06/2016 09:45             19/06/2016 10:00                       0
               19640 19/06/2016 10:00             19/06/2016 10:15                       0
               19641 19/06/2016 10:15             19/06/2016 10:30                       0
               19642 19/06/2016 10:30             19/06/2016 10:45                       0
               19643 19/06/2016 10:45             19/06/2016 11:00                       0
               19644 19/06/2016 11:00             19/06/2016 11:15                       0
               19645 19/06/2016 11:15             19/06/2016 11:30                       0
               19646 19/06/2016 11:30             19/06/2016 11:45                       0
               19647 19/06/2016 11:45             19/06/2016 12:00                       0
               19648 19/06/2016 12:00             19/06/2016 12:15                       0
               19649 19/06/2016 12:15             19/06/2016 12:30                       0
               19650 19/06/2016 12:30             19/06/2016 12:45                       0
               19651 19/06/2016 12:45             19/06/2016 13:00                       0
               19652 19/06/2016 13:00             19/06/2016 13:15                       0
               19653 19/06/2016 13:15             19/06/2016 13:30                       0
               19654 19/06/2016 13:30             19/06/2016 13:45                       0
               19655 19/06/2016 13:45             19/06/2016 14:00                       0
               19656 19/06/2016 14:00             19/06/2016 14:15                       0
               19657 19/06/2016 14:15             19/06/2016 14:30                       0
               19658 19/06/2016 14:30             19/06/2016 14:45                       0
               19659 19/06/2016 14:45             19/06/2016 15:00                       0
               19660 19/06/2016 15:00             19/06/2016 15:15                       0
               19661 19/06/2016 15:15             19/06/2016 15:30                       0
               19662 19/06/2016 15:30             19/06/2016 15:45                       0
               19663 19/06/2016 15:45             19/06/2016 16:00                       0
               19664 19/06/2016 16:00             19/06/2016 16:15                       0
               19665 19/06/2016 16:15             19/06/2016 16:30                       0
               19666 19/06/2016 16:30             19/06/2016 16:45                       0
               19667 19/06/2016 16:45             19/06/2016 17:00                       0
               19668 19/06/2016 17:00             19/06/2016 17:15                       0
               19669 19/06/2016 17:15             19/06/2016 17:30                       0
               19670 19/06/2016 17:30             19/06/2016 17:45                       0
               19671 19/06/2016 17:45             19/06/2016 18:00                       0
               19672 19/06/2016 18:00             19/06/2016 18:15                       0
               19673 19/06/2016 18:15             19/06/2016 18:30                       0
               19674 19/06/2016 18:30             19/06/2016 18:45                       0
               19675 19/06/2016 18:45             19/06/2016 19:00                       0
               19676 19/06/2016 19:00             19/06/2016 19:15                       0
               19677 19/06/2016 19:15             19/06/2016 19:30                       0
               19678 19/06/2016 19:30             19/06/2016 19:45                       0
               19679 19/06/2016 19:45             19/06/2016 20:00                       0
               19680 19/06/2016 20:00             19/06/2016 20:15                       0
               19681 19/06/2016 20:15             19/06/2016 20:30                       0
               19682 19/06/2016 20:30             19/06/2016 20:45                       0
               19683 19/06/2016 20:45             19/06/2016 21:00                       0
               19684 19/06/2016 21:00             19/06/2016 21:15                       0
               19685 19/06/2016 21:15             19/06/2016 21:30                       0
               19686 19/06/2016 21:30             19/06/2016 21:45                       0
               19687 19/06/2016 21:45             19/06/2016 22:00                       0
               19688 19/06/2016 22:00             19/06/2016 22:15                       0
               19689 19/06/2016 22:15             19/06/2016 22:30                       0
               19690 19/06/2016 22:30             19/06/2016 22:45                       0
               19691 19/06/2016 22:45             19/06/2016 23:00                       0
               19692 19/06/2016 23:00             19/06/2016 23:15                       0
               19693 19/06/2016 23:15             19/06/2016 23:30                       0
               19694 19/06/2016 23:30             19/06/2016 23:45                       0
               19695 19/06/2016 23:45             20/06/2016 00:00                       0
               19696 20/06/2016 00:00             20/06/2016 00:15                       0
               19697 20/06/2016 00:15             20/06/2016 00:30                       0
               19698 20/06/2016 00:30             20/06/2016 00:45                       0
               19699 20/06/2016 00:45             20/06/2016 01:00                       0
               19700 20/06/2016 01:00             20/06/2016 01:15                       0
               19701 20/06/2016 01:15             20/06/2016 01:30                       0
               19702 20/06/2016 01:30             20/06/2016 01:45                       0
               19703 20/06/2016 01:45             20/06/2016 02:00                       0
               19704 20/06/2016 02:00             20/06/2016 02:15                       0
               19705 20/06/2016 02:15             20/06/2016 02:30                       0
               19706 20/06/2016 02:30             20/06/2016 02:45                       0
               19707 20/06/2016 02:45             20/06/2016 03:00                       0
               19708 20/06/2016 03:00             20/06/2016 03:15                       0
               19709 20/06/2016 03:15             20/06/2016 03:30                       0
               19710 20/06/2016 03:30             20/06/2016 03:45                       0
               19711 20/06/2016 03:45             20/06/2016 04:00                       0
               19712 20/06/2016 04:00             20/06/2016 04:15                       0
               19713 20/06/2016 04:15             20/06/2016 04:30                       0
               19714 20/06/2016 04:30             20/06/2016 04:45                       0
               19715 20/06/2016 04:45             20/06/2016 05:00                       0
               19716 20/06/2016 05:00             20/06/2016 05:15                       0
               19717 20/06/2016 05:15             20/06/2016 05:30                       0
               19718 20/06/2016 05:30             20/06/2016 05:45                       0
               19719 20/06/2016 05:45             20/06/2016 06:00                       0
               19720 20/06/2016 06:00             20/06/2016 06:15                       0
               19721 20/06/2016 06:15             20/06/2016 06:30                       0
               19722 20/06/2016 06:30             20/06/2016 06:45                       0
               19723 20/06/2016 06:45             20/06/2016 07:00                       0
               19724 20/06/2016 07:00             20/06/2016 07:15                       0
               19725 20/06/2016 07:15             20/06/2016 07:30                       0
               19726 20/06/2016 07:30             20/06/2016 07:45                       0
               19727 20/06/2016 07:45             20/06/2016 08:00                       0
               19728 20/06/2016 08:00             20/06/2016 08:15                       0
               19729 20/06/2016 08:15             20/06/2016 08:30                       0
               19730 20/06/2016 08:30             20/06/2016 08:45                       0
               19731 20/06/2016 08:45             20/06/2016 09:00                       0
               19732 20/06/2016 09:00             20/06/2016 09:15                       0
               19733 20/06/2016 09:15             20/06/2016 09:30                       0
               19734 20/06/2016 09:30             20/06/2016 09:45                       0
               19735 20/06/2016 09:45             20/06/2016 10:00                       0
               19736 20/06/2016 10:00             20/06/2016 10:15                       0
               19737 20/06/2016 10:15             20/06/2016 10:30                       0
               19738 20/06/2016 10:30             20/06/2016 10:45                       0
               19739 20/06/2016 10:45             20/06/2016 11:00                       0
               19740 20/06/2016 11:00             20/06/2016 11:15                       0
               19741 20/06/2016 11:15             20/06/2016 11:30                       0
               19742 20/06/2016 11:30             20/06/2016 11:45                       0
               19743 20/06/2016 11:45             20/06/2016 12:00                       0
               19744 20/06/2016 12:00             20/06/2016 12:15                       0
               19745 20/06/2016 12:15             20/06/2016 12:30                       0
               19746 20/06/2016 12:30             20/06/2016 12:45                       0
               19747 20/06/2016 12:45             20/06/2016 13:00                       0
               19748 20/06/2016 13:00             20/06/2016 13:15                       0
               19749 20/06/2016 13:15             20/06/2016 13:30                       0
               19750 20/06/2016 13:30             20/06/2016 13:45                       0
               19751 20/06/2016 13:45             20/06/2016 14:00                       0
               19752 20/06/2016 14:00             20/06/2016 14:15                       0
               19753 20/06/2016 14:15             20/06/2016 14:30                       0
               19754 20/06/2016 14:30             20/06/2016 14:45                       0
               19755 20/06/2016 14:45             20/06/2016 15:00                       0
               19756 20/06/2016 15:00             20/06/2016 15:15                       0
               19757 20/06/2016 15:15             20/06/2016 15:30                       0
               19758 20/06/2016 15:30             20/06/2016 15:45                       0
               19759 20/06/2016 15:45             20/06/2016 16:00                       0
               19760 20/06/2016 16:00             20/06/2016 16:15                       0
               19761 20/06/2016 16:15             20/06/2016 16:30                       0
               19762 20/06/2016 16:30             20/06/2016 16:45                       0
               19763 20/06/2016 16:45             20/06/2016 17:00                       0
               19764 20/06/2016 17:00             20/06/2016 17:15                       0
               19765 20/06/2016 17:15             20/06/2016 17:30                       0
               19766 20/06/2016 17:30             20/06/2016 17:45                       0
               19767 20/06/2016 17:45             20/06/2016 18:00                       0
               19768 20/06/2016 18:00             20/06/2016 18:15                       0
               19769 20/06/2016 18:15             20/06/2016 18:30                       0
               19770 20/06/2016 18:30             20/06/2016 18:45                       0
               19771 20/06/2016 18:45             20/06/2016 19:00                       0
               19772 20/06/2016 19:00             20/06/2016 19:15                       0
               19773 20/06/2016 19:15             20/06/2016 19:30                       0
               19774 20/06/2016 19:30             20/06/2016 19:45                       0
               19775 20/06/2016 19:45             20/06/2016 20:00                       0
               19776 20/06/2016 20:00             20/06/2016 20:15                       0
               19777 20/06/2016 20:15             20/06/2016 20:30                       0
               19778 20/06/2016 20:30             20/06/2016 20:45                       0
               19779 20/06/2016 20:45             20/06/2016 21:00                       0
               19780 20/06/2016 21:00             20/06/2016 21:15                       0
               19781 20/06/2016 21:15             20/06/2016 21:30                       0
               19782 20/06/2016 21:30             20/06/2016 21:45                       0
               19783 20/06/2016 21:45             20/06/2016 22:00                       0
               19784 20/06/2016 22:00             20/06/2016 22:15                       0
               19785 20/06/2016 22:15             20/06/2016 22:30                       0
               19786 20/06/2016 22:30             20/06/2016 22:45                       0
               19787 20/06/2016 22:45             20/06/2016 23:00                       0
               19788 20/06/2016 23:00             20/06/2016 23:15                       0
               19789 20/06/2016 23:15             20/06/2016 23:30                       0
               19790 20/06/2016 23:30             20/06/2016 23:45                       0


288 rows selected.

sys@sl01$1 SQL> 

Veja que estou solicitando todos os snapshots entre os dias 18 e 20. A partir disso, iniciamos a captura do workload desse intervalo de tempo.

sys@sl01$1 SQL> exec DBMS_SQLTUNE.CREATE_SQLSET('EXA_SQLSET01');

PL/SQL procedure successfully completed.

sys@sl01$1 SQL> DECLARE
   2    baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
   3    BEGIN
   4       OPEN baseline_ref_cursor FOR
   5     select VALUE(p) from TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(19504, 19790, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL')) p;
   6  
   7       DBMS_SQLTUNE.LOAD_SQLSET('EXA_SQLSET01', baseline_ref_cursor);
   8     END;
   9    /

PL/SQL procedure successfully completed.

Temos agora, o nosso SQLSET criado a partir dos snapshots 19504 e 19790.

Conferindo …

sys@sl01$1 SQL> select id, name, owner, created, last_modified, statement_count from DBA_SQLSET ORDER BY id, created;

        ID NAME                           OWNER                     CREATED             LAST_MODIFIED       STATEMENT_COUNT
---------- ------------------------------ ------------------------- ------------------- ------------------- ---------------
         3 EXA_SQLSET01                   SYS                       21/06/2016 07:36:37 21/06/2016 07:40:12             475

 

2) Carga de trabalho atual – Cursor Cache

Outra maneira de capturar o workload do seu database é através do cursor de dados. Nesse método nada mais é do que ativar um tempo estimado e deixar capturando as cargas em tempo real que está acontecendo no momento.

No exemplo abaixo, decidimos capturar o nosso SQL Tuning set baseado no workload de 2 horas do nosso database.

sys@sl01$1 SQL> BEGIN
  2       DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name=>'EXA_SQLSET02');
  3  END;
  4  /

PL/SQL procedure successfully completed.

sys@sl01$1 SQL> BEGIN
  2        DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
  3          sqlset_name     => 'EXA_SQLSET02',
  4          time_limit      => 7200,
  5          repeat_interval => 10
  6  ); END;
  7  /

PL/SQL procedure successfully completed.

 

Executando o Exadata Simulation – tcellsim.sql

Uma vez que o SQLSET está criado, o próximo passo é executar o Exadata Simulation através do script tcellsim.sql

O script tcellsim.sql fica localizado no $ORACLE_HOME/rdbms/admin do binário do Oracle.

Veja abaixo a execução:

sys@sl01$1 SQL> @?/rdbms/admin/tcellsim

10 Most active SQL tuning sets
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NAME                OWNER             SQL_COUNT  DESCP
------------------  ----------------- ----------  --------------------
EXA_SQLSET02        SYS                  1598


Specify the name and owner of SQL tuning set to use
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for sts_name: EXA_SQLSET02
Enter value for sts_owner: SYS


 >> SQL tuning set specified: EXA_SQLSET02 owned by SYS


Run Cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 1. create a spa analysis task to test cell simulation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 >> Name of SPA analysis task: TASK_484

>> 2. Test execute statements with cell simulatin DISABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 3. Test execute statements with cell simulation ENABLED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

>> 4. Compare peformance and generate analysis report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

General Information
---------------------------------------------------------------------------------------------

 Task Information:        Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : TASK_484         SQL Tuning Set Name        : EXA_SQLSET02
  Task Owner   : SYS              SQL Tuning Set Owner       : SYS
  Description  :                  Total SQL Statement Count  : 1598

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : EXEC_462                  Started             : 06/21/2016 18:44:27
  Execution Type             : COMPARE PERFORMANCE       Last Updated        : 06/21/2016 20:44:27
  Description                :                           Global Time Limit   : UNLIMITED
  Scope                      : COMPREHENSIVE             Per-SQL Time Limit  : UNUSED
  Status                     : COMPLETED                 Number of Errors    : 3
  Number of Unsupported SQL  : 2

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                          After Change Execution:
 ---------------------------------------------     ---------------------------------------------
  Execution Name      : cell_simulation_DISABLED   Execution Name      : cell_simulation_ENABLED
  Execution Type      : TEST EXECUTE               Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE              Scope               : COMPREHENSIVE
  Status              : COMPLETED                  Status              : COMPLETED
  Started             : 06/21/2016 21:33:00        Started             : 06/21/2016 21:38:45
  Last Updated        : 06/21/2016 21:38:43        Last Updated        : 06/21/2016 21:44:25
  Global Time Limit   : UNLIMITED                  Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                     Per-SQL Time Limit  : UNUSED
  Number of Errors    : 3                          Number of Errors    : 3

 
 ---------------------------------------------
 Comparison Metric: IO_INTERCONNECT_BYTES
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------


Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  6.06%
 Improvement Impact  :  18.50%
 Regression Impact   :  -4.01%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall          1598                    0
 Improved          340                    0
 Regressed           2                    0
 Unchanged        1251                    0
 with Errors         3                    0
 Unsupported         2                    0

Top 10 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
|             |               | Impact on | Execution | Metric     |   Metric    |   Impact |
| object_id   | sql_id        | Workload  | Frequency | Before     |   After     |   on SQL |
---------------------------------------------------------------------------------------------
| 12341       | 05quhx2czmaau |   7.41%   |       582 | 3987577734 |   93984294  |   97.64% |
| 98271       | 07n9yv8rac2qq |   4.22%   |        38 |   85698319 |    3989341  |   95.34% |
| 24183       | 09p5nmnfq5gwg |   2.19%   |        41 |        779 |          0  |     100% |
| 82348       | 0za8d11cxbpr2 |   -.09%   |      2317 |   53382262 |   59460726  |  -11.39% |
| 24210       | 00ajuu5902429 |  -3.22%   |     22122 |       3894 |          0  |     100% |
| 22349       | 10s3r3f17ccu3 |   -.02%   |       314 |    5505024 |     294712  |   94.70% |
| 18820       | 1gfaj4z5hn1kf |   -.02%   |   1234112 |   13153621 |      32768  |   99.75% |
-----------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------

O Exadata Simulation executa o SQL Set duas vezes, assim como falamos e no final apresenta o report.

Várias consultas foram mensuradas pelo Exadata simulation com uma melhora significativa, como é o caso da consulta 05quhx2czmaau que apresentou uma melhoria de 97.64%. Outros sqlids como os 09p5nmnfq5gwg e 00ajuu5902429, o Exadata Simulation calcula uma melhora de 100%.

Como apresentamos acima, o Exadata Simulation é uma ótima ferramenta e de fácil acesso para avaliar o desempenho do seu banco de dados em um storage cell do Exadata (offloading de dados, rede infiniband, etc ..). É claro, que uma avaliação como essa não substituí um teste real em um HW Exadata com todas as features trabalhando em conjunto, mas é um ponto de partida para não trabalhar no escuro.

Oracle 12c In-Memory Column Store – Parte 1: Quando apenas o “In-Memory” não é o bastante.
April 6, 2016

database-in-memory-01-2595522

Essa é uma série de posts sobre a nova feature “Oracle In-Memory Column Store” presente na versão do Oracle 12c. O Oracle há muitos anos, tem fornecido a leitura de blocos diretamente da memória RAM através do buffer cache (v$bh). Ao longo desses anos a Oracle tem feito um grande trabalho, aperfeiçoando cada vez mais o algoritmo LRU afim de otimizar as buscas por blocos quentes e mantê-los cada vez por mais tempo em memória para um acesso muito mais rápido, servindo como um verdadeiro “cache” para o banco de dados.

Com a vinda do IMDB, a arquitetura e a performance muda completamente comparado a leitura no buffer cache da memória do database, a começar pela maneira como o Oracle armazena os dados em memória. Assim como o nome da feature sugere, o Oracle In-Memory Column Store, passa agora a armazenar os dados de forma colunar, totalmente otimizado para a leitura via SDRAM, trabalhando como um verdadeiro DBMS orientado a coluna: Column-oriented DBMS

Não é preciso dizer que a RAM é muito mais rápida que um acesso a disco (isso todo mundo já sabe), assim não é nem preciso comentar ou mostrar aqui, que com a utilização do IMDB o ganho de performance é absurdo. Mas … será que o IMDB vem para resolver todo e qualquer tipo de problema na performance do ambiente?! O que pretendo apresentar nessa série de posts é justamente casos como esses, evidências e provas na utilização da nova feature, que assim como qualquer outra, apresentar suas vantagens e desvantagens e melhores formas de se utilizar.

É incrível a quantidade de DBAs e empresas que acreditam só em marketing mágica, que com apenas um “alter table” todos os problemas de performance irão desaparecer. IMDB não é sinônimo de “desligar o cérebro do DBA”.

A partir de agora, vou apresentar um comportamento bastante incomum em alguns ambientes com o Oracle Inmemory. Através do utilitário Swingbench criei a tabela SOE.ORDER_ITEMS de 12G. Essa será a nossa tabela teste para o uso do IMDB. Aqui mais detalhes da tabela:

sys@ora01 SQL> @size soe.order_items                                                                                           
                                                                                           
OWNER                     SEGMENT_NAME                   SEGMENT_TYPE             SIZE_MB  
------------------------- ------------------------------ ------------------ ------------- 
SOE                       ORDER_ITEMS                    TABLE                   12,398.0 
                                                                            -------------
sum                                                                              12,398.0

1 row selected.

sys@ora01 SQL> -- Atualizando as estatísticas da tabela:
sys@ora01 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SOE', 'ORDER_ITEMS', method_opt => 'FOR TABLE FOR ALL COLUMNS SIZE AUTO', cascade => true);

PL/SQL procedure successfully completed.

sys@ora01 SQL> @tbstat soe.order_items

=============================================
===        O B J E C T   T A B L E        ===
=============================================

OWNER: SOE                              TABLE: ORDER_ITEMS

           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ORDER_ID                        NOT NULL NUMBER(12)
    2      LINE_ITEM_ID                    NOT NULL NUMBER(3)
    3      PRODUCT_ID                      NOT NULL NUMBER(6)
    4      UNIT_PRICE                               NUMBER(8,2)
    5      QUANTITY                                 NUMBER(8)
    6      DISPATCH_DATE                            DATE
    7      RETURN_DATE                              DATE
    8      GIFT_WRAP                                VARCHAR2(20)
    9      CONDITION                                VARCHAR2(20)
   10      SUPPLIER_ID                              NUMBER(6)
   11      ESTIMATED_DELIVERY                       DATE

================================================================================
Table Statistics
================================================================================
TABLE_NAME               : ORDER_ITEMS
STATUS                   : VALID
LAST_ANALYZED            : 29/03/2016 22:08:46
PARTITIONED              : NO
TEMPORARY                : N
DEGREE                   : 1
BUFFER_POOL              : DEFAULT
CACHE                    : NO
CELL_FLASH_CACHE         : DEFAULT
COMPRESSION              : DISABLED
RESULT_CACHE             : DEFAULT
ROW_MOVEMENT             : DISABLED
LOGGING                  : YES
SAMPLE_SIZE              : 85789721
==========================================================================================================================================================================
Column Statistics
==========================================================================================================================================================================
ID#   NAME                                       ANALYZED               NDV           DENSITY        # NULLS   # BUCKETS          SAMPLE      HISTOGRAM
==========================================================================================================================================================================
1     ORDER_ID                                29-03-16 22:08       28434432        0.00000004              0           1        85789721           NONE
2     LINE_ITEM_ID                            29-03-16 22:08              5        0.20000000              0           1        85789721           NONE
3     PRODUCT_ID                              29-03-16 22:08            999        0.00100100              0           1        85789721           NONE
4     UNIT_PRICE                              29-03-16 22:08           2000        0.00050000              0           1        85789721           NONE
5     QUANTITY                                29-03-16 22:08             10        0.10000000              0           1        85789721           NONE
6     DISPATCH_DATE                           29-03-16 22:08            124        0.00806452              0           1        85789721           NONE
7     RETURN_DATE                             29-03-16 22:08           4507        0.00022188       84059125           1         1730596           NONE
8     GIFT_WRAP                               29-03-16 22:08              6        0.16666667              0           1        85789721           NONE
9     CONDITION                               29-03-16 22:08              3        0.33333333              0           1        85789721           NONE
10    SUPPLIER_ID                             29-03-16 22:08            999        0.00100100              0           1        85789721           NONE
11    ESTIMATED_DELIVERY                      29-03-16 22:08           4507        0.00022188              0           1        85789721           NONE
==========================================================================================================================================================================
Index Information
==========================================================================================================================================================================
INDEX_NAME                 COLUMNS                                               ANALYZED           DIST_KEYS     DENSITY       NUM_ROWS     UNIQUE    VISIBLE    STATUS
==========================================================================================================================================================================
ITEM_ORDER_IX              ORDER_ID                                           29-03-16 22:08         28434432   0.0000000       82834901       NO        YES        OK
ITEM_PRODUCT_IX            PRODUCT_ID                                         29-03-16 22:08              999   0.0010010       79984685       NO         NO        OK
ORDER_ITEMS_PK             LINE_ITEM_ID ,ORDER_ID                             29-03-16 22:08         85615194   0.0000000       85615194      YES        YES        OK

sys@ora01 SQL> 

Veja que nesse momento, a tabela SOE.ORDER_ITEMS está com o InMemory desabilitado:

sys@ora01 SQL> select owner, table_name, inmemory from dba_tables where owner='SOE' and table_name='ORDER_ITEMS';

OWNER                     TABLE_NAME                     INMEMORY
------------------------- ------------------------------ --------
SOE                       ORDER_ITEMS                    DISABLED

1 row selected.

Vamos executar uma consulta de encontro a tabela e analisar os resultados. Veja que estou forçando a consulta a ser executada em serial e obtendo o máximo de estatísticas para análise, isso através dos hints NO_PARALLEL e GATHER_PLAN_STATISTICS respectivamente.

sys@ora01 SQL> SET TIMING ON
sys@ora01 SQL> SELECT /*+ monitor gather_plan_statistics no_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p;

SUM(QUANTITY)
-------------
      1376607

Elapsed: 00:00:43.51

1 row selected.

sys@ora01 SQL> -- Buscando o sqlid da query acima
sys@ora01 SQL> @lastsql

HASH_VALUE SQL_ID          CH#    PLAN_HASH HASH_HEX   SQL_TEXT
---------- ------------- ----- ------------ ---------- -----------------------------------------------------------------------------------------------------------------
3284254310 97h2yq31w3gm6     0   2407813087  c3c1be66  SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL */ sum(QUANTITY) from  soe.order_items where PRODUCT_ID=:p

Vamos analisar agora, a consulta 97h2yq31w3gm6:

sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6', report_level=>'ALL', type => 'TEXT') as report FROM dual;

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ monitor gather_plan_statistics no_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 SQL ID              :  97h2yq31w3gm6
 SQL Execution ID    :  16777216
 Duration            :  44s
 Module/Action       :  SQL*Plus/-
 Fetch Calls         :  1

Binds
========================================================================================================================
| Name | Position |  Type  |                                           Value                                           |
========================================================================================================================
| :P   |        1 | NUMBER | 509                                                                                       |
========================================================================================================================

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|      44 |    4.92 |       39 |     1 |   251K | 250K |   2GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=2407813087)
=================================================================================================================================================================================
| Id |               Operation                |      Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |      Activity Detail       |
|    |                                        |                 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |        (# samples)         |
=================================================================================================================================================================================
|  0 | SELECT STATEMENT                       |                 |         |      |        39 |     +2 |     1 |        1 |      |       |          |                            |
|  1 |   SORT AGGREGATE                       |                 |       1 |      |        39 |     +2 |     1 |        1 |      |       |          |                            |
|  2 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS     |   85876 |  697 |        40 |     +1 |     1 |     306K | 249K |   2GB |   100.00 | db file parallel read (40) |
|  3 |     INDEX RANGE SCAN                   | ITEM_PRODUCT_IX |   85876 |    2 |        39 |     +2 |     1 |     306K |  643 |   5MB |          |                            |
=================================================================================================================================================================================


1 row selected.

sys@ora01 SQL> SET HEADING ON

Temos aqui o plano da nossa consulta de encontra a tabela ORDER_ITEMS. Veja que a consulta levou cerca de 44s para ser processada, sendo que, 39s foram gastos em I/O e 5s em CPU. A leitura total em disco foi cerca de 2G.

A partir de agora, iniciaremos o nosso teste com o Oracle In-memory 12c. O objetivo aqui, é colocar a nossa tabela ORDER_ITEMS em modo In-Memory Column Store e realizar novamente a mesma consulta e obter algumas conclusões. O primeiro passo, é popular a coluna na memória:

sys@ora01 SQL> ALTER TABLE soe.order_items INMEMORY;

Table altered.

sys@ora01 SQL> ALTER SESSION SET "_inmemory_populate_wait"=TRUE;

Session altered.

sys@ora01 SQL> EXEC DBMS_INMEMORY.POPULATE('SOE','ORDER_ITEMS');

PL/SQL procedure successfully completed.

sys@ora01 SQL> COLUMN seg_inmem_size FORMAT 99,999,990.9 HEADING 'In-Memory|SIZE_MB'
sys@ora01 SQL> COLUMN seg_orig_size_megs FORMAT 99,999,990.9 HEADING 'ORIGINAL|SIZE_MB'
sys@ora01 SQL> COLUMN seg_megs_not_populated FORMAT 99,999,990.9 HEADING 'NOT POPULATE|SIZE_MB'
sys@ora01 SQL> COLUMN populate_status HEADING 'POPULATE|STATUS'
sys@ora01 SQL> SELECT
2          inst_id 
3          , v.owner
4          , v.segment_name
5          , v.populate_status
6          , v.inmemory_size/1024/1024 seg_inmem_size
7          , v.bytes/(1024*1024) seg_orig_size_megs
8          , v.bytes_not_populated/(1024*1024) seg_megs_not_populated
9          , v.inmemory_priority
10         , v.inmemory_compression 
11     FROM v$im_segments v 
12     WHERE UPPER(segment_name) LIKE UPPER(CASE WHEN INSTR('&1','.') > 0 THEN substr('&1',instr('&1','.')+1) ELSE '&1' END )
13     AND UPPER(owner) LIKE UPPER(CASE WHEN INSTR('&1','.') > 0 THEN SUBSTR('&1',1,instr('&1','.')-1) ELSE user END )
14   / 

                                                         POPULATE      In-Memory      ORIGINAL  NOT POPULATE   In-Memory      In-Memory
OWNER                     SEGMENT_NAME                   STATUS          SIZE_MB       SIZE_MB       SIZE_MB    PRIORITY      COMPRESSION
------------------------- ------------------------------ --------- ------------- -------------  -------------  --------- -----------------
SOE                       ORDER_ITEMS                    COMPLETED       3,554.1      12,398.0          0.0       NONE      FOR QUERY LOW

1 row selected.


Como mostra a view v$im_segments, veja que o status da nossa tabela está totalmente populada na área do InMemory, com um tamanho aproximadamente de 3500M.

Com a tabela na memória, iremos executar a mesma consulta e verificar os resultados, mas antes, vamos executar um flush no buffer cache/shared pool, para que os testes anteriores não sejam levados em consideração.

sys@ora01 SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

sys@ora01 SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

sys@ora01 SQL> SELECT /*+ monitor gather_plan_statistics no_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p;

SUM(QUANTITY)
-------------
      1376607

Elapsed: 00:00:43.03

1 row selected.

sys@ora01 SQL> @lastsql

HASH_VALUE SQL_ID          CH#    PLAN_HASH HASH_HEX   SQL_TEXT
---------- ------------- ----- ------------ ---------- -----------------------------------------------------------------------------------------------------------------
3284254310 97h2yq31w3gm6     0   2407813087  c3c1be66  SELECT /*+ monitor gather_plan_statistics no_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p

sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6', report_level=>'ALL', type => 'TEXT') as report FROM dual;


SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ monitor gather_plan_statistics no_parallel */ SUM(quantity) FROM soe.order_items WHERE product_id=:p

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 SQL ID              :  97h2yq31w3gm6
 SQL Execution ID    :  16777216
 Duration            :  44s
 Module/Action       :  SQL*Plus/-
 Fetch Calls         :  1

Binds
========================================================================================================================
| Name | Position |  Type  |                                           Value                                           |
========================================================================================================================
| :P   |        1 | NUMBER | 509                                                                                       |
========================================================================================================================

Global Stats
================================================================
| Elapsed |   Cpu   |    IO    | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
================================================================
|      44 |    4.92 |       39 |     1 |   251K | 250K |   2GB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=2407813087)
=================================================================================================================================================================================
| Id |               Operation                |      Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |      Activity Detail       |
|    |                                        |                 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |        (# samples)         |
=================================================================================================================================================================================
|  0 | SELECT STATEMENT                       |                 |         |      |        39 |     +2 |     1 |        1 |      |       |          |                            |
|  1 |   SORT AGGREGATE                       |                 |       1 |      |        39 |     +2 |     1 |        1 |      |       |          |                            |
|  2 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS     |   85876 |  697 |        40 |     +1 |     1 |     306K | 249K |   2GB |   100.00 | db file parallel read (40) |
|  3 |     INDEX RANGE SCAN                   | ITEM_PRODUCT_IX |   85876 |    2 |        39 |     +2 |     1 |     306K |  643 |   5MB |          |                            |
=================================================================================================================================================================================


1 row selected.

sys@ora01 SQL> SET HEADING ON

Nada mudou em relação a primeira execução. Veja que interessante, mesmo com a minha tabela totalmente carregada na memória via IMDB a quantidade de leitura física da primeira execução com a segunda foi exatamente a mesma. O tempo total da consulta também não mudou, levamos cerca de ~ 44s para retornar os dados.

Se a ORDER_ITEMS está agora totalmente disponível via InMemory Column Store, porque o Oracle está fazendo a mesma quantidade de leitura em disco? Ele não deveria buscar os dados em memória e evitar essa grande quantidade de leitura em disco?

Como regra geral (assim como acontece no SmartScan do Exadata), para que o otimizador do Oracle decida ir vi InMemory, um acesso “FULL SCAN” deve ser feito na tabela. Observe no plano de execução da consulta acima, que o caminho escolhido pelo Oracle é através do INDEX ITEM_PRODUCT_IX (via INDEX RANGE SCAN).

=================================================================================================================================================================================
| Id |               Operation                |      Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |      Activity Detail       |
|    |                                        |                 | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |        (# samples)         |
=================================================================================================================================================================================
|  2 |    TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS     |   85876 |  697 |        40 |     +1 |     1 |     306K | 249K |   2GB |   100.00 | db file parallel read (40) |
|  3 |     INDEX RANGE SCAN                   | ITEM_PRODUCT_IX |   85876 |    2 |        39 |     +2 |     1 |     306K |  643 

O Whitepaper do Oracle Database In-Memory 12c, é bem claro ao dizer que a busca dos dados via In-memory será feito apenas quando o acesso na tabela for via FULL SCAN. Assim jamais a nossa consulta acima irá consulta os dados presentes na nossa RAM, já que o otimizador está escolhendo o caminho através do index ITEM_PRODUCT_IX.

Podemos resolver essa situação adicionando o hint FULL na consulta, ou deixando o Index ITEM_PRODUCT_IX invisible. Veja só o resultado:

sys@ora01 SQL> alter index soe.ITEM_PRODUCT_IX invisible;

Index altered.

sys@ora01 SQL> SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL */ sum(QUANTITY) from  soe.order_items where PRODUCT_ID=:p;

SUM(QUANTITY)
-------------
      1376607

Elapsed: 00:00:00.05

1 row selected.

sys@ora01 SQL> SET HEADING OFF LINES 32767
sys@ora01 SQL> SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '97h2yq31w3gm6', report_level=>'ALL', type => 'TEXT') as report FROM dual;

SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR GATHER_PLAN_STATISTICS NO_PARALLEL */ sum(QUANTITY) from soe.order_items where PRODUCT_ID=:p

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SYS (1150:44245)
 SQL ID              :  97h2yq31w3gm6
 SQL Execution ID    :  16777217
 Duration            :  .041563s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@Flavios-MacBook-Pro.local (TNS V1-V3)
 Fetch Calls         :  1

Binds
========================================================================================================================
| Name | Position |  Type  |                                           Value                                           |
========================================================================================================================
| :P   |        1 | NUMBER | 509                                                                                       |
========================================================================================================================

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    0.04 |    0.04 |     0.00 |     0.00 |     1 |    249 |   18 | 144KB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=3419397814)
=========================================================================================================================================================
| Id |           Operation           |    Name     |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity | Activity Detail |
|    |                               |             | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |   (# samples)   |
=========================================================================================================================================================
|  0 | SELECT STATEMENT              |             |         |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  1 |   SORT AGGREGATE              |             |       1 |      |         1 |     +0 |     1 |        1 |      |       |          |                 |
|  2 |    TABLE ACCESS INMEMORY FULL | ORDER_ITEMS |   85876 | 7880 |         1 |     +0 |     1 |     306K |   12 | 98304 |          |                 |
=========================================================================================================================================================


1 row selected.

sys@ora01 SQL> 

Aqui podemos notar o poder do INMEMORY, observe o tempo, caiu de 44s para apenas 0.4s. Veja também que agora a operação de consulta não foi mais via INDEX RANGE SCAN e sim via TABLE ACCESS INMEMORY FULL, o que mostra que a busca dos dados foi exclusivamente na memória para a tabela ORDER_ITEMS. Outro ponto importante é a quantidade de leitura física que de 2G caiu para 144KB. O tempo 0.4s foi exclusivamente o tempo da CPU em processar os dados  (CPU Time(s)).

Chegamos aqui, com algumas conclusões:

  • Vou ter performance colocando todas as minhas tabelas em IMDB? É necessário testar, não tem outro caminho. Cada ambiente é única e existe muita variável no meio do caminho. Como os testes acima, mesmo ativando o IMDB, não vimos nenhuma melhora até analisar e corrigir.
  • Porque o Oracle decide um acesso via INDEX, sendo que meus dados estão na memória? Bom, isso vou explicar melhor em um próximo post, mas basicamente o Oracle considera o custo do INDEX menor, por isso acaba optando pela utilização do INDEX. Alguns parâmetros no banco também afetam essa decisão, vamos conferir isso melhor nos próximos posts.
  • Se o IMDB só funciona via FULL SCAN, devo deixar todos meus INDEXES invisíveis? NUNCA!!! Isso é um mito que surgiu com o Exadata. É importante lembra que tanto no IMDB como no Exadata, o INDEX não é um vilão. Um acesso via INDEX pode muitas vezes ser muito mais benéfico, já que o objetivo do INDEX é justamente economizar leitura em disco. A TI é um mundo lógico e deve ser sempre levado, como os gringos gostam de falar, com systematic approach. Não existe mágica. Nos próximos posts, vou mostrar melhor esse tema.
  • Vou precisar mudar minha aplicação para utilizar o IMDB? Como está na documentação Oracle, você não precisa mover uma vírgula da aplicação para utilizar o IMDB. Agora fazemos outra pergunta: Vou ter performance em todas as tabelas que coloquei no INMEMORY? Mais uma vez …. Depende! É necessário testar.
  • O IMDB realmente é performático? Sim, extremamente performático comparado com tudo que temos no mercado! Como disse no começo do post, não é necessário explicar a imensa diferença de performance da RAM contra o disco, todos já sabem. É necessário no entanto saber aproveitar e saber utilizar a feature da melhor forma. Como vimos nesse simples exemplo, uma consulta teste, caiu de 44s para 0.4s, e o mais importante, sem mudar uma vírgula na consulta.
  • Minha aplicação OLTP cheia de INDEX, vou ter algum ganho com o IMDB? Provavelmente. Mas uma vez: É necessário testar! Deve-se sempre avaliar, estatísticas, index, hints, etc …

Aguarde os próximos posts, tem muita coisa interessante sobre essa nova incrível feature do Oracle.

Até lá :)

 

Script para criação dinâmica de AWR
December 16, 2014

O relatórios do Automatic Workload Repository (AWR), é com certeza uma das maiores armas que temos para realizar troubleshooting em um ambiente banco de dados Oracle. A análise de AWR, é uma consequência natural do dia a dia de um DBA, eu mesmo (principalmente no início da carreira) já passei incontáveis horas e horas (e mais horas) investigando e tentando entender cada uma das informações fornecidas no relatório.

O gerador de AWR apresentado aqui neste post, foi criado através de uma necessidade que encontrei afim de obter o máximo de informações possíveis contidas nos relatórios. Como consultor, deparo com situações onde a análise de Performance Diagnostics and Tuning é realizada um dia depois do incidente, assim o AWR pode ser uma grande ajuda nesses casos.

Um grande erro que mascara as informações do AWR, é gerar o relatório com um grande período de dados, como por exemplo gerar um único report com informações contidas entre as 08 da manhã até as 6 da tarde, isso acaba mascarando os dados e muitas vezes escondendo o real problema com informações irrelevantes fazendo você perder muito tempo consultando um relatório e não apresenta nenhum dado util. Assim, nesses casos o recomendado é você gerar vários reports para cada intervalo das snap entre 08 da manhã até as 6 da tarde. É aí que entra o script gerador de AWR, que ao contrário do script fornecido pela Oracle awrrp.sql que gera apenas um único relatório entre dois pontos no tempo, o gerador AWR consegue produzir vários relatório AWR entre o range de snapshot id fornecido, tendo como exemplo o caso do relatório entre as 8 da manhã as 6 da tarde, o script gerador awr irá produzir um relatório AWR para as 08 da manhã, um outro relatório para as 9 da manhã, outro para as 10 da manhã e assim por diante … até o snapshot id da 6 horas da tarde.

Apenas 4 parâmetros serão solicitados para a execução do script:

1 parâmetro: O tipo do relatório que você deseja gerar, html ou text.
2 parâmetro: A quantidade de dias que será listado os snapid na tela. Apenas para poder visualizar os snapshotid disponíveis.
3 parâmetro: O snapshot id inicial
4 parâmetro: O snapshot id final

Veja a execução do script gerador_awr.sql:


SQL> @gerador_awr
====> Showing the AWR Settings Inverval/Retention

	   Minute	      Minute		   Days
Snapshot Interval Snapshot Retention Snapshot Retention
----------------- ------------------ ------------------
	       60	       11520		      8


====> Entering the type of AWR Report: [html/text]: text


====> Entering the number of days of snapshots listed: 1

	DBID INST_ID INSTANCE_NAME   STARTUP_TIME		  SNAP_ID BEGIN_TIME		       END_TIME 		    ERROR_COUNT
------------ ------- --------------- ---------------------------- ------- ---------------------------- ---------------------------- -----------
   665418562	   1 dbtst1	     11/10/2014 19:57		     2626 15/12/2014 00:00	       15/12/2014 01:00 		      0
								     2627 15/12/2014 01:00	       15/12/2014 02:00 		      0
								     2628 15/12/2014 02:00	       15/12/2014 03:00 		      0
								     2629 15/12/2014 03:00	       15/12/2014 04:00 		      0
								     2630 15/12/2014 04:00	       15/12/2014 05:00 		      0
								     2631 15/12/2014 05:00	       15/12/2014 06:00 		      0
								     2632 15/12/2014 06:00	       15/12/2014 07:00 		      0
								     2633 15/12/2014 07:00	       15/12/2014 08:00 		      0
								     2634 15/12/2014 08:00	       15/12/2014 09:00 		      0
								     2635 15/12/2014 09:00	       15/12/2014 10:00 		      0
								     2636 15/12/2014 10:00	       15/12/2014 11:00 		      0
								     2637 15/12/2014 11:00	       15/12/2014 12:00 		      0
								     2638 15/12/2014 12:00	       15/12/2014 13:00 		      0
								     2639 15/12/2014 13:00	       15/12/2014 14:00 		      0
								     2640 15/12/2014 14:00	       15/12/2014 15:00 		      0
								     2641 15/12/2014 15:00	       15/12/2014 16:00 		      0
								     2642 15/12/2014 16:00	       15/12/2014 17:00 		      0
								     2643 15/12/2014 17:00	       15/12/2014 18:00 		      0
								     2644 15/12/2014 18:00	       15/12/2014 19:00 		      0
								     2645 15/12/2014 19:00	       15/12/2014 20:00 		      0
								     2646 15/12/2014 20:00	       15/12/2014 21:00 		      0
								     2647 15/12/2014 21:00	       15/12/2014 22:00 		      0
								     2648 15/12/2014 22:00	       15/12/2014 23:00 		      0
								     2649 15/12/2014 23:00	       16/12/2014 00:00 		      0




====> Entering the BEGIN SNAP ID : 2630

====> Entering the END SNAP ID : 2640


Generation AWR SNAPID ...2630 TO 2640
WORKLOAD REPOSITORY report for

DB Name 	DB Id	 Instance     Inst Num Startup Time    Release	   RAC
------------ ----------- ------------ -------- --------------- ----------- ---
dbtst        665418562 dbtst1	     1 11-Oct-14 19:57 11.2.0.4.0  YES

Host Name	 Platform			  CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
dm01dbadm01.pass Linux x86 64-bit		    48	  24	   2	 251.99

	      Snap Id	   Snap Time	  Sessions Curs/Sess Instances
	    --------- ------------------- -------- --------- ---------
Begin Snap:	 2630 15-Dec-14 05:00:00     2,368	 4.9	     2
  End Snap:	 2631 15-Dec-14 06:00:03     2,368	 4.9	     2
   Elapsed:		  60.04 (mins)
   DB Time:		   0.17 (mins)

Load Profile			Per Second   Per Transaction  Per Exec	Per Call
~~~~~~~~~~~~~~~ 	   ---------------   --------------- --------- ---------
	     DB Time(s):	       0.0		 0.4	  0.00	    0.00
	      DB CPU(s):	       0.0		 0.4	  0.00	    0.00
      Redo size (bytes):	     634.1	    95,187.8
  Logical read (blocks):	      30.7	     4,603.3
	  Block changes:	       1.6	       240.5
 Physical read (blocks):	       0.0		 1.3
Physical write (blocks):	       0.2		33.8
       Read IO requests:	       0.0		 1.3
      Write IO requests:	       0.2		26.6
	   Read IO (MB):	       0.0		 0.0
	  Write IO (MB):	       0.0		 0.3
 RAC GC blocks received:	       0.2		32.7
   RAC GC blocks served:	       0.1		21.8
	     User calls:	       1.6	       233.1
	   Parses (SQL):	       1.0	       151.2
      Hard parses (SQL):	       0.0		 5.3
     SQL Work Area (MB):	       0.1		16.5
		 Logons:	       0.1		11.0
	 Executes (SQL):	       1.7	       249.7
	      Rollbacks:	       0.0		 0.0
	   Transactions:	       0.0

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	    Buffer Nowait %:  100.00	   Redo NoWait %:  100.00
	    Buffer  Hit   %:   99.97	In-memory Sort %:  100.00


output removido
output removido
...
...
output removido
output removido


	.00	   0	    0	     0	      0
			  ------------------------------------------------------

Dynamic Remastering Stats	   DB/Inst: PREPROD/preprod1  Snaps: 2638-2639

		  No data exists for this section of the report.
			  ------------------------------------------------------

End of Report


AWR begin snap 2630 to end snap 2631 generated, file: gen_awr_dbtst1_2630_2631_report.txt
AWR begin snap 2631 to end snap 2632 generated, file: gen_awr_dbtst1_2631_2632_report.txt
AWR begin snap 2632 to end snap 2633 generated, file: gen_awr_dbtst1_2632_2633_report.txt
AWR begin snap 2633 to end snap 2634 generated, file: gen_awr_dbtst1_2633_2634_report.txt
AWR begin snap 2634 to end snap 2635 generated, file: gen_awr_dbtst1_2634_2635_report.txt
AWR begin snap 2635 to end snap 2636 generated, file: gen_awr_dbtst1_2635_2636_report.txt
AWR begin snap 2636 to end snap 2637 generated, file: gen_awr_dbtst1_2636_2637_report.txt
AWR begin snap 2637 to end snap 2638 generated, file: gen_awr_dbtst1_2637_2638_report.txt
AWR begin snap 2638 to end snap 2639 generated, file: gen_awr_dbtst1_2638_2639_report.txt

SQL> 

No final é apresentado os 9 relatórios AWR entre o range de snapshot fornecido.

Aqui está todos os reports AWR criados localmente pela execução do script:

Flavios-MacBook-Pro:~ flaviosoares$ ll gen_awr_dbtst1_263*
-rw-r--r--  1 flaviosoares  staff  113049600 Dec 16 00:10 gen_awr_dbtst1_2630_2631_report.txt
-rw-r--r--  1 flaviosoares  staff  114753536 Dec 16 00:11 gen_awr_dbtst1_2631_2632_report.txt
-rw-r--r--  1 flaviosoares  staff  113672192 Dec 16 00:11 gen_awr_dbtst1_2632_2633_report.txt
-rw-r--r--  1 flaviosoares  staff  113213440 Dec 16 00:11 gen_awr_dbtst1_2633_2634_report.txt
-rw-r--r--  1 flaviosoares  staff  114327552 Dec 16 00:11 gen_awr_dbtst1_2634_2635_report.txt
-rw-r--r--  1 flaviosoares  staff  120291328 Dec 16 00:11 gen_awr_dbtst1_2635_2636_report.txt
-rw-r--r--  1 flaviosoares  staff  125730816 Dec 16 00:11 gen_awr_dbtst1_2636_2637_report.txt
-rw-r--r--  1 flaviosoares  staff  115408896 Dec 16 00:11 gen_awr_dbtst1_2637_2638_report.txt
-rw-r--r--  1 flaviosoares  staff  113082368 Dec 16 00:11 gen_awr_dbtst1_2638_2639_report.txt

Notas do script:

– O script foi testado nas versões Oracle 11g/12c.
– O script foi testado em RAC e Single instance.
– É necessário a permissão de leitura e escrita no diretório atual da execução do script
– O script detecta automaticamente se existe um restate de instância entre o range de snapshot id fornecido e o avisa sobre o fato.
– Caso você queria executar o script em ambiente Windows, você obterá uma falha na hora de remover os arquivos temporários RUN_gen_awr_report.sql e store_set_saved.sql.


PARA O DOWNLOAD DO SCRIPT: gerador_awr.sql


Quaisquer erros/sugestões/melhorias/bugs e afins por favor me avise, o script ainda está em fase de desenvolvimento …

Espero que esse script o ajude muito em seus troubleshooting :)

Um abraço

AWR – Medindo IOPS e Throughput de I/O do Oracle Database
August 27, 2014

Uma conseqüência natural do trabalho de um DBA é passar horas e horas em cima de um relatório AWR identificando problemas e causas de performance do banco de dados.

Um dos enganos mais comum da maioria é conseguir identificar o IOPS e throughput de I/O dentro do próprio Oracle AWR. Esse ponto é essencial para você conseguir definir a sua infra-estrutura de I/O para combinar com a sua aplicação e além de conseguir levantar dados para debater de forma inteligente com sua equipe de storage.

Antes, vamos realmente entender o que é IOPS e throughput de dados, que são termos completamente diferentes:

IOPS – É a unidade padrão que mensura uma operação de Input/Output (I/O) por segundo. Usado para descrever I/O rate de aplicações principalmente utilizado para acessos randômicos I/O (leituras de indexes) e aplicações do tipo OLTP.

Throughput (Mbytes/s) – Usado para mensurar grandes taxas de transferências de blocos. Usado principalmente para aplicações seqüenciais de dados como DSS e OLAP. (full table scan)

O relatório de AWR pode ser seu aliado para identificar essas métricas de I/O na sessão Instance Activity Stats:

IOPS = “physical read total I/O requests” + “physical write total I/O requests”
MBytes/s = “physical read total bytes” + “physical write total bytes”

Ou você pode utilizar a seguinte consulta para mostrar esses valores:

select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
snap_id
from dba_hist_sysmetric_summary group by snap_id
order by snap_id;

Exemplo relatório AWR:

physical read total IO requests   =  1,257.23
physical write total IO requests  =  1,746.11
physical read total bytes         =  76,314,718.10
physical write total bytes        =  85,321,521.11

Com os dados acima do AWR reportado, temos: ~ 3003 IOPS e ~ 155 Mbytes/s

Esse método é interessante ser utilizado após mudança de equipamentos/aplicação ou até mesmo identificar gargalos do ambiente e resing necessários de Hardware. Com isso em mãos fica mais fácil dialogar sobre esses temas.

Com o Oracle 11g é possível obter dados de IOPS e throughput através do Oracle Calibrate (DBMS_RESOURCE_MANAGER.CALIBRATE_IO) que é utilizado para identificar até onde o meu Hardware pode chegar, assim o Oracle acaba utilizando esse método para várias operações internas, como por exemplo o AUTO DOP. Aqui o contexto é um pouco diferente, através das métricas de IOPS e throughput identificados pelo AWR, eu consigo além de definir por tempo, posso alcançar a métrica onde minha aplicação ou hardware está mais consumindo de I/O.

Considerações finais:

– Para ambientes RAC, você deve agregar os valores de IOPS e MBytes/s para todas as instâncias do RAC. Assim o IOPS e MBbytes no ambiente cluster será a soma de todos os valores de cada node.

– Utilize o AWR em cima dos horários de maior utilização do ambiente.

– Nunca utilize relatórios AWR mais do que 1 hora. É comum encontramos relatórios AWR das 09 as 18 horas por exemplo, isso pode mascarar o real problema do seu banco de dados. Utilize tempos curtos de 1 hora baseado nos picos de maior cargas no banco de dados.

– Para alcançar IOPS e Throughput desejado, não apenas discos pode ser a razão e sim uma série de fatores agrupados, como o próprio banco de dados, o block device manager, multipathing driver, SCSI driver, HBA, System Bus, Switch/iSCSI, Storage Array, Disks, RAID e etc .. por isso é importante avaliar cada quesito em particular quando se fala em performance de I/O.

Um caso de estudo do evento Direct Path Reads. Por que ele é tão importante para o Oracle Exadata?
May 29, 2014

Você DBA provavelmente já deve ter ouvido falar do Oracle Smart Scan. Pois bem, o Smart Scan realmente é um dos segredos por trás da extrema velocidade de processamento das instruções dentro do Oracle Exadata Machine. Existe também o conceito de offloading dos dados dentro da arquitetura do Exadata, que refere ao fato do processamento dos dados ser feito a nível da camada de storage (storage layer) e não mais dentro da camada de banco de dados (database layer). O Smart Scan por outro lado é mais focado a nível de SQL e não de dados como o Offloading, mais como podemos ver nos documentos e manuais, a Oracle acabou que juntando esses dois conceitos e chamando apenas de “Smart Scan”.

Apesar de toda velocidade de processamento que a máquina Exadata possuí, ela não seria o que é sem a capacidade de realização de Offloading e Smart Scan. Sem essas features, o Exadata seria apenas mais uma máquina de alto poder de processamento, porém sem grande inteligência de manipulação dos dados … e como vemos, é essa inteligência que faz toda a diferença.

Mais afinal, o que o “Direct Path Reads” tem haver com o Offloading de Dados e o Exadata Smart Scan? A resposta para essa pergunta é simples: O offloading e/ou Smart Scan não acontecerá, caso sua instrução não utilize o Direct Path Reads.

Dois pré-requisitos básicos, são necessários para o Offloading/Smart Scan:

1. Obviamente, realizar a instrução em um storage Exadata.
2. Realizar o “Direct Path Reads” na sua instrução.

É claro que isso envolve algumas restrições, mais basicamente tendo esses requisitos, a sua consulta irá acabar realizando o Smart Scan.


O que é Direct Path Reads?

O Direct Path Reads foi criado pela Oracle para ignorar o Buffer Cache. O Buffer Cache como você já deve saber, é uma área da SGA destinada a manter os blocos recentes lidos, assim todos os usuários conectados na instância é capaz de ler e compartilhar desse cache sem a necessidade de ler esses blocos novamente do disco. Isso é um excelente ganho de performance, já que o evitamos o acesso a disco sempre que uma instrução é realizada. A Oracle fez um excelente trabalho ao longo dos anos, aperfeiçoando cada vez mais esse cache através dos algoritmos LRU e MRU, veja mais aqui: http://docs.oracle.com/cd/B28359_01/server.111/b28318/memory.htm#CNCPT1224

Realmente existe muito mais vantagem do que desvantagem em utilizar o Buffer Cache, porém a grande desvantagem, por ser um processo automático de gerenciamento de Buffer, o Oracle acaba por colocar “sujeiras” dentro desse cache, removendo dados que inclusive eram mais acessados pelas demais sessões. Imagina esse caso por exemplo, um relatório que é disparado uma única vez no mês para cálculos de fechamento que movimenta uma enorme quantidade de dados, por qual razão você gostaria de colocar todos esses dados gerados do relatório dentro do buffer cache do seu banco, sendo que essa instrução será executada apenas uma única vez e não será compartilhado com outras sessões dentro do banco. Inclusive, todo esses dados gerados, pode ser maior do que o próprio buffer cache, causando assim um extremo overhead em remover dados mais acessados e adicionar dados que nunca irá ser acessado. Será um tremendo trabalho em alocar, desalocar e realocar tudo novamente.

Foi aí que surgiu o Direct Path Reads.

O mecanismo de Direct Path Reads já está disponível no kernel do Oracle há muito tempo. Ele foi inicialmente implementado para trabalhar exclusivamente com os processos slaves sempre que uma instrução era disparada via paralelismo. Como os processos paralelos, como via de regra, devem ler grandes quantidades de dados o Direct Path Reads entrou na jogada para ignora completamente o mecanismo padrão do buffer. Foi decidido a partir daí, que os blocos deveriam ser armazenados em suas próprias memórias (PGA) e não mais na SGA quando se utiliza-se a consulta via DPR.

De acordo com o metalink, a partir do Oracle 11gR2, o kernel foi modificado para decidir realizar mais Direct Path Reads do que na versão 10g, ou seja na versão 10g o serial table scans tem muito mais chance de ser realizado no buffer compartilhado (scattered reads) do que na própria memória do processo (direct path reads).


Como identifico o evento Direct Path Reads?

Existem várias formas de se identificar o evento “Direct Path Reads”, uma delas é através das views de wait’s do Oracle, como por exemplo a v$session_wait.

A view v$session_wait mostra sempre o atual evento de espera ocorrido pela sessão. Usando o SQL abaixo, podemos identificar através da coluna EVENT, a utilização do evento “direct path read” para a consulta em questão (sql_id).

SELECT s.sid, w.state, w.event, s.sql_id, s.sql_child_number, 
	w.seq#, w.seconds_in_wait, w.p1text||'= '||w.p1 p1,
	w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
FROM v$session s, v$session_wait w 
WHERE w.sid = s.sid AND w.sid = "";

Vamos a uma prova de teste. A partir de agora, vou utilizar incessantemente o parâmetro oculto chamado “_serial_direct_read” para forçar a utilização do direct reads. Vou falar mais desse parâmetro mais a frente, o importante agora é saber que através dele podemos forçar a utilização do evento Direct Path Reads.

Os testes serão feito através da tabela fss.hsk1. Essa tabela, nada mais é do que uma tabela de teste que sempre utilizo em meus testes com Oracle. A tabela é criada dentro do owner FSS e contém cerca de 4G (você pode mudar o tamanho da tabela criada, alterando a quantidade de linhas inseridas na tabela, veja os comentários dentro do script). Através dos links abaixo, você poderá utilizar também a mesma tabela que vou demonstrar os testes a seguir.

Criação do usuário FSS: fss_user.sql
Criação das tabelas do usuário FSS: fss_create_tables.sql

Lembrando também, que todos esses testes foram feitos em um Oracle 11.2.0.3.

Identificando o uso do Direct Read, através da view de espera v$session_wait

Vamos ao teste que interessa. Com a sessão 1, iremos executar a seguinte instrução SQL na tabela fss.hsk1. Primeiro vamos definir o parâmetro oculto _serial_direct_read para ALWAYS, dessa forma eu estou forçando com que todas as minhas consultas sejam executadas via “Direct Path Reads”

SQL> ALTER SESSION SET "_serial_direct_read"=ALWAYS;

Session altered.

SQL> select avg(length(col1) + length(col2)) from fss.hsk1 where col3 > 1;

Rapidamente, enquanto executa a consulta acima, com a sessão 2, vamos ver através da v$session_wait que o evento de espera atual é o “direct path”:

--> SESSÃO 2

SQL> SELECT 
1  s.sid, w.state, w.event, s.sql_id, s.sql_child_number, w.seq#, w.seconds_in_wait, 
2  w.p1text||'= '||w.p1 p1, w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
3  FROM v$session s, v$session_wait w
4  WHERE w.sid = s.sid AND w.sid=152;
		
     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             36b84f5s2yj4a     0     36081               0 file number= 5          first dba= 3307    block cnt= 1

1 row selected.

SQL> /

     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             74kfrv5xqpbxf     0     52652               0 file number= 4          first dba= 157056  block cnt= 128

1 row selected.

SQL> /

     SID STATE               EVENT                        SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1                      P2                 P3
-------- ------------------- ---------------------------- ------------- ----- --------- --------------- ----------------------- ------------------ -----------------
     152 WAITED SHORT TIME   direct path read             74kfrv5xqpbxf     0     56786               0 file number= 4          first dba= 63360   block cnt= 128

Vamos agora voltar para a sessão 1, e mudar o parâmetro oculto “_serial_direct_read” para NEVER, e executar a mesma consulta, observe agora que não vamos mais ter o evento direct path read, mais sim o db file scattered read, ou seja a nossa consulta estará alocando todo os dados para a SGA:

--> SESSÃO 1

SQL> ALTER SESSION SET "_serial_direct_read"=NEVER;

Session altered.

SQL> select avg(length(col1) + length(col2)) from fss.hsk1 where col3 > 1;
--> SESSÃO 2

SQL> SELECT 
1  s.sid, w.state, w.event, s.sql_id, s.sql_child_number, w.seq#, w.seconds_in_wait, 
2  w.p1text||'= '||w.p1 p1, w.p2text||'= '||w.p2 p2, w.p3text||'= '||w.p3 p3
3  FROM v$session s, v$session_wait w
4  WHERE w.sid = s.sid AND w.sid=152;

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     23902               0 file#= 4    block#= 37124    blocks= 128


1 row selected.

SQL> /

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     26483               0 file#= 4    block#= 26500    blocks= 128


1 row selected.

SQL> /

     SID STATE               EVENT                     SQL_ID          CH#      SEQ# SECONDS_IN_WAIT P1          P2               P3
-------- ------------------- ------------------------- ------------- ----- --------- --------------- ----------- ---------------- --------------
     152 WAITED SHORT TIME   db file scattered read    74kfrv5xqpbxf     0     26977               0 file#= 4    block#= 80004    blocks= 128

Identificando o uso do Direct Read, através das views de estatísticas

Podemos também identificar o evento direct path através das views v$sesstat e v$mystat, no caso a v$sesstat representa todas as estatísticas de todas as sessões do banco, já a v$mystat representa apenas as estatísticas da minha atual sessão. Diferente da view v$session_wait que mostra o estado atual da sessão, as views de estatísticas são acumulativas para todas as estatísticas. Nesse caso, a estatística chamada “table scans (direct read)” representa a quantidade que o evento direct path foi utilizado dentre todas as instrução realizadas para a mesma sessão.

Pelo motivo das views de estatísticas v$sesstat e v$mystat serem acumulativas, precisamos realizar o antes e o depois e termos um delta para a comparação se aquela sessão sofreu ou não um aumento das estatísticas.

Podemos realizar o teste da seguinte maneira:

SQL> col value format 999999999999999
SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         0

1 row selected.

Veja acima, que a minha sessão está com a estatística “table scans (direct read)” com o valor zerado. Isso mostra que a sessão até o momento não realizou nenhuma leitura de bloco através do evento Direct Path Reads.

Vamos agora, alterar o parâmetro oculto “_SERIAL_DIRECT_READ” para NEVER, afim de forçar a leitura via FULL TABLE SCANS na tabela fss.hsk1 sem a utilização do DPR.

SQL> ALTER SESSION SET "_serial_direct_read"=NEVER;

Session altered.

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Após realizar a consulta, vamos novamente realizar a consulta para verificar o valor da estatística “table scans (direct read)”.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         0

1 row selected.

Veja que a estatística continua com o valor zero. Vamos executar a mesma instrução SQL, porém agora forçando o uso do Direct Reads.

SQL> ALTER SESSION SET "_serial_direct_read"=ALWAYS;

Session altered.

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Com a execução acima, voltemos a verificar o valor da estatística “table scans (direct read)”.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         1

Como você pode ver, a estatística passo de 0 para 1, isso aconteceu porque a instrução foi executada via Direct Path Reads. Para cada consulta então que realizo o evento de Direct Reads, o valor de 1 é adicionado a estatística “table scans (direct read)”. O mesmo procedimento é válido também para a estatística “index fast full scans (direct read)”

SQL> select avg(length(col1)) from fss.hsk1 where col3 > 1;

AVG(LENGTH(COL1))
-----------------
       175.015666

1 row selected.

Veja novamente que vamos ter a estatística “table scans (direct read)” com o valor agora de 2.

SQL> SELECT s.name, m.value
  2  FROM v$mystat m, v$statname s
  3  WHERE m.statistic# = s.statistic#
  4  AND s.name = 'table scans (direct read)';

NAME                                                          VALUE
-------------------------------------------------- ----------------
table scans (direct read)                                         2

Identificando o uso do Direct Read, através do evento de 10046

Um método muito rápido de identificar também se sua consulta está utilizando “db file scattered read” ao invés de “direct path reads” é através do evento 10046.

Setando esse evento para a sessão, e observando o trace, podemos identificar facilmente se a consulta está sendo feita via “db file scattered read” ou “direct path reads”.

Veja o exemplo abaixo:

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'

Session altered.

Com a sessão alterada para o evento 10046, vamos identificar o arquivo de trace da sessão:

SQL> SELECT tracefile 
2    FROM v$process WHERE addr = (
3        SELECT paddr FROM v$session 
4        WHERE sid = (SELECT sid FROM v$mystat WHERE rownum < 2)
5    );

TRACEFILE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbtst/dbtst/trace/dbtst_ora_60173.trc

Com os mesmos testes realizados acima, onde forçamos a utilização do Direct Reads, seu arquivo de trace irá se parecer como a listagem abaixo para a instrução com o _serial_direct_read para ALWAYS.

WAIT #140675437128128: nam='direct path read' ela= 780 file number=6 first dba=42624 block cnt=128 obj#=76837 tim=1397656466688788
WAIT #140675437128128: nam='direct path read' ela= 824 file number=6 first dba=42752 block cnt=128 obj#=76837 tim=1397656466692249
WAIT #140675437128128: nam='direct path read' ela= 831 file number=6 first dba=42880 block cnt=128 obj#=76837 tim=1397656466696735
WAIT #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=43008 block cnt=128 obj#=76837 tim=1397656466701094
WAIT #140675437128128: nam='direct path read' ela= 765 file number=6 first dba=43136 block cnt=128 obj#=76837 tim=1397656466705783
WAIT #140675437128128: nam='direct path read' ela= 574 file number=6 first dba=43268 block cnt=124 obj#=76837 tim=1397656466708691
WAIT #140675437128128: nam='direct path read' ela= 590 file number=6 first dba=43392 block cnt=128 obj#=76837 tim=1397656466711190
WAIT #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=43520 block cnt=128 obj#=76837 tim=1397656466713200
WAIT #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=43648 block cnt=128 obj#=76837 tim=1397656466715460
WAIT #140675437128128: nam='direct path read' ela= 562 file number=6 first dba=43776 block cnt=128 obj#=76837 tim=1397656466718398
WAIT #140675437128128: nam='direct path read' ela= 524 file number=6 first dba=43904 block cnt=128 obj#=76837 tim=1397656466720576
WAIT #140675437128128: nam='direct path read' ela= 489 file number=6 first dba=44032 block cnt=128 obj#=76837 tim=1397656466723296
WAIT #140675437128128: nam='direct path read' ela= 792 file number=6 first dba=44160 block cnt=128 obj#=76837 tim=1397656466726823
WAIT #140675437128128: nam='direct path read' ela= 726 file number=6 first dba=44292 block cnt=124 obj#=76837 tim=1397656466731733
WAIT #140675437128128: nam='direct path read' ela= 782 file number=6 first dba=44416 block cnt=128 obj#=76837 tim=1397656466736128
WAIT #140675437128128: nam='direct path read' ela= 786 file number=6 first dba=44544 block cnt=128 obj#=76837 tim=1397656466740659
WAIT #140675437128128: nam='direct path read' ela= 621 file number=6 first dba=44672 block cnt=128 obj#=76837 tim=1397656466743702
WAIT #140675437128128: nam='direct path read' ela= 808 file number=6 first dba=44800 block cnt=128 obj#=76837 tim=1397656466747454
WAIT #140675437128128: nam='direct path read' ela= 568 file number=6 first dba=44928 block cnt=128 obj#=76837 tim=1397656466751477
WAIT #140675437128128: nam='direct path read' ela= 553 file number=6 first dba=45056 block cnt=128 obj#=76837 tim=1397656466753675
WAIT #140675437128128: nam='direct path read' ela= 579 file number=6 first dba=45184 block cnt=128 obj#=76837 tim=1397656466758527
WAIT #140675437128128: nam='direct path read' ela= 610 file number=6 first dba=45316 block cnt=124 obj#=76837 tim=1397656466761760
WAIT #140675437128128: nam='direct path read' ela= 768 file number=6 first dba=45440 block cnt=128 obj#=76837 tim=1397656466765429
WAIT #140675437128128: nam='direct path read' ela= 751 file number=6 first dba=45568 block cnt=128 obj#=76837 tim=1397656466768958
WAIT #140675437128128: nam='direct path read' ela= 757 file number=6 first dba=45696 block cnt=128 obj#=76837 tim=1397656466772449
FETCH #140675437128128:c=342947,e=1194482,p=45285,cr=45289,cu=0,mis=0,r=1,dep=0,og=1,plh=3450470040,tim=1397656466776035
STAT #140675437128128 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=45289 pr=45285 pw=0 time=1194103 us)'
STAT #140675437128128 id=2 cnt=799999 pid=1 pos=1 obj=76837 op='TABLE ACCESS FULL HSK1 (cr=45289 pr=45285 pw=0 time=1363830 us cost=12370 size=145598544 card=799992)'
WAIT #140675437128128: nam='SQL*Net message from client' ela= 1640 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656466782990
FETCH #140675437128128:c=0,e=223,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3450470040,tim=1397656466785440
WAIT #140675437128128: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656466788033

Agora a mesma execução sem a utilização do Direct Reads, deverá se parecer com o seguinte trace:

(Observe a grande quantidade de "db file scattered read")

WAIT #140675437130048: nam='db file scattered read' ela= 774 file#=6 block#=43396 blocks=128 obj#=76837 tim=1397656143526663
WAIT #140675437130048: nam='db file scattered read' ela= 476 file#=6 block#=43524 blocks=128 obj#=76837 tim=1397656143536192
WAIT #140675437130048: nam='db file scattered read' ela= 618 file#=6 block#=43652 blocks=128 obj#=76837 tim=1397656143545942
WAIT #140675437130048: nam='db file scattered read' ela= 569 file#=6 block#=43780 blocks=128 obj#=76837 tim=1397656143555649
WAIT #140675437130048: nam='db file scattered read' ela= 504 file#=6 block#=43908 blocks=128 obj#=76837 tim=1397656143564865
WAIT #140675437130048: nam='db file scattered read' ela= 817 file#=6 block#=44036 blocks=128 obj#=76837 tim=1397656143576862
WAIT #140675437130048: nam='db file scattered read' ela= 500 file#=6 block#=44164 blocks=124 obj#=76837 tim=1397656143589249
WAIT #140675437130048: nam='db file scattered read' ela= 721 file#=6 block#=44292 blocks=128 obj#=76837 tim=1397656143602144
WAIT #140675437130048: nam='db file scattered read' ela= 597 file#=6 block#=44420 blocks=128 obj#=76837 tim=1397656143612393
WAIT #140675437130048: nam='db file scattered read' ela= 710 file#=6 block#=44548 blocks=128 obj#=76837 tim=1397656143622807
WAIT #140675437130048: nam='db file scattered read' ela= 790 file#=6 block#=44676 blocks=128 obj#=76837 tim=1397656143631916
WAIT #140675437130048: nam='db file scattered read' ela= 518 file#=6 block#=44804 blocks=128 obj#=76837 tim=1397656143640901
WAIT #140675437130048: nam='db file scattered read' ela= 450 file#=6 block#=44932 blocks=128 obj#=76837 tim=1397656143649894
WAIT #140675437130048: nam='db file scattered read' ela= 998 file#=6 block#=45060 blocks=128 obj#=76837 tim=1397656143661462
WAIT #140675437130048: nam='db file scattered read' ela= 428 file#=6 block#=45188 blocks=124 obj#=76837 tim=1397656143671014
WAIT #140675437130048: nam='db file scattered read' ela= 537 file#=6 block#=45316 blocks=128 obj#=76837 tim=1397656143679979
WAIT #140675437130048: nam='db file scattered read' ela= 809 file#=6 block#=45444 blocks=128 obj#=76837 tim=1397656143705089
WAIT #140675437130048: nam='db file scattered read' ela= 743 file#=6 block#=45572 blocks=128 obj#=76837 tim=1397656143714724
WAIT #140675437130048: nam='db file scattered read' ela= 742 file#=6 block#=45700 blocks=124 obj#=76837 tim=1397656143752173
FETCH #140675437130048:c=13221989,e=13812881,p=45286,cr=45298,cu=0,mis=0,r=1,dep=0,og=1,plh=3450470040,tim=1397656143762546
STAT #140675437130048 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=45298 pr=45286 pw=0 time=13812802 us)'
STAT #140675437130048 id=2 cnt=799999 pid=1 pos=1 obj=76837 op='TABLE ACCESS FULL HSK1 (cr=45298 pr=45286 pw=0 time=7547357 us cost=12370 size=145598544 card=799992)'
WAIT #140675437130048: nam='SQL*Net message from client' ela= 1056 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656143767562
FETCH #140675437130048:c=0,e=566,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3450470040,tim=1397656143768778
WAIT #140675437130048: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=76837 tim=1397656143770042

Identificando o uso do Direct Read, via Oracle Internals (apenas para Oracle Geek guys : )

O evento Direct Path Reads é realizado através da função do sistema operacional chamada kcbldrget, que significa Kernel Block Direct Read Get.

Para identificar o uso do Direct Path Reads, basta identificar se a função kcbldrget foi disparada do processo do sistema operacional. Isso é possível através do comando pstack do Linux. Com esse comando, podemos identificar stack trace (pila do trace) da execução do processo, ou seja, todo os caminhos via chamadas de SO que o processo passou.

Através do SPID, podemos executar o seguinte procedimento após a execução do mesmo SQL com o parâmetro oculto "_serial_direct_read" para ALWAYS, forçando assim a execução via DPR. No momento da execução, conectado no sistema operacional (no meu caso o Linux), realizamos o comando pstack apontando para o SPID da SESSÃO B, que no caso é o número 50834.

[root@oralnx001 ~]# pstack 50834
#0  0x0000003f1960ee33 in __pread_nocancel () from /lib64/libpthread.so.0
#1  0x00000000093521fb in skgfqio ()
#2  0x0000000009222e03 in ksfd_skgfqio ()
#3  0x0000000009222b68 in ksfdgo ()
#4  0x000000000234e30e in ksfdaio ()
#5  0x00000000021ef424 in kcflbi ()
#6  0x0000000000ebc90a in kcbldio ()
#7  0x0000000000ebba84 in kcblrs ()
#8  0x0000000000ebb165 in kcblgt ()
#9  0x0000000000eb9941 in kcbldrget ()
#10 0x000000000907b554 in kcbgtcr ()
#11 0x000000000905ff29 in ktrget3 ()
#12 0x000000000905f784 in ktrget2 ()
#13 0x0000000009016ead in kdst_fetch ()
#14 0x0000000000c87f89 in kdstf00000010000kmP ()
#15 0x0000000008ffc6e8 in kdsttgr ()
#16 0x0000000009245970 in qertbFetch ()
#17 0x000000000926cc1f in qergsFetch ()
#18 0x0000000009136e83 in opifch2 ()
#19 0x00000000091404e8 in opiefn0 ()
#20 0x000000000914dfc4 in opipls ()
#21 0x000000000913d4d4 in opiodr ()
#22 0x00000000091e7043 in rpidrus ()
#23 0x0000000009354764 in skgmstack ()
#24 0x00000000091e8b5e in rpiswu2 ()
#25 0x00000000091e8188 in rpidrv ()
#26 0x00000000091d14d1 in psddr0 ()
#27 0x00000000091d10e7 in psdnal ()
#28 0x0000000003736b52 in pevm_EXIM ()
#29 0x000000000372831b in pfrinstr_EXIM ()
#30 0x00000000093eae35 in pfrrun_no_tool ()
#31 0x00000000093e9509 in pfrrun ()
#32 0x00000000093f0b61 in plsql_run ()
#33 0x000000000371cb6b in peicnt ()
#34 0x0000000002fa18b1 in kkxexe ()
#35 0x00000000091450f9 in opiexe ()
#36 0x0000000001b5cb07 in kpoal8 ()
#37 0x000000000913d4d4 in opiodr ()
#38 0x00000000092e02d6 in ttcpip ()
#39 0x00000000017ece01 in opitsk ()
#40 0x00000000017f19fa in opiino ()
#41 0x000000000913d4d4 in opiodr ()
#42 0x00000000017e8d3c in opidrv ()
#43 0x0000000001de40cb in sou2o ()
#44 0x0000000000a0b0c1 in opimai_real ()
#45 0x0000000001dea03c in ssthrdmain ()
#46 0x0000000000a0b02d in main ()

Esse monte de código acima, representa cada chamada de sistema operacional feita pelo processo em execução do SPID 50834. Como você pode ver na linha em negrito, temos uma chamada para o sistema operacional para a função kcbldrget, que significa que a instrução executada por esse processo utilizou o método Direct Reads :)

Condições para se utilizar o Direct Path Reads

Como mencionei acima, a partir do Oracle 11g é muito mais provável que sua consulta utilize o direct path reads, porém existe maneiras de "tentar" identificar se sua consulta irá ou não, utilizar automaticamente o evento.

Além dos processos parallel slaves que são sempre executados via direct path reads, para instruções não paralelas elas funcionam em certas condições. O calculo é baseado em diversos fatores como a quantidade de blocos do objeto e o tamanho do buffer. Existe ainda o parâmetro oculto _SMALL_TABLE_THRESHOLD que determina a quantidade mínima de blocos que uma tabela deve ter para a utilização do DPR. O valor default desse parâmetro é 2680, o que significa que caso uma tabela tenha a quantidade de blocos maior que 2680, o seu full table scan será mais favorável a utilizar direct path reads.

Veja um exemplo:

SQL> show parameter _small_table_threshold

NAME                           VALUE
------------------------------ -------
_small_table_threshold         2680

Acima como se pode ver, o parâmetro oculto "_SMALL_TABLE_THRESHOLD" está definido para o valor default de 2680 blocos. Vou agora, criar uma tabela buscando todos os dados da view dba_objects.

SQL> create table t as select * from dba_objects;

Table created.

SQL> select blocks from dba_segments where segment_name='T';

    BLOCKS
----------
      1152

Observe que a nossa tabela ficou com 1152 blocos, bem abaixo do valor do parâmetro _small_table_threshold. Vamos executar um FULL TABLE SCAN de encontro a tabela e verificar se foi realizado automaticamente o direct path reads de encontro a tabela.

SQL> select s.name, m.value 
2    from v$statname s, v$mystat m 
3    where m.statistic#=s.statistic# 
4    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   0

1 rows selected.

SQL> select count(*) from t;

  COUNT(*)
----------
     75214

1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic# 
5    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   0

1 rows selected.

Veja acima, que a estatística table scans (direct read) não foi alterado. Vamos aumentar a quantidade de blocos da tabela e realizar o mesmo teste.

SQL> insert into t (select * from t);

75214 rows created.

SQL> insert into t (select * from t);

150428 rows created.

SQL> commit;

Commit complete.

SQL> select blocks from dba_segments where segment_name='T';

    BLOCKS
----------
      4352

1 row selected.

Agora sim temos blocos acima da quantidade necessária especificada pelo parâmetro _small_table_threshold. Antes de fazer o teste, vamos limpar a área de memória do Oracle para que o teste anterior não interfira nesse novo.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Feito a limpeza, vamos checar novamente a mesma consulta:

SQL> select count(*) from t;

  COUNT(*)
----------
    300856

1 row selected.

SQL> select 
2      s.name, m.value 
3      from v$statname s, v$mystat m 
4      where m.statistic#=s.statistic# 
5      and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                                   1

1 row selected.

Agora sim a nossa consulta foi realizada via direct read.

Um outra ponto muito importante para o direct reads, é que ele apenas funciona quando um full scan acontece, ou seja, a função "direct path reads" (kcbldrget) somente é chamada após um full scans. Note que o termo full scans representa os termos TABLE ACCESS FULL e INDEX FAST FULL SCAN no seu plano de execução. Dessa forma, pelo simples fato de uma operação em sua consulta a uma tabela for do tipo UNIQUE SCAN, o Direct Path Reads irá acontecer.

Processos Parallel e o Direct Path Reads

Os processos paralelos são outro ponto importante, como comentei acima, não importa que tipo de processo parallel você tem utilizado (AUTO DOP, IN MEMORY PARALELL, QUEUEING PARALLEL, etc ..) ou o tipo de Degree, a sua consulta sempre que utilizar a operação FULL SCANS ela irá ser feita via Direct Path Reads.

Veja no exemplo abaixo, que mesmo definindo o parâmetro "_serial_direct_read" para "never" vamos ter nossa consulta paralela utilizando o direct reads:

SQL> ALTER SESSION SET "_serial_direct_read"=never;

Session altered.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                    VALUE
-------------------------------------------------- ----------
table scans (direct read)                          0

1 row selected.

SQL> select count(*) from t;

  COUNT(*)
----------
    300856


1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                  VALUE
-------------------------------------------------- --------
table scans (direct read)                                 0

1 row selected.

SQL> alter table t parallel 2;

Table altered.

SQL> select count(*) from t;

  COUNT(*)
----------
    300856

1 row selected.

SQL> select 
2    s.name, m.value 
3    from v$statname s, v$mystat m 
4    where m.statistic#=s.statistic#  
5    and s.name = 'table scans (direct read)';

NAME                                                  VALUE
-------------------------------------------------- --------
table scans (direct read)                                26


1 row selected.

Ou veja, processos parallel ignoram completamente o parâmetro _serial_direct_read.


Um pouco sobre o parâmetro _serial_direct_read

Você já deve ter entendido bem a utilização do parâmetro oculto _serial_direct_read. Ele força ou ignora a utilização do "Direct Path Reads". Vale a pena lembrar que por se tratar de um parâmetro oculto, não é nem um pouco legal você definir em seu ambiente de produção sem antes consultar a Oracle.

O parâmetro _serial_direct_read ele possuiu esses valores a partir do Oracle 11g:

1 ALWAYS
2 AUTO
3 NEVER
4 TRUE
5 FALSE

Para cada um deles ele trata de um modo diferente a instrução via "Direct Path Reads". O modo default dele é AUTO, o que significa que ele será automático, seguindo toda aquela regra que já expliquei sobre a quantidade e blocos e vários outros fatores.

Através do suporte da Oracle do documento "Exadata Smartscan Is Not Being Used On Insert As Select (Doc ID 1348116.1)", a Oracle descreve um problema em que o Smartscan não acontece devida a não execução da instrução via "Direct Path Reads" (um dos caso mais comum de falta de performance do Exadata). Na nota ele informa o uso do parâmetro _serial_direct_read para TRUE como a resolução do problema.

....

Por hoje é isso, espero que tenha consigo demonstrar a importante do Direct Path Reads para o Oracle. Para os próximos posts, quero mostrar um pouco mais da utilização do Direct Path Reads junto com as features do Exadata (Storage Index, Predicate Filtering, Column Projection, etc ...). Um abraço e até lá :)

Performance Tuning Utilities
January 9, 2013

Já ouviram falar de OSWatcher? LTOM? SQLTXPLAIN e OPDG?

Bom, se vocês nunca ouviram  falar dessas ferramentas chegou a hora de conhece-las.

A nota do metalink 438452.1 mostra essas e diversas outras ferramentas sobre performance tuning.

Bom apetite, aproveitem :)

Segment Advisor Script v1.0
December 12, 2012

Só nós DBA’s sabemos as inúmeras funções, sintaxes e comandos que precisamos decorar no nosso dia a dia e isso não é uma tarefa fácil, além do que, as soluções sempre precisam ser rapidamente propostas. Por isso que gosto de facilitar as coisas sempre que posso, assim como esse script, que além de simples ele realiza é uma formarápida os passos que nem sempre são lembrados, isso evita aquele tempão gasto procurando na documentação a sintaxe exata de tal procedimento. Qual DBA que nunca passou por isso que atire a primeira pedra!

O objetivo principal do script é facilitar a execução do Oracle Segment Advisor, de uma forma bem simples, apenas três argumentos e nada mais e o melhor de tudo: não é preciso criar qualquer tipo de objeto no banco de dados, todo trabalho é executado através de um bloco anônimo de PLSQL e o único requisito aqui é o bom e velho SQL*Plus.

Você pode conferir a primeira versão (sim, próximas poderão surgir) você pode fazer aqui.

Como usar Segment Advisor Script?

SYNTAX: { @run_sa “Owner Segment” “Segment Name” “Segment Type” }

Ele não precisa nada mais além do que três argumento:

1. “Owner Segment” :  O primeiro representa o nome do owner do segmento.

2. “Segment Name”:  O segundo representa o nome do segmento.

3. “Segment Type”:  O terceiro representa o tipo do segmento. Que pode ser um TABLE, INDEX etc …

Usando o Segment Advisor Script

Para explicar melhor vamos a um teste prático.

Primeiro de tudo, precisamos de uma tabela grande favorável para o teste que vamos fazer, para isso criei a tabela chamada TBIG dentro do schema FSOARES.

FSOARES@dbtst> create table tbig as select * from dba_source;

Table created.

FSOARES@dbtst> insert into tbig (select * from tbig);

633054 rows created.

FSOARES@dbtst> /

1266108 rows created.

FSOARES@dbtst> /

2532216 rows created.
...
FSOARES@dbtst> commit;
FSOARES@dbtst> @size tbig

SEGMENT_NAME                    SEGMENT_TYPE        SIZE_MB
------------------------------ ------------------ ---------------
TBIG                             TABLE              5,244.00

Pronto, temos agora uma tabela de 5G que está perfeita para o nosso teste. Vamos ver quantos registros temos:

FSOARES@dbtst> set timing on
FSOARES@dbtst> select count(*) from tbig;

COUNT(*)
----------
40515456

1 row selected.
Elapsed: 00:04:08.21

Ok, temos cerca de 40515456 registros e levamos cerca de 4 minutos para sabermos isso. Vamos agora apagar uns 98% dessa tabela deixando apenas alguns milhares de registro. Para fazer essa operação mais racional,  quero descobrir  quantos registros tenho por usuário nessa tabela, afim de deixar apenas os menores owners:

FSOARES@dbtst> select count(*), owner from tbig group by owner order by 1;

  COUNT(*) OWNER
---------- ------------------------------
       576 IX
       576 OUTLN
      1088 PM
      2176 FLOWS_FILES
      2176 HR
      2880 SYSTEM
     13696 OE
     14912 ORDPLUGINS
     19392 WMSYS
     70464 EXFSYS
    183808 ORACLE_OCM
    230528 DBSNMP
    231552 ORDSYS
    548288 XDB
    731264 OLAPSYS
   1258816 CTXSYS
   1340416 MDSYS
   2624256 APEX_030200
   9466112 SYS
  23772480 SYSMAN

20 rows selected.

Elapsed: 00:01:14.06
FSOARES@dbtst> delete tbig where owner NOT IN ('IX', 'OUTLN');                         
40514304 rows deleted.

Elapsed: 00:29:57.41
FSOARES@dbtst> commit;

Commit complete.

Elapsed: 00:00:00.01

Aqui, a nossa tabela TBIG está somente com os dados usuário IX e OUTLN, o resto dos outros usuário foi simplesmente apagado. Bom como temos agora apenas alguns registros vamos realizar aquele mesmo count para ver o a quantidade de registro, vamos ver agora o quanto tempo levará. Primeiro é claro, vamos remover a consulta do nosso cache.

FSOARES@dbtst> alter system flush shared_pool;

System altered.

FSOARES@dbtst> alter system flush buffer_cache;

System altered.

FSOARES@dbtst> select count(*) tbig

  COUNT(*)
----------
      1152

1 row selected.

Elapsed: 00:03:53.93

Temos agora cerca de mil registros e levamos quase o mesmo tempo para realizar o count da tabela quando ela estava com mais de 40 milhões de registro!? Como isso pode ser? Tivemos o mesmo tempo para contar de 0 a 1152 e de 0 a 40 milhões?

Bom, sem dúvidas há alguma coisa de errado com nosso segmento de tabela TBIG. É aí que entra o Segment Advisor, que vai nos aconselhar o que fazer com esse segmento.

Veja como é simples:

FSOARES@dbtst> @run_sa fsoares tbig table

---------------------------------------------------------------------------------
-- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com )

Running the Segment Advisor for Segment 
Owner   : FSOARES
Segment Name: TBIG
Segment Type: TABLE

Segment Advisor successfuly completed

For delete the task TaskName_FSOARES_cxdnLahXMf run: 
SQL> exec  dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf');

-- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig"

 TABLESPACE_NAME   : USERS
 SEGMENT_OWNER     : FSOARES
 SEGMENT_NAME      : TBIG
 SEGMENT_TYPE      : TABLE
 PARTITION_NAME    :
 ALLOC_MB          :    5,244.00
 RECLAIM_MB        :    4,567.54
 USED_MB           :      676.46
 PCT_SAVE          : 87 %
 RECOMMENDATIONS   : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes.
 SOLUTION 1        : alter table "FSOARES"."TBIG" shrink space
 SOLUTION 2        : alter table "FSOARES"."TBIG" shrink space COMPACT
 SOLUTION 3        : alter table "FSOARES"."TBIG" enable row movement

---------------------------------------------------------------------------------

Observe a recomendação, ele sugere realizar um shrink na tabela que ganharemos com isso cerca de 87% de espaço que hoje não está sendo utilizado. Opa!  … é 87% é um bom ganho, então vamos aplicar as recomendações sugeridas.

FSOARES@dbtst> alter table "FSOARES"."TBIG" enable row movement;

Table altered.

FSOARES@dbtst> alter table "FSOARES"."TBIG" shrink space;

Table altered.

Após as recomendações aplicadas, vamos agora executar novamente o count de encontro a tabela TBIG e observar o tempo:

FSOARES@dbtst> alter system flush shared_pool;

System altered.

FSOARES@dbtst> alter system flush buffer_cache;

System altered.

FSOARES@dbtst> select count(*) from tbig;

  COUNT(*)
----------
      1152

1 row selected.

Elapsed: 00:00:00.01

Depois da recomendação aplicada, o tempo simplesmente caiu para 0.01 segundos.

Viu como ficou bem mais simples utilizar o Segment Advisor agora com o run_sa.sql :)

Com apenas três argumentos e já temos nossas recomendações.

FSOARES@dbtst> @run_sa fsoares tbig table

---------------------------------------------------------------------------------
-- Segment Adviser Script v1.0 by Flavio Soares ( http://flaviosoares.com )

Running the Segment Advisor for Segment 
Owner   : FSOARES
Segment Name: TBIG
Segment Type: TABLE

Segment Advisor successfuly completed

For delete the task TaskName_FSOARES_cxdnLahXMf run: 
SQL> exec  dbms_advisor.delete_task('TaskName_FSOARES_cxdnLahXMf');

-- Showing the Segment Advice Recommendations for the object "table" "fsoares" "tbig"

 TABLESPACE_NAME   : USERS
 SEGMENT_OWNER     : FSOARES
 SEGMENT_NAME      : TBIG
 SEGMENT_TYPE      : TABLE
 PARTITION_NAME    :
 ALLOC_MB          :    5,244.00
 RECLAIM_MB        :    4,567.54
 USED_MB           :      676.46
 PCT_SAVE          : 87 %
 RECOMMENDATIONS   : Enable row movement of the table FSOARES.TBIG and perform shrink, estimated savings is 4789413285 bytes.
 SOLUTION 1        : alter table "FSOARES"."TBIG" shrink space
 SOLUTION 2        : alter table "FSOARES"."TBIG" shrink space COMPACT
 SOLUTION 3        : alter table "FSOARES"."TBIG" enable row movement

---------------------------------------------------------------------------------

Dúvidas, melhorias, bugs, recomendações serão muito bem vindas!

Um abraço e aproveitem!

Instalando Oracle Statspack
October 3, 2012

Statspack tem sido utilizado desde a versão do Oracle 8i para análise e monitoramento de performance. Não muito diferente da versão Oracle 11g (mais nova até o momento), ele fornece um relatório rápido de seu ambiente afins de processos de tuning do seu banco de dados Oracle

Para mais informações do Statspack consulte o arquivo no seu ORACLE HOME: $ORACLE_HOME/rdbms/admin/spdoc.txt

É verdade sim, que o AWR fornece um relatório melhor comparado com o Statspack, porém usuário que não possuem licença para utilizar Enterprise Manager, devem continuar utilizando o Statspack.

StatsPack tem sido construido para ser instalado somente com uma conexão SYSDBA. A instalação é simples e trivial, uma vez conectado como SYSDBA no SQLPLUS basta executar o script:

FSOARES@oracle11g> @spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password:
...

Alguns perguntas serão feitas como:

  • Senha do usuário PERSTATS
  • Tablespace Default do usuário PERSTATS
  • Tablespace Temporário do usuário PERFSTATS

Caso queria remover Statspack basta executor o spdrop.sql encontrado no $ORACLE_HOME/rdbms/admin. Qualquer falha na execução do script no momento da criação ou algum cancelamento inadequado, deve-se primeiro remover o StatsPack com o script spdrop.sql depois sim criar com o spcreate.sql.

Na criação do script, um arquivo spckg.list é criado, reveja o arquivo para qualquer possível erro que encontrar.

Rolling Upgrades com Oracle Data Guard 11g
July 19, 2012

Em Abril deste ano, escrevi um artigo para a Oracle sobre a utilização de Rolling Upgrade com Oracle Data Guard 11g.

A Oracle chama de Rolling Upgrade a possibilidade de você realizar upgrade de versão com o mínimo (quase nada) de down time e nesse caso específico é utilizado o Oracle DataGuard 11g … isso mesmo, utilizando Data Guard. Você pode conferir tudo isso na integra aqui: http://www.oracle.com/technetwork/pt/articles/database-performance/rolling-upgrades-com-data-guard-11g-1576838-ptb.html

Estou publicando a versão em PDF do artigo para quem interessar:

Download Artigo

Abraço a todos !

Exadata e Eu!
December 8, 2011

Olá Pessoal,

Ontem participei do Oracle Open World 2011 Latin America, foi a primeira vez que participei do evento (essa é uma das vantagens de se morar em SP, os eventos são bem mais próximos) e gostei muito.

Fiquei quase que o tempo todo no stand da Discover, o pessoal se divertiu muito lá com games interativos :)

Pude ver de perto o Oracle Exadata e Exalogic e participei de algumas palestras de demonstrações, realmente valeu a pena.

Next Page »