Tempfile always with size on the limit

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Post Reply
albertomattos
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Thu, 01 Oct 2015 1:51 pm

Dear colleagues DBA's, I have a question: My bank is always with tempfile at the limit of 32 GB. Is this harmful to the Bank's performance? Or is it normal? I do not see errors in the applications. But whenever I create a new file and exclude the previous one, it quickly grows for 32 GB. Is there any form of this file "suffer a cleaning" from time to time, or just dropping and creating a new one?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

It is not harmful.
The temp file is used for temporary operations, type, sort, index, distinct, group by, etc.
When it does not fit in memory, he saves there.

After using, it releases for other temporary operations. That's why it grows fast and then at the 32GB limit.

(let's wait for a DBA to answer more technically) : Roll:
User avatar
fbifabio
Moderador
Moderador
Posts: 199
Joined: Tue, 22 Feb 2011 1:51 pm
Location: São Paulo - SP
Contact:
Fábio Prado
www.fabioprado.net

Being on the use limit from time to time is no problem, the problem is if it is always at the limit, this can affect the performance of many SQLs in the BD. Evaluate the use by running multiple times during the SQL below.

- See Space in use or maximum already used by Tablespace Temp:

Select all

select    a.tablespace_name, b.Total_MB, 
          b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB, 
          round(used_blocks*8/1024)                Current_Used_MB, 
          round(max_used_blocks*8/1024)             Max_used_MB 
from      v$sort_segment a, 
          (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

Perform a cleaning in it may not help you a lot as it is already recycled automatically, which may need to do if it is in the limit is to increase the size of it or create new temporary tablespaces by assigning them to specific users or creating a group of temporary tablespaces.


Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 3 guests