Instrutor

O professor é o Ricardo Portilho Proni que é uma referência técnica no Brasil. Para quem não conhece, ele possui uma grande quantidade de certificações, incluindo Oracle ACE e palestrante em diversos eventos sobre Oracle. Possui uma experiência profissional muito grande. Sou totalmente suspeito em falar sobre o Portilho, pois quem me conhece, sabe que eu sou fã de carteirinha dele desde 2010.

A Turma

Tive a grande alegria de re-encontrar amigos e conhecer novos. Na turma estavam os seguintes alunos:
* Fernando Franquini (DBA Capin do certificacaobd.com.br. Atualmente DBA na Softplan )
* Eduardo Ribeiro ( DBA na UOL Diveo )
* Hugo Torralbo ( DBA no Itaú / Unibanco )
* Itagyba Kuhlmann ( Arquiteto de soluçoes na Pernambucanas )
* Thomas Glufke ( Oracle EBS Senior Developer na Dell Computers )
* Abaixo, o instrutor Ricardo Portilho ( Instrutor e Owner da Nerv Informática )

O curso

São três dias de treinamento, alternando entre teoria, exemplos práticos, laboratório e muita troca de experiência. Como sempre, turmas pequenas.

O assunto Tuning é quase sempre um tabu, pois muita coisa pode afetar o comportamento do Oracle, mesmo em ambientes idênticos. É bem comum o profissional simplesmente “ir testando” algumas coisas pra ver se melhora a performance. É neste momento que o curso faz a diferença!

Como o Portilho mesmo disse, se o curso fosse sobre carros, “eu não vou te ensinar a pilotar, eu vou te ensinar a ser um mecânico!“. Isso é realmente verdade. O curso não é baseado em dicas, ou seja, receitas de bolo: “Se isso, faça aquilo”. É totalmente voltado ao funcionamento do otimizador, plano de execução, funcionamento e comportamento de índices e estatísticas, etc.

É realmente impressionante fazer um TRACE do otimizador e ver o que ele faz pra decidir qual plano de execução vai optar, e todas transformações que uma query sofre. Com os tópicos que estudamos, é possível analisar o motivo da demora e ajustá-las para ganhar performance.

Se você quiser saber mais detalhes de todos assuntos que são abordados, veja o link do curso aqui.

Opinião Pessoal

Já fiz alguns cursos de Tuning, mas até hoje nenhum deles foi tão aprofundado no assunto. Eu diria que o curso é voltado tanto pra DBAs como para desenvolvedores. É ideal se você já conhece algumas coisas sobre o assunto e quer ir mais a fundo.

Se você tiver a oportunidade de ir pra São Paulo e fazer este treinamento, tenho certeza que será um grande investimento e um upgrade na sua carreira.

Tem algumas coisas que estão praticamente enraizadas no mundo Oracle: me refiro a algumas práticas que quase todo mundo faz e que foi adotado como padrão. O texto abaixo não contém nenhuma nova feature do Oracle, nem é nenhuma novidade pra ninguém. Mas por incrível que pareça, eu vejo em quase todas empresas essa prática e isso normalmente gera problemas. Me refiro a utilização de um NVL pra fazer um filtro opcional na query. Abaixo está um exemplo:

SELECT *
FROM tabela
WHERE campo = NVL ( p_parametro1 , campo )

Quase todo programa tem um sistema de filtro como esse acima.
Se o usuário informar o p_parametro1, a query vai mostrar apenas as linhas cujo campo é igual ao p_parametro1.
Se o usuário não informar nada, cai no NVL, e ele faz join com a própria coluna, trazendo tudo.

Aqui está um exemplo real do que foi citado acima.
Primeiro, vamos criar uma BIND variável no SQL*Plus, chamada PARJOB, que será o parâmetro da nossa query:

SQL> var parjob VARCHAR2(100)
SQL> var
variable parjob
datatype VARCHAR2(100)
 
SQL>

Agora, vamos rodar uma query usando o sistema de NVL pra filtrar. Detalhe, o parâmetro está NULO!

SQL> SELECT *
  2  FROM emp
  3  WHERE job = NVL ( :parjob, job)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       17-Nov-1981   5000.00               10
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
14 rows selected
parjob
---------
 
