No artigo de hoje irei comentar sobre um assunto que foi parte do meu trabalho na semana passada: o gerenciamento do armazenamento e desempenho das colunas LOB (Large Object), mais especificamente, os tipos de dados CLOB (Character LOB) e BLOB (Binary LOB). Para melhor entendimento deste artigo, é necessário ter conhecimento prévio dos tipos de dados LOB (CLOB, BLOB etc.).
Para os DBAs que já administram ou que passarão a administrar tabelas com tipos de dados LOB, ressalto que por questões de performance e manutenção, vale a pena separar o armazenamento das colunas LOB em um tablespace próprio, ou seja, em um tablespace separado da tabela. Quando colunas do tipo CLOB ou BLOB são armazenadas no mesmo tablespace da tabela e quando essas colunas armazenam mais que 4000 bytes ou 4000 caracteres em cada linha da tabela, elas causam fragmentação dos datafiles e consequentemente sérios problemas de performance.
Para demonstrar um pouco destes conceitos, vamos criar uma tabela chamada CLIENTE, que contém uma coluna do tipo BLOB para armazenar as fotos de cada cliente. Segue abaixo o código DDL para criar a tabela, a coluna BLOB e o índice da coluna BLOB (criado automaticamente em colunas do tipo CLOB e BLOB) em tablespaces separados:
CREATE TABLE CLIENTE ( id NUMBER,
nome VARCHAR2(50),
foto BLOB)
LOB (FOTO) STORE AS lb_foto (TABLESPACE users_l
INDEX ix_foto (TABLESPACE users_i))
TABLESPACE users_d;
CREATE TABLE CLIENTE ( id NUMBER,
nome VARCHAR2(50),
foto BLOB)
LOB (FOTO) STORE AS lb_foto (TABLESPACE users_l
INDEX ix_foto (TABLESPACE users_i))
TABLESPACE users_d;
Observações:
- A tabela será armazenada no tablespace USERS_D;
- A coluna foto do tipo BLOB será armazenada no tablespace USERS_L;
- O índice da coluna foto será armazenado no tablespace USERS_I. O índice, assim como a coluna BLOB, também está sendo armazenado em um tablespace separado para facilitar o gerenciamento.
Agora iremos ver a parte mais intrigante deste artigo. Supondo que a tabela CLIENTE possui 1000 linhas e que cada linha tem um tamanho de 1 MB, a tabela terá um tamanho total de 1000 MB . Em cada linha, a coluna foto tem um armazenamento médio de 980 kbytes. Se executarmos uma instrução para apagar as 100 primeiras linhas (Ex.: DELETE FROM CLIENTE WHERE ROWNUM <101), com que tamanho físico irá ficar a tabela?
1- 1000 MB;
2- 995,70 MB.
3- 900 MB;
A resposta correta é a opção 1. Fisicamente falando, a tabela continuará ocupando blocos nos tablespaces de dados e índices até que eles sejam reutilizados (sobrescritos). Um dos procedimentos para liberar o espaço físico das tabelas é a sua compactação através do conhecido SHRINK e depois um resize no(s) datafile(s) referentes ao seu(s) tablespace(s) Se executarmos em seguida um SHRINK na tabela, fisicamente ela inda ocupará o espaço da opção 2, pois o SHRINK só libera o espaço das colunas não lob. Para liberar todo o espaço ocupado pelas linhas que foram deletadas, é necessário compactar também cada coluna LOB que a tabela possui. Neste caso, somente após executar o SHRINK na tabela e na coluna LOB é que a gente vai chegar ao tamanho de 900 MB, da opção 3.
Para liberar o espaço não utilizado pelas linhas apagadas na coluna LOB, temos que compactar a coluna foto, executando o comando abaixo:
ALTER TABLE CLIENTE MODIFY LOB (FOTO) (SHRINK SPACE);
É importante ressaltar que, por padrão, dados do tipo LOB serão armazenados no mesmo segmento da tabela se eles tiverem até 4000 bytes ou 4000 caracteres (dependendo da configuração do BD). Se você quiser, você pode alterar a definição da coluna para que ela sempre tenha o seu próprio segmento, executando o comando abaixo:
ALTER TABLE CLIENTE MODIFY LOB (FOTO) STORE AS (DISABLE STORAGE IN ROW);
Outra dica para quem quer melhorar o desempenho no acesso às colunas do tipo LOB, é habilitar o cache, que por padrão é desabilitado em LOB. A exceção só ocorre se os dados tiverem menos que 4000 bytes ou 4000 caracteres, pois nestes casos eles são armazenados no mesmo segmento da tabela, usando também a definição de CACHE. Quando uma instrução SQL referencia uma linha de uma coluna do tipo LOB (maior que 4000 bytes ou caracteres), o BD acessa os dados diretamente do(s) datafile(s) e devolve o resultado para o cliente. Na configuração padrão, os dados não vão para a Buffer Cache, portanto, toda vez que você acessar uma coluna do tipo LOB, você estará fazendo I/O físico. Para otimizar o seu desempenho, na definição da coluna, especifique que ela seja armazenada em cache. Deste modo, o I/O físico minimiza, sendo substituído por I/O lógico, que é muito mais rápido! Tome apenas 1 cuidado. Colunas do tipo LOB possuem muitos dados. Certifique-se antes de que a Buffer Cache do BD terá espaço suficiente para comportar os dados da coluna. Segue abaixo um exemplo para modificar a definição de uma coluna do tipo LOB:
CONCLUSÃO
Tabelas com colunas LOB requerem cuidados especiais! Para facilitar o gerenciamento, melhorar performance e evitar fragmentação de linhas, armazene as colunas LOB em um tablespace separado. Se você tem tabelas com colunas LOB que sofrem muitas deleções, é necessário criar rotinas de manutenção periódicas para compactar as colunas LOB e liberar espaço de armazenamento não utilizado pelas linhas que foram deletadas.
Em um dos BDs de produção que eu administro, temos um sistema que possui uma tabela temporária que tem uma coluna do tipo BLOB. Por ser uma tabela temporária, ocorrem constatemente muitas inserções e deleções nesta tabela. A tabela é de um sistema desenvolvido por terceiros. Na semana passada verifiquei que este sistema havia crescido muito mais do que o esperado nos últimos meses. Descobri que a causa do crescimento da da tabela temporária foi porque ela estava sendo muito utilizada e porque não estava sendo liberado o espaço da coluna BLOB das linhas deletadas. Todos os dados do sistema (sem índices) estavam ocupando 33 GB de espaço de armazenamento em seu respectivo tablespace. Após compactar a coluna BLOB, foram liberados aproximadamente 18 GB. Depois de compactar a tabela, para liberar espaço físico em disco, tive que redimensionar o(s) datafile(s) do tablespace LOB.
A dica da compactação em LOBs é muito útil, pois pode ajudar a melhorar o desempenho de sistemas, liberar espaço em disco e até mesmo diminuir o tempo dos backups.
1- 1000 MB;
2- 995,70 MB.
3- 900 MB;
A resposta correta é a opção 1. Fisicamente falando, a tabela continuará ocupando blocos nos tablespaces de dados e índices até que eles sejam reutilizados (sobrescritos). Um dos procedimentos para liberar o espaço físico das tabelas é a sua compactação através do conhecido SHRINK e depois um resize no(s) datafile(s) referentes ao seu(s) tablespace(s) Se executarmos em seguida um SHRINK na tabela, fisicamente ela inda ocupará o espaço da opção 2, pois o SHRINK só libera o espaço das colunas não lob. Para liberar todo o espaço ocupado pelas linhas que foram deletadas, é necessário compactar também cada coluna LOB que a tabela possui. Neste caso, somente após executar o SHRINK na tabela e na coluna LOB é que a gente vai chegar ao tamanho de 900 MB, da opção 3.
Para liberar o espaço não utilizado pelas linhas apagadas na coluna LOB, temos que compactar a coluna foto, executando o comando abaixo:
ALTER TABLE CLIENTE MODIFY LOB (FOTO) (SHRINK SPACE);
É importante ressaltar que, por padrão, dados do tipo LOB serão armazenados no mesmo segmento da tabela se eles tiverem até 4000 bytes ou 4000 caracteres (dependendo da configuração do BD). Se você quiser, você pode alterar a definição da coluna para que ela sempre tenha o seu próprio segmento, executando o comando abaixo:
ALTER TABLE CLIENTE MODIFY LOB (FOTO) STORE AS (DISABLE STORAGE IN ROW);
Outra dica para quem quer melhorar o desempenho no acesso às colunas do tipo LOB, é habilitar o cache, que por padrão é desabilitado em LOB. A exceção só ocorre se os dados tiverem menos que 4000 bytes ou 4000 caracteres, pois nestes casos eles são armazenados no mesmo segmento da tabela, usando também a definição de CACHE. Quando uma instrução SQL referencia uma linha de uma coluna do tipo LOB (maior que 4000 bytes ou caracteres), o BD acessa os dados diretamente do(s) datafile(s) e devolve o resultado para o cliente. Na configuração padrão, os dados não vão para a Buffer Cache, portanto, toda vez que você acessar uma coluna do tipo LOB, você estará fazendo I/O físico. Para otimizar o seu desempenho, na definição da coluna, especifique que ela seja armazenada em cache. Deste modo, o I/O físico minimiza, sendo substituído por I/O lógico, que é muito mais rápido! Tome apenas 1 cuidado. Colunas do tipo LOB possuem muitos dados. Certifique-se antes de que a Buffer Cache do BD terá espaço suficiente para comportar os dados da coluna. Segue abaixo um exemplo para modificar a definição de uma coluna do tipo LOB:
ALTER TABLE tabela MODIFY LOB(coluna) (CACHE);
CONCLUSÃO
Tabelas com colunas LOB requerem cuidados especiais! Para facilitar o gerenciamento, melhorar performance e evitar fragmentação de linhas, armazene as colunas LOB em um tablespace separado. Se você tem tabelas com colunas LOB que sofrem muitas deleções, é necessário criar rotinas de manutenção periódicas para compactar as colunas LOB e liberar espaço de armazenamento não utilizado pelas linhas que foram deletadas.
Em um dos BDs de produção que eu administro, temos um sistema que possui uma tabela temporária que tem uma coluna do tipo BLOB. Por ser uma tabela temporária, ocorrem constatemente muitas inserções e deleções nesta tabela. A tabela é de um sistema desenvolvido por terceiros. Na semana passada verifiquei que este sistema havia crescido muito mais do que o esperado nos últimos meses. Descobri que a causa do crescimento da da tabela temporária foi porque ela estava sendo muito utilizada e porque não estava sendo liberado o espaço da coluna BLOB das linhas deletadas. Todos os dados do sistema (sem índices) estavam ocupando 33 GB de espaço de armazenamento em seu respectivo tablespace. Após compactar a coluna BLOB, foram liberados aproximadamente 18 GB. Depois de compactar a tabela, para liberar espaço físico em disco, tive que redimensionar o(s) datafile(s) do tablespace LOB.
A dica da compactação em LOBs é muito útil, pois pode ajudar a melhorar o desempenho de sistemas, liberar espaço em disco e até mesmo diminuir o tempo dos backups.
Por hoje é só!
[]s
Fábio, bom dia!
ResponderExcluirVi que você comenta sobre o campo Blob no seu Blog, porém gostaria de saber qual o melhor Data Type para utilizar para gravação de imagens no banco, no caso seria imagem dos produtos, estava utilizando o Data Type LONG RAW. Dei uma pesquisada na internet mas não achei comparações claras entre BLOB e LONG RAW, saberia qual o melhor Data Type para esta situação?
Rodrigo, escolha BLOB ao invés de LONG RAW. BLOB é um tipo de dado mais novo que LONG RAW e por isso possui várias vantagens que vc encontrará no link http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i3237.
Excluir[]s
Bom dia, estamos tendo problemas com muitos inserts de de imagens onde acaba travando as sessões do banco. Apenas derrubando todas as sessões o banco volta a ficar disponível. Tem alguma dica para isso ?
ResponderExcluirDesculpe a demora, mas só vi hoje essa pergunta. Matar as sessões não deverá resolver o problema pela raiz. Sugiro analisar melhor o que está ocorrendo no BD e nessas sessões para tentar resolver o problema. Não tenho uma receita de bolo pronta para dizer o que fazer... são muitas coisas... mas ensino boa parte delas em meus treinamentos "Database Performance Tuning".
ExcluirBoa tarde Fábio.
ResponderExcluirVocê disse "Certifique-se antes de que a Buffer Cache do BD terá espaço suficiente para comportar os dados da coluna"
Como verifico se o Buffer Cache tem o espaço necessário?
Veja o tamanho da sua SGA e Buffer Cache fazendo consultas em visões de performance dinâmicas, tais como a v$sgastat e calcule o tamanho total dos dados da coluna que poderiam ser carregadas em memória consultando por exemplo a visão dba_segments. Existem muitas outras formas de verificar isso e explico boa parte delas nos treinamentos "Database Performance Tuning" (http://www.fabioprado.net/p/database-performance-tuning.html).
Excluir