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(*)
----------
64Decorrido: 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)
------------------
64Decorrido: 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(*)
----------
64Decorrido: 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.
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
Oi Vieri ! Lindo, não?
Ela só funcionará do DBMS_ADVANCED_REWRITE se for exatamente igual. E se for exatamente igual, não haverá uma nova compilação.
Abraço !
Grande Portilho..!!
Show de bola isto heim..!!
Agora uma dúvida.. “Funciona com BIND?”
Abraços Mestre..!!
Regis Araujo
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!
Oi pessoal.
Não, não funciona com Binds… ainda. 😉
Abraços !
Opa..!
Poxa.. q pena.. Seria uma mão na roda heim.. heheeh..
Abraços..!
Regis Araujo
Ahh..!!
O complicado seria aturar o desenvolvedor.. pois ele vai achar q oq ele fez esta certo.. Ai é que mora o perigo..!!
Abraços..!
Show de bola, muito interessante… parabens cara
abraço
Essa feature é realmente fantástica, parabéns Portilho.
Muito boa dica …
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
Eu também adorei essa quando vi, é o que sempre quisemos.
Abraço a todos !
Excelente!!! Estava a procura disso faz tempo!
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
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 !
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
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