Arquivo do autor:christianodba
ENTENDENDO AS DIFERENÇAS ENTRE O DELETE E O TRUNCATE E QUANDO ESCOLHER UM OU OUTRO.
Ao precisarmos excluir uma grande quantidade de dados de uma tabela, podemos ficar em dúvida se devemos usar o DELETE (Um comando DML) ou o TRUNCATE (Um comando DDL).
Existem algumas diferenças que precisam ser levadas em consideração, bem como vantagens e desvantagens.
1. O DELETE como todo comando DML gera Redo, ocasionando uma grande carga de trabalho ao banco de dados, já que além do Redo, os dados deletados ficam gravados no UNDO do Oracle; até que a transação seja comitada, esses dados não podem ser sobrescritos. Isso acontece para que o rollback possa ser refeito, caso não tenha sido emitido um COMMIT. O TRUNCATE não gera Redo.
2. O DELETE permite sempre que seja feito o ROLLBACK da transação, caso você não tenha ainda comitado. Para isso, recomenda-se NUNCA colocar o COMMIT no final do script, para sempre termos a opção de voltarmos atrás. O TRUNCATE já faz o COMMIT automaticamente, sem opção de ROLLBACK.
3. O DELETE apaga os dados da tabela, mas a marca d’água superior da tabela permanece igual, não voltando para o início, como em uma tabela nova. O TRUNCATE ‘reseta’ a marca d’água superior, fazendo com que a tabela se comporte realmente como uma nova. Isso é significativo, porque selects que fazem full scans só lêem abaixo da marca d’água superior. Assim no DELETE, como a marca d’água permanece no mesmo local que estava quando a tabela possuía milhões de registros, assim mais leitura física é feita. O TRUNCATE reduz o scan da tabela, o que eficientemente melhora o desempenho de leitura.
4. Em casos de Delete seguidos de COMMIT, ou TRUNCATE, a única opção de recuperar os dados sem apelar para a restauração de um backup, é o recurso chamado FLASHBACK.
Podemos fazer um select de como a tabela estava antes da limpeza, como no exemplo abaixo
SQL> select sysdate from dual;
’08/03/2013 11:43:28′
SQL> delete tb_inadimplentes;
SQL> commit;
SQL> select * from tb_inadimplentes:
NO DATA FOUND
SQL> select * from tb_inadimplentes as of timestamp to_timestamp(’08/03/2013 11:40:00′,’dd-mm-yy hh24:mi:ss’);
===> dados retornados com sucesso.
Neste caso você vai conseguir recuperar seus dados. Mas esse trabalho geralmente é feito com a ajuda do DBA, já que o FLASHBACK é um recurso que precisa estar ativo no BD e você precisa ter grant para fazer a consulta de flashback.
OTIMIZANDO A CARGA DE DADOS EM TABELAS
Se temos uma carga de dados para ser feita em uma tabela, podemos tomar algumas medidas que podem otimizar o processo, gerando menos redo e consequentemente, menor carga de trabalho ao banco de dados.
Podemos tomar as seguintes medidas:
1) Alterar a tabela em questão, modificando o atributo LOGGING para NOLOGGING.
Isso deve ser feito pelo DBA, o comando segue abaixo:
SQL> alter table ‘tabela’ nologging;
2) Usar os hints:
a) INSERT /*+ APPEND */ para tabelas que usam subqueries para determinar que dados serão inseridos;
b) INSERT /*+ APPEND_VALUES */ em queries que usam a cláusula VALUES.
3) Usar o comando CREATE TABLE… AS SELECT
Vamos aos exemplos:
SQL> insert /*+APPEND */ into tb_teste select * from tb_producao;
O caminho direto combinado com o atributo NOLOGGING da tabela usada gera uma quantidade mínima de Redo.
OBS: Se você não entende o mecanismo de Redo do Oracle, segue uma breve explicação:
Um banco de dados Oracle é composto pelos seguintes componentes:
a) Arquivo de Controle;
b) Arquivos de Dados;
c) Arquivos de Redolog Online:
As transações feitas num banco Oracle não gravam diretamente nos arquivos físicos, porque isso geraria um gargalo de disco enorme. Ao invés disso, os dados são armazenados em uma área de memória chamadA DABABASE BUFFER CACHE, ou seja, os blocos são lidos no disco, armazenados em cache para posteriormente serem gravados de volta.
Para proteger as transações e atender aos requisitos do ACID (Atomicidade,Consistência,Indivisibilidade e Durabilidade), o Oracle armazena todas as alterações em outro cache chamado LOG BUFFER. Essas alterações gravam por sua vez nos arquivos físicos chamados Redolog Online, de tempos em tempos, de acordo com um algoritmo específico. Esses dados servem para a eventual recuperação da instância em caso de um desligamento acidental. Isso evita a perda de dados. Isso garante que toda alteração que foi comitada não será perdida, e que toda transação não comitada sofrerá rollback.
No caso de uma carga de dados direta, desativar a geração de redo na tabela alvo diminui o esforço do Banco de Dados, visto será gerado redo mínimo, ou seja, menos recursos serão usados, menos passos serão dados para o insert ter sucesso.
3) Usar o SLQLoader também é uma excelente opção de carga de dados em massa, mas essa ferramenta merece um artigo mais detalhado.
Bibliografia: Oracle Database 11gR2 – Performance Tuning Cookbook (Sam Alapati,Darl Kuhn, Bill Padfield)
Otimizando meu SQL
Gostaria de compartilhar algumas dicas que podem ajudar a otimizar o código SQL. São dicas básicas, que muitas vezes passam batido na hora de implementar novos objetos no banco, ou alterar objetos já existentes.
1. Alias: Sempre utilize alias (Apelidos) na suas tabelas Em joins, o Oracle precisa procurar no Dicionário de Dados informações sobre as tabelas existentes, para saber qual delas contém o campo que estamos nos referindo. Mesmo que não haja ambiguidade, ao colocarmos alias, já estamos dizendo ao Oracle onde está o campo.
Ex:
select e.first_name,e.last_name,e.email,d.department_name
from employees e inner join departments d
on e.department_id=d.department_id
where d.department_id=30;
2. Índices: Os índices são fundamentais para a pesquisa. Em uma tabela com milhões de registros, o Oracle não precisará lê-la toda para procurar os dados que desejamos na cláusula WHERE. O índice já apontará para quais linhas precisamos. Estude sempre o plano de execução da sua consulta, para saber quando e onde criar os seus índices.
3. Union All: Union ALl serve para juntar em um mesmo result set dados de tabelas com estruturas similares. Por exemplo, imagine duas tabelas EMPLOYEES e OLD_EMPLOYEES. Ambas têm os mesmos campos, mas dados diferentes. A forma que temos de trazer esses dados em um único select é com o Union. A diferença entre Union e Union All é que o segundo traz todos os dados sem classificá-los, o que economiza tempo e recurso. O Union primeiro vai classificar os dados e eliminar registros duplicados. Isso pode causar bastante demora e lentidão. Quando você não se importar com valores repetidos, ou quando não houver, use Union All.
4. Join: Ao fazer joins com a sintaxe proprietária da Oracle (Usando as tabelas na cláusula FROM separadas por vírgulas, sempre organize de forma que a menor seja a última. Por exemplo:
Tabela VENDEDORES- 500 registros
Tabela CIDADES – 120 registros
Tabela MUNICÍPIOS – 5560 registros
Tabela ESTADOS – 26 registros
O correto seria armar nossa consulta assim: FROM municipios,vendedores,cidades,estados WHERE…
O Oracle irá pesquisar primeiro a tabela estados, que contém apenas 26 registros, o que diminuirá a quantidade de registros que precisará ser lida nas tabelas subsequentes. Se tivéssemos colocado a maior por último, ela seria lida toda ou, na melhor das hipóteses, supondo que houvesse um índice nela a ser usado, seria lido, para cada registro encontrado nela, o equivalente nas tabelas subsequentes. Essa dica, vale para as versões 9i e anteriores que usam o otimizador baseado em regras. A partir do 10g, o otimizador padrão do Oracle é baseado em custo, o que faz com que as estatísticas coletadas regularmente pelo banco dêm condições do otimizador escolher o melhor caminho, o que com bastante frequência é o melhor.
5. NULLs: Quando usamos IS NULL ou IS NOT NULL na cláusula WHERE, o índice é ignorado, pois NULL é um valor desconhecido, não possuindo entrada no índice (A não ser que seja um índice de Bitmap). Normalmente o NULL causa uma leitura full da tabela.
6. NOT EQUAL, <> e != Tais operadores geralmente levam a uma leitura full da tabela, ainda que haja um índice criado. Se o otimizador for baseado em custo, e houver estatísticas atualizadas, alguns valores na cláusula WHERE podem usar índices, mas é preferível evitar operadores de desigualdade.
7. Funções: Se usarmos uma função na cláusula WHERE, o Oracle ignorará o índice, a menos que seja um índice baseado em função. O exemplo abaixo forçará uma leitura completa, mesmo que haja um índice na coluna hiredate.
select e.employee_id, e.first_name, e.department_id
from employees e
where TRUNC(e.hire_date) = ’07/06/2002′
Há ainda muitas outras dicas sobre otimização de SQL. O importante é estamos sempre procurando a melhor forma de executarmos nossas consultas. Se tem uma coisa que deixa um DBA nervoso é ele ver um TABLE ACCESS FULL em um Explain Plain.
Processos em Segundo Plano no Oracle – Continuação
3. CKPT = Checkpoint
Os checkpoints forçam o flush do Database Buffer para os arquivos de dados, atualizando esses arquivos com todos os vetores de alterações que estavam somente em memória. No caso da necessidade de recuperação da instância, por uma falha de energia, por exemplo, isso reduz o tempo necessário para recuperar o banco de dados. Desde a versão 8i, o Checkpoint não ocorre mais de forma completa, fazendo o flush de todo o buffer; em vez disso, são feitos checkpoints incrementais, avançando o ponto de recuperação mais adiante. O DBWn faz checkpoints incrementais, em uma taxa que balanceia entre o seu algoritmo preguiçoso e o LGWr que grava praticamente em tempo real. O endereço de byte de redo (RBA) posiciona o checkpoint mais á frente. Esse ó ponto onde uma recuperação começará se for necessário. O CKPT também atualiza continuamente o Control File com a posição atual do Checkpoint. Para finalizar, é preciso saber que um checkpoint total só ocorre em dois casos: Por um comando explícito do DBA, ou em um shutdown ordenado do banco de dados, onde todos os vetores são aplicados e o Control File é atualizado com o último RBA.
Processos em Segundo Plano no Oracle
Os principais processos em uma instância Oracle são descritos a seguir:
1. DBWn = Database Writer.
Em uma instância Oracle, os processos de usuários (Sessões abertas pelo usuário através de uma conexão AD-HOC ao banco ou uma aplicação) não escrevem diretamente nos arquivos de Dados. Em vez disso, eles solicitam ao Pmon (Proccess Monitor) que é quem faz o acesso ao servidor. Os dados são lidos a primeira vez nos arquivos de dados para, então, serem alocados Cache de Buffer do Banco (Em memória). Quando esses dados forem solicitados novamente, estarão disponíveis no cache, cujo acesso é bem mais rápido do que em discos magnéticos. Isso evita gargalos de I/O no servidor. Acontece que quando uma sessão de usuário altera dados em uma tabela, ela está alterando os dados no Cache de Buffer, em memória. Esses dados precisam ainda voltar para os arquivos de dados, para que não sejam perdidos. Esse é o papel do DBWn. Podemos ter até 20 processos DBWn iniciados em uma instância Oracle. De tempos em tempos ele lê o buffer e grava os dados em disco. Seu algoritmo garante que ele grave a menor quantidade de dados possível o mais raramente possível. Quatro fatores fazem o DBWn gravar:
a. Nenhum buffer livre,
b. Muitos buffers sujos (Que estão mais atualizados que os arquivos de dados),
c. Um intervalo de 3 segundos,
d. Um comando checkpoint.
Você pode estar se perguntando “como garantiremos que dados não sejam perdidos, já que a gravação do DBWn não é em tempo real?”. Temos um outro processo chamado LGWR.
2. LGWR = Log Writter
O LGWR é o processo responsável por gravar todas as alterações feitas aos dados nos Redo Log Files. Todas as alterações feitas aos dados são primeiramente gravadas no Buffer de Log. Esse processo é feito praticamente em tempo real, tendo em vista que é nele que está a garantia de que o Oracle não perderá informação no caso de uma falha da instância. Veja que os arquivos de dados do banco estarão sempre desatualizados com relação ao buffer, porque o DBWn grava o mais raramente possível. Já o LGWR grava praticamente em real time. Isso significa que no caso de uma recuperação, os Redo Log serão aplicados aos arquivos de dados para evitar a perda de informações. O LGWR faz flush dos dados do Log Buffer para os arquivos Redo Log em três situações:
a. Um comando commit emitido,
b. Quando o Buffer de Log está 1/3 ocupado,
c. Exatament antes do DBWn gravar no arquivo de dados.
Aprendendo sobre o Oracle.
No primeiro post, vamos falar sobre a estrutura do banco de dados Oracle. O que compõe um sevidor Oracle? A instância e o Banco de Dados.
Enquanto a instância está alocada somente na memória, o Banco de Dados é composto de arquivos físicos (Arquivo de Controle, Arquivos de Dados e os Arquivos de Redo Log Online).
A instância é composta por processos que rodam em memória. Nela estão configuradas as áreas de memória do Servidor Oracle, a SGA e a PGA. Quando um servidor Oracle é desligado abruptamente, ao ser reiniciado, o servidor executa a recuperação da instância; isso porque muitas alterações feitas pelos usuários ainda não foram gravadas nos arquivos de dados. Veremos sobre recuperação posteriormente.
A SGA é a ÁREA GLOBAL DO SISTEMA. Ela tem outras subdivisões, mas sua característica principal é que ela é a área compartilhada por todas as sessões abertas no Banco de Dados. Ela é composta principalmente de:
a. Cache de buffer do Banco de Dados;
b. Buffer de Log;
c. Shared Pool.
Como itens opcionais, mas que geralmente são utilizados temos:
d. Large Pool
e. Java Pool
f. Streams Pool.
Quando uma instância Oracle é inicada através do comando startup, O arquivo de parâmetros é lido. Nele estão informações sobre o tamanho da memória que deve ser alocada para a Instância Oracle. A seguir, vamos ver cada um de seus componentes:
a. Cache de buffer do Banco de Dados: Quando uma sessão de usuário lê ou altera dados no Oracle, ela de fato está acessando o buffer do cache de banco de dados, não os arquivos de dados no Disco. O objetivo principal é otimizar o desempenho, evitando I/O em leituras físicas (disco). De tempos em tempos o processo chamado DBWn grava esses dados (buffers) em disco (Quando o buffer está cheio, quando há muitos dados alterados que ainda não foram gravados – Buffers sujos, um timeout de 3 segundos, ou através de um checkpoint).
b. Buffer de Log: É uma área de preparação pequena, para os vetores de alteração, antes que eles sejam gavados nos arquivos Redo Log Online (Mais sobre eles mais tarde). Esse mecanismo é uma garantia de que os dados não serão perdidos nunca, visto que os Redo Log Online armazenam dados que são aplicados aos arquivos de dados no caso de recuperação da Instãncia após uma queda. O Buffer de Log é gravado nos arquivos de Log Online praticamente em tempo real. Ao ser emitido um comando COMMIT, a gravação é de fato em tempo real.
c. Shared Pool: Esta é a estrutura mais complexa da SGA. Ela é subdividida e:
c1. Cache de Biblioteca;
c2. Cache do Dicionário de Dados;
c3. Área PL/SQL;
c4. Cache de resultado de SQL e de funções PL/SQL.
c1. Cache de Biblioteca: Armazena código executado em período recente. Poupa esforço do Oracle em analizar por parse todo o código. Se houver alteração, o conteúdo do cache é descartado, e um novo é gravado no lugar.
c2. Cache Dicionário de Dados: Armazena as definições dos objetos usados recentemente. A estrutura das tabelas, índices e outros metadados.
c3. Área PL/SQL: Quando um objeto PL/SQL é invocado por uma sessão ele é, a princípio, lido no Dicionário de Dados (Leitura Física). Então é armazenado em cache, o que gera menos I/O e agiliza o acesso.
c4. Cache de resultado de SQL e de funções PL/SQL: Novo no Oracle 11g, Armazena o resultado de consultas SQL e funções PL/SQL, novamente para otimizar o acesso, evitando sempre leitura em disco de dados que foram consultados há pouco. Em caso de alteração, o mecanismo do Oracle invalida os dados em cache, e executa uma nova consulta.
Quanto aos itens que não são obrigatórios, mas geralmente são usados na Instância, vejamos sua descrição:
d. Large Pool: É uma área opcional usada por vários processos que, de outra forma, usariam o Shared Pool, como por exemplo o RMAN (Utilitário de backup), ou o Shared Server (Servidor compartilhado) e alguns outros.
e. Java Pool: Usada para instanciar objetos Java quando a aplicação tiver procedures Java no Banco de Dados. Mesmo sendo opcional, o Java Pool tem sido um padrão nas últimas versões do Oracle.
f. Streams Pool: É utilizada quando do recurso Oracle Streams está configurado no banco de dados. É um recurso de replicação do banco de dados para um banco remoto.
Além da SGA, temos também a PGA – PROGRAM GLOBAL AREA: A PGA é uma área de memória que é inicializada para cada sessão conectada ao Oracle. É uma área privada, que contém dados sobre a sessão.
Essas são, de forma resumida, as estruturas de memória usadas por uma Instância Oracle. No próximo artigo, descreveremos os processos mais conhecidos em uma instância Oracle. Esses processos também fazem parte da Instância.
Christiano Rios DBA 