| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Hanging Oracle connections
Hi,
I have a really really tricky problem with an Oracle 8.1.6 database running on a Solaris machine. The database is accessed by a cluster of ColdFusion 4.5.1 servers.
The database chokes on several open connections that constantly poses
a significant load on the database. There should really not be much
load on the DB, but UPTIME shows something different and PS -EF shows
an enourmous amount of Oracle processes. See the bottom of this
question for a screen dump from the
uptime and ps commands.
I have been able to track down where I think it goes wrong. Only a special type of queries make the connection hang. These are queries using the interMEDIA text CONTAINS function that queries a CLOB field containing section indexed XML data. But only queries that uses two CONTAINS functions in the WHERE clause. E.g. queries of the following form:
SELECT * FROM tab1,tab2
WHERE tab1.aaaa_id = tab2.id
AND CONTAINS (tab1.text,'xxxx AND yyyy WITHIN SEC1') > 0
AND CONTAINS (tab1.text,'zzzz WITHIN SEC2') > 0;
Is it wrong to use two CONTAINS methods?
Have you experienced this problem before. Please Help!!
bash-2.03$ uptime
12:07pm up 5 day(s), 19:41, 2 users, load average: 30.82, 30.77,
30.26
bash-2.03$ ps -ef
UID PID PPID C STIME TTY TIME CMD
root 0 0 0 May 22 ? 0:02 sched
root 1 0 0 May 22 ? 0:24 /etc/init -
root 2 0 0 May 22 ? 0:00 pageout
root 3 0 0 May 22 ? 115:58 fsflush
root 8559 1 0 May 22 console 0:00 /usr/lib/saf/ttymon -g
-h -p jekyll console login: -T
root 538 1 0 May 22 ? 0:00 /usr/lib/lpsched root 455 1 0 May 22 ? 0:42 /usr/sbin/rpcbind root 18 1 0 May 22 ? 0:03 vxconfigd -m boot root 59 1 0 May 22 ? 0:00
/usr/lib/devfsadm/devfseventd
root 61 1 0 May 22 ? 0:00
/usr/lib/devfsadm/devfsadmd
root 737 1 0 May 22 ? 0:00 /usr/lib/saf/sac -t 300 root 618 1 0 May 22 ? 0:00
/usr/sadm/lib/wbem/cimomboot start
root 2067 1 0 May 22 ? 0:09 /usr/lib/nfs/mountd root 493 1 0 May 22 ? 0:00 /usr/sbin/inetd -s root 517 1 0 May 22 ? 0:00 /usr/sbin/cron root 532 1 0 May 22 ? 0:10 /usr/sbin/nscd root 612 1 0 May 22 ? 0:00
/usr/lib/osa/bin/sparcv9/rdaemon 30 200 5
root 458 1 0 May 22 ? 0:00 /usr/sbin/keyserv root 2071 1 0 May 22 ? 0:00 /usr/lib/nfs/nfsd -a 16 root 513 1 0 May 22 ? 0:01 /usr/sbin/syslogd root 501 1 0 May 22 ? 0:00
/usr/lib/autofs/automountd
root 619 612 0 May 22 ? 0:00
/usr/lib/osa/bin/sparcv9/rdaemon 30 200 5
root 559 1 0 May 22 ? 0:00 /usr/lib/utmpd root 597 1 0 May 22 ? 0:18
/usr/lib/osa/bin/arraymon
root 578 1 0 May 22 ? 0:00 /usr/lib/sendmail -bd -q15m root 636 1 0 May 22 ? 0:00 /sbin/sh -
/usr/lib/vxvm/bin/vxrelocd root
oracle 21168 1 3 May 22 ? 2427:29 oracleo816 (LOCAL=NO) root 627 1 0 May 22 ? 0:00 /usr/sbin/vold root 639 1 0 May 22 ? 0:00 /bin/ksh
/opt/VRTSvmsa/bin/vmsa_server
root 643 636 0 May 22 ? 0:00 /sbin/sh -
/usr/lib/vxvm/bin/vxrelocd root
root 644 643 0 May 22 ? 0:00 vxnotify -f -w 15 root 710 1 0 May 22 ? 10:09
/opt/SUNWcluster/bin/rpc.pmfd
oracle 28556 1 3 May 23 ? 1400:26 oracleo816 (LOCAL=NO) oracle 21476 1 3 May 23 ? 1307:02 oracleo816 (LOCAL=NO) root 731 1 0 May 22 ? 0:00 /usr/lib/dmi/snmpXdmid -s jekyll root 681 639 0 May 22 ? 0:01
root 690 1 0 May 22 ? 0:00 /opt/SUNWsma/bin/smad root 719 1 0 May 22 ? 0:00 /usr/lib/snmp/snmpdx -y -c /etc/snmp/conf root 740 737 0 May 22 ? 0:00 /usr/lib/saf/ttymon root 688 639 0 May 22 ? 0:00
/opt/VRTSvmsa/vmsa/server/cmdserver
root 694 639 0 May 22 ? 0:01
root 691 690 0 May 22 ? 0:33 /opt/SUNWsma/bin/smad root 697 1 0 May 22 ? 0:00 /usr/dt/bin/dtlogin -daemon root 730 1 0 May 22 ? 0:00 /usr/lib/dmi/dmispd oracle 14086 1 3 May 24 ? 863:34 oracleo816 (LOCAL=NO) root 742 719 0 May 22 ? 1:56 mibiisa -r -p 32791 oracle 13152 1 0 May 22 ? 0:29 ora_arc1_o816 root 20947 8073 0 12:07:20 ? 0:00 sleep 30 oracle 3231 1 0 May 22 ? 1:16 ora_lgwr_o816 oracle 4429 1 4 May 24 ? 941:12 oracleo816 (LOCAL=NO) root 1153 1 0 May 22 ? 0:01
root 1085 1 0 May 22 ? 1:08
/opt/SUNWcluster/bin/clustd -n -f /etc/opt/SUNWcluster/conf/sc-job.cdb
root 1798 1 0 May 22 ? 0:02 ha_dbms_serv root 3016 710 0 May 22 ? 0:02
oracle 3235 1 0 May 22 ? 0:03 ora_smon_o816 oracle 24444 1 4 May 23 ? 1284:43 oracleo816 (LOCAL=NO) root 1646 1 0 May 22 ? 16:47 /opt/SUNWpnm/bin/pnmd -s -c sc-job -l 0 daemon 2063 1 0 May 22 ? 0:09 /usr/lib/nfs/statd -a buster -p //buster/.statmon root 20969 7931 0 12:07:34 ? 0:00 sleep 10 oracle 8887 1 0 May 22 ? 2:21 extprocPLSExtProc (LOCAL=NO) root 2065 1 0 May 22 ? 0:00 /usr/lib/nfs/lockd -g 90 oracle 28662 1 3 May 23 ? 1395:51 oracleo816 (LOCAL=NO) root 8401 1 0 May 23 ? 0:26 haoracle_fmon -m buster o816 60 10 120 300 /etc/opt/SUNWscor/haoracle_config_V1 oracle 507 1 4 May 24 ? 886:08 oracleo816 (LOCAL=NO) oracle 3225 1 0 May 22 ? 0:00 ora_pmon_o816 oracle 3237 1 0 May 22 ? 0:00 ora_reco_o816 oracle 8872 8871 0 May 22 ? 4:58 oracleo816 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 14452 1 0 20:59:02 ? 0:00 oracleo816 (LOCAL=NO) oracle 2284 1 0 May 22 ? 0:14
/u01/OraHome1/bin/tnslsnr LISTENER -inherit
oracle 19628 1 4 May 23 ? 1287:57 oracleo816 (LOCAL=NO) oracle 3239 1 0 May 22 ? 0:03 ora_arc0_o816 oracle 22534 1 3 May 22 ? 2440:28 oracleo816 (LOCAL=NO) oracle 3229 1 0 May 22 ? 1:12 ora_dbw0_o816 oracle 3233 1 0 May 22 ? 0:12 ora_ckpt_o816 root 8202 7926 0 May 23 ? 0:00
/opt/SUNWcluster/ha/nfs/nfs_mon sc-job buster 0
root 8120 1 0 May 23 ? 1:09
/opt/SUNWcluster/bin//netfmd -t 10
root 7931 1 0 May 23 ? 0:58 /bin/ksh
oracle 16719 1 1 11:35:11 ? 1:05 oracleo816 (LOCAL=NO) oracle 19864 8423 0 11:59:10 ? 0:00 oracleo816 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 5982 1 3 May 23 ? 1203:51 oracleo816 (LOCAL=NO) oracle 6697 6695 0 10:18:34 pts/2 0:00 -bash oracle 23653 1 3 May 22 ? 2428:22 oracleo816 (LOCAL=NO) root 20978 20892 0 12:07:41 pts/3 0:00 ps -ef oracle 4907 1 3 May 23 ? 1366:56 oracleo816 (LOCAL=NO) root 8073 1 0 May 23 ? 0:01 /bin/ksh
/opt/SUNWcluster/bin/monitor_rpcbind
oracle 8871 1 0 May 22 ? 0:03 /u01/OraHome1/bin/ctxsrv -user ctxsys/ctxsys -log ctx.log root 8423 8401 0 May 23 ? 0:18 haoracle_fmon -m buster o816 60 10 120 300 /etc/opt/SUNWscor/haoracle_config_V1 oracle 17309 1 0 May 22 ? 0:02 oracleo816 (LOCAL=NO) oracle 10659 1 4 May 24 ? 958:50 oracleo816 (LOCAL=NO) oracle 9055 1 3 May 24 ? 952:15 oracleo816 (LOCAL=NO) oracle 4665 1 3 May 23 ? 1215:48 oracleo816 (LOCAL=NO) oracle 14454 1 0 20:59:02 ? 0:00 oracleo816 (LOCAL=NO) oracle 15568 1 3 11:26:52 ? 4:44 oracleo816 (LOCAL=NO) oracle 15834 1 0 May 22 ? 52:58 oracleo816 (LOCAL=NO) oracle 16476 1 3 May 23 ? 1327:56 oracleo816 (LOCAL=NO) oracle 16907 1 3 May 23 ? 1319:38 oracleo816 (LOCAL=NO) oracle 19394 1 3 11:55:34 ? 1:23 oracleo816 (LOCAL=NO) oracle 22159 1 3 May 23 ? 1282:37 oracleo816 (LOCAL=NO) oracle 14073 1 3 May 24 ? 859:10 oracleo816 (LOCAL=NO) oracle 13587 1 4 May 24 ? 856:06 oracleo816 (LOCAL=NO) root 7926 710 0 May 23 ? 0:00 /bin/ksh
oracle 788 1 3 May 24 ? 860:01 oracleo816 (LOCAL=NO) oracle 2447 1 4 May 24 ? 961:36 oracleo816 (LOCAL=NO) oracle 14450 1 0 20:59:01 ? 0:00 oracleo816 (LOCAL=NO) oracle 9385 1 4 17:01:06 ? 174:50 oracleo816 (LOCAL=NO) oracle 19989 1 0 12:00:02 ? 0:00 oracleo816 (LOCAL=NO) oracle 18500 1 4 May 25 ? 674:21 oracleo816 (LOCAL=NO) oracle 20858 1 3 12:06:41 ? 0:08 oracleo816 (LOCAL=NO) root 6695 493 0 10:18:34 ? 0:00 in.telnetd root 20890 493 0 12:07:02 ? 0:00 in.telnetd oracle 20892 20890 0 12:07:03 pts/3 0:00 -bash oracle 17607 1 3 11:41:53 ? 2:56 oracleo816 (LOCAL=NO) oracle 18189 1 3 11:46:24 ? 2:24 oracleo816 (LOCAL=NO) oracle 19837 1 3 11:58:57 ? 1:11 oracleo816 (LOCAL=NO)Received on Mon May 28 2001 - 06:15:05 CDT
 |
 |