How to isolate general Oracle 10g issues through SQL
first thing you should do is get AWR(10g on)/statspack(if your on an older version of the DB) running on your database so that it generates a snap every few minutes (few being say 15, 30 ..if you need more resolution, make the snaps more frequent). this will give you a system overview on IO, CPU etc and will show you the TOP SQL (in terms of IO, CPU, runtime, executions and the like). it will advise on SGA / buffer cache usage too.

From that point onwards you should start looking at the worst sql/areas and run tools like explain plans/tkprof/sql trace on sql statements and plsql profiler on any slow running pl/sql code.