Marcadores

sábado, 7 de março de 2015

Planos de execução curiosos

Analisar planos de execução deve ser uma atividade recorrente na vida de um desenvolvedor Oracle.

Para sair um pouco do padrão, separei alguns planos com algumas operações diferentes dos típicos "TABLE ACCESS FULL", "INDEX UNIQUE SCAN".

Bora lá?

set autot trace explain

No primeiro caso utilizei uma tabela chamada "objetos" com uma coluna "object_id" como chave primária.
Imagine o típico cenário onde uma pesquisa é feita e um parâmetro passado pode ser nulo e, neste caso, o parâmetro deve ser ignorado.
Nesse cenário temos um impasse, se o parâmetro for passado, a cardinalidade da query diminuiria e poderemos utilizar um determinado índice, no entanto, se o parâmetro não for passado a cardinalidade da query aumentaria significativamente e um "FULL TABLE SCAN" poderia ser a melhor opção.

Para simular isso devemos criar uma variável:
variable b number;

Uma das alternativas é construir a seguinte query:

select * from objetos where object_id = :b or :b is null;

... e obtermos o plano:

Execution Plan
----------------------------------------------------------
Plan hash value: 4071356626

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   720 | 64800 |    56   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| OBJETOS |   720 | 64800 |    56   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter(:B IS NULL OR "OBJECT_ID"=TO_NUMBER(:B))

Outra alternativa seria:


select * from objetos where object_id = nvl(:b,object_id);

... e então o plano ficaria:

Execution Plan
----------------------------------------------------------
Plan hash value: 1003103769

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         | 14378 |  1263K|    58   (0)| 00:00:01 |
|   1 |  CONCATENATION                |         |       |       |            |          |
|*  2 |   FILTER                      |         |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | OBJETOS | 14377 |  1263K|    56   (0)| 00:00:01 |
|*  4 |   FILTER                      |         |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| OBJETOS |     1 |    90 |     2   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | OBJ_PK  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter(:B IS NULL)
   3 - filter("OBJECT_ID" IS NOT NULL)
   4 - filter(:B IS NOT NULL)
   6 - access("OBJECT_ID"=:B)

Exatamente o que precisávamos.

Para os que não a conhecem eu vos apresento a operação "CONCATENATION".
É como se o Oracle reescrevesse a nossa query da seguinte forma:

select * from objetos where object_id = :b and :b is not null
UNION ALL
select * from objetos where object_id is not null and :b is null;

Dessa forma ele consegue um "duplo" plano de execução aplicando os filtros e os acessos adequados para cada situação.

No nosso exemplo, o predicado é aplicado na PK, mas é bom saber que essa situação não é conveniente quando se tem uma coluna nullable e queremos preservar as correspondências nulas quando o parâmetro não é passado.

Agora vou mostrar alguns casos peculiares utilizando outras estruturas que não são exatamente tabelas no banco de dados.

Veja a operação que acontece quando se usa uma nested table instanciada em tempo de execução:

select * from table(sys.odcinumberlist(1,2,3));

Execution Plan
----------------------------------------------------------
Plan hash value: 1748000095

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

É um pouco diferente da nested table sendo consultada depois de instanciada, como acontece em processos PLSQL.
Veja o que acontece quando pedimos o plano de execução da própria DBMS_XPLAN.DISPLAY


select * from table(dbms_xplan.display);

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

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

Apesar de esquisitas essas operações são bem simples de entender: O SQL Engine está acessando estruturas de memória da própria PGA.

Um pouco de engenharia reversa, agora:

Quem já viu esse plano de execução?

Execution Plan
----------------------------------------------------------
Plan hash value: 4223128547

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  XMLTABLE EVALUATION |      |       |       |            |          |
-----------------------------------------------------------------------------

Essa é a query que o originou:

select * from xmltable('xml' passing xmltype('<xml>1</xml>') columns num number path '/');

Quem ainda não brincou com XMLTABLEs estou preparando um post específico sobre esse assunto.

Para o próximo caso, vou preparar o modelo criando uma tabela com duas colunas com valores únicos, id1 e id2, e índices únicos criados nelas

create table obj as select rownum id1, rownum id2, lpad('#',4000,'#') v from dual connect by level <= 10000;

Table created.


create unique index obj_idx1  on obj (id1);

Index created.


create unique index obj_idx2  on obj (id2);

Index created.


exec dbms_stats.gather_table_stats(USER,'OBJ');

PL/SQL procedure successfully completed.

Depois de coletar as estatísticas vou tentar selecionar somente as colunas indexadas e realizar um "INDEX FAST FULL SCAN"

select id1, id2 from obj;

Execution Plan
----------------------------------------------------------
Plan hash value: 730912574

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 | 80000 |  2753   (1)| 00:00:34 |
|   1 |  TABLE ACCESS FULL| OBJ  | 10000 | 80000 |  2753   (1)| 00:00:34 |
--------------------------------------------------------------------------

Hmmm... não deu certo pois, o FFS só acontece quando desconsideramos valores nulos. Então deixe-me dizer claramente ao Oracle que essas colunas não aceitam nulos:

alter table obj modify(id1 number not null);