SQL>

Repare que todas as 14 linhas da EMP vieram corretamente.
Caso a gente setar um valor pro parâmetro, ele deve filtrar.

SQL> begin
  2    :parjob := 'MANAGER';
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parjob
---------
MANAGER
 
SQL>

Se executarmos novamente a mesma query, vai funcionar o filtro:

SQL> SELECT *
  2  FROM emp
  3  WHERE job = NVL ( :parjob, job)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
parjob
---------
MANAGER
 
SQL>

Funcionou perfeitamente: Trouxe apenas as linhas MANAGER, conforme o filtro.

Afinal, qual é o problema disso?
Essa prática pode começar a gerar resultados errados, bugs e dor de cabeça sem fim.
Isso porque usar o NVL dessa forma só funciona quando nenhuma linha é NULL. E é nisso que as pessoas erram. Usam essa forma de fazer filtro sempre, sem consultar se o campo poder ter NULLs ou não.

Vamos fazer o mesmo exemplo para o campo MGR, que é um campo que contém nulos. Vamos criar outra variável BIND e vamos usar ela no filtro do gerente. (MGR)

SQL> var parmgr NUMBER;
SQL> var
variable parjob
datatype VARCHAR2(100)
 
variable parmgr
datatype NUMBER
 
SQL>

Pra testar, vamos setar um valor:

SQL> begin 
  2    :parmgr := 7839;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parmgr
---------
7839
 
SQL> 
SQL> 
SQL> SELECT *
  2  FROM emp
  3  WHERE mgr = NVL ( :parmgr, mgr)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
parmgr
---------
7839
 
SQL>

Até aqui funcionou! Trouxe apenas as linhas que o MGR é 7839.
Mas e se deixar nulo o parâmetro? Deve trazer todas linhas, certo?

SQL> begin
  2    :parmgr := NULL;
  3  end;
  4  /
 
PL/SQL procedure successfully completed
parmgr
---------
 
SQL> 
SQL> SELECT *
  2  FROM emp
  3  WHERE mgr = NVL ( :parmgr, mgr)
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
13 rows selected
parmgr
---------
 
SQL>

Só veio 13 linhas? Onde está a linha do KING ? Faltando uma linha! Justamente aquela que está NULA!
Por isso essa prática é péssima. Você só pode usar NVL pra fazer filtro dessa forma quando tiver certeza absoluta que não existem valores NULL na coluna.

SOLUÇÃO PRÁTICA

Em vez de usar NVL, basta usar um simples OR:

SQL> SELECT *
  2  FROM emp
  3  WHERE ( :parmgr IS NULL OR mgr=:parmgr )
  4  /
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       17-Nov-1981   5000.00               10
 7698 BLAKE      MANAGER    7839 01-May-1981   2850.00               40
 7782 CLARK      MANAGER    7839 09-Jun-1981   2450.00               10
 7566 JONES      MANAGER    7839 02-Apr-1981   2975.00               20
 7788 SCOTT      ANALYST    7566 19-Apr-1987   3000.00               20
 7902 FORD       ANALYST    7566 03-Dec-1981   3000.00               20
 7369 SMITH      CLERK      7902 17-Dec-1980    800.00               20
 7499 ALLEN      SALESMAN   7698 20-Feb-1981   1600.00    300.00     30
 7521 WARD       SALESMAN   7698 22-Feb-1981   1250.00    500.00     30
 7654 MARTIN     SALESMAN   7698 28-Sep-1981   1250.00   1400.00     30
 7844 TURNER     SALESMAN   7698 08-Sep-1981   1500.00      0.00     30
 7876 ADAMS      CLERK      7788 23-May-1987   1100.00               20
 7900 JAMES      CLERK      7698 03-Dec-1981    950.00               30
 7934 MILLER     CLERK      7782 23-Jan-1982   1300.00               20
 
14 rows selected
parmgr
---------
 
SQL>

Dessa forma você não precisa se preocupar se existe NULL ou não no campo. Uma preocupação a menos.

