• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

Error in alert log: sys.mon_mods$ "DROP_SEGMENTS": invalid identifier

Good morning,

We are having some peformance issues in an Oracle database V9.
We had to recreate the Oracle Alert Log due to its size 3 days ago.
When looking into this alert log now, it displays this error every 10 seconds (and nothing else but this error):

Tue May 03 11:17:59 2011
Errors in file d:\oracle\admin\v8\bdump\v_smon_5816.trc:
ORA-00904: "DROP_SEGMENTS": invalid identifier

By looking at the solutions, I can see the cause of it: column drop_segments missing from  sys.mon_mods$.
I wonder if this error can also be the cause of a performance issue?

Thanks and Kind Regards

  • 5
  • 5
1 Solution
slightwv (䄆 Netminder) Commented:
Many things can cause a performance problem.  If this is the cause, don't know.  We don't have enough information.

The alert log should recreate itself so I'm not sure what you did to 'recreate' it.

To fix the error, try the steps from:
FABRICIO76Author Commented:
-I do have the steps to fix the error.
-In order to recreate the alert log, the following script was run:
sys.dbms_system.ksdwrt(2, 'Alert log has been recycled');
-I am not familiar to how this column drop segments can affect to the database performance? what information would you need?

slightwv (䄆 Netminder) Commented:
sys.dbms_system.ksdwrt appears to be an undocumented procedure that writes something to the alert log.  I suppose that will 'recreate' it but is unnecessary.  If you remove the alert log, Oracle will automatically create a new one the next time it needs to write to it.

>>I do have the steps to fix the error

Then why haven't you fixed the error yet?

>>what information would you need?

First:  Define "performance problem".  This can mean anything.  Memory peaked, CPU peaked, disks, etc...

Also, people make entire careers out of tuning.  These types of questions are very hard to answer here.

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

FABRICIO76Author Commented:
->Then why haven't you fixed the error yet?
The database needs to be rebooted and I am awaiting for the client confirmation

->First:  Define "performance problem".  This can mean anything.  Memory peaked, CPU peaked, disks, etc...
It takes more than 40 minutes to perform a cashier closure when in other databases with same structures take seconds.
We checked CPU, disk space, memory and tablaspaces

->Also, people make entire careers out of tuning.  These types of questions are very hard to answer here.
OK then
slightwv (䄆 Netminder) Commented:
>>We checked CPU, disk space, memory and tablaspaces

And what is slow?

I suggest you take a look into StatsPack.  Take a snap before the close, then after the close and run the report to see if it can help you narrow down the areas to focus on.
FABRICIO76Author Commented:
Hello Slightwy,

Let me explain this a little better:

We have a long term situation at this site with performance, heavy quearies that take couple of seconds in other sites are taking here 30 - 45 seconds.

This goes beyond the error posted here: ORA-00904: "DROP_SEGMENTS": invalid identifier

My question is: ¿Is this error a possible or known performance affecting issue?

I ask this because I do have a fix for this ORA error, but applying this fix means stopping the site for hours. The site is already very unhappy with the performance issue and I will only stop the site if this ORA error fix could be related to the performance issue.

I hope that makes my point more clear.


slightwv (䄆 Netminder) Commented:
I doubt it is the error being generated in the alert log.  There is likely something else causing it.

As I've mentioned in http:#a35512352, people make entire careers out of tuning.  You need a solid tuning methodology to determine what is causing the problems.  We really cannot answer "what will make it run faster" without supporting information and some knowledge about the system.

>>We checked CPU, disk space, memory and tablaspaces

I was actually referring to disk I/O bottlenecks but OK.

During the run, what if any of those did you see maxed/slow/performing badly?

For example: high CPU, maxed disk reads and/or writes, memory paging out?

I really suggest you run the statskpack snaps before and after.  If you do and can post the report, we can take a look.
FABRICIO76Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for FABRICIO76's comment http:/Q_26990910.html#35512313

for the following reason:

The steps outlined fixed the issue.<br />I was asking the experts if they could confirm in advance those steps will fix it but haven´t received any positive feedback.
slightwv (䄆 Netminder) Commented:
Unfortunately I provided the only answer possible.  There is no magic wand for tuning.  You first need to figure out what is 'slow' from a system perspective then figure out what is causing it.

You never posted back to ask for additional information so I had to assume you figured it out.
FABRICIO76Author Commented:
All right I will give you these point, but I hope in the next one we have a better feedback.

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now