DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: please help....

  1. #1
    Join Date
    Apr 2001
    Posts
    21

    Angry waiting for ORACLE GURUs reply

    Hi

    Started With : SMON was taking 98% of cpu time. all the other query timing out.

    Observation : tempdb grown to 25G single file

    Suggestion from oracle support :

    1. start oracle with event

    event = "10061 trace name context forever, level 10"

    which bypass the SMON cleaning. so cpu usage down to normal. but still becoz of big tempdb all other query taking long time.

    2. asked support if we can simply drop TEMP tablespace. they said yes. and i started DROP TABLESPACE TEMP WITH content

    and it is running for past 24 hours and still no luck. And oracle svrmgrl sucks :( i can't put it in background. so if somehow i loose connection ...:(

    now i checked DBA_SEGMENT table if count(*) is dropping. it looks like this

    SQL> SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE like 'TEMP%';

    COUNT(*)
    ----------
    288318

    SQL> /

    COUNT(*)
    ----------
    287718

    SQL>

    3. i created another tablespace by TEMP_NEW and assigned that as TEMP for all users. while this DROP ..running nobody is connected to DB.

    Questions : 1. is dropping count(*) sign is good. that it is doing something

    2. is there is any quick way to drop the TEMP tablespace which is 25G :(

    Any other suggestions are WELCOME.

    I am modufying actual posting to say it is RUNNING FOR PAST 3 DAYS .
    i need this DB up by tomorrow afternoon. i can't think of anything else

    thanks and regards
    DBKID.

    [Edited by dbkid on 12-02-2001 at 09:07 PM]

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well smon shouldnt free temp extents in a temporary tablespace so may I ask if your tablespaces status is temporary or permanent? If it's permanenet change it to temporary and you should see a boost in the perfomance database wise

    Regarding loose connection with svrmgrl, write a shell script then you can either put it in crontab or do

    nohup your_shellscript_name

    this way even you lose connection the process will keep on running

    look man nohup if you dont know what it does


  3. #3
    Join Date
    Apr 2001
    Posts
    21
    thanks pando

    for taking a look on my problem.
    as you asked , for status of temp tablespace it is TEMP

    select tablespace_name,status,contents,logging from dba_tablespaces

    TABLESPACE_NAME STATUS CONTENTS LOGGING
    ------------------------------ --------- --------- ---------
    TEMP ONLINE TEMPORARY LOGGING

    regarding cron job. yes i know how to put in b/g . but i thought that DROP TABLESPACE TEMP wouldn't take long. so i started from svrmgrl :(

    anyway my earlier questios are still UN-ANSWERED. Please please advise. i am running out of ideas

    thanks & regards
    DBKID

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    alter tablespace temp offline drop;
    (for dropping the temp tablespace faster)
    Vinit

  5. #5
    Join Date
    Dec 2001
    Posts
    2
    Had similar problem.

    Oracle advised us to allow smon to finish running.
    SMON is not removing temp extents.
    Once they are allocated they stay allocated.
    However, SMON cleans them and changes them
    to free status.

    Once this complete, I created a new temp tablespace,
    moved all users to the new temp, and gave the
    delete tablespace temp including contents command.

    This command took approximately 20 hours to complete.

    The temp tablespace datafile was approximately 18 GB
    at the time.

    When your count(*) from dba_extents reaches 0 for that
    tablespace, it should drop the tablespace.

    Hope this helps!

  6. #6
    Join Date
    Apr 2001
    Posts
    21

    Question down from 370000 to 98000...BUT

    Hi vette86 and all friends

    so DROP TABLESPACE was running for past 3 days. and still hasn't finished completly.

    vette86 you said , you had similar problem. do you remember what kind of machine you had. mine is sun c cpu 333 MHz machine.

    i had to kill the drop tablespace and let people use the machine.

    i am not sure whether i should start drop tablespace thing again or not becoz it chews up 98% of 1 cpu time. but i have to do it at some time.

    any advices / ideas are welcome. can somebody also explores the locally managed temp files idea given by oracle support. though i am reading the documentation for it. but your experience will make great difference.

    thanks and regards
    DBKID

  7. #7
    Join Date
    Dec 2001
    Posts
    2
    dbkid,

    I have an HP9000 L2000 server with dual 440 processors.

    The database is the only thing running on this machine.

    I do not remember the drop taking that much processor to
    complete, but it has been a little while.

    The tablespace scheme that I am using on the new temp
    tablespace is as follows:

    initial extent 512K
    next 512K
    min 1024K

    For each datafile use autoextend with 1024K as increment
    and max file size set at 500MB. I currently have 6 datafiles
    for this tablespace. If you limit the filesize to 500MB, you
    won't have problems moving/copying the file if needed.

    You should also check your sort_area_size parameter.
    If it is set too low, you will start sorting in the temp tablespace (thus the file getting large).

    I have 2GB Ram on that machine and set the sort_area_size
    to 4MB. Have only really hit the temp tablespace once.
    An application programmer at the company ran a query
    that was looking (sorting) nearly 20Million records.

    Hope this info helps!

    Scott Matthesen

  8. #8
    Join Date
    Apr 2001
    Posts
    21

    Thumbs up thanks for wonderful tip , BUT...

    Hi vette86

    thanks for wonderful tip regarding the sort area. i changed that to 3 MB.

    Can you please tell me about the locally managed temp file. advantages and disadavantaged related to it.

    btw , i had to kill the drop tablespace temp command becoz we needed DB for some other urgent work. so i started again the DROP TABLESPACE TEMP last night, it is still running. what i see now is :-

    SQL> select count(*) from DBA_EXTENTS where segment_type like 'TEMP%' and
    tablespace_name = 'TEMP';
    COUNT(*)
    ----------
    34200

    which is going DOWN.

    &
    this one is going UP

    SQL> select count(*) from dba_free_space where tablespace_name = 'TEMP';
    COUNT(*)
    ----------
    372110

    but i think at the end it will clear off the dba_free_space table also.

    correct me if i am wrong

    thanks and regards
    - DBKID

  9. #9
    Join Date
    Apr 2001
    Posts
    21

    so finally.it finished after....( GOOD Lesson)

    Hi Friends,

    so finally my drop tablespace finished after 8 days and 3 hours. sometime oracle sucks :( ( please do not mind )

    so i have question for you guys again :

    1. as per my knowledege SMON awakes every 3 min and cleans the free extents from dba_extents and puts in dba_free_space.

    there is nobody connected to database and still i see this quesry returning same rows

    select count(*) from dba_extents where segment_type = 'TEMPORARY'

    don't you think it should clear it.

    i am running oracle 8.1.7 db.


    FYI : Stats on my DROP TABLESPACE
    - initial count on dba_extents for TEMP segment 378,560
    - machine sun U2
    - how long it took to drop temp tablespace - 8days + 3 hours
    - it initially cleared everything from dba_extents and put it in dba_free_space and then it cleared dba_free_space

    Now i am using locally managed temp file which is cool

    thanks and regards
    DBKID

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if your tablespace is temporary SMON only frees the extents when you shutdown the db so that 3 minutes theory doesnt reallya pply in your case

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