Table altered.

alter table obj modify(id2 number not null);

Table altered.

Tentando de novo:

select id1, id2 from obj;

Execution Plan
----------------------------------------------------------
Plan hash value: 63238214

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  | 10000 | 80000 |    42   (0)| 00:00:01 |
|   1 |  VIEW                  | index$_join$_001 | 10000 | 80000 |    42   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| OBJ_IDX1         | 10000 | 80000 |    26   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| OBJ_IDX2         | 10000 | 80000 |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access(ROWID=ROWID)

Aí está. Dois "INDEX FAST FULL SCAN" na mesma tabela e então realizou o JOIN dos dois índices. Veja o nome do objeto criado internamente para isso "index$_join$_001".


Vou reconstruir a tabela e agora não vou colocar a constraint NOT NULL

drop table obj;

Table dropped.

create table obj as select rownum id1, rownum id2, lpad('#',4000,'#') v from dual connect by level <= 10000;

Table created.


create unique index obj_idx1  on obj (id1);

Index created.


create unique index obj_idx2  on obj (id2);

Index created.


exec dbms_stats.gather_table_stats(USER,'OBJ');

PL/SQL procedure successfully completed.

Agora, se a query disser que não deseja verificar valores nulos, obtemos o mesmo INDEX_JOIN:

select id1, id2 from obj where id1 is not null and id2 is not null;

Execution Plan
----------------------------------------------------------
Plan hash value: 63238214

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  | 10000 | 80000 |    42   (0)| 00:00:01 |
|   1 |  VIEW                  | index$_join$_001 | 10000 | 80000 |    42   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| OBJ_IDX1         | 10000 | 80000 |    26   (0)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| OBJ_IDX2         | 10000 | 80000 |    26   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access(ROWID=ROWID)
   3 - filter("ID1" IS NOT NULL)
   4 - filter("ID2" IS NOT NULL)


drop table obj;

Table dropped.


Para encerrar esse post, o plano de execução mais curioso que já vi.
Vejamos o que acontece quando usamos a cláusula ROLLUP no GROUP BY:


select * from dual group by rollup (dummy);

Execution Plan
----------------------------------------------------------
Plan hash value: 2656630603

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     2 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT ROLLUP|      |     1 |     2 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL         | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Nada de mais, certo?
Agora veja o que acontece quando colocamos duas vezes a mesma coluna no ROLLUP:

select * from dual group by rollup (dummy,dummy);

Execution Plan
----------------------------------------------------------
Plan hash value: 189552792

----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |     1 |     2 |     9  (12)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION    |                          |       |       |            |          |
|   2 |   MULTI-TABLE INSERT          |                          |       |       |            |          |
|   3 |    SORT GROUP BY NOSORT ROLLUP|                          |     1 |     2 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL         | DUAL                     |     1 |     2 |     2   (0)| 00:00:01 |
|   5 |    DIRECT LOAD INTO           | SYS_TEMP_0FD9D66B3_366A0 |       |       |            |          |
|   6 |    DIRECT LOAD INTO           | SYS_TEMP_0FD9D66B4_366A0 |       |       |            |          |
|   7 |   VIEW                        |                          |     3 |     6 |     6   (0)| 00:00:01 |
|   8 |    VIEW                       |                          |     3 |     6 |     6   (0)| 00:00:01 |
|   9 |     UNION-ALL                 |                          |       |       |            |          |
|  10 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D66B3_366A0 |     1 |     2 |     2   (0)| 00:00:01 |
|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D66B3_366A0 |     1 |     2 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D66B4_366A0 |     1 |     2 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Alguém pode me explicar isso?

Toda essa parafernalha de coisas tem um motivo: evitar ter que sumarizar os registros duas vezes.
O o Oracle cria duas tabelas temporárias, uma pro group by (que ele chamou de "SYS_TEMP_0FD9D66B3_366A0") e outra pro rollup ("SYS_TEMP_0FD9D66B4_366A0 ") e utiliza um MULTI TABLE INSERT para carregá-las, para depois expor o resultset a partir delas mostrando os dados do group by, quantas vezes for necessário.
Louco né?

Eu gostaria de falar do BITMAP CONVERSION TO/FROM ROWID, mas para deixar o blog mais interativo vou pedir que alguém me mande um exemplo com a criação de um modelo de dados para o estudo de caso. Pode ser?


Por hoje é só
Espero que tenham gostado desse post.

exit

sexta-feira, 6 de fevereiro de 2015

Curso Itinerante de Tecnologia Oracle





Esse ano resolvi juntar um pessoal e compartilhar um pouco do conhecimento que venho colecionando sobre tecnologia Oracle.


Como trabalho na região da Paulista, quis aproveitar um pouco da energia do lugar: muitos restaurantes, cafés, bares, livrarias.





Mas como oferecer um curso de tecnologia Oracle dessa forma? Hoje em dia é muito simples e bastam alguns ingredientes:

- Internet (via hotspots ou um bom 3g ou 4g);

- Uma instância Oracle em nuvem;

- Notebooks com um mínimo para se instalar o instant client e o SQL*Plus;

