Bizarro locking/memory issues

[Winkie]

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.

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

[davey23uk]

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?

[Winkie]

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

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

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.

[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

[Winkie]

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?

[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.

[Winkie]

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 :(

[davey23uk]

as the oracle user

sqlplus "/ as sysdba"
or
sqlplus internal

[Winkie]

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.

[Winkie]

Right, that completed, ran and I am off to wait for the next lag issue, thanks for all your help guys