Hi gurus,
I'm using 11.2.0.3.
Since yesterday, one query fills my temporary tablespace. I know it's the sorting operation is the culprit.
However, this query is run everyday for many months without trouble, but yesterday it filled up the temp tablespace which was 2500MB. I extended it to 4096MB but it filled up too.
The query result is about 60.000 rows which have to be sorted.
2 days ago, it was about 57.000.
Since the statement is the same, I can't believe that a difference of 3.000 rows needs more than 2GB to be processed :/
I use this query to monitor temp usage:
{code}select 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;{code}
pga_aggregate_target is 200MB
I have 2 questions:
1) Why, when running the query as sysdba, the temporary tablespace used MB constantly stays at 50MB (and I get the results), but when I run it as any db user, the temporary tablespace is filled up to 4000MB (and the query is aborted)?
Both users (SYS and USER) have the same default temp tablespace.
2) I can't believe a rise of +5% of the rows having to be sorted needs a rise of twice the temp tablespace.
Could it be because before it used to use the RAM memory and now it uses a temp tablespace? But my PGA is only 200MB :/
Well, I really can't understand this sudden behaviour. Can somebody help me to point this issue?
Thanks a lot !