Alessandro Guimarães – Oracle Blog

June 16, 2011

Extended Statistics – Multicolumn Statistics

Filed under: banco de dados — Tags: , , , — agleite @ 3:05 pm

Extendend Statistics é uma feature do 11g que ajuda o otimizador baseado em custo (CBO) tomar melhores decisões utilizando estatisticas  em grupo de colunas, quando várias colunas de um tabela estão na clausula where da consulta.

Além das estatisticas padrão, histogramas também podem ser criados nestes grupos de colunas melhorando a estimativa de custos quando existe um desvio na distribuição dos dados do grupo de colunas.

Para o CBO decidir como executar um consulta, ele utiliza as estatísticas disponíveis para calcular o custo de possíveis metodos de acesso.   Seletividade por exemplo, é um dos fatores utilizados neste calculo e consequentemente na escolha do melhor metodo de acesso.  Antes do 11g, as estatisticas so podiam ser criadas em colunas separadamente. A inabilidade do CBO de perceber o relacionamento entre colunas de uma tabela limitava significativamente a exatidão na estimativa do custo.

Para demonstrar como funciona a feature primeiro vamos criar uma tabela de pedidos

ALESSANDRO@orcl> create table pedidos
2  as
select level nr
3    4           , ‘Cliente ‘ || to_char(level) Nome_Cliente
5            , case
6              when level <= 500 then ‘Ipanema’
when level <= 550 then ‘Lagoa’
7    8              when level <= 600 then ‘Olinda’
9              when level <= 650 then ‘Centro’
10             when level <= 700 then ‘Bexiga’
11             when level <= 750 then ‘Areinha’
when level <= 800 then ‘Aldeota’
12   13             when level <= 850 then ‘Itapoa’
14             when level <= 900 then ‘Pajucara’
15             else ‘Bessa’
16             end Bairro
, case
17   18             when level <  500 then ‘RJ’
when level <= 550 then ‘MG’
when level <= 600 then ‘PE’
19   20   21             when level <= 650 then ‘TE’
22             when level <= 700 then ‘SP’
when level <= 750 then ‘MA’
23   24             when level <= 800 then ‘CE’
25             when level <= 850 then ‘BA’
26             when level <= 900 then ‘AL’
else ‘PB’
27   28             end UF
from dual
29   30  connect by level <= 1000
/
31
Table created.

Vamos dar uma olhada no conteudo da tabela

ALESSANDRO@orcl> select bairro, count(*)
2  from pedidos
3  group by bairro
4  order by bairro desc;

BAIRRO     COUNT(*)
——– ———-
Pajucara         50
Olinda           50
Lagoa            50
Itapoa           50
Ipanema         500
Centro           50
Bexiga           50
Bessa           100
Areinha          50
Aldeota          50

10 rows selected.

ALESSANDRO@orcl> select uf, count(*)
2  from  pedidos
3  group by uf
4  order by count(*) desc;

UF   COUNT(*)
— ———-
RJ        499
PB        100
MG         51
TE         50
MA         50
CE         50
BA         50
AL         50
SP         50
PE         50

10 rows selected.

Temos  então 10 bairros e 10 UF, com uma associação clara entre bairro e UF.

Vamos então gerar as estatisticas

ALESSANDRO@orcl> exec dbms_stats.gather_table_stats(user,’pedidos’);

PL/SQL procedure successfully completed.

Ok, Agora vamos dar uma olhada na seguinte query.
ALESSANDRO@orcl> explain plan
2  for
3  select * from pedidos
4  where bairro=’Ipanema’
5  and   UF = ‘RJ’
6  /

1* select * from table(dbms_xplan.display)
ALESSANDRO@orcl> /

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3882451908

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |    10 |   260 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PEDIDOS |    10 |   260 |     4   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“BAIRRO”=’Ipanema’ AND “UF”=’RJ’)

13 rows selected.

ALESSANDRO@orcl>

Como não geramos histogramas o CBO previu 10 linhas. Isto aconteceu por que o predicado bairro=’Ipanema’  tem 10 valores distintos, o mesmo acontece com  UF=’RJ’ .  O calculo que o CBO faz : 1000 linhas *(1/10)*(1/10) = 10 linhas.

Veja o que acontece quando faço esta pesquisa agora

