Forum OpenACS Q&A: Tools for finding slow queries?

Posted by Janine Ohmer on
I've got an ACS Oracle site which has developed an intermittent slowness.  I'm fairly sure there is a bad query out there which occasionally sucks up all available resources.  But which one?  I need a tool to help me figure this out.

I've looked around, done some Google searches, and it appears that my choices are limited.  I can pay a lot of money for Quest's Spotlight, or I can use Oracle's Statspack for free.  I can't even find a GUI frontend for Statspack that will run on Linux.  Is the market really this limited?

Tora, Toad and the like are great tools, but they don't seem to help much in this area.  Of course, if I'm overlooking something please let me know!

Posted by C. R. Oldham on
Didn't ACS 3.x have a "show queries that are becoming pigs" link in the Cassandracle monitoring pages?
Posted by David Walker on
I know this has nothing to do with your question but the postgres answer is to set the following in your postgresql.conf

stats_start_collector = true
stats_command_string = true

after restart the following query will show currently running queries

psql -U postgres template1 -c "select * from pg_stat_activity"

Posted by Janine Ohmer on
CR, I think you're right... I'll have to dig that up, thanks!

David, that's good to know - I'm sure I'll need that tidbit someday. :)

I bought an Oracle Press book on using Statpack tonight - supposedly it includes bunches 'o scripts to make it easier to use.  We'll see...

Posted by Michael Bryzek on
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:

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.

Posted by Brian Fenton on
Never used Statspack. I use the following query to find queries that are going to the disk too much. If it returns too little/many rows, play around with the 10000 figure.

select  sql_text, disk_reads, loads, optimizer_cost,
parsing_user_id, serializable_aborts, au.username
from  v$sql, all_users au
where  disk_reads > 10000
and  parsing_user_id = au.user_id
order by disk_reads desc;

Posted by Carl Coryell-Martin on
hey Janine, I wrote are replacement for ns_db that can be configured to log elapsed time of every query into the error log.

I then have two tcl scripts that will parse the resulting error logs, pull out all the queries and collect statistics about each one.

I recently analyzed 16M lines of log found 635K queries.  My script found 500 unique queries and gave me the mean, max and min execution time and the number to times the query was run.

Ping me if you are interested, and I'll email you the scripts.

Posted by Carl Coryell-Martin on
Okay I uploaded the scripts to the file storage you can find the here:

These are scripts written for personal use so you may have to modify them to suit your purposes, but they will likely save you a couple hours of futzing.

magic numbers are at the top of the files, edit them to taste.

The replacement for ns_db:
The log parser
The query analyzer