Obviamente, não estamos fazendo testes relacionados ao plano de execução, visto que o resultado das duas queries é diferente: Uma não traz NULLS e outra traz. O uso do NVL certamente é mais performático quando usando num campo com índice, pois ele não precisa percorrer a tabela buscando os NULLS e utiliza o índice). Como são resultados diferentes, não temos porque comparar performance.

Caso você queria saber mais sobre campos NULL, comparação de NULLs, aqui tem alguns textos e experimentos realizados pela comunidade:
Comparação de campos com null
Null é igual a branco ?
Interessante – Campo NULL
Igualdade Desigual ??? alguém sabe porque NULL não é igual a NULL?

Este é mais um daqueles casos que temos que chamar o Padre Quevedo pra dizer: “Este fenômeno nôm exziste“. Abaixo, vamos fazer uma análise de um programa com erro e como foi solucionado o erro.

O PROBLEMA

O relatório funcionava perfeitamente. Mas raramente, ou seja, 1% das vezes em que era executado, o relatório saía com os valores de todos os campos duplicados! Exemplo:  Deveria sair o valor R$ 20,00  mas saía R$ 40,00. Isso ocorria em todas colunas!

FUNCIONAMENTO DO RELATÓRIO

O usuário informa os parâmetros em uma sessão do Oracle Forms. (Concorrente do Oracle EBS)
O relatório é gerado por uma procedure em uma outra sessão, e insere as linhas em uma tabela comum do Oracle.
Nesta tabela existe um campo chamado SESSAO onde é gravado o SESSION_ID que é passado via parâmetro. (A mesma sessão do Oracle Forms). Dessa forma, vários usuários podem executar o relatório ao mesmo tempo que o programa sempre filtra pela sessão, assim cada um só vê o que é seu, pois cada usuário está numa sessão diferente do Forms.

Ou seja, após disparar o relatório, a procedure é executada em background (é disparado um concorrente do Oracle EBS, que é basicamente, uma sessão do banco executando o programa).

O programa funcionava basicamente em 4 etapas:

1. A procedure limpa a tabela onde os dados serão inseridos e dá um COMMIT. Basicamente, roda um

DELETE FROM tabela WHERE SESSAO=p_sessao;
COMMIT;

2. Procedure roda diversos cursores inserindo nessa tabela. (cada cursor demora +ou- um minuto).
É gravado nessa tabela o número da sessão corrente do Oracle passada via parâmetro.
3. COMMIT;
4. É disparado uma outra sessão (XML Publisher) que monta o relatório com base nos valores inseridos.
Novamente, é passado o número SESSION_ID via parâmetro e o relatório mostra apenas o que é da sessão do usuário que informou os parâmetros.

O que ocorria para dar as linhas duplicadas

O erro ocorria quando o mesmo usuário executava duas vezes o relatório em um curto espaço de tempo.
Dessa forma, o seguinte “fenômeno” era realizado:

É interessante notar que caso o usuário rodasse o relatório em um periodo de tempo um pouco maior, ou seja, após o segundo COMMIT da primeira execução, o erro não aconteceria, pois o DELETE da segunda execução ia conseguir ver os dados inseridos na primeira execução e limpá-los corretamente.

Correção do problema

Após descoberto o que ocorria realmente, foi muito fácil solucionar o problema, pois em vez de se usar o número da sessão apenas, foi também concatenado a esse número outro número que não iria se repetir. Dessa forma, mesmo que o usuário executasse duas vezes o relatório, o número da sessão seria único e o problema não ocorreria.

Este é um exemplo real de bug encontrado em programa, que foi difícil achar o problema devido a raridade que o erro ocorria.

O Bug ocorre na seguinte situação:
* O programa X grava dados de data na tabela usando o seguinte formato de datas:  YYYYMMDD.
* O programa Y lê dessa tabela usando o formato DDMMYYYY. (Sim, é um erro, um bug no programa).

Até o ano de 2012, este erro não aparecia, veja um exemplo:
* O programa X salva a data 23–abr–2011 = 20110423 (YYYYMMDD)
* Quando essa data era lida pelo programa Y, ele lia  20110423 (DDMMYYYY) = 20–nov–0423. Data errada, mas não acusava erro!