ALESSANDRO@orcl> explain plan
2  for
3  select * from pedidos
4  where bairro=’Ipanema’ and UF=’PE’;

Explained.

ALESSANDRO@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3882451908

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |    10 |   260 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PEDIDOS |    10 |   260 |     4   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“BAIRRO”=’Ipanema’ AND “UF”=’PE’)

13 rows selected.

Esta consulta não retorna linhas e ainda assim o resultado é o mesmo.

Vamos dar uma ajudinha para o otimizador gerando histogramas
ALESSANDRO@orcl> exec dbms_stats.gather_table_stats(user, ‘pedidos’, method_opt=>’FOR ALL COLUMNS’);

PL/SQL procedure successfully completed.

ALESSANDRO@orcl> explain plan
2  for
3  select * from pedidos
4  where bairro=’Ipanema’
5  and   uf=’RJ’;

Explained.

ALESSANDRO@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3882451908

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |   250 |  6500 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PEDIDOS |   250 |  6500 |     4   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“BAIRRO”=’Ipanema’ AND “UF”=’RJ’)

13 rows selected.

Agora a previsão foi de 250 linhas. Isto foi calculado atraves do histogramas que dizem ao CBO que ipanema ocorre 500 vezes em 1000 e rj ocorre 499 vezes em 100

E para Ipanema em PE temos :

ALESSANDRO@orcl> explain plan
2  for
3  select * from pedidos
4  where bairro=’Ipanema’
5  and   uf=’PE’;

Explained.

ALESSANDRO@orcl>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3882451908

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |    25 |   650 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PEDIDOS |    25 |   650 |     4   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“BAIRRO”=’Ipanema’ AND “UF”=’PE’)

13 rows selected.

Ai está 25 linhas.

Antes do 11g isso é o melhor que podiamos conseguir. Com multicolumns statistics o calculo fica bem mais exato

ALESSANDRO@orcl> exec dbms_stats.gather_table_stats(user,’pedidos’,method_opt=>’FOR COLUMNS (bairro,uf)’);

ALESSANDRO@orcl> explain plan
2  for
3  select * from pedidos
4  where bairro=’Ipanema’
5  and   uf=’PE’;

Explained.

ALESSANDRO@orcl>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3882451908

—————————————————————————–
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT  |         |     1 |    27 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| PEDIDOS |     1 |    27 |     4   (0)| 00:00:01 |
—————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“BAIRRO”=’Ipanema’ AND “UF”=’PE’)

13 rows selected.

ALESSANDRO@orcl>

Agora sim. Como o otimizador não vai calcular 0 (zero) ele usa 1. Quanto mais preciso for o calculo, melhor vai ser o metodo de acesso que o otimizador vai escolher para uma determinada consulta, se vai utilizar indexes ou não por exemplo

Isto pode fazer uma enorme diferença em um ambiente de produção.

Com esta pequena demostração vimos como influenciar o otimizador sem alterar uma linha de codigo.
Se quiserver ver em pdf clica extendstats

September 5, 2010

Oracle | Bugs Esquecidos

Filed under: banco de dados — Tags: , , , — agleite @ 10:23 am

Esta semana me deparei com a seguinte situação: Migrar uma base Oracle Standard Edition 9.2 .0.1.  para 10.2.0.4

No script utilizado para exportação da base o parametro buffer estava para 800000. Para aqueles que não estão familiarizados com export, buffer determina em bytes o numero maximo de linhas em um array capturado pelo export. Se você, por exemplo, colocar 0 (zero) o export traz uma linha por vez.

Assim que olhei o parametro pensei: “Muito pequeno, vou aumentar e o export vai ser mais rapido”

Alterei entao de 800k para 80M . Como podes notar não aumentei muito por que existia uma limitação de memória no servidor.Mesmo assim isso deveria da para tornar a exportação mais rapida. Comecei então o export. Tudo ia bem até em uma determinada tabela onde o export parecia estar travado. Um olhadinha em v$session_wait (9i)  e o evento de espera para a sessão era SQL*Net message from client. Por curiosidade fui ver quantas linhas tinha esta tabela e o resultado foi 0(zero) linhas. Depois de uns 4 minutos o export voltou a fazer seu trabalho, no entanto voltou a travar novamente em uma outra tabela que também estava com zero linhas.

