Temporary tablespace is always full

[Mohajervepessaran]

In our network dataware house system ( Orcale 8.0.6.0 ) running on HP_UX 11 the temporary tablespace is always full even if this tablespace is 7GB. This causes that every now and then the summarisation fail. The setting of max_extents to unlimited didn't help. What can be the reason?

[monasingh_1]

I think you need to extend the temporary table space.Is the tablespace autoextend on? 7GB is not always a lot, it will depend how it is being used. We have a requirement of 9GB+ for our datawarehouse system.

[Sanjay Kumar Suri]

What is value set for the parameter sort_area_size?

Can the same be increased?

sks

[Mohajervepessaran]

The sort_area_size is increased from 64k to 655K.

[Indira Aramandla]

Hi,

When you create a new temporary tablespace, Oracle will begin creating and using temp segments. When a process finishes using a temp segment, it doesn't deallocate it, but simply marks it free. Then, the next process that needs temp space can grab a free temp segment, and does not need to allocate one. So the extends in a temporary tablespace will never get deallocated unless you shut down the DB or manually deallocate them.

The SMON process will wake up at regular intervals to clear the temporary tablespace. For that you must have pctincrease for the temporary tablespace > than ZERO.

So as quick fix I sometimes do this but not recommended as a long term solution.
ALTER TABLESPACE "TEMP" DEFAULT STORAGE ( PCTINCREASE 1 );

After the temporary tablespace is almost emptied, then reset it back to ZERO.

ALTER TABLESPACE "TEMP" DEFAULT STORAGE ( PCTINCREASE 0 ) ;

The tablespace has increased so dramatically because you either require all that space for the concurrent sorting needs of the database, or perhaps there was some rogue process or processes that performed a huge sort, or perhaps you are encountering a bug where an excessive amount of temporary space is being consumed.

Applying the latest patchset release for 8.1.7 should address the latter case. Monitoring sort utilization can aid in tracking down the middle case.

To see how much temp space is really being used at any given point in time, use V$SORT_USAGE.

If you are using locally managed temp tablespaces with a uniform size (multiple of sort_area_size), then you should not have any problems of such type.

And id=f required you can safely drop and recreate the temporary tablespace

I hope this helps

Indira A

[Sanjay Kumar Suri]

What is the database size of your dataware house system?

sks

[Eric Antunes]

It must be a Oracle process with a very large consistent_gets value!

Try to identify it with:

select s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by status,si.consistent_gets+si.block_gets desc

[Mohajervepessaran]

Sanjay:
My SGA size is 1288072168 bytes.

Antunes:
If there would be an Oracle process with a very large consistent_gets value what should I do?

[Eric Antunes]

Mohajervepessaran,

If there is such a process you can kill it if it is not important (if it's a report or some other process that ca be killed without compromising the data integrity):

alter system kill 'sid,serial#';

And must tune his sql for better performance next time.

[Sanjay Kumar Suri]

I am not asking SGA size. What I am aksing is the total database size in GB or TB.

How many concurrent users log onto this system?

What are their typical query or the duration of their query (since this being a dataware house database)?

sks

[Yogeeraj_1]

hi,

If you created your temporary tablespace using "create temporary tablespace...." and using LMTs....

Temporary tablespaces should appear "full" after a while in a normally running database. Extents are allocated once and then managed by the system. Rather than doing the rather expensive operation of "space management" (data dictionary updates), the system will allocate an extent in TEMP and then keep it and manage it itself. This is normal and to be expected and is not an indication that you do not have any temporary space.

You may wish to see the dynamic performance views V$SORT_USAGE and V$SORT_SEGMENT for more information regarding the usage of space within these temporary segments.

For instance, V$SORT_USAGE will tell you WHO is using WHAT.

hope this helps!

regards
Yogeeraj