Pois o ano de 2013 chegou e esse erro apareceu. Veja:
* O programa X salva a data 23–abr–2013 = 20130423 (YYYYMMDD)
* Quando essa data era lida pelo programa Y, ele lia  20130423 (DDMMYYYY) = 20-???-0423. Como não existe mês 13, dá erro!

O interessante é que esse bug está aparecendo só agora, depois de muitos anos de erro oculto. Finalmente em 2013 ele está fazendo os programas pararem de rodar.

Exemplo/Situação compartilhada por Rafael Lopes Lima (Programador Oracle EBS)

Aqui na empresa, prestamos serviço pra vários clientes. Um particularmente, exige que o trabalho seja feito no próprio notebook que eles fornecem. Nesse caso, eu tenho na minha mesa um PC (na esquerda) e um notebook (na direita), este, exclusivamente para uso de um cliente.

O problema é que isso gera uma série de incômodos, por exemplo:

• Quando conecto na VPN usando o notebook, acabo perdendo a conexão com a internet. Caso eu precise copiar algum script (aqui do site, por exemplo) tenho que desconectar da VPN ou copiar pra um pen-drive, etc.
• Outro problema é o espaço na mesa. É um pouco desconfortavel usar 2 teclados, 2 mouses. Tem que girar os braços pra um lado ou pra outro (fail), e quase sempre eu me confundo e acabo usando o mouse errado (double fail).

A solução pra tudo isso: Um programa chamado SYNERGY, que é open-source, free, etc.

O Synergy possibilita que um teclado e um mouse possa ser usados em 2 ou mais computadores!

Como funciona

Você instala o Synergy em todos computadores que você quer usar o teclado. No meu caso:

No PC, eu instalei e ativei ele como SERVER.
No notebook, eu instalei e ativei ele como CLIENTE, neste caso, eu precisei colocar o IP do meu PC – que é onde está o server.

Benefícios

• Basta ir com o mouse pra direita da tela, que o mouse “entra” no outro computador, como se fosse um único PC com tela estendida! Ou seja, posso usar agora o mesmo mouse e teclado pra ambos!
• É possível copiar um texto no PC e colar o texto no notebook! Então, mesmo com a VPN ligada que eu perdia o acesso a internet, eu consigo copiar do email, de sites e colar lá.

Neste caso, o Synergy se mostrou extremamente eficiente, e por isso achei que valia a pena compartilhar.

Primeiro, vamos criar a tabela de teste:

CREATE TABLE glufke_teste
(nro_nota  NUMBER, item NUMBER, valor NUMBER);
INSERT INTO glufke_teste VALUES ( 50, 1, 1.45);
INSERT INTO glufke_teste VALUES ( 50, 2, 3.91);
INSERT INTO glufke_teste VALUES ( 50, 3, 5.04);

SQL> SELECT * FROM glufke_teste;

  NRO_NOTA       ITEM      VALOR
---------- ---------- ----------
        50          1       1,45
        50          2       3,91
        50          3       5,04

SQL>

Nosso objetivo é ratear um valor proporcionalmente nessas linhas. Como exemplo prático, vamos distribuir o valor R$ 49,30!

Sabemos que a soma das 3 linhas é 10,40:

SQL> SELECT SUM(VALOR) FROM GLUFKE_TESTE;

SUM(VALOR)
----------
      10,4

SQL>

Então, através de uma simples regra de 3, vemos o percentual de cada linha! Ou seja, temos que aplicar o valor de 49,30 conforme esses percentuais!

SQL> SELECT VALOR, VALOR/10.4*100 PERC FROM GLUFKE_TESTE;

     VALOR       PERC
---------- ----------
      1,45 13,9423076
      3,91 37,5961538
      5,04 48,4615384

SQL>

Com isso, basta colocar o valor 49.30 ali que temos distribuído! Repare que a soma do valor rateado é exatamente os 49,30 ! Ou seja, está distribuído conforme nossa necessidade!

SQL> select valor, valor/10.4* 49.3 vlr_rateado from glufke_teste;

     VALOR VLR_RATEADO
---------- -----------
      1,45 6,873557692
      3,91 18,53490384
      5,04 23,89153846

SQL>

Usando funções analíticas pra obter esse resultado!

