Extendendo o otimizador do Oracle 11g para obter estatísticas de funções pipelined

Por Paulo Alexandre Petruzalek
Postado en julho 2012

Introdução

A idéia deste artigo surgiu a partir de um problema clássico: qual a melhor forma de tratar uma lista de valores (ex: produtos, situações, tipos, classes, etc) passada por string para uma stored procedure ou função que por sua vez necessita tratar estas situações em querys SQL.

Vamos tomar como exemplo um hipotético sistema de vendas. Digamos que neste sistema existe o requisito de um relatório que possa filtrar os produtos vendidos dinamicamente de acordo com as necessidades do usuário. Este filtro seria passado para a procedure através de um parâmetro VARCHAR2 que contém os valores desejados concatenados e separados por vírgulas (ex.: 'A,B,C').

Uma das soluções mais comuns para este tipo de problema é fazer uso de um SQL dinâmico e concatenar o valor do filtro associado em uma cláusula IN. Para um relatório muito longo talvez esta possa ser uma solução aceitável, mas como boas práticas eu sempre procuro utilizar uma solução que envolva bind variables a fim de preservar os recursos do banco e aumentar a escalabilidade. Além disso, esta seria a única forma de implementação aceitável no caso de um ambiente OLTP que possua um requisito similar para querys pontuais.

