| Re: Temp Tablespace not being flushed automatically [message #50830] |
Tue, 09 April 2002 17:31  |
Kishore
Messages: 45
Registered: September 2000
|
Member |
|
|
Thank you and that's very interesting to know. I never tried that. In my explanation what I am suggesting is one does not have to shutdown the database (shutting down, cleans up temp segment anyway without any doubt). But while the database is running, Oracle (SMON) automatically clean up the temporary segment but leaves the extents alone so that other sessions could use them without allocating again. Eventhough these free extents are reusable, it is highly possible that some transactions/sessions may demand more space than allocated to tablespace. In this case only option is to increase the temporary tablespace size.
Anyway, could you please provide the link and/or document and/or explanation of how it could free up temporary tablespace when it's parameter PCTINCREASE is set 0. Also, the usual practice is to keep PCTINCREASE 0 for a temporary tablepace for performance reasons anyway. Do I need to 'alter' PCTINCREASE to '0', even if it is already '0' to make this happen?
Thank you very much,
Kishore
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #50833 is a reply to message #50830] |
Wed, 10 April 2002 04:20  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Kishore,
What you have said above about SMON and all is very true. SMON automatically cleans up the space. But the case is when enough extents have been allocated and some process is asking for more, and that's when the Temp space really get's full. Now either you add more (if you have some space to add) or clean up. And to clean up without recycling the db, use this command.
The method I have mentioned 'works'. I do not know the mechanism behind it. I do not know of any docs or links on this. Someone had a thread in this very site 'orafaq' sometime back and that's how I picked it up. I have used it a lot and is very handy for me.
My guess, (I said guess) is that although the pctincrease is normally 0 in temp space, once this command is given, it initializes it again, thus freeing up all the allocated extents (making them 'free') and starting from scratch again. The space used in temp really goes to zero.
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #50837 is a reply to message #50830] |
Wed, 10 April 2002 07:03 |
Kishore
Messages: 45
Registered: September 2000
|
Member |
|
|
Sanjay,
Thank you again and it's very interesting. Since I am first time hearing this thing about temp table spaces I am trying to understand the mechanism. I hope you wouldn't mind and I really appreciate you for the explanation you are providing. I am bit confused because my understanding is as follows.
1. Let's assume that we have 16 MB of 'temp table space' with storage as INITIAL 4M NEXT 4M PCTINCREASE 0. Assume block overhead is also negligible.
2. Assume a session1 (first time) creates a sort segment which contains 2 extents (that means it has occupied 8M) based on sizes above. At this point, WHILE THIS SEGMENT IS IN USE, table space only contains 8M (16M - 8M) of free space.
3. Assume session2 is trying to create a sort segment (while the previous session1's sort segment is in use) and it needs 3 extents for its sort. Therefore session2 needs 12M now. Since it needs 4M more than table space has, the query will fail. The option here is to increase the table space.
At this point I couldn't understand by setting PCTINCREASE 0, what EXTENTS IT WILL CLEAN OR MAKE FREE(so that session2 could use)? It can not clean the segments which are in use.
4. In other case, if session1 completes its sort it will release the segment and makes those 2 extents 'free' (cleaning - SMON's duty). At this point table space behaves as if it has 8M free. Session2 at this point could actually use this free extent/s when it comes to the point of allocating 3rd extent.
5. If session2 is also done, SMON flushes/cleans out all segment headers and the table space will contain 'FREE EXTENTS' and behaves like as if it's free table space and ready for further use.
This way Oracle can keep track of free extents and used extents in temp table spaces and makes sure the space has been utilized properly.
But I couldn't understand how 'PCTINCREASE 0' setting could manage it better! I hope you won't get bored.
Thank you again and have a nice time,
Regards,
Kishore
|
|
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #242335 is a reply to message #242298] |
Fri, 01 June 2007 10:58 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Last message from Kishore was in January 2004 and last visit in January 2005, I don't think it will answer you!
He did not say there is a command to flush the temporary tablespace, he said smon "flushes" it.
Do you have any problem. If so post it as well as your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