Agora, usando a função analitica SUM, pra buscar “em tempo de execução” a soma da coluna valor (abaixo, a coluna TOTAL), e fazer o cálculo todo numa única tacada!

SELECT
  nro_nota
, item
, valor
, sum(valor) over (order by nro_nota) total
, valor / sum(valor) over (order by nro_nota) * 49.3 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 50
/

  NRO_NOTA       ITEM      VALOR      TOTAL VLR_RATEADO
---------- ---------- ---------- ---------- -----------
        50          1       1,45       10,4 6,873557692
        50          2       3,91       10,4 18,53490384
        50          3       5,04       10,4 23,89153846

SQL>

Vamos adicionar mais um campo chamado VLR_ARR, que arredonda pra 2 casas decimais o valor rateado:

SELECT
  nro_nota
, item
, valor
, sum(valor) over (order by nro_nota) total
, valor / sum(valor) over (order by nro_nota) * 50 vlr_rateado
, ROUND(   valor / sum(valor) over (order by nro_nota) * 49.3 ,2 ) vlr_arr
FROM glufke_teste
WHERE nro_nota = 50
/

  NRO_NOTA       ITEM      VALOR      TOTAL VLR_RATEADO VLR_ARR
---------- ---------- ---------- ---------- ----------- -----------
        50          1       1,45       10,4 6,971153846        6,87
        50          2       3,91       10,4 18,79807692       18,53
        50          3       5,04       10,4 24,23076923       23,89

SQL>

PROBLEMA A VISTA!
Quando fazemos rateios e arredondamos o valor, a chance de dar uma diferença de 1 centavo ou mais é muito grande! Se somarmos o valor rateado não fecha com os 49.30!

Veja: 6.87 + 18.53 + 23.89 = 49.29 !!! (diferença de 1 centavo!)

Resolvendo problema do centavo

Vamos usar funções analíticas pra jogar essa diferença de 1 centavo para uma das linhas da nota! (no caso, vamos jogar para a linha de maior valor).

Primeiro, vamos descobrir se realmente existe uma diferença de 1 centavo ou mais:

SELECT
  VLR_RATEADO
, ROUND(VLR_RATEADO,2)                        VLR_ARR
, COUNT(1) OVER (ORDER BY 1)                  QTD_LIN
, ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO) LIN
, SUM(VLR_RATEADO) OVER (ORDER BY 1)          SOMA
, SUM(ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1) SOMA_ARR
FROM
(
 SELECT
   nro_nota
 , item
 , valor
 , valor / SUM(valor) OVER (ORDER BY nro_nota) * 49.3 vlr_rateado
 FROM glufke_teste
 WHERE nro_nota = 50
 )
/

VLR_RATEADO    VLR_ARR    QTD_LIN        LIN       SOMA   SOMA_ARR
----------- ---------- ---------- ---------- ---------- ----------
6,873557692       6,87          3          1       49,3      49,29
18,53490384      18,53          3          2       49,3      49,29
23,89153846      23,89          3          3       49,3      49,29

SQL>

Acima, as colunas QTD_LIN e LIN vão nos ser úteis pra aplicar a diferença apenas na última linha, ou seja, quando QTD_LIN = LIN (no caso 3=3).
A coluna SOMA, contém o valor total.
A coluna SOMA_ARR é a soma do valor arredondado! Repare que tem 1 centavo a menos!

Então, basta fazer um DECODE que aplica essa diferença (49.3 – 49.2 –> SOMA – SOMA_ARR )
Veja:

SELECT
  VLR_RATEADO
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (ORDER BY 1)
       , ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (ORDER BY 1) - SUM( ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1)+ ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (ORDER BY nro_nota) * 49.3 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 50
)
/

VLR_RATEADO VLR_RATEADO_ARR VLR_COM_DIF
----------- --------------- -----------
6,873557692            6,87        6,87
18,53490384           18,53       18,53
23,89153846           23,89       23,90

SQL>

O select acima, aplica a diferença de 1 centavo apenas na ultima linha. Dessa forma, a diferença de centavo vai pra uma das linhas e o total rateado sempre fecha corretamente!
A soma do VLR_RATEADO está errada! ( 6,87 + 18,53 + 23,89 = 49,29)
Já o campo VLR_COM_DIF está com a soma correta, fechando os 49,30 !

