good answe
ashraf gamal, August 09, 2001 - 5:08 pm UTC
Fantastic Answer
Rajesh Dayal, August 11, 2001 - 12:48 am UTC
The explanation is real good. I don't think you have missed anything about SMON.
GREAT !!!
Ganesh R, August 11, 2001 - 8:39 am UTC
Excellent is the Only word to Describe this article.
Growing value for SMON
Nathan, September 30, 2003 - 5:28 am UTC
Hi,
we have a round-robin database ( Buggy Version 9.0.1.2 ) , we add 32 million rows in a day in one the tables. There are are few tables which get loaded everyday. In some tables partiions are added and a oldest partion dropped . We use some java and external tables. The DB is 24X7 and everytime after bouncing the db the performance falls by 30 minutes a day ( based on logs from report tables population and generation). I found a pattern ..by looking at SMON it increases by 5 megs a day . Is there any way i can reset smon without restarting the DB ?
( Patching is not immediately feasible as we cannot bring the DB down for the next few months due to management issues )
Is there any other solution ?
Thanks
Nathan
September 30, 2003 - 7:13 am UTC
what do you mean by "smon increases by 5 megs a day"
have you looked at what smon is doing?
what does it mean to have "performance fall by 30 minutes a day"?
but -- the real confusing thing is:
a) 24x7
b) yet you bounce (else you would not have an issue)
c) but yet you cannot bounce to patch.
hmmm?
SMON Growth!
Nathan, September 30, 2003 - 9:08 am UTC
Tom, I agree I could have been more clearer rather than being stingy with my details,
I tried executing the following query for the past one week
SELECT sess.sid , sess.username
, substr(nvl(sess.program,p.program),-20) prog
, to_char(sum(decode(name.name , 'session pga memory'
, value,0)),'999,999,999,999')curr_pga
, to_char(sum(decode(name.name,'session pga memory max'
,value,0)),'999,999,999,999')max_pga
FROM v$session sess, v$sesstat stat
, v$statname name ,v$process p
WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name like 'session pga memory%'
and sess.paddr = p.addr
group by
sess.sid, sess.username,
substr(nvl(sess.program,p.program),-20)
This query has returns a various process of which SMON continously seems to increase and never releases the memory back. I was monitoring the DB for a performance issue which slowly increases to intolerable levels after a week.
We had to bring down the db for a system maintenance shutdown (for an hour ) to change a essential component after a six month wait. ( The management unfortunately does not act on the changes that easily )and make a fuss about it. We had to bring down for the second time with a shorted notice in less than three months as Sun forced us for a shutdown to change some component which they discovered was faulty with the particualr model. Now I have been pushing the management for a patch, we were not given enough downtime for patching the two databases on the server.
The DB was slowing down with report generation and dataload every day by half hour ( reports are generated 30 minutes later than the previous day ) everyday. I have the statspack details ... however I'm puzzled bcos the same amount of data is loaded every day and reports generated we poll our network devices and collect statistics , there is very little increase in data almost neglible( .0001% increase in a day ). The DB is used by our 24X7 customer support who depend heavily on our DB to diagnose the problems so we are unable to bring the DB down as often as we would please to. I would eventually patch it.. in the next two months time... but wondered if there was a different way of sorting the memory leak ( Reference metalink - Bug reference 2209289 ). Just looking for a temporary work around. Hope you would understand the practical difficulties in a DBA's world.
Regards
Nathan
I can post three spreports , I failed to deduce much as my knowledge is limited and i'm still learning from you. :)
Thank you!
September 30, 2003 - 10:02 am UTC
I understand the practical difficulties, i just couldn't understand what you meant by certain things. this is much clearer.
for that bug, you will need to patch. there are no workarounds, it takes a binary patch to correct the SMON issue.
SMON offlining
Nishant Choudhary, January 31, 2005 - 11:25 am UTC
Sir,
Sorry to put this question here .. didn't find the related topic ...
the follwoing error was encountered in alert log file from past 2-3 days and i din't find any thing on google regarding this ...
============================================================
Sat Jan 29 15:46:13 2005
ARC1: Evaluating archive log 2 thread 1 sequence 7268
ARC1: Beginning to archive log 2 thread 1 sequence 7268
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORADATA\ARCHIVE\ARCH_1_7268.ARC'
ARC1: Completed archiving log 2 thread 1 sequence 7268
Sat Jan 29 16:12:38 2005
SMON offlining US=27
SMON offlining US=28
SMON offlining US=29
Sat Jan 29 16:15:18 2005
Thread 1 advanced to log sequence 7270
Sat Jan 29 16:15:18 2005
ARC0: Evaluating archive log 3 thread 1 sequence 7269
Sat Jan 29 16:15:18 2005
Current log# 1 seq# 7270 mem# 0: D:\ORACLE\ORADATA\CMSS\REDO01.LOG
Sat Jan 29 16:15:18 2005
ARC0: Beginning to archive log 3 thread 1 sequence 7269
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORADATA\ARCHIVE\ARCH_1_7269.ARC'
ARC0: Completed archiving log 3 thread 1 sequence 7269
============================================================
Sun Jan 30 15:45:50 2005
ARC1: Evaluating archive log 3 thread 1 sequence 7320
ARC1: Beginning to archive log 3 thread 1 sequence 7320
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORADATA\ARCHIVE\ARCH_1_7320.ARC'
ARC1: Completed archiving log 3 thread 1 sequence 7320
Sun Jan 30 16:14:24 2005
SMON offlining US=21
SMON offlining US=22
SMON offlining US=23
SMON offlining US=24
SMON offlining US=25
Sun Jan 30 16:15:50 2005
Thread 1 advanced to log sequence 7322
Sun Jan 30 16:15:50 2005
ARC0: Evaluating archive log 1 thread 1 sequence 7321
Sun Jan 30 16:15:50 2005
Current log# 2 seq# 7322 mem# 0: D:\ORACLE\ORADATA\CMSS\REDO02.LOG
Sun Jan 30 16:15:50 2005
ARC0: Beginning to archive log 1 thread 1 sequence 7321
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE\ORADATA\ARCHIVE\ARCH_1_7321.ARC'
ARC0: Completed archiving log 1 thread 1 sequence 7321
========================================================
ALTER TABLESPACE TBMS_CALL_DATA_IDX BEGIN BACKUP
Completed: ALTER TABLESPACE TBMS_CALL_DATA_IDX BEGIN BACKUP
Sun Jan 30 07:00:35 2005
SMON offlining US=11
SMON offlining US=12
SMON offlining US=13
Sun Jan 30 07:17:30 2005
ALTER TABLESPACE TBMS_CALL_DATA_IDX END BACKUP
Sun Jan 30 07:17:30 2005
=========================================
plz help
thanx in advance
January 31, 2005 - 11:45 am UTC
why would you google when you should be metalinking?
anyway, us = undo segment = ok, everything is as it should be.
automatic undo, they come, they go -- as needed.
SMON cleanup
Norm Leach, February 06, 2007 - 3:14 pm UTC
Tom,
At the top of this thread, you stated the following as one of the duties of SMON:
"Cleans up OBJ$ ¡V OBJ$ is a low-level data dictionary table that contains an entry
for most every object (table, index, trigger, view, and so on) in the database. There are
many times entries in here that represent deleted objects, or objects that represent 'not
there' objects, used in Oracle's dependency mechanism. SMON is the process that removes
these no longer needed rows."
When is this done? Is it on a timer? Is it when obj$ reaches a certain threshold that needs cleanup? Is it whenever resources are available?
The reason I'm asking is this. We recently removed a group of private synonyms (approx. 70,000) from our database, and did this in multiple development instances as well as our production instance. In each case, we saw the SMON cleanup pop up and a large increase in our amount of archiving/checkpointing. However, in each case, it was hours after the drop process that we saw SMON come up and our archiving increase (in one case, as many as 8 hours later).
Just wondering why there is that large of a gap in there.
Thanks.
February 06, 2007 - 4:21 pm UTC
it would be a periodic thing - with large gaps between "clean ups". It is just to make the initial operation appear faster, with stuff done in the background "later, whenever"