- Uma galera com a faca nos dentes para aprender.


Na nuvem, subi uma instância Oracle via Amazon Web Services - RDS.

Coloquei uma configuração básica para não gerar muito custo, mas que, mesmo assim, supera muito configurações de instalações do server em um desktop ou notebook.


O aluno leva seu próprio notebook e na primeira aula já fazemos a instalação do instant client. Configuramos o TNS para acessar a instância na Amazon e, assim, o aluno já leva pra casa, e qualquer outro lugar, seu laboratório prontinho.


Quais os benefícios de realizar um curso assim?

1) Comida e bebida.

Estando em um lugar que serve comida, é educado consumir alguma coisa para não ficar só chupinhando a internet dos outros. Acontece que o curso fica muito mais descontraído e, depois de um dia inteiro de trabalho, por que não pedir um belo sanduba e uma gelada para segurar as próximas três horas de curso?


2) Lab portátil.

Como já mencionei, o profissional já sai da primeira aula equipado com o seu laboratório prontinho. Leva pra qualquer lugar e pode aplicar o conhecimento através de desafios off class (pra não dizer "lição de casa").


3) Uma única instância para administrar.

Com todos acessando uma única instância, fica mais fácil de eu preparar as aulas e os desafios; crio uma única massa de testes, administro uma única role de usuários por classe.


4) Cloud computing.

É importante que o profissional Oracle tenha contato com arquiteturas de sistemas mais abrangentes. Faz muita diferença para o estudante sair um pouco da caixa e experimentar soluções em Cloud e que dão maior versatilidade de acesso. Ao tomar decisões sobre seus modelos de negócio, futuramente, essas tecnologias já farão parte naturalmente de seu leque de escolhas.


5) Momentos de emoção.

Essa, na verdade, pode ser uma desvantagem para quem não tem aquele espírito aventureiro. Nem todo hotspot tem aquela estabilidade de conexão, então algumas vezes temos que partir para um outro plano, ou seja, outro bar ou restaurante. Mas isso é o que não falta na região da Paulista.


Já estamos na aula 7 e muita coisa já foi exercitada.

Se o modelo funcionar, quem sabe a gente não se encontra pra tomar umas e "Hands ON!!!""


Abraços e até a próxima.

quinta-feira, 7 de novembro de 2013

Sites essenciais para profissionais Oracle

Essa postagem é para divulgar a nova seção "Sites Essenciais" do blog que não aparece nas visualizações mobile (a não ser que se opte pela visualização para web).

Resolvi compartilhar nessa seção os sites que fazem parte da minha rotina de profissional Oracle.

Óbvio que falta bastante coisa, mas esses lugares realmente estão entre as páginas que mais acesso no que diz respeito a essa tecnologia.

Documentação Oracle
Se ainda não tem essa página entre os favoritos de seu navegador, então você:
1) já baixou toda documentação em pdf no seu computador.
2) só digita "d" na barra de endereço do seu navegador e ele já autocompleta docs.oracle.com.
3) não lê absolutamente nada de Inglês, aliás ainda não desvendou o significado das palavras mágicas "SELECT", "FROM" e "WHERE".
4) tem apenas um único endereço de favoritos - google.com - que, vez ou outra, te encaminha para lá
5) não é profissional de TI e só está lendo este post para dar uma força para um conhecido que está blogando sobre Oracle (Beijo, mãe!).
6) demorou.

Ask Tom
Dispensa apresentações. Não poderia faltar aqui.

Nerv Informática
Em português. Entre neste site e se informe sobre cursos de formação Oracle e Banco de Dados em geral com o extraordinário profissional Ricardo Portilho. Não deixe de acompanhar o blog.

Ora Ora - Tecnologia Oracle e Afins
Em português. Blog inteligente, cheio de curiosidades e com excelente senso de humor. O autor Marcus Rangel aborda assuntos bem diversos e está constantemente atualizado sobre as tecnologias Oracle.

OakTable.net
Galera expert em Oracle do mundo todo trocando idéias sobre tudo o que se pode imaginar. Que mal pode haver nisso?

Jonathan Lewis
Um dos autores do Oracle Server. Conhece os meandros do CBO. Ótima referência para quem se aventura nas praias do Oracle Performance Diagnostics and Tuning.

Adrian Billington's oracle-developer.net
Esse é o meu favorito. Específico para desenvolvedores, apresenta uma variedade surpreendente de soluções para os mais diversos desafios.

Julian Dyke's Oracle Internals
O nome Oracle Internals é convidativo, por isso não vou estragar a surpresa.

Steven Feuerstein
Um dos autores do PL/SQL, portanto o site específico sobre o assunto. Não deixe de acessar o "Help Improve PL/SQL" e o "Take the PL/SQL Challenge".

Esse são meus top most. Se tiver alguma sugestão não deixe de me enviar.




sábado, 2 de novembro de 2013

Afinal, existe divisão por zero?

Uma das habilidades mais importantes que venho tentando desenvolver enquanto desenvolvedor Oracle é a de realizar um bom teste; um teste que me deixe por a cabeça tranquilamente no travesseiro depois de publicar as melhorias de performance.