Veremos a seguir que uma solução para este problema que permite o uso de bind variables é o emprego de uma PIPELINED TABLE FUNCTION. Este tipo de função possui a propriedade de assumir o comportamento de uma tabela e ser utilizada como uma fonte de dados numa cláusula FROM. Esta solução eu vi pela primeira vez apresentada pelo Tom Kyte em um artigo do AskTom (http://asktom.oracle.com) e atende perfeitamente o quesito bind variables, mas possui uma limitação: o otimizador por padrão não consegue coletar estatísticas deste tipo de objeto e é forçado a utilizar estimativas que frequentemente estão incorretas e podem afetar negativamente (e muito) os planos de execução.

Até pouco tempo a única solução que eu conhecia para esta limitação era empregar uma hint CARDINALITY para fixar as estatísticas manualmente, mas graças a um artigo do Adrian Billington (do site oracle-developer.net) descobri a possibilidade de utilizar um recurso bastante poderoso do otimizador do Oracle 11g conhecido como Extensible Optimizer Interface.

O propósito deste artigo, portanto, é apresentar o problema da lista de valores concatenados e suas diversas soluções para, finalmente, demonstrar as capacidades da interface de extensão do otimizador como uma solução mais elegante e versátil para o problema

Conceitos básicos

Este artigo irá fazer uso de alguns recursos avançados do Oracle e, portanto, é recomendado para DBAs e programadores PL/SQL mais experientes. No entanto, vou procurar explicar os conceitos da forma mais clara possível para que o texto possa ser acessível às mais diversas audiências. Note que é um requisito importante para o entendimento deste artigo possuir um conhecimento básico do otimizador, incluindo a leitura de planos de execução e as características das estatísticas que são necessárias para o seu funcionamento (ex: cardinalidade, seletividade e custo).

Outros conceitos para o entendimento completo deste artigo irei descrever a seguir:

Primeiro, a PIPELINED TABLE FUNCTION é uma função que pode ser utilizada na cláusula FROM de uma query, da mesma forma que uma VIEW inline ou uma tabela. Por exemplo, a seguinte sintaxe é válida:

SQL> select * from table(str2tbl('1,2,3'));

COLUMN_VALUE
------------------------------
1
2
3

No caso, o operador TABLE é responsável por dizer ao Oracle interpretar o resultado da função str2tbl como uma tabela. A função str2tbl, por sua vez, é uma função do tipo PIPELINED. Esta função foi criada originalmente pelo Tom Kyte, e o seu código pode ser encontrado livremente na internet. Eu tomei a liberdade de modificar um pouco o estilo de codificação por questões de gosto pessoal, mas a funcionalidade é idêntica. Sua implementação é a seguinte:

SQL> create or replace type typ_str2tbl_nst as table of varchar2(30);
  2  /

Type created.

SQL> create or replace function str2tbl (
  2    p_string    varchar2,
  3    p_delimiter char default ',' )
  4  return typ_str2tbl_nst pipelined
  5  as
  6    l_tmp varchar2(32000) := p_string || p_delimiter;
  7    l_pos number;
  8  begin
  9    loop
 10  	 l_pos := instr( l_tmp, p_delimiter );
 11  	 exit when nvl( l_pos, 0 ) = 0;
 12  	 pipe row ( rtrim( ltrim( substr( l_tmp, 1, l_pos-1) ) ) );
 13  	 l_tmp := substr( l_tmp, l_pos+1 );
 14    end loop;
 15  end str2tbl;
 16  /

Function created.

Como você pode ver, o que diferencia esta função de uma função tradicional são basicamente duas linhas: na linha 4 a palavra-chave PIPELINED define o tipo da função e, na linha 12, as palavras-chave PIPE ROW substituem a finalidade do RETURN. Ou seja, enquanto as funções normais possuem uma sequencia linear e retornam o valor apenas depois de completar todo o processamento (devolvendo um valor pela palavra-chave RETURN), as funções PIPELINED retornam multiplas linhas enquanto decorre o seu processamento normal, uma linha para cada instrução PIPE ROW.

Além da PIPELINED TABLE FUNCTION, iremos recorrer as extensões objeto-relacional do Oracle 11g, através da declaração de object types. Um objeto em Oracle funciona como uma classe em Java ou C++: trata-se de uma estrutura que pode conter tanto campos (valores) como métodos e funções. A criação de um objeto é feita através do comando CREATE TYPE, conforme demonstrado a seguir:

SQL> create or replace type typ_objeto as object(
  2    valor1 integer,
  3    valor2 float,
  4    valor3 varchar2(255),
  5    member function fn return number);
  6  /

Type created.

SQL> create or replace type body typ_objeto as
  2    member function fn return number
  3    as
  4    begin
  5      return 1;   
  6    end fn;
  7  end;
  8  /

Type body created.

Assim como as packages, os objetos possuem um header que contém as definições e um body que contém as implementações. Uma vez criado, o objeto pode ser utilizado livremente dentro do banco de dados como se fosse um tipo de dado comum, ou seja, é possível declarar colunas com o tipo do objeto em tabelas, criar coleções baseadas neste objeto, utilizá-lo como parãmetro de procedures e funções, etc. Exemplo:

SQL> select typ_objeto(1,2,'abcde') as meu_objeto from dual;

MEU_OBJETO(VALOR1, VALOR2, VALOR3)
--------------------------------------------------------------------------------
TYP_OBJETO(1, 2, 'abcde')

Para podermos trabalhar com a Optimizer Extensible Interface, necessitaremos criar um objeto especial que será chamado pelo otimizador na fase de hard parse. Este objeto irá implementar algumas funções específicas da API do otimizador conforme veremos adiante.

Preparando o terreno

Para começar, vamos criar uma massa de dados significativa para trabalharmos. O objetivo é simular uma tabela de fatos como, por exemplo, vendas. Para isso, vamos criar uma tabela base utilizando a view all_objects como referência:

SQL> create table vendas nologging as
  2  select trunc(dbms_random.value * 365 + sysdate - 365) as data,
  3         substr(owner, 1, 2) as tipo,
  4         object_name as produto,
  5         object_id as valor
  6         mod(rownum, 100) as quantidade
  7    from all_objects,
  8         (select 1 from dual connect by level <= 100)
  9   order by data;

Table created.

Explicando o comando acima, utilizei a dbms_random para gerar valores entre -365 e +365, que somados a SYSDATE – 365, vão resultar em um range de datas entre 2 anos atrás e a data atual. As outras colunas foram derivadas da view all_objects para simular os demais dados da venda. Vamos ver como ficou o perfil dos dados a seguir:

SQL> select count(*) from vendas;

  COUNT(*)
----------
   1210100

SQL> with x as (select count(*) qtd from vendas group by produto)
  2  select min(qtd),
  3         max(qtd),
  4         count(*) produtos,
  5         sum(qtd) total,
  6         trunc(sum(qtd)/count(*)) media
  7    from x;

  MIN(QTD)   MAX(QTD)   PRODUTOS      TOTAL      MEDIA
---------- ---------- ---------- ---------- ----------
       100        500       9209    1210100        131

Como era de se esperar, existem pelo menos 100 entradas para cada produto (derivado do object_name da all_objects) e no máximo 500, o que nos dá um bom candidato para um índice, pois cada acesso por produto retornará muito menos de 20% das linhas da tabela. Na vida real deveríamos considerar adicionar mais algumas colunas neste índice além do produto para auxiliar no relatório, porém para manter a simplicidade do exemplo vamos nos limitar a criar o índice em apenas uma coluna

SQL> create index idx_vendas on vendas(produto);

Index created.

Vamos agora atualizar as estatísticas da tabela vendas e capturar o plano de execução de uma busca genérica por alguns produtos.

SQL> exec dbms_stats.gather_table_stats(user,'VENDAS');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> set tab off
SQL> set linesize 150
SQL> select * from vendas where produto in ('A','B');

Execution Plan
----------------------------------------------------------
Plan hash value: 2573622372

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |   308 | 10472 |   253   (0)| 00:00:04 |
|   1 |  INLIST ITERATOR             |            |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| VENDAS     |   308 | 10472 |   253   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | IDX_VENDAS |   308 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PRODUTO"='A' OR "PRODUTO"='B')

Como esperado, o otimizador corretamente escolheu acessar os dados pelo índice.

O problema da lista de valores como parâmetro

Voltando ao nosso problema original, precisamos construir um relatório que permita como parâmetro de entrada uma lista de produtos separados por vírgulas. Usando SQL dinâmico podemos atender este requisito facilmente, conforme o código abaixo:

SQL> create or replace procedure sp_relatorio_vendas( p_data_inicio in  date,
  2                                                   p_data_fim    in  date,
  3                                                   p_produtos    in  varchar2,
  4                                                   p_relatorio   out sys_refcursor )
  5  as
  6  begin
  7    open p_relatorio for 'select * from vendas ' ||
  8                         'where data between :dt_inicio and :dt_fim ' ||
  9                         'and produto in (' || p_produtos || ')'
 10    using p_data_inicio, p_data_fim;
 11  end;
 12  /

Procedure created.

SQL> var rel refcursor;
SQL> set autotrace off
SQL> set feedback on
SQL> exec sp_relatorio_vendas(sysdate-7,sysdate,'''SRSIDX'',''OL$''',:rel);

PL/SQL procedure successfully completed.

SQL> print :rel

DATA      TIPO PRODUTO                             VALOR QUANTIDADE
--------- ---- ------------------------------ ---------- ----------
19-JUN-12 P    OL$                                  5464         49
21-JUN-12 S    OL$                                  5456         26
22-JUN-12 O    OL$                                   452         21
23-JUN-12 S    OL$                                  5456         34
17-JUN-12 T    SRSIDX                               8608         14

5 rows selected.

Observe que a abordagem acima, embora funcional, possui dois problemas: primeiro, a passagem da lista de valores necessitou ser tratada para incluir aspas adicionais nos tipos requisitados ou causaria erro na concatenação da cláusula IN. Esta é uma potencial fonte de erros caso este tratamento não seja feito e, de modo geral o uso da SP fica menos intuitivo.

O segundo ponto é a própria concatenação em si. Por mais que tenhamos utilizado bind variables para os campos de data, a concatenação da cláusula IN vai gerar um hard parse e consumir espaço na shared pool para cada combinação distinta de tipos de vendas. Este tipo de codificação pode ser fatal em um ambiente OLTP ou misto.

No entanto, o plano de execução está fazendo uso do índice conforme esperado. Podemos ver isso através do SQL trace ou, como se trata de uma query simples, capturando o seu plano executando-a isoladamente:

SQL> var dt1 varchar2
SQL> var dt2 varchar2
SQL> select *
  2    from vendas
  3   where data between :dt1 and :dt2
  4     and produto in ('SRSIDX,OL$');

Execution Plan
----------------------------------------------------------
Plan hash value: 1426441069

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    34 |   128   (0)| 00:00:02 |
|*  1 |  FILTER                      |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| VENDAS     |     1 |    34 |   128   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IDX_VENDAS |   154 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:DT1)<=TO_DATE(:DT2))
   2 - filter("DATA">=:DT1 AND "DATA"<=:DT2)
   3 - access("PRODUTO"='SRSIDX,OL$')

