.comment-link {margin-left:.6em;}

Thursday, November 24, 2005

Histograma - Definição SIZE

Para melhor entendimento deste artigo, lei primeiro este

Size é o número máximo de buckets definido para cada coluna.

O que é um bucket?
É uma "caixinha" (registro/coluna) onde o Oracle organiza as informações que pertence ao grupo de valores distintos. Ou seja, para cada valor distinto, o Oracle tem condição para decidir sobre o plano de execução (CBO).

Qual o critério para definir size?
Size deveria ser o número de valores distintos do agrupamento. No exemplo dos torcedores eu tinha 5 valores distintos, lembra? São Paulo, Corinthians, Palmeiras, Santos e o VOCEM. Então ficou fácil não? Size = 5.

Mas e se em nosso conjunto entrasse alguém que torce para o Juventos. Opa, problema! Se eu voltasse a analizar e informar size menor que o número de valores distintos, a coleta de estatística vai fazer o melhor possível para calcular as informações dentro dos buckets, porém nem sempre é o bastante. Portanto, quando for definir histograma, é preciso pensar bem o quanto o número de valores distintos vai crescer ou mudar.

Para coletar a estatística e o size devido, eu deixo o Oracle fazer o trabalho. Eu particularmente uso o seguinte:

begin
dbms_stats.gather_table_stats
(
user, 'T',
method_opt => 'for columns x'
cascade => true
)
end;
/


Porque dessa forma, estarei seguro de que o dbms_stats não vai coletar estatística a mais de onde não quero. Informo o nome da coluna e deixo que ele calcule o numero de buckets necessários. Não uso o SIZE AUTO porque o cálculo é baseado em workload da coluna além dos valores distintos (pelo menos na versão 9i não tive ganho quando usei esta cláusula) então, deixo como demonstrado acima.

E agora, quando usar histogramas?
Sempre que NÃO exista uniformidade na distribuição de valores sobre o grupo. Voltando ao exemplo dos torcedores, se em nosso conjunto não tivéssemos os torcedores do VOCEM, não seria interessante usar histogramas, porque não ganharíamos nada, haveria necessidade de estudar melhor os dados e talvez usar bitmap, cluster, etc. Tudo depende, mas se há distruibuição uniforme no grupo ou constante variação de valores distintos, a coluna não é candidata ao histograma.
Outra ocasião onde deveríamos evitar o histograma é quando a coluna é comparada com bind variable. De novo o exemplo, se o campo torce_para for comparado com bind variable, ele não é candidado a histogramas. O otimizador precisa conhecer o valor literal da comparação para percorrer os buckets(endpoint_number, endpoint_value).
Ex.

where torce_para = 'VOCEM' - usará o histograma
where torce_para = 'SAO PAULO' - usará o histograma
where torce_para = :time - NÃO usará histograma.
(9ir2 confirmado) - ainda não fiz o teste na 10gr2
Adendo 12/12/2005
Quando usamos bind-variable, a partir da versão 9i foi introduzido o peeking para minimizar o efeito no uso de histogramas com distribuição de dados irregular através de bind-variable. Eu, particularmente, ainda prefiro manter literal qual eu sei que isso irá beneficiar o uso de índice. Em uma próxima oportunidade comentarei o uso do peeking (vantagens e desvantagens).
Fim adendo

Onde eu investigo mais?
No exemplo abaixo, a tabela T tem duas colunas a e x, onde a é sequencial de 1 a 10000 e x tem valores distintos de 1, 2, 3, 4, 5, 9992, 9993, 9994, 9995 e 9996. A distribuição é a seguinte:

X COUNT(*)
------------- -------------
1 1
2 1
3 1
4 1
5 9991
9992 1
9993 1
9994 1
9995 1
9996 1

10 rows selected.

