Statspack is a wonderful tool for finding problem queries. I've found it very simple and quick to use when problem solving. Tom Kyte's book (expert one-on-one) provides an excellent overview of installing, running and interpreting the reports of statspack in oracle 8i. A google search for "installing statspack" provides some good online links.
Installation is also simple: you create a tablespace and source a single sql file (ORACLE_HOME/rdbms/admin/statscre.sql in my version of oracle). To use it, you execute a pl/sql procedure to gather statistics at a single point in time. Later (an hour, a day, whatever) you take another static snapshot.
You can then generate a report showing you what changed in between any two snapshots (by sourcing a SQL script named spreport.sql). This report will include the SQL queries you are probably interested in.
There are many other resources out there on using and interpreting the results of statspack (including the oracle documentation). For Oracle 9i:
http://www.csis.gvsu.edu/GeneralInfo/Oracle/server.920/a96533/statspac.htm
Be aware that the commands to install and run the reports for statspack changed somewhere in the 8i family.
If you're looking to identify problem SQL queries, I think it would be very useful to get statspack running and to start looking at the reports even if they contain a lot of information you might not care about. The sections outlining common SQL queries speak for themselves and I've found them extremely useful to tune the databases I've worked with.