Com estes fatos em mente, vamos partir para a implementação utilizando funções pipelined.

A solução por meio de funções PIPELINED

De modo a obter uma solução consistente tanto em usabilidade como performance, vamos substituir o SQL dinâmico do relatório por uma chamada da função str2tbl:

SQL> create or replace procedure sp_relatorio_vendas( p_data_inicio in  date,
  2                                                   p_data_fim    in  date,
  3                                                   p_produtos    in  varchar2,
  4                                                   p_relatorio   out sys_refcursor )
  5  as
  6  begin
  7    open p_relatorio for select *
  8                           from vendas
  9                          where data between p_data_inicio and p_data_fim
 10                            and produto in (select column_value
 11                                              from table(str2tbl(p_produtos)));
 12  end sp_relatorio_vendas;
 13  /

Procedure created.

Note que a lista de tipos separados por vírgulas vai ser decomposta pela função str2tbl alimentando a cláusula IN. Com esta alteração o cursor principal da SP passa a ser único para todas as chamadas possíveis, uma vez que todas as variáveis da SP agora passam a ser passadas como bind variables para o cursor. Além disso, não é mais necessário o "triple quote" na hora de chamar a SP, pois não estamos mais concatenando valores:

SQL> exec sp_relatorio_vendas(sysdate-7,sysdate,'SRSIDX,OL$',:rel);

