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