smon takes long time to shutdown
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: smon takes long time to shutdown

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    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?

    any suggestions?
    sun solaris 2.6

    thanks
    Jigar

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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]

  3. #3
    Join Date
    Dec 2000
    Posts
    46
    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.

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    sometimes you can speed up your shutdowns by telling Oracle to do a checkpoint first :

    alter system checkpoint;


  5. #5
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    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;

    TEMPORARY_TABLESPACE
    ------------------------------
    TEMP

    check the size of the extents:
    SQL> select bytes, count(* )From dba_extents
    2 where tablespace_name = 'TEMP'
    3 group by bytes;

    BYTES COUNT(*)
    --------------- ---------------
    5,242,880 409

    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.

    SQL> R
    1 select tablespace_name from dba_tablespaces
    2* where CONTENTS ='TEMPORARY'

    TABLESPACE_NAME
    ----------------------
    TEMP

    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.

    d.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width