Trying to shutdown a database which takes about 10 mins sometimes to shutdown. According to my research I found out that smon creates temporary segments which it tries to colensce (release) and that takes time. I believe Oracle was saying that by increasing the sort area size this might help. My question is how do i know on which tablespace has these temporary segments been created?
What about increasing hash area size?
sun solaris 2.6
what you are using? SHUTDOWN immediate or just SHUTDOWN ? Is your temporary tablespace created as temp/permannet ? Check contents in DBA_TABLESPACES view.
If you see your TEMP tablespace usage while database is up and runnig... you can see the size of temp segments increase and decrease...cuz, TEMP will be used for sorting purpose..
[Edited by sreddy on 12-27-2000 at 07:45 PM]
Do you know if there were active sessions when you initiated shutdown?
Hope you are aware of the fact that if you use "shutdown" oracle waits for
all active session to disconnect (but would prevent new connections) and
if you use "shutdown immediate" and If you did have any active session,
oracle still has to disconnect all those sessions and their active transaction.
Just a thought.
sometimes you can speed up your shutdowns by telling Oracle to do a checkpoint first :
alter system checkpoint;
jlakhani, SORT_AREA_SIZE is an init.ora parameter that only affect under what conditions a user will sort to disk, rather than in memory. setting this will have very little or no effect on your slow shutdown.
first i would check to what tablespace users are using for temp segments:
SQL> SELECT DISTINCT TEMPORARY_TABLESPACE FROM DBA_USERS;
check the size of the extents:
SQL> select bytes, count(* )From dba_extents
2 where tablespace_name = 'TEMP'
3 group by bytes;
From this query you should not see more than a few thousand extents. This is the coalescing that must go on before shutdown. I have 409 extents of 5M in size. my shutdowns are pretty quick, so this is OK. furthermore, you should only get one line from the query as written, since you want your extents to be the same size.
You should make sure you are using temporary tablespaces for temp segs.
1 select tablespace_name from dba_tablespaces
2* where CONTENTS ='TEMPORARY'
If you are using 8i, you might want to consider temporary tablespaces (listed in DBA_TEMP_FILES, rather than DBA_DATA_FILES). however, this is just a topic for consideration and the above info should give you some ways to check out if temp segs are causing your slow shutdowns.
Click Here to Expand Forum to Full Width