quarta-feira, 15 de agosto de 2012

Optimizer Extensible Interface

Hoje convido a todos a ler o meu mais novo artigo publicado na Oracle Technology Network:

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

Este artigo trata de um recurso avançado do Oracle para adicionar estatísticas a funções pipelined, que sem isso seriam apenas estimadas gerando planos nem sempre ótimos. Eu entendo que esta é uma feature muito interessante e pouco conhecida, e com alguns tweaks pode ser adaptada para obter estatísticas de qualquer objeto/coleção tratado com o operador TABLE.

Para maiores informações, recomendo a leitura do artigo do Adrian Billington abaixo:

setting cardinality for pipelined and table functions

E para quem quiser conhecer mais artigos técnicos publicados pela OTN, acessem o link abaixo:

http://www.oracle.com/technetwork/pt/articles/index.html

quarta-feira, 18 de julho de 2012

Despivotando

Recentemente eu comecei a participar nos forums da Oracle Technology Network para ajudar a comunidade (e também ser ajudado). Há pouco tempo atrás respondi uma pergunta interessante que gostaria de compartilhar com vocês, pois mostra o uso de uma nova feature do Oracle 11g: a clausula UNPIVOT do SELECT.

O objetivo da pergunta era, dado uma tabela com N colunas, mostrar através de uma query quais colunas tem valores nulos e em quais linhas. A principio uma solução simples porém trabalhosa, como apontado pelo OP, seria fazer uma query com diversas clausulas OR testando cada coluna isoladamente por NULL. Porém isso ainda envolveria um trabalho adicional para analisar o result set e identificar quais são as colunas nulas, possivelmente envolvendo uma infinidade de CASEs, ou ainda, substituindo o OR por UNION ALL e criando uma query monstruosa.

Pois bem, é aí que entra a mágica do UNPIVOT. Em uma única clausula podemos transformar todas as colunas separadas numa única coluna e varrer apenas esta coluna pelos valores nulos. Infelizmente não tenho o Oracle 11g disponível aqui para postar o resultado, mas encorajo vocês a executarem o código abaixo:

select * from (select rownum row_num, case when dbms_random.value < 0.5 then null else dbms_random.value end a, case when dbms_random.value < 0.5 then null else dbms_random.value end b, case when dbms_random.value < 0.5 then null else dbms_random.value end c, case when dbms_random.value < 0.5 then null else dbms_random.value end d, case when dbms_random.value < 0.5 then null else dbms_random.value end e from dual connect by level <= 10) unpivot include nulls (my_value for my_column in(a, b, c, d, e)) where my_value is null;

Para quem quiser acessar a pergunta original, basta seguir o link:

https://forums.oracle.com/forums/message.jspa?messageID=10434991

segunda-feira, 16 de julho de 2012

Publicações na Oracle Technology Network

Hoje tenho o prazer de divulgar a vocês o resultado positivo do meu último trabalho: a publicação de dois artigos meus na Oracle Technology Network. Estes artigos tratam basicamente da relação das trocas de contexto com a performance de procedimentos e os mecanismos para otimizá-las. Para quem quiser conferir, basta seguir os links abaixo:



Além disto, para quem tem vocação para a escrita e gostaria de compartilhar seu conhecimento nas tecnologias da Oracle, gostaria de fazer o convite para enviarem artigos para a OTN. Os detalhes estão no link abaixo:


Para finalizar, agradeço o apoio que me foi dado pela publicação destes artigos e fiquem ligados que em breve devo apresentar novos tópicos tanto neste blog como na OTN.

domingo, 24 de junho de 2012

Habilitando as setas no sqlplus para Linux

Hoje vou postar um truque rápido que me ajudou muito nos últimos dias. Quem já utilizou o sqlplus em Windows e depois migrou para Linux já deve ter percebido uma limitação irritante da versão para Linux que é a falta da usabilidade das setas do teclado, tanto para navegar na linha atual (setas direita e esquerda) como para buscar um comando no histórico (setas para cima e para baixo).

Pois bem, hoje vou apresentar um simples utilitário que "devolve" essa capacidade para o sqlplus. Ele é chamado de rlwrap.

Como estou usando uma variante do Ubuntu (o Lubuntu para ser mais exato), vou mostrar como instalá-lo utilizando o apt-get. Porém acredito que outras distribuições também devem ter pacotes disponíveis para ele (ex.: usando o yum no RHEL). Vamos a sua instalação:


paulo@sayuri:~$ sudo apt-get install rlwrap
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following NEW packages will be installed:
  rlwrap
<...>


Uma vez concluída a instalação vamos modificar o comando sqlplus para ser chamado pelo rlwrap:


paulo@sayuri:~$ alias sqlplus='rlwrap sqlplus'

Pronto! Agora basta invocar o sqlplus normalmente pela linha de comando e as setas estarão disponíveis.

quinta-feira, 1 de março de 2012

Caso de Performance 3: Bind variables

Olá pessoal. Hoje vou continuar com a demonstração do efeito de usar (e não usar) bind variables no Oracle. Começo ressaltando que embora eu esteja usando um exemplo em PL/SQL pela praticidade, isto é válido para qualquer linguagem que se comunica com o banco, incluindo java, C++ e outras. De modo geral, vou provar para vocês que montar uma query com valores concatenados sempre é uma má idéia, pois força o hard parsing aumentando intensamente o consumo de recursos pelo banco e simplesmente destrói a shared pool no curto prazo.

O código de exemplo é bastante simples: vamos montar uma tabela T com duas colunas, X e Y, ambas numéricas e com valores de 1 a 1000. Em seguida, vamos fazer um bloco anônimo em PL/SQL que lê esta tabela passando o valor de X e retornando Y para uma variável em memória. Faremos este bloco repetir este processamento 100 vezes (portanto, executamos 100.000 vezes a query de recuperação de valor) e tomamos o tempo gasto nesta operação.