PL/SQL procedure successfully completed.

SQL> print :rel

DATA      TIPO PRODUTO                             VALOR QUANTIDADE
--------- ---- ------------------------------ ---------- ----------
17-JUN-12 T    SRSIDX                               8608         14
19-JUN-12 P    OL$                                  5464         49
21-JUN-12 S    OL$                                  5456         26
22-JUN-12 O    OL$                                   452         21
23-JUN-12 S    OL$                                  5456         34

5 rows selected.

No entanto nem todas as mudanças são positivas. Como a função str2tbl não possui estatísticas definidas, o otimizador se vê obrigado a estimar a cardinalidade da mesma, resultando em um plano sub-ótimo como veremos a seguir.

SQL> select *
  2    from vendas
  3   where data between :dt1 and :dt2
  4     and produto in (select column_value
  5                       from table(str2tbl('SRSIDX,OL$')));

Execution Plan
----------------------------------------------------------
Plan hash value: 213261543

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |    36 |  1971   (5)| 00:00:24 |
|*  1 |  FILTER                             |         |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI              |         |     1 |    36 |  1971   (5)| 00:00:24 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL |       |       |            |          |
|*  4 |    TABLE ACCESS FULL                | VENDAS  |  3033 |   100K|  1941   (5)| 00:00:24 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:DT1)<=TO_DATE(:DT2))
   2 - access("PRODUTO"=VALUE(KOKBF$))
   4 - filter("DATA">=:DT1 AND "DATA"<=:DT2)

O plano que anteriormente fazia acesso através de um INDEX RANGE SCAN agora está fazendo um TABLE ACCESS FULL na tabela vendas com um HASH JOIN no resultado da função pipelined. Nós sabemos que este plano está errado porque conhecemos a cardinalidade da função str2tbl. Mas como será que o Oracle está enxergando esta cardinalidade? Vamos ver o plano:

SQL> select * from table(str2tbl('SRSIDX,OL$'));

Execution Plan
----------------------------------------------------------
Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |       |       |            |          |
---------------------------------------------------------------------------------------------

Observe que a estimativa do Oracle está errada por 4 ordens de magnitude! Isso porque na falta de estatísticas ele se vê obrigado a usar o valor default, que é dependente do tamanho do bloco do seu banco de dados (no meu caso está configurado como 8K).

