ORA–01555 – Snapshot too Old
Primeiro, vamos imaginar nosso cenário, onde iremos nos deparar com um ambiente VLDB /OLTP, com muitas DMLs ocorrendo e uma longa query sendo executada.
Vamos abordar o erro, revendo alguns pontos:
Parâmetros:
UNDO_MANAGEMENT = MANUAL/AUTO. Este parâmetro governa como será o gerenciamento do espaço de UNDO. Usaremos para este estudo de caso apenas AUTO(AUM).
UNDO_RETENTION = Este parâmetro estipula um tempo mínimo em segundos que o Oracle manterá para consistência na leitura as extensões dos segmentos de rollback com estado UNEXPIRED. O Valor default é 900 segundos.
RETENTION GUARANTEE (versão 10g e acima) = Parâmetro que muda o comportamento padrão do Oracle com relação as extensões do segmento de UNDO em estado UNEXPIRED.
Estado das extensões dos segmentos de Rollback:
ACTIVE: Extensões cujo conteúdo pertencem a uma transação ainda não commitada. Este tipo de segmento nunca será sobrescrito.
UNEXPIRED: Extensões pertencentes a transações commitadas, porém cujo período de tempo após o commit ainda não ultrapassou o valor estipulado no parâmetro UNDO_RETENTION.
EXPIRED: Extensões pertencentes a transações commitadas e cujo tempo estipulado no parâmetro UNDO_RETENTION já expirou.
O estado das extensões pode ser verificado através da view de dicionário de dados DBA_UNDO_EXTENTS. A quantidade de blocos das extensões utilizadas em um determinado período pode ser obtida através da view V$UNDOSTAT.
Consistência na Leitura :
Significa garantir que os dados obtidos através do SELECT estejam no mesmo “snapshot”, ou seja, que mesmo que estes dados tenha sido alterados durante a execução da query, possamos obter sua “Before Image” correspondente ao momento que a query foi iniciada. Uma “before image” consiste em informações capazes de desfazer a operação, no caso:
Para um insert, será gerado um comando delete baseado em ROWID.
Para um update, será gerado um comando update contendo o ROWID + o valor anterior de cada coluna afetada pela DML.
Para um delete, será gerado um comando insert contendo o ROWID + o valor de cada coluna necessária.
Olhando para estes cenários, geralmente um comando INSERT gerará menos UNDO do que um UPDATE/DELETE. Detalhe: DMLs em colunas indexadas geram muito mais UNDO(Pois as alterações nas estruturas do index também geram UNDO), logo, na criação de tabelas que logo após a criação já receberão uma grande quantidade de dados, é interessante que quaisquer indexes que essa tabela venha a ter sejam criados posteriormente a inserção dos registros. Para uma melhor avaliação, podemos checar a coluna v$transaction.used_ublk, fazendo uma JOIN com as tabelas v$session ( v$transaction.ses_addr = v$session.saddr) e com a tabela v$open_cursor, pelo saddr também.
select c.user_name, c.SID, a.used_ublk, c.sql_text
from v$transaction a join v$session b on a.ses_addr = b.saddr
JOIN v$open_cursor c on b.saddr = c.saddr.(Consumo de UNDO para segmentos ACTIVE).
Delayed Block Cleanout:
Uma DML altera blocos do banco de dados. Segmentos de Undo são gerados para garantir que um ROLLBACK possa ser efetuado e para que a consistência na leitura seja mantida. Um pointer no cabeçalho do bloco é criado, apontando o segmento de UNDO que possui a ”before image”. O Oracle cria um mecanismo de lock para garantir que as demais sessões não possam alterar estes registros. Caso uma outra sessão necessite fazer o FETCH desses dados, o segmento de UNDO correspondente será utilizado para garantir a consistência(a menos que uma imagem consistente já exista no BUFFER Cache). Assim que a sessão responsável disparar um commit/rollback para finalizar a operação, o cleanout desses blocos pode ser feito imediamente ou posteriormente, dependendo do número de registros modificados. Apesar de um cleanout imediato (commit cleanout) ser mais eficiente(consumo de recursos) que um delayed cleanout, estes possuem um alto custo de CPU e dependendo da frequência de COMMITs, podem facilmente “custar” mais do que um delayed cleanout.
Quando um delayed cleanout ocorre, o Oracle não revisita imediatamente cada bloco modificado para atualizá-lo, apenas marca cada cabeçalho do segmento de UNDO como commitado. Quando uma outra transação utilizar algum destes blocos, verá o pointer e irá buscar a ”before image”. Ao checar que esta já foi commitada, irá realizar o cleanout no bloco.
Agora que temos os conceitos que precisamos, vamos checar possíveis cenários:
Cenário 1:
Instance configurada desta forma: UNDO_MANAGEMENT = AUTO, UNDO_RETENTION = 3600, UNDO Tablespace não configurado como AUTOEXTEND e temos uma query que levará em torno de 3 horas para ser executada.
Ponto de atenção: O fato do parâmetro UNDO_RETENTION estar setado para 1 hora não fará que a query seja encerrada por ultrapassar este threshold.
O Oracle gerencia os segmentos de Rollback de forma cíclica. Quando as extensões livres não forem suficientes , serão utilizadas as extensões que estejam em estado EXPIRED. Caso não haja, ou mais extensões sejam necessárias, primeiro haverá uma tentativa de alocar mais extensões. Caso não seja possível(por exemplo, porque chegamos no tamanho máximo permitido para o tablespace), serão utilizados as extensões em estado UNEXPIRED, porque por padrão, a prioridade é garantir que novas DMLs sejam executadas com sucesso. Neste caso, para nossa query de 3 horas, dependendo do número de DMLs ocorrendo, pode ser necessário que as extensões UNEXPIRED que necessitamos sejam sobrescritos, levando ao ORA-01555. Detalhe: Neste cenário, aonde não conseguimos mais alocar extensões, se as DMLs estiverem afetando milhares/milhões de registros sem um número adequado de commits e todos as extensões em estado EXPIRED/UNEXPIRED forem utilizados, iremos nos deparar com o ORA-30036.
Cenário 2:
Instance configurada desta forma: UNDO_MANAGEMENT = AUTO, UNDO_RETENTION = 14400, RETENTION GUARANTEE habilitado, UNDO Tablespace não configurado como AUTOEXTEND e temos uma query que levará em torno de 3 horas para ser executada.
Idem ao cenário acima, porém neste caso, quando não for possível alocar mais extensões para as DMLs utilizarem e todos as extensões em estado EXPIRED forem consumidos, o Oracle não utilizará as extensões em estado UNEXPIRED, pois o comportamento padrão de priorizar DMLs foi alterado. Logo, as DMLs começarão a falhar (ORA-30036), até que nossa query termine.
Cenário 3:
Instance configurada desta forma: UNDO_MANAGEMENT = AUTO, UNDO_RETENTION = 14400, RETENTION GUARANTEE habilitado, UNDO Tablespace configurado para AUTOEXTEND e temos uma query que levará em torno de 3 horas para ser executada.
Para este cenário, geralmente não iremos nos deparar com o ORA-01555 nem com o ORA-30036, a menos que não haja mais espaço em disco para acomodar o crescimento do tablespace.
Considerações Finais:
Existe um detalhe muito importante com relação ao parâmetro UNDO_RETENTION: Este valor é dinâmico. Há um Parâmetro “_UNDO_AUTOTUNE” introduzido na versão 10g e habilitado por padrão que permite que o Oracle possa aumentar ou mesmo ignorar o valor estipulado no parâmetro UNDO_RETENTION, conforme análise de estatísticas e do espaço de UNDO disponível. Neste caso, por exemplo, mesmo que nosso parâmetro esteja configurado para um valor de 3600, dependendo da carga atual no banco de dados, o Oracle pode aumentar este valor dinamicamente para 7200 para atender a uma query. Este parâmetro pode trazer(em casos raros) comportamentos pouco comuns para o banco, incluindo geração de ORA-01555, quando teoricamente o banco poderia acomodar a operação. Porém só é recomendado desabilitá-lo após um profundo estudo do seu ambiente e que você possa garantir que o tablespace de UNDO e os parâmetros correspondentes estejam propriamente dimensionados para acomodar as operações de sua aplicação. Os valores dinamicamente setados pelo Oracle podem ser vistos através da coluna TUNNED_UNDORETENTION na view de dicionário v$UNDOSTAT(DBA_HIST_UNDOSTAT). Nesta view ainda podem ser obtidas informações muito preciosas para prevenir a ocorrência tanto do ORA-01555 quanto do ORA-30036, como as colunas:
|
MAXQUERYID: |
Nos informa o SQL_ID da query com maior tempo de execução para aquele período(período compreendido pelas colunas BEGIN_TIME and END_TIME); |
|
MAXQUERYLEN: |
O maior Valor, em segundos, que uma query necessitou para ser executada neste período. Útil para dimensionar o parâmetro UNDO_RETENTION. |
|
UNDOBLKS: |
Mostra o número total de blocos de UNDO consumidos no período – útil para dimensionar o tablespace de UNDO; |
|
TXNCOUNT: |
Número total de transações efetuadas no período. |
|
UNXPSTEALCNT: |
Muito interessante para nosso estudo de caso. Esta coluna informa quantas vezes o Oracle tentou obter espaço em UNDO utilizando extensões em estado UNEXPIRED. Útil para dimensionar o UNDO tablespace. |
|
SSOLDERRCNT: |
Informa quantas vezes o ORA-01555 ocorreu para aquele período. Útil para dimensionar o parâmetro UNDO_RETENTION. |
|
NOSPACEERRCNT: |
Informa quantas vezes foi necessário alocar mais espaço no tablespace de UNDO e não foi possível. Muito útil para dimensionar o UNDO tablespace. |
Conclusão:
Mesmo com a opção de gerenciamento automático de UNDO habilitada, o que nos ajuda muito na administração do banco de dados já que o Oracle pode contornar situações de pico e possíveis bootlenecks automaticamente, não podemos tirar essa tarefa de nosso checklist, visto que ainda assim, problemas podem surgir. Manter vigilância sobre os tempos que as querys estão utilizando, o consumo de espaço de UNDO e a relação de blocos em estados ACTIVE/UNEXPIRED de forma pró-ativa é crucial, principalmente levando em consideração que o UNDO é um componente chave não só para a integridade dos dados, mas também para as diferentes opções que a tecnologia FLASHBACK permite.
Referências
- http://docs.oracle.com
- http://asktom.oracle.com
- https://blogs.oracle.com
- http://dbpedias.com
- http://dba-oracle.com/