Então podemos notar que o para x=5 deveríamos fazer FTS e para os demais usar índice. Sabemos também que x tem 10 valores distintos. Porém, não vamos informar ao Oracle o size 10, vamos deixá-lo descobrir sozinho.


SQL> begin
2 dbms_stats.gather_table_stats(
3 user, 'T',
4 cascade => true,
5 method_opt => 'for columns x');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> col column_name format a15
SQL>
SQL> select column_name, endpoint_number, endpoint_value,
2 endpoint_number - nvl(lag(endpoint_number) over (order by endpoint_value),0) qdt_rows
3 from user_histograms
4 where table_name = 'T';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE QDT_ROWS
--------------- --------------- -------------- -------------
X 1 1 1
X 2 2 1
X 3 3 1
X 4 4 1
X 9995 5 9991
X 9996 9992 1
X 9997 9993 1
X 9998 9994 1
X 9999 9995 1
X 10000 9996 1

10 rows selected.


Como esperado, 10 buckets e para ajudar-nos a entender como as informações são dispostas a coluna QDT_ROWS fiz justamente subtraindo o valor anterior do endpoint_number para saber a quantidade de registros distribuídos (Notaram que é idêntico ao count(*) agroupado por x) - Dessa forma o Oracle sabe onde a literal está e calcula se é vantajoso o uso de FTS ou Índice.

Agora vamos aos testes:

SQL> set autotrace traceonly
SQL> select * from t where x = 3;

1 row selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=17)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1)

SQL> select * from t where x = 5;

9991 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9991 Bytes=169847)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=9991 Bytes=169847)


Perfeito, como esperado, x=5 FTS e x=3 Índice.
Um último teste para provar que se utilizamos bind variable, o histograma é inútil.

SQL> var n number
SQL> exec :n := 3

PL/SQL procedure successfully completed.

SQL> select * from t where x = :n;

1 row selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=17000)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=17000)

ops$mportes@FCCUAT9I> exec :n := 5

PL/SQL procedure successfully completed.

ops$mportes@FCCUAT9I> select * from t where x = :n;

9991 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=17000)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=17000)


Full Table Scan nos 2 casos.
Para leitura complementar sugiro o metalink docs:
1031826.6
72539.1

E para quem quiser reproduzir o teste acima eu usei o seguinte script

set echo on
drop table t;

create table t nologging as
with
v as ( select level l
from dual connect by 1=1 and level <= 10000 )
select l a, 5 x from v
/
commit;
update t
set x = rownum
where rownum <= 4 and x = 5;

update t
set x = 9991+rownum
where rownum <= 5 and x = 5;

select x, count(*)
from t
group by x
/

create index t_idx on t (x);

begin
dbms_stats.gather_table_stats(
user, 'T',
cascade => true,
method_opt => 'for columns x');
end;
/
col column_name format a15

select column_name, endpoint_number, endpoint_value,
endpoint_number - nvl(lag(endpoint_number) over (order by endpoint_value),0) qdt_rows
from user_histograms
where table_name = 'T';


set autotrace traceonly
select * from t where x = 3;
select * from t where x = 5;

var n number
exec :n := 3
select * from t where x = :n;
exec :n := 5
select * from t where x = :n;
set autotrace off
set echo off

Labels:


Wednesday, November 23, 2005

Histograma - Introdução.

Imagine o seguinte exemplo: Na cidade de São Paulo o conjunto de torcedores de times de futebol é distribuído segundo a tabela abaixo:


torce_para count(*)
-------------- ----------
São Paulo 4.000.000
Corinthians 8.000.000
Palmeiras 6.000.000
Santos 2.000.000
VOCEM de Assis 8


Se existe um índice em torce_para e uma query pedir os torcedores do VOCEM de Assis, é muito provável que o Oracle fará full table scan.

Mas por que?
É muito mais barato para o Oracle fazer um FTS que um acesso a milhões de linhas do índice.