Então para resolver este problema precisamos informar de alguma forma as estatísticas corretas para o otimizador. Como eu comentei antes, uma das maneiras de fazer isso é através de uma hint CARDINALITY. Porém esta hint vai exigir que, ou fixemos o valor de cardinalidade na SP, ou transformemos a SP novamente em um SQL dinâmico (para desta vez concatenar o valor da cardinalidade na hint, o que nos trás novamente o problema do hard parse).

Portanto, a hint CARDINALITY não nos parece uma boa idéia. E qual a alternativa? É aí que entra a interface de extensão do otimizador (Optimizer Extension Interface).

Resolvendo a falta de estatísticas com extensões do otimizador.

O primeiro passo para criar a extensão do otimizador é definir um tipo de objeto que será a nossa interface com o otimizador. A versão apresentada aqui será uma interface bastante resumida, apenas com o mínimo de funcionalidade para o nosso exemplo. Quem quiser a referência completa, deve buscar a informação no Oracle Database Data Cartridge Developer's Guide, disponível no site de documentação da Oracle.

Vamos começar com a implementação do header do nosso objeto:

SQL> create or replace type typ_str2tbl_stats as object (
  2    dummy integer,
  3
  4    static function ODCIGetInterfaces ( p_interfaces out SYS.ODCIObjectList )
  5    return number,
  6
  7    static function ODCIStatsTableFunction ( p_function  in  SYS.ODCIFuncInfo,
  8                                             p_stats     out SYS.ODCITabFuncStats,
  9                                             p_args      in  SYS.ODCIArgDescList,
 10                                             p_string    in  varchar2,
 11                                             p_delimiter in  char default ',' )
 12    return number
 13  );
 14 /

Type created.

Este tipo contém uma variável e dois métodos. Na verdade não precisaríamos de nenhuma variável neste exemplo, porém o Oracle requer que um tipo de objeto possua pelo menos uma variável em sua definição, portanto foi criada uma variável dummy apenas para atender a esta regra.

Quanto aos métodos, o método ODCIGetInterfaces é obrigatório e serve para comunicar ao otimizador a versão para qual a extensão foi desenvolvida. Tanto sua definição quanto implementação (que veremos a seguir) segue um padrão e não precisa ser modificada.

O segundo método, ODCIStatsTableFunction, é o método que efetivamente será chamado para retornar as estatísticas da função. Sua definição segue o seguinte formato:

   static function ODCIStatsTableFunction ( p_function  in  SYS.ODCIFuncInfo,
                                            p_stats     out SYS.ODCITabFuncStats,
                                            p_args      in  SYS.ODCIArgDescList,
                                            lista_de_parametros )
   return number

Onde a lista_de_parametros tem que ser a mesma lista de parâmetros da função alvo. No caso, a str2tbl possui dois parâmetros e, portanto, são eles que são vistos na definição do nosso tipo typ_str2tbl_stats. Estes parâmetros serão copiados da chamada da função str2tbl para a chamada do método ODCIStatsTableFunction quando o otimizador solicitar as estatísticas da função.

Agora, vamos ao type body:

SQL> create or replace type body typ_str2tbl_stats
  2  as
  3    static function ODCIGetInterfaces ( p_interfaces out SYS.ODCIObjectList )
  4    return number
  5    as
  6    begin
  7      p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') );
  8
  9      return ODCIConst.success;
 10    end ODCIGetInterfaces;
 11
 12    static function ODCIStatsTableFunction ( p_function  in  SYS.ODCIFuncInfo,
 13                                             p_stats     out SYS.ODCITabFuncStats,
 14                                             p_args      in  SYS.ODCIArgDescList,
 15                                             p_string    in  varchar2,
 16                                             p_delimiter in  char default ',' )
 17    return number
 18    as
 19    begin
 20      p_stats := SYS.ODCITabFuncStats( ceil( length( p_string ) / 2 ) );
 21
 22      return ODCIConst.success;
 23    end ODCIStatsTableFunction;
 24
 25  end;
 26  /

Type body created.