Vou dedicar alguns posts para falar disso e hoje gostaria de começar esse tópico.

Um recurso bastante interessante que costumo utilizar em meus testes para verificar se determinados conceitos foram assimilados é a divisão por zero.

Ao falar de performance em aplicações Oracle por aí, já ouvi alguns mitos. Um deles é:
Substitua tudo que estiver na cláusula SELECT da condição EXISTS por NULL que você terá melhoria de performance. Assim:

select 1 
from   dual 
where  exists
   (select NULL/* estamos falando deste NULL*/ 
    from   dual);

Ouvi falar também que o SQL Engine do ORACLE simplesmente ignora o que estiver na cláusula SELECT do EXISTS.

Para saber se isso é verdade, uma das formas é criar uma função e ver ver se realmente a função é chamada de dentro do SELECT da condição EXISTS - uma função que demore intencionalmente um segundo para ser executada pode ajudar.

create or replace function f_funcao_lenta return number is
begin
  -- Criamos a demora forçada
  dbms_lock.sleep(1);
  return 1;
end;

SQL> select 1 col from dual where exists(select f_funcao_lenta from dual);

       COL
----------
         1

1 row selected.

Elapsed: 00:00:00.02
SQL>

Esse breve teste nos dá uma dica, mas podemos estar lidando com alguma forma de cache desta função, uma vez que estamos numa versão 12c do Oracle Server.

Outra forma de verificar o conceito é então utilizar uma divisão por zero e esperar por um erro ORA-01476: divisor is equal to zero.

SQL> select 1 col from dual where exists(select 1/0 from dual);

       COL
----------
         1

1 row selected.

Elapsed: 00:00:00.03
SQL>

Neste teste não houve exceção gerada. Num primeiro momento podemos ter a impressão de estarmos diante de um bug, pois não existe divisão por zero e a instrução pergunta claramente se EXISTE 1/0.
O que o Oracle na verdade está respondendo é: SIM, existe ao menos um registro na tabela dual. E essa é a informação relevante para o SQL Engine.

Desta forma, então, o Oracle resolve ignorar a expressão "1/0".
Se habilitarmos o autotrace do SQL*Plus podemos ter uma dica do que acontece.

SQL> set autot trace
SQL> select 1 col from dual where exists(select 1/0 from tst_exists);

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 46656579

-------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |     5   (0)| 00:00:01 |
|*  1 |  FILTER            |            |       |            |          |
|   2 |   FAST DUAL        |            |     1 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TST_EXISTS |     1 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "TST_EXISTS" "TST_EXISTS"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Podemos ver no plano de execução o filtro EXISTS reescrito.
Já rodei esse teste nas versões 10g, 11g e 12c e o resultado é o mesmo: a divisão por zero não interfere no resultado.

Conclusão: Não baseie suas estratégias de performance em reescrever as cláusulas SELECTS das condições EXISTS. O máximo que você pode economizar de tempo será durante o parse da query.

Por hoje á só. Até o próximo post ou comentário.

segunda-feira, 14 de outubro de 2013

Cade o registro que estava aqui? ou Que mal pode fazer pedir para o Oracle analisar uma tabela?

Outro dia um amigo meu foi envolvido num processo que começou a dar problema após uma pequena alteração de melhoria de performance.

Após um belo tempo tentando entender o que estava acontecendo ele resolveu me mostrar o código para tentar desvendar o mistério.




Modelagem de teste bem resumida:
SQL>
SQL> desc tst_temp;
 Name                           Null?    Type
 ------------------------------ -------- ------
 N                                       NUMBER

SQL> desc tst_fisica;
 Name                           Null?    Type
 ------------------------------ -------- ------
 N                              NOT NULL NUMBER

SQL>






Simplificação do que o processo fazia:
SQL>
SQL> DECLARE
  2   n NUMBER;
  3  BEGIN
  4   --
  5   INSERT INTO tst_temp VALUES(1);
  6   --
  7   INSERT INTO tst_fisica VALUES(1);
  8   --
  9   EXECUTE IMMEDIATE 'ANALYZE TABLE tst_fisica COMPUTE STATISTICS';
 10   --
 11   SELECT COUNT(1) qtd_temp
 12   INTO   n
 13   FROM   tst_temp t1
 14   JOIN   tst_fisica t2
 15   ON     t1.n = t2.n;
 16   --
 17   dbms_output.put_line('Encontrados ' || n || ' registro(s)');
 18   --
 19  END;
 20  /
Encontrados 0 registro(s)

PL/SQL procedure successfully completed.

SQL>

Veja que a mensagem nos diz: Encontrados 0 registro(s)

A pergunta era: Onde foi para o registro que acabei de inserir?

Essa é a pergunta que eu te faço, meu caro leitor.

Tente descobrir o que ocorre neste processo.

Se não tiver a menor ideia, vou dar uma ajudinha.


Seguem as instruções de criação das tabelas usadas nos testes:
SQL>
SQL> CREATE GLOBAL TEMPORARY TABLE tst_temp(n NUMBER);

Table created.

SQL> CREATE TABLE tst_fisica(n NUMBER PRIMARY KEY);

Table created.

SQL>