Mas existe só 8 torcedores do VOCEM, como assim milhões de linhas?
Então sem histograma a coleta de estatística para o índice será mais ou menos (total de linhas tabela) / (seletividade do índice) então 20 milhões / 5 = 4 milhões de registros aproximadamente por valor distinto no índice (Cálculo muito a grosso modo).

Porém, sabemos que isso não corresponde a verdade, então damos uma "força" para o otimizador usando histograma. Nele será armazenado a informação necessária para o otimizador decidir quando é hora de FTS (São Paulo) e quando é hora de usar índice (VOCEM de Assis).

Veja um exemplo abaixo: A tabela t tem aproximada 7.4 milhões de linhas. A coluna x_hist tem 5 valores distintos como mostrado na query abaixo:


SQL> select nvl(to_char(x_hist),'Total') x_hist, count(*)
2 from t
3 group by rollup(x_hist)
SQL> /

X_HIST COUNT(*)
---------------------------------------- -------------
0 1835294
1 1835293
2 1835293
3 1835294
5 10
Total 7341184

6 rows selected.


A coluna x_hist é um índice. Como nossa aplicação usa corretamente o CBO, faremos 2 coletas de estatísticas, uma sem o uso de histograma (primeira) e outra com o uso de histograma, logo após cada coleta temos a query buscando os registros por x_hist.


SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T',
5 estimate_percent => 10,
6 cascade => true,
7 degree => 4 );
8 end;
9 /

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on
SQL>
SQL> select * from t
2 where x_hist = 5
3 /

OBJECT_ID OBJECT_NAME X_HIST
------------- ------------------------------ -------------
97 ACCESS$ 5
4349 AGGXMLIMP 5
4356 AGGXMLINPUTTYPE 5
1812 ALL_ALL_TABLES 5
5653 ALL_APPLY 5
5672 ALL_APPLY_CONFLICT_COLUMNS 5
5676 ALL_APPLY_DML_HANDLERS 5
5687 ALL_APPLY_ERROR 5
5665 ALL_APPLY_KEY_COLUMNS 5
5657 ALL_APPLY_PARAMETERS 5

10 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2986 Card=1836663 Bytes=45916575)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2986 Card=1836663 Bytes=45916575)




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


Como podemos ver, mesmo com índice o otimizador escolheu Full Table Scan e o custo de consistent gets está elevado para trazer apenas 10 linhas. Está errado? Não, do ponto de vista do otimizador não. Ele não conhece que para, justamente aquele x_hist, temos somente 8 linhas e com apenas 1 leitura física poderíamos responder ao predicado. Então, o próximo passo é ajudar ao otimizador conhecer o que ele tem. Vamos analizar a tabela agora passando um size de 10 para a coluna x_hist onde ele irá conhecer os valores adequados para a decisão.


SQL>
SQL> set autotrace off
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T',
5 estimate_percent => 10,
6 method_opt => 'for columns x_hist size 10',
7 cascade => true,
8 degree => 4 );
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on
SQL>
SQL> select * from t
2 where x_hist = 5
3 /

OBJECT_ID OBJECT_NAME X_HIST
------------- ------------------------------ -------------
97 ACCESS$ 5
4349 AGGXMLIMP 5
4356 AGGXMLINPUTTYPE 5
1812 ALL_ALL_TABLES 5
5653 ALL_APPLY 5
5672 ALL_APPLY_CONFLICT_COLUMNS 5
5676 ALL_APPLY_DML_HANDLERS 5
5687 ALL_APPLY_ERROR 5
5665 ALL_APPLY_KEY_COLUMNS 5
5657 ALL_APPLY_PARAMETERS 5

10 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=25)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Bytes=25)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1)




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


Agora sim, o otimizador (já com as informações necessárias) fez o acesso ao índice, 1 chamada física 6 consistent gets e respondeu a nossa query de forma eficiente.

Labels:


Thursday, November 17, 2005

Trabalhando com intervalos de DATAS