Como disse anteriormente, a implementação do método ODCIGetInterfaces é padrão. Já o método ODCIStatsTableFunction possui uma implementação relativamente simples: o parâmetro p_stats é a forma que este método tem para comunicar ao otimizador as informações necessárias para a elaboração do plano de execução.

No caso, estamos interessados em informar uma cardinalidade mais próxima do real do que os valores default. Existem duas maneiras de fazer isso: passando a cardinalidade estimada para o construtor do objeto ODCITabFuncStats (conforme foi feito na listagem de exemplo) ou passando um valor NULL para este construtor e atribuindo a cardinalidade diretamente na propriedade num_rows deste objeto:

p_stats :=  ODCITabFuncStats(NULL);
p_stats.num_rows := cardinalidade;

Como nós podemos implementar este método da forma que acharmos mais conveniente, poderíamos inclusive contar a quantidade de itens na lista de valores e passar o valor exato de linhas de volta para o otimizador. No entanto, optei por uma abordagem indireta, om fazendo uma estimativa do número de linhas pelo tamanho da string de entrada, conforme o cálculo da linha 20.

No caso, este algoritmo assume que a cardinalidade da função é equivalente a metade do comprimento da string, arredondado para cima. O que para nós é suficiente, pois a ordem de magnitude da estimativa é compatível com a cardinalidade real.

O último passo agora é associar o objeto de estatísticas typ_str2tbl_stats à função str2tbl. Isto é feito pelo seguinte comando:

SQL> associate statistics with functions str2tbl using typ_str2tbl_stats;

Statistics associated.

Vamos capturar mais uma vez o plano de execução para testemunhar o funcionamento da nossa extensão do otimizador:

SQL> select * from table(str2tbl('SRSIDX,OL$'));

Execution Plan
----------------------------------------------------------
Plan hash value: 2407808827

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     5 |    10 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| STR2TBL |       |       |            |          |
---------------------------------------------------------------------------------------------

SQL> select *
  2    from vendas
  3   where data between :dt1 and :dt2
  4     and produto in (select column_value
  5                       from table(str2tbl('SRSIDX,OL$')));

Execution Plan
----------------------------------------------------------
Plan hash value: 1071006446

----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |     2 |    72 |   412   (1)| 00:00:05 |
|*  1 |  FILTER                               |            |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID         | VENDAS     |     1 |    34 |   127   (0)| 00:00:02 |
|   3 |    NESTED LOOPS                       |            |     2 |    72 |   412   (1)| 00:00:05 |
|   4 |     SORT UNIQUE                       |            |       |       |            |          |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| STR2TBL    |       |       |            |          |
|*  6 |     INDEX RANGE SCAN                  | IDX_VENDAS |   154 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:DT1)<=TO_DATE(:DT2))
   2 - filter("DATA">=:DT1 AND "DATA"<=:DT2)
   6 - access("PRODUTO"=VALUE(KOKBF$))


Graças ao uso da extensão do otimizador pelo objeto typ_str2tbl_stats, agora o Oracle consegue estimar corretamente a cardinalidade da nossa função pipelined e com isso um melhor plano de execução é gerado.

Conclusão

A Optimizer Extension Interface é um recurso poderoso de otimização, permitindo passar estatísticas corretas para o Oracle onde ele não consegue determiná-las automaticamente. Note que neste exemplo outras atitudes poderiam ser tomadas para otimizar ainda mais o relatório, mas para não prejudicar o componente didático, optei pela abordagem mais simples apenas para demonstrar o recurso em questão. O uso desta técnica permitiu de uma forma elegante e sem hints atender a um requisito do sistema sem problemas de performance.

Esse assunto por si só é muito interessante e renderia muito mais discussões. Existem muito mais recursos das extensões do otimizador que podem ser exploradas que não foram apresentadas aqui. Recomendo, portanto, a quem interessar se aprofundar no assunto, consultar o manual Oracle Database Data Cartridge Developer's Guide na documentação da Oracle.




Postado por Paulo Alexandre Petruzalek. Oracle Advanced PL/SQL Developer Certified Professional, Oracle PL/SQL Developer Certified Associate, Oracle Database 11g Administrator Certified Associate, Oracle Database SQL Certified Expert, OPN Certified Specialist.