These are some good sql statements which I use every now and again to check performance of my ColdFusion applications, and specifically to debug a few common oracle errors.
Get a list of open sessions (database connections). Should directly relate to the restrict connections to x (whe using the limit connections option) setting under each datasource in the ColdFusion administrator. You can also verify that connections are re-used when maintain connections is on. A list of open sessions is also nice when running coldfusion in a clustered environment.
[sql]
SELECT se.osuser,se.username,se.status,se.sid "O-sid",
p.pid "O-pid" ,se.process "user pid", p.spid "wkg-pid",
to_char(se.logon_time,'MM/DD/YY HH24:MI') "logon",se.machine
FROM v$session se, v$process p
WHERE addr=paddr and se.osuser is NOT NULL and se.username is NOT NULL
ORDER BY se.osuser
[/sql]
Get the number of cursors a session has open. Good when troubleshooing the Maximum Open Cursors Exceded error. Cursors are used in some stored procedures, but it should relate to the number of pooled statements currently open on each connection (As set for that datasource in the ColdFusion Administrator).
[sql]
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid
[/sql]
Get the total number of cursors a session has ever opened
[sql]
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 2 and v.sid = s.sid
[/sql]
These were all tested on Oracle 8.1.6 If you can verify that they work on other versions I’d love to hear about it.