Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    51

    Unhappy Unanswered: Very slow instance

    Hi all,

    one of my production instances is with a very strange behavior :

    SMON is consuming 33% cpu - it means a whole cpu since last shutdown (12 hours before) - I did shutdown abort as the normal and immediate did not work.
    There is a big temporary segment that can be causing this problem :
    4.2 TEMPORARY 7399.99219
    I've created another temporary tablespace and pointed all users to it, the utilization was extremely slow.

    I know SMON must clean up the TEMP segment, there is no way around this, that is why shutdown immediate and shutdown normal did not work. It will not shutdown cleanly until this TEMP segment has been.

    The question : how can i speed up the process of clearing this temp segment ?? All processes within the db are very, very, very slow

    Thanks,

    Antonio Freitas

  2. #2
    Join Date
    Sep 2003
    Location
    Virginia, USA
    Posts
    246
    Sounds like a big report was being run. Maybe rebuilding a lot of mat views, or Oracle Text index rebuilds?

    The shutdown immediate might have worked if you let it run long enough; it sometimes takes a few hours depending on how much to rollback.

    Be sure to use the best syntax when creating temporary tablespaces, otherwise SMON has problems keeping it clean. Use CREATE TEMPORARY TABLESPACE name TEMPFILE 'filename' SIZE size1 EXTENT MANAGEMENT LOCAL UNIFORM SIZE size2 ....

    It's importan to use the CREATE TEMPORARY TABLESPACE command versus the old CREATE TABLESPACE command with trailing keyword TEMPORARY. It is then required to use TEMPFILE versus DATAFILE. I recommend making your Temp tablespace locally managed, and using uniform extent sizes versus autoallocated extent sizes.
    MarkRem
    Author, Oracle Database 10g: From Nuts to Soup
    http://www.remidata.com/book_nuts2soup.htm

Posting Permissions

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