(I'm on dialup currently, so I can't reply all that often)
Janine, please turn on timed_statistics (need to bounce the instance, about 1% overhead) and don't worry about frequent snapshots. Each snapshots takes about a blocks to be written - this just pales in light of the expected load. If you put perfstat into its own tablespace you could measure it and you would NEVER worry about this again.
With timed_statistics=on all the empty columns will be filled with values and we will see on which wait event Oracle is losing time.
If you think it is the RAID or the Oracle instance which are slacking do this: create a a few files (large, mid-sized, small ones) and copy them around from a script. Measure the performance. Do the same with a few tables and a few access paths (table scan, index access, small commits) and measure the performance. What is the result?
A question to the Heidelberg people: is this the same machine that serves the WebCT production system? Were/Are you happy with its performance?
Don't ignore the SQL results!
Look how much acs-service-contracts queries are there. It almost looks like a denial-of-service attack: one query is executed 84.000 in about 4 hours. Another one just looks whether there is one service contract - probably just to be able to gracefully tell the user "service-contract foo$$%$%"bar doesn't exist.
What about aggressive caching for service contracts (and replacing it for the next release - I don't like the package anyway because I think it is a complexifying replacement for tcl namespaces that is ALSO expensive)
What is this query about: select dotlrn_communities_all.*,
dotlrn_community.url(... ? It is *extremely* expensive.
The next query in the ordered by gets is also extremely expensive: what does it do?
Number is probably using cc_users.
Can you at least try to cache the service-contracts and then take snapshots during the day when there is at least some activity on the system?
