Re: Hanging Oracle connections

Lars Fastrup wrote:

> 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
> sun -d /dev/console -l c
> 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
> /opt/VRTSvmsa/jre/bin/../bin/sparc/green_threads/jre
> -nojit -noasyncgc -cp /opt
> 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
> /opt/VRTSvmsa/jre/bin/../bin/sparc/green_threads/jre
> -nojit -noasyncgc -cp /opt
> 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
> /opt/SUNWcluster/bin/ccdd -f
> /etc/opt/SUNWcluster/conf/ccd.database.init
> 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
> /bin/../java/bin/../jre/bin/../bin/sparc/native_threads/java
> com.sun.scm.admin.
> 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
> /opt/SUNWcluster/ha/nfs/nfs_probe_local_restart
> buster
> 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
> /opt/SUNWcluster/ha/nfs/nfs_probe_loghost buster
> 0 0 60
> 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)

The solution is, I suspect reasonably simple.

Oracle 8.1.6 was not exactly one of Oracle's proudest achievements. And it will no longer be supported as of October of this year.

Time to upgrade to 8.1.7.1.0.

Daniel A. Morgan Received on Mon May 28 2001 - 09:22:42 CDT