Dessa forma, com apenas 1 select foi possível realizar o rateio e ainda aplicar a diferença do arredondamento em uma das linhas, graças as funções analíticas!

Outro Exemplo:

INSERT INTO glufke_teste VALUES ( 51, 1, 5);
INSERT INTO glufke_teste VALUES ( 51, 2, 5);
INSERT INTO glufke_teste VALUES ( 51, 3, 5);
INSERT INTO glufke_teste VALUES ( 51, 4, 7.5);
INSERT INTO glufke_teste VALUES ( 51, 5, 7.5);

Vamos aplicar o valor 10 nesses valores:

SELECT
  NRO_NOTA
, ITEM
, VALOR
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (ORDER BY 1)
       , ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (ORDER BY 1)
       - SUM( ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1)
       + ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (ORDER BY nro_nota) * 10 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 51
)

  NRO_NOTA       ITEM      VALOR VLR_RATEADO_ARR VLR_COM_DIF
---------- ---------- ---------- --------------- -----------
        51          1          5            1,67        1,67
        51          2          5            1,67        1,67
        51          3          5            1,67        1,67
        51          4        7,5            2,50        2,50
        51          5        7,5            2,50        2,49     -- < descontou 1 centavo aqui!

SQL>

Para terminar a saga, vamos adicionar a palavra-chave PARTITION BY, e dessa forma, podemos fazer o calculo acima para todas as notas! No exemplo abaixo, vamos ratear o valor 10 pra cada nota. Ou seja, no total, a soma vai dar 20, pois temos apenas 2 notas no exemplo:

SELECT
  NRO_NOTA
, ITEM
, VALOR
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (partition by nro_nota  ORDER BY 1)
       , ROW_NUMBER()    OVER (partition by nro_nota ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (partition by nro_nota ORDER BY 1)
       - SUM( ROUND(VLR_RATEADO,2)) OVER (partition by nro_nota ORDER BY 1)
       + ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (partition by nro_nota ORDER BY nro_nota) * 10 vlr_rateado
FROM GLUFKE_teste
)

  NRO_NOTA       ITEM      VALOR VLR_RATEADO_ARR VLR_COM_DIF
---------- ---------- ---------- --------------- -----------
        50          1       1,45            1,39        1,39
        50          2       3,91            3,76        3,76
        50          3       5,04            4,85        4,85
        51          1       5,00            1,67        1,67
        51          3       5,00            1,67        1,67
        51          2       5,00            1,67        1,67
        51          5       7,50            2,50        2,50
        51          4       7,50            2,50        2,49

8 rows selected

SQL>

Observe que a soma da coluna VLR_COM_DIF fecha exatamente os 20. Ja a coluna VLR_RATEADO_ARR dá diferença de centavo!

Neste LINK tem diversos exemplos muito bons! Até mais!

Nestes casos, podemos usar a ALL_SOURCE, que contém os códigos de todos programas de banco. Normalmente um select com LIKE resolve o problema:

SELECT * FROM all_source
WHERE UPPER(text) LIKE '%INSERT%TABELA%'

O problema é que nem sempre o código está no padrão, por exemplo, o nome da tabela pode estar no lado ou na próxima linha!

INSERT INTO tabelax...

INSERT 
INTO tabelax...

O mesmo vale pra UPDATES! Ou seja, se o comando INSERT estiver na linha anterior, essa consulta simples não vai retornar o que procuramos!

UPDATE tabelax SET...

UPDATE 
tabelax
SET...

Para resolver o problema, eu fiz esse SQL que busca no banco todas linhas que contém a tabelax E a linha anterior! Com isso você pode encontrar os INSERTS e UPDATES que estão na linha antes também!

SELECT *
FROM (
      SELECT a.owner, a.name, a.line, a.text
      FROM all_source a
      , (
          SELECT owner, name, line, type
          FROM all_source
          WHERE UPPER(text) LIKE '%%ENG_REVISED_ITEMS%'    ----> TABELA A PROCURAR !!!!
            AND owner='APPS'
            AND type <>'PACKAGE'         
         )   B         
      WHERE a.owner = b.owner
        AND a.name  = b.name
        AND a.type  = b.type
        AND (a.line  = b.line or a.line  = b.line-1)
      )
