Pular para o conteúdo

DBMS_ADVANCED_REWRITE

DBMS_ADVANCED_REWRITE

Aprenda mais truques como esse no meu Curso Oracle Performance Diagnostic & Tuning.

Imagine que um desenvolvedor fez esta obra de arte na aplicação:

SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

SessÒo alterada.

SQL> SET TIMING ON;
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

COUNT(*)
----------
64

Decorrido: 00:00:56.62

Neste SELECT, um índice está sendo utilizado forçadamente quando um Full Table Scan seria mais rápido.

Analisando o SELECT, você ve que uma consulta sem o HINT realmente é muito mais rápida:

SQL> SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

COUNT(OBJECT_NAME)
------------------
64

Decorrido: 00:00:07.75

Mas o problema é que o desenvolvedor não concorda, ou a aplicação é fechada, e o SELECT não pode ser alterado.

Sem problemas ! A partir do 10g, você tem controle completo sobre os comandos SQLs executados no Banco de Dados, utilizando a Package DBMS_ADVANCED_REWRITE.

Nesta Package, você escolhe um SOURCE_STMT e um DESTINATION_STMT: sempre que um SQL igual ao SOURCE_STMT for executado, ele será trocado pelo DESTINATION_STMT.

SQL> BEGIN
2    SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
3       NAME             => 'PORTILHO_REWRITE',
4       SOURCE_STMT      => 'SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = ''T''',
5       DESTINATION_STMT => 'SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = ''T''',
6       VALIDATE         => FALSE,
7       REWRITE_MODE     => 'TEXT_MATCH');
8  END;
9  /

Procedimento PL/SQL concluÝdo com sucesso.

Decorrido: 00:00:00.70
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';

COUNT(*)
----------
64

Decorrido: 00:00:07.90

Voilá. O tempo ficou muito melhor, o SQL foi reescrito, e o desenvolvedor nem sabe disso.

Aprenda mais truques como esse no meu Curso Oracle Performance Diagnostic & Tuning.

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 11

Sem votos ! Seja o primeiro a classificar !

17 comentários em “DBMS_ADVANCED_REWRITE”

  1. Avatar de Vieri

    Fantástico!!
    Muito tempo que não clicava em um link com informação do 10G com empolgação…

    Temos algum contra, frente a N³ prós, pricnipalmente de dor de cabela com desenv mal criados..rsrs

    Se for query extremamente executada pode gerar latch’s e hard parses ?!?!

    abraços

  2. Avatar de Hudson Santos

    Muito bom!!

    Literalmente uma mão na roda, só é perigoso porque se a todo sql mal escrito que um desenvolvedor fizer pro sistema, eu for alterando eu trabalho pra ele.

    E tenho a mesma dúvida do Regis.

    Abraços!

  3. Avatar de Braga

    Olá Portilho,

    Grande dica. Já peguei sistemas fechados aos quais o desenvolvedor não tinha como alterar o código e se, na época, tivesse conhecimento de tal feature a vida seria mais fácil.

    Novamente obrigado por mais essa dica de ouro.

    []s
    Braga

  4. Avatar de Cláudio

    A idéia é muito interessante, mas não estou conseguindo executá-la.
    Há algum parâmetro ou algo diferente que precisa ser setado?

    Segue o meu teste e os problemas encontrados:

    Tenho um usuário user1 e uma tabela tabtest;
    create table tabtest (num integer, mensag varchar2(10));

    Tentei então eliminar a hint reescrevendo o SQL, similar ao seu exemplo:

    SQL> BEGIN
    2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
    3 NAME => ‘Teste’,
    4 SOURCE_STMT => ‘Select /*+ rule*/ NUM, MENSAG from user1.tabtest order by NUM’,
    5 DESTINATION_STMT => ‘Select NUM, MENSAG from user1.tabtest order by NUM’,
    6 VALIDATE => FALSE,
    7 REWRITE_MODE => ‘TEXT_MATCH’);
    8 END;
    9 /
    BEGIN
    *
    ERRO na linha 1:
    ORA-30394: source statement identical to the destination statement
    ORA-06512: at “SYS.DBMS_ADVANCED_REWRITE”, line 29
    ORA-06512: at “SYS.DBMS_ADVANCED_REWRITE”, line 185
    ORA-06512: at line 2

    Apesar de os comandos não serem idênticos, o Oracle entede que são. Ao tentar diferenciá-los (where 1=1) obtenho um outro erro:

    SQL> BEGIN
    2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
    3 NAME => ‘Teste’,
    4 SOURCE_STMT => ‘Select /*+ rule*/ NUM, MENSAG from user1.tabtest order by NUM’,
    5 DESTINATION_STMT => ‘Select NUM, MENSAG from user1.tabtest where 1=1 order by NUM’,
    6 VALIDATE => FALSE,
    7 REWRITE_MODE => ‘TEXT_MATCH’);
    8 END;
    9 /
    BEGIN
    *
    ERRO na linha 1:
    ORA-30389: the source statement is not compatible with the destination
    statement
    ORA-00907: missing right parenthesis
    ORA-06512: at “SYS.DBMS_ADVANCED_REWRITE”, line 29
    ORA-06512: at “SYS.DBMS_ADVANCED_REWRITE”, line 185
    ORA-06512: at line 2

    Sabe como eu poderia resolver isso?

    Grato,
    Cláudio Henrique

  5. Avatar de Ricardo Portilho Proni

    Oi Cláudio.

    Você tem que dar as seguintes permissões ao usuário:
    SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO SCOTT;
    SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;

    E o primeiro teste utiliza SQLs idênticos para o Oracle, pois a única diferença está dentro dos comentários. Mesmo o comentário fazendo diferença no plano de execução, sintaticamente para o Oracle é o mesmo SQL.

    Abraço !

  6. Avatar de Cláudio

    Olá Porfírio,

    Obrigado pela ajuda, mas também não funcionou…
    Dei as permições ao usuário, porém continuo recebendo os mesmos erros.
    Também tentei executar como sysdba, mas também não resolveu.

    No primeiro teste a diferença é somente o comentário mesmo, mas no exemplo do post também tinha apenas esta diferença e funcionou, correto?

    Mesmo com os grants citados não consigo habilitar o query rewrite.
    Os parâmetros estão definidos como:

    NAME TYPE VALUE
    ———————————— ———– ——–
    query_rewrite_enabled string TRUE
    query_rewrite_integrity string ENFORCED

    Alguma sugestão do que mais pode ser?

    Grato,
    Cláudio

  7. Avatar de Cláudio

    Obrigado pela força Portilho,

    Com estas orientações consegui avançar na questão.
    Me parece que o Oracle tem problemas em lidar com o ORDER BY no QUERY_REWRITE.

    Caso eu encontre a solução posto aqui pra gente.

    Obrigado pelo apoio,
    Cláudio

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress