Re: Hanging Oracle connections

work_at_fastrup.net (Lars Fastrup) wrote in message news:<ef099431.0105280315.2339be23_at_posting.google.com>...
> 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!!
[snipped]

Interesting. I'd like to know whether it's true your two CONTAINS caused the problem. Can you create a tiny table and run a query with two CONTAINS? In any case, what does v$session_wait tell you? What's the execution plan?

Yong Huang
yong321_at_yahoo.com Received on Mon May 28 2001 - 13:41:33 CDT