WHERE UPPER(text) LIKE '%INSERT%'                          ----> COMANDO QUE PODE ESTAR NA LINHA ANTERIOR !!!!
ORDER BY 1,2,3,4;

1 – Baixar e instalar Oracle XE

Download nesse link: http://www.oracle.com/technetwork/database/express-edition/overview/index.html

2 – Baixar Oracle Apex 4.1

Download nesse link: http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html?ssSourceSiteId=ocombr

Descompactar arquivos no C:\oraclexe\apex

CMD> cd c:\oraclexe\apex
CMD> sqlplus sys@xe as sysdba
SQL> @apexins SYSAUX SYSAUX TEMP /i/
CMD> sqlplus sys@xe as sysdba
SQL> @apxldimg.sql c:\oraclexe
SQL> @apxxepwd.sql <password>
SQL> exec dbms_xdb.setListenerLocalAccess (l_access => FALSE);
SQL> exit
CMD> cd C:\oraclexe\apex\builder\pt-br
CMD> set NLS_LANG=American_America.AL32UTF8
CMD> sqlplus sys@xe as sysdba
SQL> ALTER SESSION SET CURRENT_SCHEMA = APEX_040100;
SQL> @load_pt-br.sql
SQL> exit
CMD> exit

3 – Como acessar

Acesse o Apex pelo navegador: http://127.0.0.1:8080/apex
Workspace: Internal
Username: Admin
Password: <password>
Agora é só criar sua Workspace e começar a trabalhar..

Texto criado por Alex Pagliarini
Comentários aqui

Aprender Oracle

Achei muito bom o evento, muito bem organizado, ótimos palestrantes e congressistas. Valeu cada minuto que estava lá. Parabéns a todos e ano que vem, pretendo ir de novo!!!

http://aprenderoracle.com/2011/07/review-do-evento-guob-2011/

PHPDBA

Um evento ótimo, mas sem excelência, seria como ganhar um iPad de presente numa caixa de papelão. Que se dane a caixa de papelão, eu ganhei um iPad!

O GUOB Tech Day é como ganhar um iPad das mãos do Steve Jobs, tirando uma foto com ele entregando o produto embalado, na caixa original, com uma dedicatória e ainda ganhar um bônus infinito de créditos na AppleStore!

Eu saio do GUOB com um espírito renovado. Cheio de idéias, cheio de intenções, cheio de vontade de crescer na área, de ser um profissional mais completo, de ajudar a comunidade. Sinto-me muito bem por participar de uma comunidade de DBAs quando estou ali, no meio daquela galera. (Paulo Henrique)

http://phpdba.wordpress.com/2011/07/22/guob-tech-day-2011-attendant-feedback/

iMasters

O conjunto de palestrantes nacionais e internacionais foi bem escolhido e a organização, instalações, local e demais detalhes foram ótimos. Recomendo a todos que estiverem interessados no mundo de tecnologias Oracle que se filiem gratuitamente ao GUOB e que participem da comunidade para tornar eventos como este cada vez melhores.

http://www.imasters.com.br/artigo/21478/outros/cobertura-do-guob-tech-day-2011

Eduardo Legatti

Quem não foi, realmente perdeu uma grande oportunidade de ver excelentes palestras e prestigiar grandes profissionais do mundo Oracle. Foi sensacional.

http://eduardolegatti.blogspot.com/2011/07/minha-experiencia-no-guob-tech-day-2011.html

CAPIN

Eu Fui! E irei em outros.
http://franquini.wordpress.com/2011/07/17/guob-tech-day-2011/

FOTOS OFICIAIS DO GUOB

http://www.guob.com.br/index.php?page=album-de-fotos

COBERTURA PELA OTN LA

Abaixo, deixo os links da cobertura feita pela OTN:

FOTOS:
Brasil – Oracle Technology Network America Latina Tour 2011

VIDEOS:
Rodrigo Almeida
Eduardo Hahn
Marcus Vinicius
Rodrigo Mufalani
Thomas Glufke
David Siquiera
Marina Neumann