Vou começar a explicar o que ocorreu, então, se não descobriu o problema, mas ainda quer tentar, pare de ler.



O processo falha por causa da combinação:
  - TABELA TEMPORÁRIA POR TRANSAÇÃO
  - INSTRUÇÃO DDL

No Oracle, por default, uma tabela global temporary é criada com a característica "ON COMMIT DELETE ROWS", que faz com que ela seja esvaziada toda vez que uma transação for finalizada, com um commit por exemplo.

Caso não seja esse o comportamento desejado, é necessário complementar a sua instrução de criação com a cláusula "ON COMMIT PRESERVE ROWS", neste caso a tabela temporária se esvazia automaticamente a cada nova sessão.

Quando apontei esse detalhe fui logo indagado: "mas no meu processo não tem COMMIT".
Realmente não há commit explícito no código, mas é uma característica do Oracle que toda instrução DDL comita a transação pendente.

No nosso caso a a instrução DDL é justamente a alteração que prometia melhoria de performance:
"ANALYZE TABLE tst_fisica COMPUTE STATISTICS"

Caso estejam pensando numa maneira de contornar o problema, já vou logo avisando que não adianta substituir o ANALYZE pelo dbms_stats:

SQL>
SQL> DECLARE
  2   n NUMBER;
  3  BEGIN
  4   --
  5   INSERT INTO tst_temp VALUES(2);
  6   --
  7   INSERT INTO tst_fisica VALUES(2);
  8   --
  9   dbms_stats.gather_table_stats(USER,'TST_FISICA');
 10   --
 11   SELECT COUNT(1) qtd_temp
 12   INTO   n
 13   FROM   tst_temp t1
 14   JOIN   tst_fisica t2
 15   ON     t1.n = t2.n;
 16   --
 17   dbms_output.put_line('Encontrados ' || n || ' registro(s)');
 18   --
 19  END;
 20  /
Encontrados 0 registro(s)

PL/SQL procedure successfully completed.

SQL>

Vejam que a API também finaliza a transação.

Na ocasião, perguntei ao meu colega (que não trabalha na mesma empresa que eu, nem nos clientes que eu atendo) qual era o prazo que ele tinha para publicar a alteração de performance que ele estava fazendo.

Ele me respondeu que já tinha publicado em ambiente de produção, mas que não sabia que a inclusão desta linha (ANALYZE) podia dar esse tipo de problema, afinal "que mal pode fazer pedir para o Oracle analisar uma tabela?"

Para ele foi um aprendizado e tanto, mas pra quem ainda não pegou, fica o recado:
1 - Antes de incluir chamadas puco ortodoxas no código, sempre converse com DBAs e outros desenvolvedores.
2 - Testem, testem, testem

A questão da performance em si, eu gostaria de deixar para um próximo post, então, por hoje é só.

SQL> DROP TABLE tst_temp;

Table dropped.

SQL> DROP TABLE tst_fisica;

Table dropped.

SQL> exit

sexta-feira, 6 de setembro de 2013

O Enigma da Data Vigente

A noção de vigência de registros é muito comum nos sistemas de informação. É fato que alguns profissionais preferem determinadas modelagens a outras e, na minha opinião, não acho que seja o caso de se criar muitas conjecturas de qual é a melhor forma de se modelar.
Sobre esse tópico em especial, tive uma experiência bem interessante quando fui chamado para tentar dar maior performance num processo de desempenho bastante comprometido.
Gostaria de dividir com vocês.
Pra simularmos resumidamente a modelagem com que me deparei na ocasião criei essa instrução CTAS:
21:03:28 SQL> CREATE TABLE tst_vig AS
21:03:28   2  SELECT MOD(ROWNUM - 1, 1000) + 1 ID,
21:03:28   3       DATE '1980-01-01' + FLOOR((ROWNUM - 1) / 1000) * 10 dt,
21:03:28   4       ROUND(dbms_random.value * 1000, 2) vlr
21:03:28   5  FROM   dual
21:03:28   6  CONNECT BY LEVEL <= 10000;

Table created.

Elapsed: 00:00:00.24
21:03:29 SQL>

Temos um campo ID que seria a chave da tabela não fosse o fato de termos recorrência da chave com início de vigências diferentes no campo que chamei de DT.
Para cada vigência em questão temos um valor correspondente que pode aumentar ou diminuir a cada nova vigência. O início de uma vigência sempre finaliza a vigência anterior fato que nos permite abrir mão de um campo "FIM_VIGENCIA".
Analisemos o caso do ID 1 como ficou:
21:03:29 SQL> SELECT id, dt, vlr
21:03:29   2  FROM   TST_VIG t
21:03:29   3  WHERE  t.id = 1
21:03:29   4  ORDER  BY t.dt DESC;

        ID DT                VLR
---------- ---------- ----------
         1 31/03/1980      797,5
         1 21/03/1980     137,31
         1 11/03/1980     793,35
         1 01/03/1980     899,46
         1 20/02/1980     870,92
         1 10/02/1980     704,71
         1 31/01/1980     311,92
         1 21/01/1980      269,7
         1 11/01/1980     415,03
         1 01/01/1980     766,38