Durante nosso trabalho do dia-a-dia, muitas vezes nos deparamos com algumas situações onde temos que completar intervalos de datas. Veja abaixo três dúvidas comuns e as respectivas sugestões.


Gostaria de um exemplo de uma query que me retorna todos os dias do mês ?
Ex :
01/10/2005
02/10/2005
03/10/2005
04/10/2005
05/10/2005




ops$marcio@WIN10GR2> var mes number
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> exec :mes := 10

PL/SQL procedure successfully completed.


MES
-------------
10

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> with
2 x as
3 ( select trunc(to_date(:mes, 'mm')) + (level-1) dt
4 from dual
5 connect by 1=1 and level <= to_number(to_char(last_day(to_date(:mes, 'mm') ), 'dd'))
6 )
7 select * from x
8 /

DT
-------------------
01/10/2005 00:00:00
02/10/2005 00:00:00
03/10/2005 00:00:00
04/10/2005 00:00:00
05/10/2005 00:00:00
06/10/2005 00:00:00
07/10/2005 00:00:00
08/10/2005 00:00:00
09/10/2005 00:00:00
10/10/2005 00:00:00
11/10/2005 00:00:00
12/10/2005 00:00:00
13/10/2005 00:00:00
14/10/2005 00:00:00
15/10/2005 00:00:00
16/10/2005 00:00:00
17/10/2005 00:00:00
18/10/2005 00:00:00
19/10/2005 00:00:00
20/10/2005 00:00:00
21/10/2005 00:00:00
22/10/2005 00:00:00
23/10/2005 00:00:00
24/10/2005 00:00:00
25/10/2005 00:00:00
26/10/2005 00:00:00
27/10/2005 00:00:00
28/10/2005 00:00:00
29/10/2005 00:00:00
30/10/2005 00:00:00
31/10/2005 00:00:00




Alguém saberia me dizer como faço para -- através de SQL, sem ter que usar
PL -- retornar os nomes dos meses com o ano entre duas datas que serão
informado pelo usuário?

Exemplo: 01/10/2004 à 28/02/2005
Retorno: OUT-2004, NOV-2004, DEZ-2004, JAN-2005 e FEV-2005.



Para 9i um exemplo seria:

SQL> var dtini varchar2(20)
SQL> var dtfim varchar2(20)
SQL>
SQL> exec :dtini := '01/10/2004'

PL/SQL procedure successfully completed.


DTINI
--------------------------------
01/10/2004

SQL> exec :dtfim := '28/02/2005'

PL/SQL procedure successfully completed.


DTFIM
--------------------------------
28/02/2005

SQL>
SQL> with
2 mes
3 as ( select add_months(
4 trunc(to_date(:dtini, 'dd/mm/yyyy')),
5 level-1) dt
6 from dual
7 connect by 1=1
8 and level <= round(months_between(
9 to_date(:dtfim, 'dd/mm/yyyy'),
10 to_date(:dtini, 'dd/mm/yyyy')))
11 )
12 select to_char(dt, 'MON-yyyy',
13 'nls_date_language=portuguese') mes_ano
14 from mes
15 /

MES_ANO
--------
OUT-2004
NOV-2004
DEZ-2004
JAN-2005
FEV-2005

5 rows selected.

estou com um certo problema aqui,

preciso fazer uma sql que me retorne um
registro para cada dia util (tirando apenas
sabado e domingo, feriados pode retornar)
de um certo periodo, mas eu não tenho uma
tabela com os dias cadastrados...

o periodo pode ser variavel, por exemplo, o usuario
pode informar de 10/10/2005 a 20/10/2005 ou de
01/01/2005 a 31/12/2005 ou quaquer outra coisa
quer der na idéia dele.

eu tava quase criando a tabela e iria cadastrar
os dias uteis de uns cinco anos mais ou menos,
mas pensei em dar uma verificada melhor se
não teria uma maneira mais adequada pra
resolver isso...



