please help....

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]

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

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

alter tablespace temp offline drop;
(for dropping the temp tablespace faster)

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!

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

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

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

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

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