Search mental e…lembrei que na 9.2 tinha algum bug sobre export lentos com colunas CLOB. No entanto nenhuma das duas tabela de alguma coluna do tipo LOB. Apelei para o Metalink e achei :

ALERT: EXPORT with large BUFFER Can Silently Produce a Dump File with Corrupted Data [ID 223399.1]

Versões afetadas : 8.1.7.3 8.1.7.4  9.0.1.4  9.2.0.1 9.2.0.2

==>  data corruption *can* occur if: BUFFER = 3200000 (or higher)

Não tive duvidas e voltei o parametro para o valor inicial de 800K. Export executou como uma bala.

March 4, 2010

Oracle | Metodos de Otimização

Filed under: banco de dados — Tags: , , — agleite @ 3:41 pm

Metodos de otimização Oracle através dos tempos.

– Pre-Historia (v5)

  • Debug code

– Idade Media (v6)

  • Counters/Ratios
  • BSTAT/ESTAT
  • SQL*Trace

– Renascença (v7)

  • Instrumentação através de eventos de espera (Wait Events)
  • Saimos de counters para timers
  • STATSPACK

– YAPP (8i) – Otimização de Instance através de estatisticas

  • Não intrusivo, sempre disponivel
  • Escopo Amplo

– Method R (9i) – Otimização de sessões utilizando 10046 SQL traces

  • Escopo Direcionado
  • Bem Seletivo

– DB Time Tuning (10g)

  • Diversos niveis de escopo
  • Infraestrutura pronta: Instrumentação, ASH, AWR, ADDM, EM

É isso, agora é só saber como utilizar estas ferramentas 🙂

September 28, 2009

OFA – Optimal Flexible Architecture | 15 Anos

Filed under: banco de dados — Tags: , , , , — agleite @ 1:14 pm

Aniversário de 15 anos do padrão OFA. Para aqueles que não sabem : OFA é um conjunto de diretrizes de instalação para termos bancos Oracle mais confiáveis, mais rápidos e com menor custo de administração. Cary Millsap fez uma apresentação em 1991 na International Oracle User Week  do documento 513 . Este documento chamado  “Configuring a growing Oracle V6 database for optimal performance.”  é  o marco inicial do que viria a ser conhecido como OFA Standard.

Este documento esta disponivel no site da Method – R, bem aqui: OFA-STANDARD

Engraçado, é que este documento tomou corpo devido situações  digamos bem pitorescas. Por exemplo :

1- Administradores Unix que ficavam apagando arquivos Oracle. Em especial os arquivos das tablespaces temporarias, que imaginem, ficavam localizadas no /tmp.

2 – Sistemas muito lentos, por que a todos os arquivos Oracle estavam armazenados em uma única file system montada em um único disco no diretório $ORACLE_HOME/dbs. Muito embora este ambiente tivesse pelo menos 8 (oito) discos.

Tirando apenas por estes exemplos, tenho calafrios só pensar no que ele encontrava. Arquivos Oracle espalhados por todos os diretorios da maquina sem nenhuma referencia a que instance ou tablespace pertencia.

Fazendo este post me lembrei de um fato ocorrido nos nem tão longe dias do versão 7.3 . Todos voces sabem que em um determinado sistema operacional, há algumas pessoas  que associam a extensão .ora ao processador de texto, o notepad por exemplo.

Temos então o seguinte dialogo :

Pessoa – “Dba sabe aquele aquele arquivo do Oracle o users.ora ?”

Dba (tranquilo, sem imaginar o que vinha pela frente) – “Sei. O que tem ? ”

Pessoa – “Tava olhando ele aqui. Achei que tinha tabelas nele mas só estou vendo um monte de carinhas e simbolos esquisitos”

Dba (um pouco nervoso) – “Como assim tas olhando o users.ora. Olhando COMO ? ”

Pessoa (com um certo desdem) – “Olhando oras …tu usa aquela ferramenta esquista um tal de svr… mgrl – quaquer coisa, eu uso o Notepad”

DBA -…….

Pessoa : “Agora ve…to tentando sair salvando e não consigo..saio sem salvar mesmo ?”

Vou omitir o resto da conversa, por respeito a Bill.

Portanto parabéns OFA e um muito obrigado  ao Sr. Cary Millsap !

Create a free website or blog at WordPress.com.