ops$marcio@WIN10GR2> variable start_date varchar2(20)
ops$marcio@WIN10GR2> variable end_date varchar2(20)
ops$marcio@WIN10GR2> exec :start_date := '10/10/2005'

PL/SQL procedure successfully completed.


START_DATE
--------------------------------
10/10/2005

ops$marcio@WIN10GR2> exec :end_date := '20/10/2005'

PL/SQL procedure successfully completed.


END_DATE
--------------------------------
20/10/2005

ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> with dates
2 as
3 (select to_date(:start_date,'dd/mm/yyyy')+level-1 dt
4 from dual
5 connect by level <= to_date(:end_date,'dd/mm/yyyy')-to_date(:start_date,'dd/mm/yyyy')+1 )
6
7 select dt, to_char(dt,'Day') Week
8 from dates
9 where to_char(dt, 'd') not in (7,1)
10 /

DT WEEK
------------------- ---------
10/10/2005 00:00:00 Monday
11/10/2005 00:00:00 Tuesday
12/10/2005 00:00:00 Wednesday
13/10/2005 00:00:00 Thursday
14/10/2005 00:00:00 Friday
17/10/2005 00:00:00 Monday
18/10/2005 00:00:00 Tuesday
19/10/2005 00:00:00 Wednesday
20/10/2005 00:00:00 Thursday

9 rows selected.

Labels:


Thursday, November 03, 2005

CAST (in list)

Participo ativamente de várias listas de Oracle e algumas vezes, vejo perguntas seguidamente aparecendo. Uma delas é sobre como usar uma lista de valores separadas por vírgula. No exemplo abaixo, a pessoa recebia uma lista e queria comparar com valores em uma tabela.


"Não estou conseguindo montar o sql para fazer o seguinte. Eu recebo
uma sequencia com n códigos (digamos 1,2,3,4,5,6,7,8) e tenho uma
tabela com vários códigos(2,3,4,45,36,26,74).

Preciso saber como seria o SQL para eu descobrir quais códigos da
sequencia que eu recebo não estão na tabela. (Neste caso 1,5,6,7,8)


Vamos ao exemplo, por favor se houver dúvida não hesite em comentar.

ops$marcio@LNX10GR2> create table t ( x number );

Table created.

ops$marcio@LNX10GR2> insert into t values (2);

1 row created.

ops$marcio@LNX10GR2> insert into t values (3);

1 row created.

ops$marcio@LNX10GR2> insert into t values (4);

1 row created.

ops$marcio@LNX10GR2> insert into t values (45);

1 row created.

ops$marcio@LNX10GR2> insert into t values (36);

1 row created.

ops$marcio@LNX10GR2> insert into t values (26);

1 row created.

ops$marcio@LNX10GR2> insert into t values (74);

1 row created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace type array as table of varchar2 (50);
2 /

Type created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace function f ( p_string in varchar2 )
2 return array
3 is
4 l_string varchar2(50) default p_string || ',';
5 l_data array := array();
6 n number;
7 begin
8 loop
9 exit when l_string is null;
10 n := instr( l_string, ',' );
11 l_data.extend;
12 l_data(l_data.count) :=
13 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
14 l_string := substr( l_string, n+1 );
15 end loop;
16 return l_data;
17 end;
18 /

Function created.

ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2> select * from t;

X
-------------
2
3
4
45
36
26
74

7 rows selected.

ops$marcio@LNX10GR2> select cast( column_value as number ) x
2 from table( cast( f('1,2,3,4,5,6,7,8') as array ) )
3 /

X
-------------
1
2
3
4
5
6
7
8

8 rows selected.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select cast( column_value as number ) x
2 from table( cast( f('1,2,3,4,5,6,7,8') as array ) )
3 minus
4 select x from t;

X
-------------
1
5
6
7
8

5 rows selected.

This page is powered by Blogger. Isn't yours?