10 rows selected.

Elapsed: 00:00:00.03
21:03:29 SQL>

Podemos perceber que há variação dos valores conforme o tempo passa. O valor atual para o registro de ID 1 seria, então, 797,50, pois é o valor cuja data de vigência tem o início mais recente.
O processo em questão deveria tirar uma fotografia diária de todos os valores vigentes no dia corrente para realizar integração com uma base auxiliar de aplicações WEB.
Neste caso, deveríamos, para o ID 1, enviar somente o registro:
        ID DT                VLR
---------- ---------- ----------
         1 31/03/1980      797,5

Para recuperarmos a data vigente não tem segredo; uma simples função MAX de agregação resolve. O que já não acontece para o campo de valor, necessariamente.

21:03:29 SQL> SELECT /* errado */
21:03:29   2   id, MAX(dt) dt_vigente, MAX(vlr) vlr_vigente
21:03:29   3  FROM   TST_VIG t
21:03:29   4  WHERE  t.id = 1
21:03:29   5  GROUP  BY ID;

        ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
         1 31/03/1980      899,46

1 row selected.

Elapsed: 00:00:00.02
21:03:29 SQL>

Pois é, isso é fácil de se perceber. Logo de cara nos damos conta que não é uma situação tão trivial assim, pois devemos considerar o preço cujo registro tem a data mais recente.
Eis a solução adotada e que apresentava performance questionável. Vou ativar o trace do SQLPLUS para nos dar alguma ideia do que estaria ocorrendo.

21:03:29 SQL> set autot on;
21:03:29 SQL>
21:03:29 SQL> SELECT id, dt, vlr
21:03:29   2  FROM   TST_VIG t
21:03:29   3  WHERE  t.id = 1
21:03:29   4  AND    dt = (SELECT MAX(dt)
21:03:29   5     FROM   TST_VIG
21:03:29   6     WHERE  t.id = ID);

        ID DT                VLR
---------- ---------- ----------
         1 31/03/1980      797,5

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3436695474

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    39 |    24   (0)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    39 |    24   (0)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     1 |    22 |    12   (0)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     1 |    12 |    12   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TST_VIG |    10 |   120 |    12   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL  | TST_VIG |    10 |   170 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("DT"="MAX(DT)" AND "T"."ID"="ITEM_1")
   4 - filter("ID"=1)
   5 - filter("T"."ID"=1)


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
        678  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

21:03:29 SQL>

A solução atende funcionalmente a necessidade de negócio e neste nosso cenário reduzido não apresenta leituras lógicas comprometedoras.
No cenário real, no entanto, tínhamos cerca de 800 milhões de registros com uma granularidade de aproximadamente 160 milhões de chaves distintas.
Diferentemente do que está representado aqui neste exemplo, tínhamos uma chave primária na tabela e índices que auxiliavam na subquery.
Mas o que me chamou a atenção quando analisei o plano de execução foi o fato de ter que percorrer a tabela inteira mais os acessos dos índices para selecionar os registros corretos.
Este tipo de situação deve ser realmente evitada, pois é exatamente esse tipo de acesso que aumenta as leituras lógicas.
Na minha caixa de ferramentas sempre levo comigo as funções analíticas. Elas são muito úteis em diversos casos e geralmente servem de apoio às estratégias de otimização de performance.
Vasculhando a minha caixa encontrei a função FIRST_VALUE que permitiu otimizar as leituras lógicas.
Vejamos uma abordagem inicial para essa função. Como toda função analítica, na requer a cláusula GROUP BY:

21:03:29 SQL> SELECT id,
21:03:29   2       dt,
21:03:29   3       vlr,
21:03:29   4       FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:03:29   5  FROM   TST_VIG t
21:03:29   6  WHERE  t.id = 1
21:03:29   7  ORDER  BY t.dt DESC;

        ID DT                VLR VLR_VIGENTE
---------- ---------- ---------- -----------
         1 31/03/1980      797,5       797,5
         1 21/03/1980     137,31       797,5
         1 11/03/1980     793,35       797,5
         1 01/03/1980     899,46       797,5
         1 20/02/1980     870,92       797,5
         1 10/02/1980     704,71       797,5
         1 31/01/1980     311,92       797,5
         1 21/01/1980      269,7       797,5
         1 11/01/1980     415,03       797,5
         1 01/01/1980     766,38       797,5

