Olá, amigos. Um dos principais conceitos sobre arquitetura física do Oracle é a marca Dágua. Uma tradução de HWM – High Water Mark, ele que indica o limite que uma tabela já ocupou de espaço físico para um determinado datafile no seu banco de dados. Mas vamos um pouco mais a fundo.
O que é uma Marca Dágua (HWM – High Water Mark)?
A marca dágua é o limite do número de blocos que uma tabela pode estar utilizando; resumindo para um conceito mais simples, toda vez que uma tabela recebe um INSERT (novos registros), essa marca na tabela aumenta dizendo ao Oracle Server a quantidade de blocos que a tabela está utilizando, automaticamente, a quantidade de blocos, multiplicado, pelo tamanho do db_block_size do banco de dados, diz o valor físico real que está sendo utilizado.
Mas esse valor real não é o valor que o Oracle irá alocar, pois irá depender de alguns outros pontos, como:
- Se a tabela está sendo gerenciada por si própria ou pela tablespace.
- Irá depender dos tamanhos dos extents, exemplo, INITIAL_EXTENT e NEXT_EXTENT.
- Também irá depender do tipo de gerenciamento, se é SEGMENT MANAGEMENT AUTO ou UNIFORM.
- E a quantidade de blocos que um EXTENT pode suportar.
Os conceitos a seguir são muito aplicados para DMT – Dictionary Management Tablespace, para LMT – Local Management Tablespace, também poderá realizar os testes, mas os conceitos para esse tipo de gerenciamento de tablespace é um pouco mais detalhada.
Vamos ver como funciona a marca dágua na prática em alguns exemplos práticos.
Vou criar uma tabela simples, chamada TSTDBA.
SQL> create table TESTE (a varchar2(100) not null, b number(7) not null);
Tabela criada.Agora, vamos analisar como está a estrutura para o Oracle, pois a tabela não possui nenhum valor e nenhuma estatística coletada.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBAAté o momento, tudo sem surpresas para nós.
Então, vamos popular essa tabela com alguns registros, veja o exemplo.
SQL> l
1 declare
2 contador integer;
3 begin
4 contador := 1;
5 while contador <= 1000 loop
6 insert into TSTDBA values (TESTE,contador);
7 contador := contador 1;
8 end loop;
9 commit;
10* end;
SQL> /
Procedimento PL/SQL concluído com sucesso.
SQL> exec dbms_stats.gather_table_stats (ownname=>RODRIGO,tabname=>TSTDBA,estimate_percent=>null,method_opt=>FOR ALL COLUMNS SIZE AUTO,degree=>6);
Procedimento PL/SQL concluído com sucesso.
Verifiquem que fiz um pequeno bloco PL/SQL para inserir dados em minha tabela, cerca de 1.000 registros. Após isso, preciso dizer ao Oracle, o status da tabela, seu volume e outras coisas mais, então, fiz um analyze na tabela para atualizar as informações estruturais e lógicas dela no dicionário Oracle, ao coletar as estatísticas usando o pacote DBMS_STATS, o resultado do SELECT acima, agora é esse.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 5 0 1000 06-10-2008 19:42:08Veja, a nossa tabela está utilizando 5 blocos, o db_block_size do meu banco de dados é de 8KB, então, resumidamente, ele deveria estar utilizando cerca de 40KB, certo?
SQL> select 8192*5 from dual;
8192*5
----------
40960O resultado para o tamanho da tabela TSTDBA é 64KB, porque o INITIAL_EXTENT da tabela é de 64KB, e como os 1.000 registros ocuparam apenas 40KB, um único extent consegui suportar.
SQL> select initial_extent/1024, next_extent from dba_tables where table_name = TSTDBA;
INITIAL_EXTENT/1024 NEXT_EXTENT
------------------- -----------
64
Pois bem! Rodrigo, e o tal do HWM, o que têm haver com isso?
Vamos começar a brincar agora, veja que após o analyze, minha tabela TSTDBA está utilizando 5 blocos de dados, certo? Teoricamente, se eu fizer um TRUNCATE TABLE, eu não vou mais utilizar nenhum bloco, e minha marca dágua deveria baixar, mas acontece isso:
SQL> truncate table TSTDBA;
Tabela truncada.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 5 0 1000 06-10-2008 19:44:18A minha tabela continua com se estivesse com 5 blocos, o que pode nos prejudicar:
Esse exemplo é bem simples, mas para tabelas com milhares de registros, poderá influenciar os FULL-TABLES SCANS. Ao realizar um INSERT convencional, ou seja, sem o hint APPEND, ele irá procurar por blocos livres e irá consumir CPU e demorar um tempo para sua execução.
Se minha marca dágua estiver muito alta, ou seja, estiver armazenando um alto valor de blocos utilizados, e você sabe que ele não está utilizando tudo isso, você terá uma alocação de EXTENTS desnecessários no banco de dados, e isso irá ocupar espaço desnecessários.
Caso eu quisesse diminuir o tamanho do meu segmento de tabela, eu não iria conseguir, pois a marca dágua é inferior aos meus 64KB. Pois bem, tente realizar um insert agora de 2.000.000 de registros e vamos ver o que acontece.
SQL> declare
2 contador integer;
3 begin
4 contador := 1;
5 while contador <= 2000000 loop
6 insert into TSTDBA values (TESTE,contador);
7 contador := contador 1;
8 end loop;
9 commit;
10 end;
11 /
Procedimento PL/SQL concluído com sucesso.
SQL> exec dbms_stats.gather_table_stats (ownname=>RODRIGO,tabname=>TSTDBA,estimate_percent=>null,method_opt=>FOR ALL COLUMNS SIZE AUTO,degree=>6);
Procedimento PL/SQL concluído com sucesso.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 4654 0 2000000 06-10-2008 22:37:24
SQL> show parameters db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> select (8192*4654)/1024/1024 as "Tamanho" from dual;
Tamanho
----------
36,359375
SQL> select segment_name, sum(bytes)/1024/1024 from dba_segments where segment_name = TSTDBA group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024
--------------------------------------------------------------------------------- --------------------
TSTDBA 37Se quiser analisar melhor como ficou a distribuição, veja a dba_extents. Abaixo vou mostrar apenas um pequeno resumo da quantidade de extents alocados e seus respectivo tamanho.
SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
2 from dba_extents
3 where segment_name = TSTDBA
4 group by segment_name;
SEGMENT_NAME COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA 52 37Bom, vimos que agora temos um valor legal de extents alocados, e mesmo após o TRUNCATE continuo com uma alocação de extents, que totaliza os 37MB da tabela, então, minha marca dágua está posicionada no 51 extent, que seria o limite do numeros de blocos alcançados.
Conseguimos entender como funciona a marca dágua, o que isso pode nos causar?
Inicialmente, podemos imaginar que é uma fragmentação, por possuir alguns blocos vazios, além da marca dágua elevar o número de extents no dicionário, prejudicando muitas vezes os planos de execução e os table full scans, vamos ter também perda de espaço físico para a tablespace, espaço que não puderam ser alocados por outro segmento. Vamos a uma demostração prática de como funciona a fragmentação.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 4654 0 2000000 06-10-2008 22:37:24
SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
2 from dba_extents
3 where segment_name = TSTDBA
4 group by segment_name;
SEGMENT_NAME COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA 52 37A minha tabela TSTDBA continua com seus 2.000.000 de registros, após o analyze acima, vimos que está a atual estrutura da tabela, e se realizarmos diversos DELETES em grandes quantidades, o que poderemos ter?
SQL> delete from TSTDBA where b between 10000 and 20000;
10001 linhas deletadas.
SQL> delete from TSTDBA where b between 50000 and 200000;
150001 linhas deletadas.
SQL> delete from TSTDBA where b between 400000 and 700000;
300001 linhas deletadas.
SQL> delete from TSTDBA where b between 1000000 and 1300000;
300001 linhas deletadas.
SQL> commit;
Commit concluído.
SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
2 from dba_extents
3 where segment_name = TSTDBA
4 group by segment_name;
SEGMENT_NAME COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA 52 37Vamos passar um analyze para validar toda a estrutura da tabela.
SQL> exec dbms_stats.gather_table_stats (ownname=>RODRIGO,tabname=>TSTDBA,estimate_percent=>null,method_opt=>FOR ALL COLUMNS SIZE AUTO,degree=>6);
Procedimento PL/SQL concluído com sucesso.Veja o resultado para os novos valores.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 4654 0 1239996 06-10-2008 23:09:01A quantidade de extents não alterou depois de apagarmos diversos registros, mesmo após calcularmos a quantidade de registro exato da tabela.
SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
2 from dba_extents
3 where segment_name = TSTDBA
4 group by segment_name;
SEGMENT_NAME COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA 52 37Para resolvermos esse problema, que vamos entender como “fragmentação”, basta reconstruirmos o mapa binário da tabela, para isso, apenas use um MOVE sem mencionar a tablespace que resolve nosso problema.
SQL> alter table TSTDBA move;
Tabela alterada.
SQL> select owner, table_name, blocks, empty_blocks, num_rows, to_char(last_analyzed,DD-MM-RRRR HH24:MI:SS) as "ANALYZE"
2 from dba_tables
3 where table_name = TSTDBA;
OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS ANALYZE
---------- ------------------------------ ---------- ------------ ---------- -------------------
RODRIGO TSTDBA 4654 0 1239996 06-10-2008 23:09:01
SQL> select segment_name, count(extent_id), sum(bytes)/1024/1024
2 from dba_extents
3 where segment_name = TSTDBA
4 group by segment_name;
SEGMENT_NAME COUNT(EXTENT_ID) SUM(BYTES)/1024/1024
-------------------- ---------------- --------------------
TSTDBA 38 23PRONTO! Veja que após nosso “rebuild” na tabela, liberamos cerca de 15MB para a tablespace, fazendo apenas uma reconstrução dos extents da tabela.
Existem muitos outros conceitos envolvidos sobre a alocação de extents, sem mencionar os freelists, gerenciamento das tablespaces (para tablespaces LMT – Local Management Tablespace, é um conceito diferente) e diferenças entre os segmentos de tabela e índice. Tudo isso foi apenas um modo de ilustrar o conceito de gerência de armazenamento e marca dágua no Oracle Server.
Existe um post no site ORA-600 , do autor Jose Laurindo Chiappa, que diz alguns mitos e “meias-verdades” sobre a fragmentação no Oracle Server, que recomendo a leitura para enteder com mais detalhes o gerenciamento por completo.
A idéia principal do post foi iniciar desde o conceito de HWM (High Water Mark) até o gerenciamento dos extents, passando por várias fases, para fornecer um melhor entendimento de como a arquitetura Oracle funciona. Mais informações, no meu blog.
Abraços,
De 0 a 10, o quanto você recomendaria este artigo para um amigo?