Objetivo:
Estamos realizando um trabalho para melhorar o desempenho de uma aplicação e identificamos que existe uma consulta que é a maior responsável pelo baixo desempenho desta, analisando a consulta verificamos que é possivel melhorar a performace, mas para isso precisamos alterar o código da aplicação. Essa aplicação é um pacote de um fornecedor e não temos acesso ao código fonte, uma possível solução para esse impasse seria a utilização do pacote “DBMS_ADVANCED_REWRITE” que permite a alteração de uma consulta sem alteração do código fonte da aplicação.
Ambiente:
SO: Linux 2.6.9-67.0.0.0.1.EL i686 i386 GNU/Linux
Base: Oracle Database 10.2.0.5.0 com archive log habilitado
Overview:
O pacote DBMS_ADVANCED_REWRITE permite interceptar instruções SQL específicas e substituí-las com instruções SQL alternativa. Este recurso pode ser muito útil quando precisamos modificar uma instrução SQL visando melhoria de performace e não dispomos do código fonte da aplicação. Infelizmente este recurso tem algumas limitações e em alguns casos não podemos utilizá-lo:
- Recurso disponível a partir da versão Oracle 10g;
- Não funciona com instrução DML, somente com instrução SELECT;
- Não funciona com “Bind Variables” (Metalink DOC ID: 392214.1);
Tópicos:
- Previlégios necessários para o usuário;
- Criar uma tabela de teste;
- Plano de execução da consulta original;
- Plano de execução da consulta otimizada;
- Utilizar o pacote para estabelecer a equivalência;
- Verificar se equivalência esta funcionando;
1. Previlégios necessários para o usuário
SQL>
SQL> conn sys/oracle@lab1 as sysdba;
Conectado.
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO appl02;
Concessão bem-sucedida.
SQL> GRANT CREATE MATERIALIZED VIEW TO appl02;
Concessão bem-sucedida.
SQL> 2. Criar uma tabela de teste
SQL>
SQL> conn appl02/oracle@lab1;
Conectado.
SQL> create table db_objetos as select * from all_objects;
Tabela criada.
SQL> create index db_obj_idx on db_objetos(OBJECT_NAME);
Índice criado.
SQL>3. Plano de execução da consulta original
Agora vamos verificar o plano de execução de uma consulta que supostamente estaria causando grande impacto de performace na base de dados:
SQL>
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE
from db_objetos
where upper(OBJECT_NAME) = upper('all_objects');
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -----------------
SYS ALL_OBJECTS VIEW
PUBLIC ALL_OBJECTS SYNONYM
SQL> Explain plan for
2 select OWNER, OBJECT_NAME, OBJECT_TYPE
3 from db_objetos
4 where upper(OBJECT_NAME) = upper('all_objects');
Explicado.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 440180848
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 270 | 133 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| DB_OBJETOS | 6 | 270 | 133 (3)| 00:00:02 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("OBJECT_NAME")='ALL_OBJECTS')
Note
-----
- dynamic sampling used for this statement
17 linhas selecionadas.
SQL>4. Plano de execução da consulta otimizada
Vamos supor que após uma analise na tabela “DB_OBJETOS” verificamos que o campo “OBJECT_NAME” possui somente textos com caracteres maiusculos, neste caso a consulta abaixo forneceria o mesmo resultado e utilizaria o indice desse campo, melhorando a performace da consulta:
SQL>
SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE
from db_objetos
where OBJECT_NAME = upper('all_objects');
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS ALL_OBJECTS VIEW
PUBLIC ALL_OBJECTS SYNONYM
SQL> Explain plan for
2 select OWNER, OBJECT_NAME, OBJECT_TYPE
3 from db_objetos
4 where OBJECT_NAME = upper('all_objects');
Explicado.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1912010479
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 90 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DB_OBJETOS | 2 | 90 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DB_OBJ_IDX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='ALL_OBJECTS')
Note
-----
- dynamic sampling used for this statement
18 linhas selecionadas.
SQL> 5. Utilizar o pacote para estabelecer a equivalência
Agora vamos utilizar o pacote DBMS_ADVANCED_REWRITE para estabelecer a equivalência de forma que sempre que a consulta original for acionada a base Oracle utilize a consulta otimizada para execução:
SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Sessão alterada.
SQL> BEGIN
2 sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
3 name => 'test_rw1',
4 source_stmt =>
5 'select OWNER, OBJECT_NAME, OBJECT_TYPE
6 from db_objetos
7 where upper(OBJECT_NAME) = upper(''all_objects'')',
8 destination_stmt =>
9 'select OWNER, OBJECT_NAME, OBJECT_TYPE
10 from db_objetos
11 where OBJECT_NAME = upper(''all_objects'')',
12 validate => false,
13 rewrite_mode => 'text_match');
14 END;
15 /
Procedimento PL/SQL concluído com sucesso.
SQL>6. Verificar se equivalência esta funcionando
Na execução abaixo podemos constatar que apesar de acionar-mos a consulta original o plano de execução utilizado pela base Oracle é da consulta otimizada:
SQL>
SQL> Explain plan for
2 select OWNER, OBJECT_NAME, OBJECT_TYPE
3 from db_objetos
4 where upper(OBJECT_NAME) = upper('all_objects');
Explicado.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1912010479
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 90 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DB_OBJETOS | 2 | 90 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DB_OBJ_IDX | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='ALL_OBJECTS')
Note
-----
- dynamic sampling used for this statement
18 linhas selecionadas.
SQL>
Nenhum comentário:
Postar um comentário