10 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 445364442

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    10 |   170 |    12   (0)| 00:00:01 |
|   1 |  WINDOW SORT       |         |    10 |   170 |    12   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TST_VIG |    10 |   170 |    12   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter("T"."ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        972  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

21:03:29 SQL>

Podemos verificar que o resultset ainda não é o esperado mas temos um bom começo. Já reduzimos o acesso à tabela e já conseguimos destacar o valor vigente.
Só precisamos fazer mais algum ajuste e teremos o resultset desejado:

21:03:29 SQL> SELECT ID, MAX(dt) dt_vigente, vlr_vigente
21:03:29   2  FROM   (SELECT id,
21:03:29   3       dt,
21:03:29   4       FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:03:29   5        FROM   TST_VIG t
21:03:29   6        WHERE  t.id = 1)
21:03:29   7  GROUP  BY ID, vlr_vigente;

        ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
         1 31/03/1980       797,5

1 row selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 4081778672

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |    12   (0)| 00:00:01 |
|   1 |  HASH GROUP BY       |         |     1 |    26 |    12   (0)| 00:00:01 |
|   2 |   VIEW               |         |    10 |   260 |    12   (0)| 00:00:01 |
|   3 |    WINDOW SORT       |         |    10 |   170 |    12   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TST_VIG |    10 |   170 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   4 - filter("T"."ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

21:03:29 SQL>

Pronto. Chegamos ao resultado esperado. Diminuímos LIOs. Diminuímos o custo da execução, mas isso também não é uma análise final pois não estamos com o índice adequado e estamos trabalhando com uma pequena fatia da tabela.
Esta análise é apenas para entendermos outras alternativas utilizando álgebras diferentes para atingir o mesmo resultado.
Essa abordagem ainda não me deixou satisfeito, pois temos no plano mostrado o método WINDOW SORT para aplicar o FIRST_VALUE e depois o HASH GROUP BY para aplicar o MAX.
Fiquei pensando se não existiria a forma agregadora da função FIRST_VALUE. Funções polivalentes são razoavelmente comuns no Oracle Server.
Se houvesse uma forma agregadora desta função, sua sintaxe seria mais ou menos assim:

21:03:29 SQL> SELECT /* pseudocodigo */
21:03:30   2       ID,
21:03:30   3       MAX(dt) dt_vigente,
21:03:30   4       FIRST_VALUE(vlr) WITHIN GROUP(ORDER BY dt DESC) vlr_vigente
21:03:30   5  FROM   TST_VIG t
21:03:30   6  WHERE  t.id = 1
21:03:30   7  GROUP  BY ID;
       FIRST_VALUE(vlr) WITHIN GROUP(ORDER BY dt DESC) vlr_vigente
       *
ERROR at line 4:
ORA-30484: missing window specification for this function


Elapsed: 00:00:00.02
21:03:30 SQL>

Como podemos ver o Oracle não tem esse suporte, então o que fazer?
Bem, eu sou viciado em documentação da Oracle e se tem uma coisa que aprendi rapidamente trabalhando com essa tecnologia é: não subestime o Oracle Server.
Se você precisa de uma solução para um problema, você certamente não foi o primeiro.
Vasculhando um pouco melhor a documentação da Oracle, mais especificamente o SQL Reference, encontrei a função que precisava.
Segue a implementação que utilizei da função FIRST

21:03:30 SQL> SELECT ID,
21:03:30   2       MAX(dt) dt_vigente,
21:03:30   3       MAX(vlr) KEEP(DENSE_RANK FIRST ORDER BY dt DESC) vlr_vigente
21:03:30   4  FROM   TST_VIG t
21:03:30   5  WHERE  t.id = 1
21:03:30   6  GROUP  BY ID;

        ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
         1 31/03/1980       797,5

1 row selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3303131835

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    17 |    12   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    17 |    12   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | TST_VIG |    10 |   170 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("T"."ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         34  consistent gets
          0  physical reads
          0  redo size
        694  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

21:03:30 SQL>

Podemos ver que ela atende a necessidade do negócio, e reduz os métodos  utilizados no plano de execução, além de tornar o código mais elegante (pelo menos na minha opinião)
Se entendermos um pouco mais a fundo a implementação da função, veremos que a sua função antagônica LAST também poderia ser usada.
Para ilustrar fiz uma implementação errada da função FIRST para esse caso, pois retirei a cláusula DESC do GROUP BY:

21:03:30 SQL> SELECT /* errado */
21:03:30   2       ID,
21:03:30   3       MAX(dt) dt_vigente,
21:03:30   4       MAX(vlr) KEEP(DENSE_RANK FIRST ORDER BY dt) vlr_vigente
21:03:30   5  FROM   TST_VIG t
21:03:30   6  WHERE  t.id = 1
21:03:30   7  GROUP  BY ID;

        ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
         1 31/03/1980      766,38

1 row selected.

Elapsed: 00:00:00.02

21:03:30 SQL>

O valor retornado é justamente o valor mais antigo, pois invertemos a ordenação. Neste caso ao invés de usar o FIRST poderíamos utilizar a função LAST:

21:03:30 SQL> SELECT ID,
21:03:30   2       MAX(dt) dt_vigente,
21:03:30   3       MAX(vlr) KEEP(DENSE_RANK LAST ORDER BY dt) vlr_vigente
21:03:30   4  FROM   TST_VIG t
21:03:30   5  WHERE  t.id = 1
21:03:30   6  GROUP  BY ID;

        ID DT_VIGENTE VLR_VIGENTE
---------- ---------- -----------
         1 31/03/1980       797,5

1 row selected.

Elapsed: 00:00:00.01

21:03:30 SQL>

E chegamos ao mesmo resultado com um pouco menos de instruções (eu não me apego muito a esse tipo de coisa, mas acaba virando uma brincadeira interessante).
Agora recriaremos a tabela com um número maior de registros (1 milhão) e a chave primária que faltou. Coletaremos as estatísticas.

21:03:30 SQL> DROP TABLE tst_vig;

Table dropped.

Elapsed: 00:00:00.03
21:03:30 SQL>
21:03:30 SQL> CREATE TABLE tst_vig AS
21:03:30   2  SELECT MOD(ROWNUM - 1, 1000) + 1 ID,
21:03:30   3       DATE '1980-01-01' + FLOOR((ROWNUM - 1) / 1000) * 10 dt,
21:03:30   4       ROUND(dbms_random.value * 1000, 2) vlr
21:03:30   5  FROM   dual
21:03:30   6  CONNECT BY LEVEL <= 1000000;

Table created.

Elapsed: 00:00:18.45
21:03:48 SQL>
21:03:48 SQL> alter table tst_vig add constraint tst_vig_pk primary key(id, dt);

Table altered.

Elapsed: 00:00:04.86
21:03:53 SQL> analyze table tst_vig compute statistics;

Table analyzed.

Elapsed: 00:00:09.55
21:04:03 SQL>

Habilitei o trace com o traceonly, pois não quero inflar meu spool e desta vez não selecionei chave nenhuma. Vai ter que ser full mesmo, ok?

21:04:03 SQL> set autot trace;

Fiz algumas execuções das três implementações várias vezes seguidas para que todos possam se beneficiar do BUFFER CACHE. Prezamos muito pelo fair play neste blog.

21:04:03 SQL> ...
21:04:06 SQL> SELECT id, dt, vlr
21:04:06   2  FROM   TST_VIG t
21:04:06   3  WHERE  dt = (SELECT MAX(dt)
21:04:06   4     FROM   TST_VIG
21:04:06   5     WHERE  t.id = ID);

1000 rows selected.

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3103771927

--------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |  1000 | 36000 |  1696   (3)| 00:00:01 |
|*  1 |  HASH JOIN              |            |  1000 | 36000 |  1696   (3)| 00:00:01 |
|   2 |   VIEW                  | VW_SQ_1    |  1000 | 22000 |   855   (4)| 00:00:01 |
|   3 |    HASH GROUP BY        |            |  1000 | 10000 |   855   (4)| 00:00:01 |
|   4 |     INDEX FAST FULL SCAN| TST_VIG_PK |  1000K|  9765K|   832   (1)| 00:00:01 |
|   5 |   TABLE ACCESS FULL     | TST_VIG    |  1000K|    13M|   838   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - access("DT"="MAX(DT)" AND "T"."ID"="ITEM_1")

Note
-----
   - this is an adaptive plan


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6096  consistent gets
          0  physical reads
          0  redo size
      15262  bytes sent via SQL*Net to client
        588  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

21:04:06 SQL>
21:04:06 SQL> SELECT ID, MAX(dt) dt_vigente, vlr_vigente
21:04:06   2  FROM   (SELECT id,
21:04:06   3       dt,
21:04:06   4       FIRST_VALUE(vlr) OVER(PARTITION BY ID ORDER BY dt DESC) vlr_vigente
21:04:06   5        FROM   TST_VIG t)
21:04:06   6  GROUP  BY ID, vlr_vigente;

1000 rows selected.

Elapsed: 00:00:01.56

Execution Plan
----------------------------------------------------------
Plan hash value: 4081778672

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1000 | 25000 |       |  5797   (1)| 00:00:01 |
|   1 |  HASH GROUP BY       |         |  1000 | 25000 |       |  5797   (1)| 00:00:01 |
|   2 |   VIEW               |         |  1000K|    23M|       |  5774   (1)| 00:00:01 |
|   3 |    WINDOW SORT       |         |  1000K|    13M|    22M|  5774   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| TST_VIG |  1000K|    13M|       |   838   (1)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3021  consistent gets
          0  physical reads
          0  redo size
      15278  bytes sent via SQL*Net to client
        588  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

21:04:08 SQL>
21:04:08 SQL> SELECT ID,
21:04:08   2       MAX(dt) dt_vigente,
21:04:08   3       MAX(vlr) KEEP(DENSE_RANK LAST ORDER BY dt) vlr_vigente
21:04:08   4  FROM   TST_VIG t
21:04:08   5  GROUP  BY ID;

1000 rows selected.

Elapsed: 00:00:00.67

Execution Plan
----------------------------------------------------------
Plan hash value: 1457491003

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |  1000 | 14000 |   862   (4)| 00:00:01 |
|   1 |  SORT GROUP BY     |         |  1000 | 14000 |   862   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TST_VIG |  1000K|    13M|   838   (1)| 00:00:01 |
------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3021  consistent gets
          0  physical reads
          0  redo size
      15278  bytes sent via SQL*Net to client
        588  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

21:04:09 SQL>

Enfim, fica um pouco mais claro a diferença entre as estratégias em termos de diminuição de custo e economia de leituras lógicas.
No caso real, o tempo de processamento caiu de +8h para 70min

Por hoje é só.
Obrigado por ler até o fim.
21:04:11 SQL> DROP TABLE tst_vig;

Table dropped.

Elapsed: 00:00:00.08
21:04:12 SQL> exit