Eh,... It might have a different name on your version of Oracle, or be under
a different schema. Try running,
SELECT OWNER, VIEW_NAME
FROM ALL_VIEWS
WHERE VIEW_NAME LIKE '%OPEN%CURSOR%';
and see what pops up.
-- Bill K.
-----Original Message-----
From: Adam Myatt [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 21, 2001 8:54 AM
To: [EMAIL PROTECTED]
Subject: RE: mysteriously dying connections (Oracle <-> tomcat)
Bill,
What if the V_$OPEN_CURSOR is not present in the SYS schema. Using the DBA
Studio, I looked through all the schemas and users and V_$OPEN_CURSOR
doesn't exist anywhere. Would it be under an alternative name?
- Adam
At 08:32 AM 6/21/2001 -0700, you wrote:
>These look like two different issues:
>
>1) Oracle is running out of cursors.
>
> a) Make sure you close every ResultSet, Statement and Connection when
>you're done with them. One trick you can use on Oracle is to log into the
>database in SQL*Plus as SYS while your application is running, and do,
> SELECT SQL_TEXT FROM V_$OPEN_CURSOR;
>to see the SQL for the cursors you have open.
>
> b) Try reusing connections and (prepared) statements wherever possible
>(assuming you use a common Oracle logon for all your accesses).
>
> c) Up the number of allowed open cursors, by putting
> open_cursors = 500
>or so in your database's init.ora file.
>
>2) Tomcat is running out of threads.
>
> Are you actually getting dozens of people connecting at the same time?
>Try increasing the "max_threads" parameter for PoolTcpConnector in your
>server.xml.
>
> -- Bill K.
>
>-----Original Message-----
>From: Jan M. STANKOVSKY [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, June 21, 2001 8:16 AM
>To: [EMAIL PROTECTED]
>Subject: Re: mysteriously dying connections (Oracle <-> tomcat)
>
>
>Same here but we have the stopping of the tomcatserver very frequently.
>At the end of the term when the students (aprox 50 working at the same
time)
>got very busy the tomcatserver
>stoped replying frequently. We have a configuration with
> Two Sun Sparc Solaris 7 Servers both tomcat 3.2.1 & 3.2.2 and one
Oracle817
>Server on Soalris 7
>
>Once I traped this error:
>--------------------------
>2001-06-19 07:41:23 - ThreadPool: Pool exhausted with 100 threads.
>2001-06-19 09:02:25 - ThreadPool: Caught exception executing
>org.apache.tomcat.service.TcpWorkerThread@3598c3, terminating thread -
>java.lang.IllegalStateException
> at org.apache.tomcat.util.ThreadPool.runIt(ThreadPool.java:227)
> at
>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:405)
> at
>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
> at java.lang.Thread.run(Thread.java:484)
>---------------------------
>
>As you can see between 07:41:23 & 09:02:25 (the shutdown of the tomcat
>server) there was no activity except angry students (times are pm).
>
>And this:
>-------------------------
>2001-06-20 04:51:10 - Ctx( /a9303541 ): Exception in: R( /a9303541 +
>/servlet/shop1 + null) - java.lang.NullPointerException
> at shopmanager.<init>(shopmanager.java:16)
> at shop1.<init>(shop1.java:14)
> at java.lang.Class.newInstance0(Native Method)
> at java.lang.Class.newInstance(Class.java:237)
> at
>org.apache.tomcat.core.ServletWrapper.loadServlet(ServletWrapper.java:268)
> at
>org.apache.tomcat.core.ServletWrapper.init(ServletWrapper.java:289)
> at org.apache.tomcat.core.Handler.service(Handler.java:254)
> at
>org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
> at
>org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:7
9
>7)
> at
>org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
> at
>org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(Http
C
>onnectionHandler.java:210)
> at
>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
> at
>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
> at java.lang.Thread.run(Thread.java:484)
>
>2001-06-20 05:01:12 - ThreadPool: Caught exception executing
>org.apache.tomcat.service.TcpWorkerThread@2c7887, terminating thread -
>java.lang.IllegalStateException
> at org.apache.tomcat.util.ThreadPool.runIt(ThreadPool.java:224)
> at
>org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:405)
> at
>org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:498)
> at java.lang.Thread.run(Thread.java:484)
>
>2001-06-20 05:01:12 - ContextManager: Removing context Ctx( /examples )
>------------
>
>5:01:12 was the time where I shutdowned the tomcat server....
>
>
>I now shutdown/restart the server hourly and it seems we don't have
>unsheduled stoppings.
>
>Another thing is, that the servlets dont get refreshed when they are
>changed/deleted (I have reload in server.xml)..
>
>
>Thanks
>
>jan
>
>
>
>~>|
>~>|problem:
>~>|--------
>~>|
>~>|database access is managed via a java class that is
>~>|instantiated and loaded into each clients session. every
>~>|PL/SQL function and/or SQL statement a client needs is
>~>|called in a method of this class.
>~>|first, a connection is opened, the statement is called,
>~>|all resultsets and statements are closed and finally the
>~>|connection is closed. (or returned back to the
>~>|connection pool if one is used)
>~>|
>~>|the driver we use is the Oracle jdbc ThinDriver (jdbc
>~>|driver type 4). after running the system for several
>~>|days, dead connections (and as we just discovered, tons
>~>|of open cursors) pile up.
>~>|eventually the webserver(tomcat) will crash or just
>~>|hang.
>~>|in the process, access gets slower and slower, there may
>~>|be delays of 3 minutes until the webserver (or, perhaps
>~>|the database, in which case the webserver would be just
>~>|waiting for the db..) gives a response.
>~>|after a restart of the webserver or the database or
>~>|both, the system runns very well again.
>~>|
>~>|we tried several connection pools. with these, there
>~>|were no delays for getting a connection, but the pool
>~>|threw timeoutExceptions (i.e. made it visible that there
>~>|are hanging connections) and after a few hundred
>~>|timeouts the VM crashed with a stack overflow.
>~>|
>~>|the connections seem to get killed/kill themselfes
>~>|randomly no matter which function was called. we log the
>~>|methods and statements that were called for the
>~>|connections that die, but there is just no pattern in
>~>|it. thats why we dont find the root of the problem.. is
>~>|it the driver ? the database ? the tomcat ?
>~>|
>~>|
>~>|
>~>|environment:
>~>|----------------
>~>|
>~>|Web Server:
>~>|Suse Linux 7.2
>~>|Apache Webserver /w Tomcat JSP Engine 3.2 /w SSL
>~>|
>~>|DB Server:
>~>|Suse Linux 7.2
>~>|Oracle 8i 8.1.7 /w multi-threaded server (MTS)
>~>|
>~>|Backup Server:
>~>|Oracle Shadow server
>~>|
>~>|
>~>|
>~>|things done:
>~>|------------
>~>|
>~>|- linux update
>~>|- apache update
>~>|- tomcat update
>~>|- jdbc thin driver update
>~>|(everywhere the newest version)
>~>|- tried several connection pools
>~>|
>~>|
>~>|
>~>|possible changes:
>~>|-----------------
>~>|
>~>|- use OCI drivers instead of thin driver
>~>|- use J-serv with GNU_jsp instead of tomcat
>~>|
>~>|
>~>|
>~>|
>~>|every help is welcome, we're frustrated to no ends.
>~>|thanks very much in advance!!
>~>|
>~>|l.sägesser
>~>|
>
>____________________________ __________________________________
>Ing. Jan M. STANKOVSKY ][ +1 4277 38438
>University of Vienna ][ +1 4277 38449
>Dep. of Informationssystems ][ [EMAIL PROTECTED]
>1010 Vienna ][ http://www.ifs.univie.ac.at/~jan
>____________________________][__________________________________
>
>
