Temp Tablespace growing, growing, growing !!!!!

[Henrique Silva_3]

We have an application that was running without any statistics, so, optimizer was basically RBO. The DB size is about 35 GB right now.

We analyzed all tables on this third party app, Open View Service Desk, so that we could use CBO instead of RBO, and what we noticed is that now, the temp table space, which was constant at about 1.5 GB, is now growing, growing, growing.

We have added a couple more data files for the temp tablespace, which was created as a temporary tablespace, as opposed locally managed tablespace. We have also, created these data files on this brand new, SAN 28 GB file system.

Now, we did this last night, and 12 hours later, the temp tablespace is 20 GB and growing. It looks like it will grow all the way to the size of this file system, 28 GB.

So, I have two questions.

Is this caused by the way CBO works ? Does it consume all available space for the temp tablespace fro the optimizer to find the best path for execution ?

When we get to the end of the file syste here, will the database stop or start reusing free blocks within that massive 28 GB temp tablespace ? I think that is what it will do, but I am a bit concerned bere :-) We did not see this behavior in the staging environment, granted we do not have the massive load that we have in production there.

And finally, what is the ratio for the size of the temp tablespace against the database size, so that we can eventually, create fixed sizes temp tablespaces ? And how to reduce them ? I do not believe you can drop temp tablespace, but might be able to drop the data files ? I need to take a look at that.

Thanks,

Henrique

[Fred Ruffet]

Hi,

Temp tablespace should absolutely not be put in autoextend mode. It will always try to use free space, prior to free old info. If it is in autoextend, it will always extend file (no link with CBO).

You must first alter tempfile to put it in "autoextend off". As long as it is now too big, and you cannot reduce it (no free space at end), you will have to create another (no autoextend) and set it as default temp tablespace. When there will be no sessions left in the old one, you will be able to remove it.

Sizing temp space is a bit hard. It is not dependent only on DB size, but also on queries you send. Better thing is to size it to something like the 1,5GB you were having, and extend it if you have problems... But maybe someone here have a cook book :)

Regards,

Fred

[Henrique Silva_3]

Thanks Fred.

The weird thing is that we have been using this application for over an year now, in RBO, and the temp tablespace was not growing as it is now. It also had a self-e extending data file on it, and I am not sure why it did not grow before, and it makes sense that it is growing now. :-(

Also, I just want to make sure that by creating a new one with autoextend off, and then, turn the auto extend off on the current one, will be OK. I do not want the DB instance to stop once the new one fills up.

This is very weird indeed.

Thanks,

Henrique

PS.: our disk activity, at the HP UX OS level, is very high now, around 100 %. I assume it is because we are constantly trying to allocate new extents for this ever growing temp tablespace.

[John Wimmer_1]

How big is your sort_area_size? It may or may not be too small.

As Fred said, the temp tablespace just grabs new extents without really cleaning up the used one. After a restart, it will clean them up. This causes smon to grab 100% of the CPU. (see the metalink articles below, if you have metalink access)
How often do you shutdown? I never saw a problem until we started running 24/7.
One way to get Oracle to clean up the temp segments is to schedule a job during off hours (if you have that luxury) that does something like this :
alter tablespace temp default storage (pctincrease 0);

How often do you analyze the tables? That may use some temp segments (not sure off the top of my head) Much like you, I don't see the difference in CBO and RBO making you temp segments grow.

Also, one of the things that constantly filling of my temp segments were queries that weren't properly limited, and then ordered. The sorts were taking 100% of my temp table space.

Metalink articles:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=1039341.6
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=61997.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=102339.1

[Patti Johnson]

It's possible that the execution plans changed for some of your queries when you converted to CBO and they are no longer as efficient as under the Rule Based optimizer. Finding the queries that are using the most sort space will help you correct the problem.
Take a look at the v$sort_segment view first to see how many sessions are currently performing sorts, them look at v$sort_usage to see how much sort area each query is using.

Here is a query that might help identify the sessions using the sort space.
SELECT a.username, sid, serial#, sql_hash_value, contents ,
extents, to_char(logon_time,'mm-dd-yy hh24:mi:ss') "Start Time"
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr
order by 4;

Once a session completes the sort segments that is was using are free for other sessions, but the sort extents will remain allocationed - just unused.

[Yogeeraj_1]

hi henrique,

Temporary tablespaces should appear "full" after a while in a normally running database.

What really happens is: 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 can also refer to the dynamic performance views V$SORT_USAGE and V$SORT_SEGMENT for more information regarding the usage of space within these temporary segments. V$SORT_USAGE tells you who is using what.

regards
Yogeeraj

[Henrique Silva_3]

Thanks everyone.

We did turned auto-extent off on the growing data file, and everything was fine after that. Our temp tablespace is about 20 GB now, for a 35 GB database :-), but we will try to get it down later.

Are there any magical formulas on what the size should be ?

I am also checking the sort_area size, and we need to move this DB to a more beefier server, which is being built right now.

Thanks again,

Henrique