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

Posted on 2011-05-03
Last Modified: 2012-05-11
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

Question by:FABRICIO76
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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:

    Author Comment

    -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?

    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.


    Author Comment

    ->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
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>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.

    Author Comment

    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.


    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    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.

    Author Comment

    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.
    LVL 76

    Accepted Solution

    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.

    Author Comment

    All right I will give you these point, but I hope in the next one we have a better feedback.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to recover a database from a user managed backup

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now