Se você tem um review do GUOB Tech Day, envie um email para thomas arroba glufke.net com o link do seu review que atualizaremos essa página!

Este recurso permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do “result set” (conjunto de dados que são retornados pela função).

Este recurso é ótimo para ETL (Extract, Transform, and Load), pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.

Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR.

Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.

1- Criando a package HR.PKG_TYPES

A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:

create or replace package HR.PKG_TYPES as
  TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
  TYPE ROWEMPTYPE IS RECORD(
          EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
          FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
          LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
          EMAIL          EMPLOYEES.EMAIL%TYPE,
          PHONE          EMPLOYEES.PHONE_NUMBER%TYPE,
          HIRE_DATE      EMPLOYEES.HIRE_DATE%TYPE,
          JOB_ID         EMPLOYEES.JOB_ID%TYPE,
          SALARY         EMPLOYEES.SALARY%TYPE,
          COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
          MANAGER_ID     EMPLOYEES.MANAGER_ID%TYPE,
          DEPARTMENT_ID  EMPLOYEES.DEPARTMENT_ID%TYPE
         );
END;
/

2- Criando a função HR.FC_OBTER_EMPREGADOS

A função HR.FC_OBTER_EMPREGADOS lê e retorna os dados de apenas 4 colunas da tabela HR.EMPLOYEES, transformando os dados das colunas LAST_NAME e EMAIL:

CREATE OR REPLACE function HR.FC_OBTER_EMPREGADOS
  return PKG_TYPES.TABLEEMPTYPE
  PIPELINED IS
  var_linha PKG_TYPES.ROWEMPTYPE;
BEGIN
  FOR CUR_ROW IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL
                    FROM HR.EMPLOYEES) LOOP
    var_linha.EMPLOYEE_ID := CUR_ROW.EMPLOYEE_ID;
    var_linha.FIRST_NAME  := CUR_ROW.FIRST_NAME;
    var_linha.LAST_NAME   := UPPER(CUR_ROW.LAST_NAME);
    var_linha.EMAIL       := UPPER(cur_row.EMAIL || '@ORACLE.COM');

    PIPE ROW(VAR_LINHA);
  END LOOP;

  RETURN;
END;
/

Obs.: A instrução PIPE ROW retorna os resultados para a sessão de usuário Oracle, linha por linha. Isso otimiza o tempo de resposta da aplicação.

3- Testando a função HR.FC_OBTER_EMPREGADOS

A query abaixo retorna os dados da função HR.FC_OBTER_EMPREGADOS, como se fossem uma tabela virtual:

SELECT * FROM TABLE(HR.FC_OBTER_EMPREGADOS);
/

Observações:

O exemplo deste artigo foi criado apenas para demonstrar o uso de Pipelined Table Functions. Para mais informações e exemplos deste tipo de função, consulte as referências no final deste artigo.

Comentários Finais:

Apesar das Pipelined Table Functions serem trabalhosas para criar, elas são ótimas para otimizar performance de queries complexas que utilizam funções de transformação para alterar os valores originais das consultas.

Na empresa em que trabalho temos um caso de uma query que demorava 54 segundos para gerar os dados da folha de ponto mensal de cada empregado. Essa query estava literalmente “parando” o servidor de Banco de Dados. Eu orientei o desenvolvedor da query a alterá-la utilizando a Cláusula WITH (ver artigo “Cláusula WITH (para tunar queries)” que eu postei neste blog em 01/10/2010) + Pipelined Table Function. Após as alterações o tempo de execução da query caiu para 0.4 segundos.

Referências:

– http://www.praetoriate.com/10g_139.htm
– http://oraclelon1.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfnsref.htm
– http://www.codeguru.com/cpp/data/mfc_database/oracle/article.php/c4285/
– http://www.databasejournal.com/features/oracle/article.php/2222781
– http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm

Fonte: http://www.fabioprado.net/2010/12/pipelined-table-functions-funcao-para.html

Fábio Prado – http://www.fabioprado.net/
Oracle Certified (OCA)
Microsoft Certified (MCT + MCPD + MCAD + MCSD + MCDBA)