Disponibilidade 24×7 …sei 
ORA-00372: file 28 cannot be modified at this time ORA-01110: data file 28: ‘/u01/app/oracle/admin/mlrepap/db/metalink_02.dbf’
November 8, 2009
September 1, 2008
Evolução do Gerenciamento de Memória no Oracle
Tudo começou lá no 9i. Alguns dos parametros da SGA ficaram dinâmicos. Portanto o comando “ALTER SYSTEM” poderia ser utilizado para aumentar ou diminuir seus valores.
O total da SGA é limitado pelo parâmetro SGA_MAX_SIZE. Cada parametro da SGA é alocado em termos de “Grãos”, que nada mais é do que uma unidade de alocação. O tamanho do grão depende do SGA_MAX_SIZE e da plataforma do hardware.
Dependendo da versão 9i, os seguintes parametros foram definidos como dinâmicos:
- 9.0.1: Shared Pool e Default Buffer Cache
- 9.2.0: Foi acrescido o Large Pool
Além disso, a equação: “DB_BLOCK_BUFFERS*DB_BLOCK_SIZE”; foi substituida pelo novo parametro DB_CACHE_SIZE e DB_nK_CACHE_SIZE para diferentes tamanhos de bloco.
Por ultimo PGA_AGGREGATE_TARGET, no livrando dos sacrificio de ajustar os diversos parametros “*_area_size”.
Ainda no 9i, quando você aumenta ou diminui o tamanho de um dos parametros acima, a memória liberada não será re-alocada para outro parametro dinâmico automaticamente. Você deve fazer isso manualmente.
Na versão 10g apareceu o ASMM (Automatic Shared Memory Management), que automatiza o gerenciamento das seguintes estruturas de memória:
db_cache_size, shared_pool_size, large_pool_size, java_pool_size e streams_pool_size (10.2)
Quando implementada, a ASMM faz com que o Oracle distribua a memória entre os componentes baseado nos requirementos de carga de trabalho. Portanto este componentes são chamado de auto-otimizaveis. Quem coordena o tamanho destes componentes e move a memória para a area com maior necessidade, é o processo background MMAN(Memory Manager).
Para implementar a ASMM, basta
Parametrizar SGA_TARGET com um valor diferente de zero e STATISTICS_LEVEL=TYPICAL (ou ALL).
Os seguintes parametros não são auto-ajustaveis:
* LOG_BUFFER
* DB_KEEP_CACHE_SIZE
* DB_RECICLE_CACHE_SIZE
* DB_nK_CACHE_SIZE
* STREAMS_POOL_SIZE (10.1)
Além claro do valor “Fixed SGA”.
Quando você define SGA_TARGET, o valor total destes parametros é subtraido do valor de SGA_TARGET, sendo o saldo restante distribuido entre os componentes auto-ajustaveis.
SGA_TARGET é dinâmico, seu valor superio é determinado por SGA_MAX_SIZE. Este por sua vez só pode ser alterado com a re-inicialização da instancia.
July 17, 2008
Indices e Estatisticas
O ponto a discutir neste post, é como o Oracle avalia blocos de indices vazios, no cálculo de estatisticas de indices e que implicações isto pode ter no CBO .
Pra entender como isso funciona, vou criar uma tabela com 10000 linhas e posteriormente deletar a maioria delas.
SQL> CREATE TABLE alex
2 AS SELECT rownum id, ‘Rush’ text
3 FROM dual
4 CONNECT BY level <=10000;
Tabela criada.
SQL> create index alex_id on alex(id);
Índice criado.
Temos então um indice com 10000 entradas. Vamos ver quanto leaf blocks nós temos
SQL> analyze index alex_id validate structure;
Índice analisado.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
———- ———- ———–
10000 21 0
Portanto neste momento temos 10000 LF_ROWS, 21 LF_BLKS e nenhuma linha indexada deletada.
Vamos deletar a maioria das linhas e consequentemente as entradas de indice.
SQL> delete alex where id <=9990;
9990 linhas deletadas.
SQL> commit;
Commit concluído.
Certo, agora nos temos um indice com a maioria de suas entradas deletadas e todos os blocos folha dos indice menos um vazios.
Vamos ver, inicialmente como o ANALYZE lida com estes blocos de indices vazios e com a deleções.
SQL> analyze index alex_id validate structure;
Índice analisado.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
———- ———- ———–
10000 21 9990
Como voce pode notar a estatistica LF_ROWS ainda mostra o valor 10000, ou seja, ainda conta as entradas dos indice apesar da deleção. LF_BLKS também continua 21, portanto os blocos vazios ainda são levados em consideração.
Vamos ver o que acontece quando utilizamos os DBMS_STATS
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘ALEX’, cascade => true, estimate_p
ercent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);
Procedimento PL/SQL concluído com sucesso.
SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘ALEX_ID’;
INDEX_NAME NUM_ROWS LEAF_BLOCKS
————- ———- ———–
ALEX_ID 10 1
Notamos imediatamente duas diferenças.
Primeira : NUM_ROWS igual a 10. Mostrando que apenas as entradas não deletadas são levadas em consideração.
Segunda : LEAF_BLOCKS igual a 1. Mostrando que apenas os blocos que contem entradas não deletadas são levados em consideração. Embora a estrutura do indice possua outrso 20 “Leaf Blocks”, estes não são considerados pelo CBO do Oracle quando utilizamos DBMS_STATS.
Vamos apenas executar uma consulta simples para ver seu plano de execução.
SQL> select * from alex where id between 1 and 10000;
ID TEXT
———- —-
9991 Rush
9992 Rush
9993 Rush
9994 Rush
9995 Rush
9996 Rush
9997 Rush
9998 Rush
9999 Rush
10000 Rush
10 linhas selecionadas.
Plano de Execução
———————————————————-
Plan hash value: 1467085234
——————–
| Id | Operation | Name |
——————–
| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID| ALEX |
|* 2 | INDEX RANGE SCAN | ALEX_ID |
———————————-
O indice é utilizado por que o custo de é baixo.
Já se usarmos o ANALYZE
Vejamos se tem diferenças
SQL> analyze index alex_id COMPUTE STATISTICS;
Índice analisado.
SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘ALEX_ID’
2 /
INDEX_NAME NUM_ROWS LEAF_BLOCKS
————— ———- ———–
ALEX_ID 10 21
Como voce pode notar o Analyze leva em consideração os blocos vazios. LEAF_BLOCKS 21 em vez de apenas 1 como anteriormente. Mas, blocos folhas (leaf blocks) são apenas uma das estatisticas que o CBO utiliza quando calcula o custo de se utilizar um determinado indice como acesso aos dados. E qual o impacto disso na nossa consulta.
SQL> select * from alex where id between 1 and 10000;
ID TEXT
———- —-
9991 Rush
9992 Rush
9993 Rush
9994 Rush
9995 Rush
9996 Rush
9997 Rush
9998 Rush
9999 Rush
10000 Rush
10 linhas selecionadas.
Plano de Execução
—————————————————–
Plan hash value: 2452671568
—————————
| Id | Operation | Name|
—————————
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| ALEX |
—————————-
Agora o CBO decidiu fazer um full table scan, devido ao custo adicional calculado ao se utilizar o indice.
Portanto blocos de indices vazios tem um grande impacto, não apenas em como a consulta pode ser realizada, mas como o CBO calcula o custo associado, dependendo de como a estatistica tenha sido gerada.
Esta é apenas uma das razões pelas quais voce deve utilizar o DBMS_STATS ao invés do ANALYZE