O bloco anônimo primeiramente vai ser implementado usando a concatenação de valores e, num segundo momento, vai ser reescrito para utilizar bind variables. A nossa prova será a diferença de tempo entre as execuções e também as estatisticas de parsing da sessão.

Vamos passo a passo:

/tmp$ sqlplus paulo/paulo

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 1 01:02:01 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
 
SQL> drop table t;
 
Table dropped.
 
SQL> create table t(x number, y number);

Table created.
 
SQL> insert into t
  2  select rownum, rownum from dual connect by level <= 1000;
 
1000 rows created.
 
SQL> commit;

Commit complete.

Ok, com a tabela criada vamos primeiro tomar a medida da quantidade de parses executadas até o momento para termos como base. Isto é feito com a query abaixo:

SQL> select sn.name, vm.value
  2    from v$mystat   vm,
  3          v$statname sn
  4   where vm.statistic# = sn.statistic#
  5      and sn.name like '%parse%';

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse time cpu                                 16
parse time elapsed                             19
parse count (total)                           445
parse count (hard)                             95
parse count (failures)                          0


Esta query resume as principais estatísticas da sessão com relação ao parse. Estamos mais interessados, no entanto, no parse count (total) e no parse count (hard). Tenha em mente o seguinte: o parse count (total) é a soma de todos os parses executados, sejam eles hard ou soft. A diferença entre um parse hard e um soft é que no hard parse o banco precisa validar a query e estabelecer o plano de execução, um processo bastante demorado e consumidor de recursos. Já no soft parse, o banco apenas verifica se a query já existe na shared pool e reaproveita o plano existente, economizando aí um tempo bastante significativo. Pelas estatisticas acima, para saber a quantidade de soft parses basta subtrair a quantidade de hard parses do total.

Vamos então executar o bloco anônimo com o código que utiliza concatenação de valores para gerar as diversas querys.

SQL> declare
  2    v_num number;
  3    t0    number := dbms_utility.get_time;
  4 
  5  begin
  6    for r1 in (select rownum from dual connect by level <= 100)
  7    loop
  8       for r2 in (select rownum from dual connect by level <= 1000)
  9       loop
 10         execute immediate 'select y from t where x = ' || r2.rownum into v_num;
 11       end loop;
 12    end loop;
 13    dbms_output.put_line('hsecs=' || (dbms_utility.get_time - t0));
 14  end;
 15  /
hsecs=11859

PL/SQL procedure successfully completed.


Como vocês podem ver, esta operação tomou um tempo significativo do banco: 11859 centésimos de segundo, ou 118.59 segundos. Acompanhe a estatistica de parses:

SQL> select sn.name, vm.value
  2    from v$mystat   vm,
  3          v$statname sn
  4   where vm.statistic# = sn.statistic#
  5      and sn.name like '%parse%';

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse time cpu                                  10881
parse time elapsed                              10980
parse count (total)                            200494
parse count (hard)                             100113
parse count (failures)                              0

Agora, temos duas coisas interessantes para observar: primeiro, a contagem de hard parses foi lá em cima, como esperado, mas por que ainda sim tivemos quase 50% de soft parses? A resposta está no fato de que sempre existem querys recursivas, ou internas do banco, que são executadas quando enviamos qualquer comando para o banco, e certamente elas foram escritas para serem reaproveitadas. Isso explica também porque a quantidade de hard parses foi um pouco acima do esperado (100.000). Enfim, vamos ao segundo teste:

SQL> declare
  2    v_num number;
  3    t0    number := dbms_utility.get_time;
  4 
  5  begin
  6    for r1 in (select rownum from dual connect by level <= 100)
  7    loop
  8       for r2 in (select rownum from dual connect by level <= 1000)
  9       loop
 10         execute immediate 'select y from t where x = :1' into v_num using r2.rownum;
 11       end loop;
 12    end loop;
 13    dbms_output.put_line('hsecs=' || (dbms_utility.get_time - t0));
 14  end;
 15  /
hsecs=798

PL/SQL procedure successfully completed.

Desta vez fizemos do jeito certo: com bind variables. E o resultado é surpreendente: de 118.59 segundos agora a mesma query executou em apenas 7.98 segundos! Vejamos como ficou a estatística da sessão:

SQL> select sn.name, vm.value
  2    from v$mystat   vm,
  3          v$statname sn
  4   where vm.statistic# = sn.statistic#
  5      and sn.name like '%parse%';

NAME                                      VALUE
---------------------------------------------------------------- ----------
parse time cpu                                  10883
parse time elapsed                              10983
parse count (total)                            200537
parse count (hard)                             100127
parse count (failures)                              0

Lembre-se que estamos na mesma sessão do bloco anterior, ou seja, 200.537 - 200.494 = 43 parses a mais, sendo que destes apenas 14 são do tipo hard! Isso quer dizer que a nossa query com bind variables foi 100% reaproveitada em todas as passadas. Acredito que ficou nítida a diferença de performance que isso proporciona.

Para encerrar, deixo uma pergunta no ar: era esperado que todas as querys recursivas e as querys dos cursores r1 e r2 fossem reaproveitadas já que estamos na mesma sessão, e portanto seriam feitos apenas soft parses das mesmas. Agora, se você parar para pensar, a query do cursor r2 é executada 100 vezes mas temos muito menos de 100 soft parses. Qual seria a explicação para isso? Infelizmente eu não tenho a resposta, mas se alguém tiver fique a vontade para postar nos comentários.

E por hoje é só pessoal! :)