Without daily internal maintenance, most databases slowly degrade in performance. For PostgreSQL, see the section called “Vacuum Postgres nightly”. For Oracle, use exec dbms_stats.gather_schema_stats('SCHEMA_NAME') (Andrew Piskorski's Oracle notes).

You can track the exact amount of time each database query on a page takes:

Identify a runaway Oracle query: first, use ps aux or top to get the UNIX process ID of a runaway Oracle process.

Log in to SQL*Plus as the admin:

[$OPENACS_SERVICE_NAME ~]$ svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SVRMGR> connect internal             
Password:

See all of the running queries, and match the UNIX PID:

select p.spid  -- The UNIX PID
       ,s.sid  ,s.serial#
       ,p.username  as os_user
       ,s.username  ,s.status
       ,p.terminal  ,p.program
  from v$session s  ,v$process p
 where p.addr = s.paddr
 order by s.username ,p.spid ,s.sid ,s.serial# ;

See the SQL behind the oracle processes:

select s.username
       ,s.sid  ,s.serial#
       ,sql.sql_text
  from v$session s, v$sqltext sql
 where sql.address    = s.sql_address
   and sql.hash_value = s.sql_hash_value
 --and upper(s.username) like 'USERNAME%'
 order by s.username ,s.sid ,s.serial# ,sql.piece ;

To kill a troubled process:

alter system kill session 'SID,SERIAL#';  --substitute values for SID and SERIAL#

(See Andrew Piskorski's Oracle notes)

Identify a runaway Postgres query. First, logging must be enabled in the database. This imposes a performance penalty and should not be done in normal operation.

Edit the file postgresql.conf - its location depends on the PostgreSQL installation - and change

#stats_command_string = false

to

stats_command_string = true

Next, connect to postgres (psql service0 ) and select * from pg_stat_activity;. Typical output should look like:

  datid   |   datname   | procpid | usesysid | usename |  current_query
----------+-------------+---------+----------+---------+-----------------
 64344418 | openacs.org |   14122 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14123 |      101 | nsadmin |
                                                         delete
                                                         from acs_mail_lite_queue
                                                         where message_id = '2478608';
 64344418 | openacs.org |   14124 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14137 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14139 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14309 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14311 |      101 | nsadmin | <IDLE>
 64344418 | openacs.org |   14549 |      101 | nsadmin | <IDLE>
(8 rows)
openacs.org=>