Friday, December 16, 2005
dbms_stats - SIZE AUTO
Quando coletamos estatísticas em tabelas cuja distruibuição de dados é irregular através de um agrupamento e usamos
...
method_opt => 'for all columns size auto',
...
muitas vezes não nos damos conta de que não haverá precisão dos dados enquanto o sistema não for usado e houver uma nova coleta.
Por que?
A opção SIZE AUTO usa a tabela sys.col_usage$ para obter precisão na distribuição dos histogramas. Ou seja, depende da primeira coleta de estatística onde a sys.col_usage$ fica aguardando informações vindas das cláusulas WHERE usadas nas queries do sistema, onde houver colunas usadas, a col_usage$ será atualiza e portanto na coleta seguinte do seu sistema, haverá a distribuição precisa dos valores por grupo.
Demonstração.
O exemplo a seguir consiste da tabela T com dados distruibuídos no grupo (X) de forma irregular. Vamos simular um sistema com a producere P onde há o uso da coluna X. A coleta de estatística será feita através da procedure GATHER_STATS_T.
Tabela t e a distruibuição dos registros agrupados por x.
ops$marcio@LNX10GR2> select x, count(*)
2 from t
3 group by x
4 /
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.
Procedure gather_stats_t será especialmente usada para coletar estatísticas da tabela T, porém é uma prática essencial caso eu optasse por coletar estatíticas através de dbms_job ou dbms_scheduler (10g).
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace
2 procedure gather_stats_t
3 is
4 begin
5 dbms_stats.delete_table_stats( user, 't' );
6 dbms_stats.gather_table_stats(
7 user,
8 't',
9 method_opt => 'for columns x size auto',
10 cascade => true
11 );
12 end;
13 /
Procedure created.
ops$marcio@LNX10GR2>
A procedure p será nosso sistema, nela estamos usando a coluna x e, a partir da segunda coleta de estatística (gather_stats_t), o Oracle já conhece nossos predicados, consequentemente nossas colunas usadas (sys.col_usage$).
ops$marcio@LNX10GR2> create or replace
2 procedure p ( p_x in number )
3 is
4 begin
5 for x in ( select * from t where x = p_x )
6 loop
7 null;
8 end loop;
9 end;
10 /
Procedure created.
ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2>
Primeira Coleta! Neste momento, vamos verificar que os histogramas não estão precisos, porque ainda não usamos nosso sistema (p).
ops$marcio@LNX10GR2> exec gather_stats_t
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
Tabela sys.col_usage$ vazia, ela ainda não nos conhece.
ops$marcio@LNX10GR2> select c.obj#, c.intcol#, c.timestamp
2 from sys.col_usage$ c, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /
no rows selected
ops$marcio@LNX10GR2>
E os histogramas ainda básicos.
ops$marcio@LNX10GR2> select column_name, endpoint_number, endpoint_value,
2 endpoint_number - nvl(
3 lag(endpoint_number)
4 over (order by endpoint_value)
5 ,0
6 ) est_rows
7 from user_histograms
8 where table_name = 'T';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE EST_ROWS
--------------- --------------- -------------- -------------
X 0 1 0
X 1 9996 1
2 rows selected.
ops$marcio@LNX10GR2>
Usamos nosso sistema e coletamos estatística novamente!
ops$marcio@LNX10GR2> exec p( 10 )
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> exec gather_stats_t
PL/SQL procedure successfully completed.
ops$marcio@LNX10GR2>
Agora sim! sys.col_usage$ nos conhece e os histogramas representam exatamente a distribuição de nossos dados.
Conclusão.
ops$marcio@LNX10GR2> select c.obj#, c.intcol#, c.timestamp
2 from sys.col_usage$ c, user_objects uo
3 where obj# = object_id
4 and object_name = 'T'
5 /
OBJ# INTCOL# TIMESTAMP
------------- ------------- -------------------
52659 2 12/12/2005 01:27:13
1 row selected.
ops$marcio@LNX10GR2> select column_name, endpoint_number, endpoint_value,
2 endpoint_number - nvl(
3 lag(endpoint_number)
4 over (order by endpoint_value)
5 ,0
6 ) est_rows
7 from user_histograms
8 where table_name = 'T';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE EST_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.
Quando usar SIZE AUTO, atentar para esta particularidade não documentada, quando implementar um novo sistema em produção, principalmente com novas tabelas, se estiver usando size auto, seria interessante, pelo menos nos primeiros dias, atualizar as estatísticas até conseguir os histogramas de forma optimizada.
