monitorando o uso temporário da Oracle
parece que o espaço de tabela temporário da Oracle ainda é um mistério para alguns, possivelmente porque não é gerenciado da mesma maneira que um espaço de tabela convencional. Extensões são alocadas e reutilizadas para que o gerenciamento de espaço não se pareça com isso para um tablespace tradicional. Por causa disso, os erros de espaço são gerenciados de maneira diferente. Vejamos como gerenciar um espaço de tabela temporário e quais ferramentas estão à disposição do DBA.
começando com a versão 8.1.5 a Oracle forneceu um espaço de tabela temporário ‘verdadeiro’ onde as extensões são alocadas e atribuídas às sessões conforme necessário. As extensões nunca são descartadas enquanto o banco de dados está sendo executado, portanto, um espaço de tabela temporário utilizado corretamente sempre terá 0 espaço ‘livre’. Nesse sentido, ‘livre’ significa não alocado, e tudo no espaço de tabela temporário será alocado, mas isso não significa que esteja sendo usado. É por isso que o gerenciamento de espaço convencional não tem lugar no espaço de tabela temporário.
então, como o tablespace temporário é gerenciado? É feito através de várias visualizações V$ :
V$TEMPFILE V$TEMPSTAT V$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL V$TEMP_SPACE_HEADER V$TEMPSEG_USAGE (Oracle 9i e versões posteriores) V$SORT_USAGE (Oracle 8.1.7, 8.1.6 e 8.1.5)
V$TEMP_EXTENT_MAP relatórios de todas as extensões alocadas na pasta temporary tablespace:
SQL> desc v$temp_extent_map Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER OWNER NUMBER RELATIVE_FNO NUMBER
O mais útil vistas para gerenciar tablespaces temporários são V$TEMP_EXTENT_POOL e V$TEMPSEG_USAGE/V$SORT_USAGE. V $ TEMP_EXTENT_POOL lista não apenas as extensões alocadas, mas também relata todas as extensões usadas em um tablespace temporário por tempfile:
SQL> desc v$temp_extent_pool Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_ID NUMBER EXTENTS_CACHED NUMBER EXTENTS_USED NUMBER BLOCKS_CACHED NUMBER BLOCKS_USED NUMBER BYTES_CACHED NUMBER BYTES_USED NUMBER RELATIVE_FNO NUMBER
V$TEMPSEG_USAGE (ou, se preferir, V$SORT_USAGE) mostra o segmento temporário de utilização, pelo usuário, para todos os tablespaces:
SQL> desc v$tempseg_usage Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) USER VARCHAR2(30) SESSION_ADDR RAW(8) SESSION_NUM NUMBER SQLADDR RAW(8) SQLHASH NUMBER SQL_ID VARCHAR2(13) TABLESPACE VARCHAR2(31) CONTENTS VARCHAR2(9) SEGTYPE VARCHAR2(9) SEGFILE# NUMBER SEGBLK# NUMBER EXTENTS NUMBER BLOCKS NUMBER SEGRFNO# NUMBER
Temporary tablespace de monitoramento de uso é uma tarefa simples de consultar V$TEMPSEG_USAGE (V$SORT_USAGE, se for de sua preferência) ao longo do tempo:
select segtype, extents, blocksfrom v$tempseg_usagewhere tablespace = 'TEMP';
Lembre-se de que tal monitoração irá produzir dados úteis; a menos que o banco de dados está relatando erros de alocação de espaço de temp o DBA realmente não tem nada para fazer.
se você quiser saber quantas extensões são alocadas e realmente usadas por arquivo de dados, a consulta a seguir produzirá um relatório bastante útil:
select tablespace_name, file_id, extents_cached extents_allocated, extents_used, bytes_cached/1024/1024 mb_allocated, bytes_used/1024/1024 mb_usedfrom v$temp_extent_pool/
mais uma vez, nenhuma ação precisa ser tomada com base em tal relatório; é apenas para fins informativos.
quem está usando seu espaço temporário, quais consultas eles estão executando e quanto desse espaço cada um está consumindo? Essa também é uma tarefa bastante fácil de concluir:
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocksfrom v$tempseg_usage u, v$sql swhere s.sql_id = u.sql_id;
a visualização V $ TEMP_SPACE_HEADER fornece uma visualização menos granular do espaço consumido e disponível; ele lista o espaço alocado e não alocado nos arquivos temporários, por arquivo. Tenha em mente que alocado não significa usado; provavelmente haverá grandes diferenças entre o que v$TEMP_SPACE_HEADER relata e o que v$TEMPSEG_USAGE fornece.
vou citar-me (de outro lugar na web) no que diz respeito ao dimensionamento adequado de espaços de tabela temporários: “então, quanto espaço você precisa em seu espaço de tabela temporário? Isso dependeria de quão ativo é o seu sistema, quantas sessões ativas simultâneas existem, o tamanho das transações e quanto espaço em disco você tem. Não é uma desgraça aumentar o tamanho da tabela temporária ao longo do tempo, à medida que os padrões de uso, o número de usuários e os volumes de dados mudam. A Oracle também informará que o espaço de tabela temporário precisa ser aumentado emitindo erros ORA – 01652 (incapaz de estender o segmento temporário em 128 no espaço de tabela temporário, por exemplo). O número relatado em um erro ORA-01652 está em blocos, não bytes, então você precisará converter isso usando o valor db_block_size para saber quantos bytes o espaço de tabela temporário necessário para concluir a transação gerando o erro. Não há ‘ regra prática ‘para dimensionar um espaço de tabela temporário porque essas regras geralmente criam situações em que a única ferramenta se torna um martelo e cada tarefa acaba como um prego e, na maioria das vezes, você bate nesse polegar com a única’ ferramenta ‘ que você recebeu.”
DBA_TEMP_FILES lhe dirá quão grande é o espaço de tabela temporário:
SQL> select tablespace_name, sum(bytes)/1024/1024 MB 2 from dba_temp_files 3 group by tablespace_name 4 /TABLESPACE_NAME MB------------------------------ ----------TEMP 1024SQL>
ele também pode relatar quais arquivos estão associados ao seu espaço de tabela temporário:
SQL> select tablespace_name, file_name, bytes 2 from dba_temp_files 3 order by tablespace_name, file_name 4 /TABLESPACE_NAME FILE_NAME BYTES--------------- ------------------------------------------------------- ----------TEMP /u2/orawiz/parlopnett/temp01.dbf 1073741824SQL>
Se você quiser, ou precisar, para diminuir o tamanho do tablespace TEMP você precisa fechar o banco de dados para baixo, abra-a no modo restrito, redimensionar a tempfile (ou tempfiles) menor, em seguida, desligamento e inicialização do banco de dados. Adicionar espaço não está tão envolvido, pois um simples ‘alterar redimensionamento do arquivo temporário do banco de dados;’ aumenta o espaço.
o que acontece se o espaço de tabela temporário não existir ou não estiver disponível? A Oracle exibirá isso:
SQL> select * from mybigtable order by mycoolkey;select * from mybigtable order by mycoolkey*ERROR at line 1:ORA-01129: user's default or temporary tablespace does not exist SQL>
se você vir Esta mensagem de erro, verifique se o espaço de tabela temporário existe e está online. Corrija quaisquer problemas encontrados (crie o espaço de tabela ausente ou coloque os arquivos temporários novamente online).
se o espaço de tabela existir, mas nenhum arquivo temporário for atribuído a ele (o que pode ocorrer após uma restauração/recuperação de banco de dados de um backup), o Oracle reports:
SQL> select * from mybigtable order by mycoolkey;select * from mybigtable order by mycoolkey*ERROR at line 1:ORA-25153: Temporary Tablespace is Empty SQL>
portanto, a tarefa é simplesmente emitir ‘alterar tablespace temporário … adicione tempfile …’comandos para deixar o espaço de tabela vazio.
gerenciar o espaço de tabela temporário não é ciência de foguetes, mas difere (às vezes consideravelmente) dos procedimentos de gerenciamento de espaço de tabela ‘normais’ empregados para arquivos de dados regulares. Saber que um espaço de tabela temporário ‘completo’ não é um problema (lembre-se de que os segmentos são alocados e reutilizados) pode tornar a tarefa um pouco mais fácil, pois o Oracle lhe dirá (com erros ORA-01652) se você ficou sem espaço. O que significa que o DBA pode gastar tempo em tarefas úteis, como redefinições de senha.
ver todos os artigos de David Fitzjarrel