Entendendo a marca d’água e a fragmentação em tabelas.Olá,
Uma 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 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 sí 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.
Vamos ver como funciona a marca d’água na prática, um 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 TSTDBA
Até 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, como a tabela está, seu volume e outras coisas mais, então, fiz um analyze na tabela para atualizar as informações estruturais dela no dicionário Oracle, ao fazer o analyze com o 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:08
Veja, 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 ---------- 40960
Mas, se consultar o seu tamanho na dba_segments temos:
SQL> select segment_name, sum(bytes)/1024 from dba_segments where segment_name = 'TSTDBA' group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024 --------------------------------------------------------------------------------- --------------- TSTDBA 64
O 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, até onde está entrando nisso?
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:18
A minha tabela continua com se estivesse com 5 blocos, o que isso 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 bocos 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 além da 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 37
Se quizer 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 37
Bom, 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á posicionado 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?
A chamada fragmentação de tabela, 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 perca de espaço físico para a tablespace, espaço que não poderam 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 37
A 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 37
Vamos 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:01
A quantidade de extents não alterou depois de apagarmos diversos registros, isso causa a conhecida fragmentação do segmento, mesmo que 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 37
Para resolvermos esse problema de fragmentação, bastamos reconstruir 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 23
PRONTO! 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 e diferenças entre os segmentos de tabela e índice, tudo isso foi apenas um modo de ilustrar os problemas que podem causar perda de performance em nossos ambientes.
Existe uma matéria que escrevi para a iMasters algum tempo atrás que explica com um pouco mais de detalhes como funciona a arquitetura de armazenamento lógico do banco de dados Oracle, o artigo Arquitetura de armazenamento lógico, que sanar algumas dúvidas.
A idéia principal do post foi iniciar desde o conceito de HWM (High Water Mark) até sua fragmentação, passando por várias fases, para fornecer um melhor entendimento de como a arquitetura Oracle funciona.
Abraços