Bizarro locking/memory issues
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Bizarro locking/memory issues

  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Bizarro locking/memory issues

    I'll preface this with an IANADBA because i really am not.

    Every day, at up to 5-10 seperate times, our Oracle 8i server has a process which begins allocating memory and will not stop until it reaches about 25% of the server's memory. This takes about 2-3 minutes usually and makes Oracle entirely unresponsive.
    Code:
    32076 oracle    15   0  161m 161m 161m S  0.0 16.1   0:03.78 oracleCCDBS (DESCRIPTION=(LOCAL=no)(ADDRESS=(PR
    We can replicate this behaviour by starting Toad on a machine, this will lock up oracle for a while too. This is a serious issue for us as response time is far more important than efficiency. We simply must have queries back within 2 seconds and this problem is causing us a fair amount of damage commercially.

    Obviously we'd hire an Oracle DBA to sort this out if I had any say in the matter, but I don't and this is a very old system designed by someone I suspect was not amazingly familiar with indexes. I have various scripts to list locks, list running jobs and even grab the SQL text from running processes but when this issue arises, oracle will entirely lock up.

    I'm at a total dead end here, I desperately need some way to identify what is causing these bizarre allocations and I cannot take this database down for more than an hour in the middle of the night.

    I prostrate myself in front of you real DBAs and humbly request someone stop me getting fired (yep i'd get fired for his mistake, charming eh?)

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    set up statspack, set up snaps for every 5 minutes and this should tell you what querys are using up the most resources.

    That process you showed has someone logging in over the network, you could do a netstat to see who owns that process and where the connection is coming from.

    Any login triggers at all that you know of that are doing stupid things?

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by davey23uk
    set up statspack, set up snaps for every 5 minutes and this should tell you what querys are using up the most resources.
    This is pretty much what i'm doing at the moment, just reading up on requirements / methods first

    Quote Originally Posted by davey23uk
    That process you showed has someone logging in over the network, you could do a netstat to see who owns that process and where the connection is coming from.
    That's a pretty good idea, we use a somewhat centralised query system though so it wouldn't work perfectly

    Quote Originally Posted by davey23uk
    Any login triggers at all that you know of that are doing stupid things?
    None at all, i'm convinced this is related to statistics gathering on data in the database but according to the guy who wrote it, that runs infrequently and caches everything.

    Statspack seems the right direction to head in for now.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    if its stats, you can save off your current stats, generate new ones and see where things go

    if it makes thing worse you can always revert to the old ones

  5. #5
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by davey23uk
    if its stats, you can save off your current stats, generate new ones and see where things go

    if it makes thing worse you can always revert to the old ones
    Yeah that won't be a problem if we can identify it, next issue:

    Connected to:
    Oracle8i Release 8.1.7.4.0 - Production
    JServer Release 8.1.7.4.0 - Production

    SQL> @?/rdbms/admin/spcreate
    ... Installing Required Packages

    Package created.


    Grant succeeded.

    from dba_object_size
    *
    ERROR at line 4:
    ORA-00942: table or view does not exist


    Disconnected from Oracle8i Release 8.1.7.4.0 - Production
    JServer Release 8.1.7.4.0 - Production
    What the hell? :(

    I'm logged in as system, should i be in as anything else?

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Try creating statspack as sys not system. You need to look at cron and dbms_jobs to see if there is a job that runs 4 times per day. Specifically do you have a job that runs 4 times per day to generate statistics, perhaps with compute? If so that will lock tables as it runs, and would give you the behavior that you describe.
    this space intentionally left blank

  7. #7
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by gandolf989
    Try creating statspack as sys not system. You need to look at cron and dbms_jobs to see if there is a job that runs 4 times per day. Specifically do you have a job that runs 4 times per day to generate statistics, perhaps with compute? If so that will lock tables as it runs, and would give you the behavior that you describe.
    These interruptions do not occur at set times, we thought it could be jobs for a while as there was only one job daemon (don't ask why) and it takes a shedload of CPU / RAM but we've reduced job frequency, increased job daemon count and reindexed referenced tables for safety. We still get a large amount of RAM usage but the problems we're having are no less frequent and still as painful:

    10661 oracle 20 0 72744 70m 70m S 0.0 7.0 218:51.40 ora_snp0_CCDBS
    10663 oracle 24 0 39184 37m 37m S 0.0 3.8 217:06.23 ora_snp1_CCDBS
    10665 oracle 25 0 64724 62m 62m S 0.0 6.2 216:37.05 ora_snp2_CCDBS
    10667 oracle 15 0 81712 79m 78m S 0.0 7.9 217:11.13 ora_snp3_CCDBS
    I have no idea how to logon as 'sys', I have a list of passwords but sys/any of them doesn't seem to work. I apologise for my total lack of knowledge here :(

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    as the oracle user

    sqlplus "/ as sysdba"
    or
    sqlplus internal

  9. #9
    Join Date
    Dec 2006
    Posts
    6
    EDIT: Ignore what I said before, wasn't running as sys.

    I've installed statspack but got an error part way through the install and had to manually install parts 2 and 3, now I get this at the end of a statspack run:

    SQL> exec statspack.snap;
    BEGIN statspack.snap; END;

    *
    ERROR at line 1:
    ORA-06564: object PERFSTAT.STATSPACK does not exist
    ORA-06512: at "SYS.DBMS_UTILITY", line 68
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
    ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
    ORA-06512: at "SYS.STATSPACK", line 1764
    ORA-06512: at line 1
    Double edit I have reinstalled statspack and it now seems to be snapping, but it's taking forever and slowing my database the hell down :(

    Hopefully i won't have to run this every 10 minutes. Lets see if it completes + dumps data.
    Last edited by Winkie; 12-14-2006 at 10:07 AM.

  10. #10
    Join Date
    Dec 2006
    Posts
    6
    Right, that completed, ran and I am off to wait for the next lag issue, thanks for all your help guys

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