tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeffrey Janner" <Jeffrey.Jan...@PolyDyne.com>
Subject RE: Tomcat/JDBC Thin Client and Oracle SQL Parsing
Date Thu, 28 Jan 2010 22:25:24 GMT
I think you're right about it being a chance coincidence on Oracle
removing the query from the cache about the same time as the restart.
Sometimes coincidences happen.

-----Original Message-----
From: Dan Denton [mailto:ddenton@remitpro.com] 
Sent: Thursday, January 28, 2010 2:25 PM
To: Tomcat Users List
Subject: RE: Tomcat/JDBC Thin Client and Oracle SQL Parsing

Thanks for the reply. I'm pretty sure we pinpointed the reason why
oracle was using the wrong indexes. Stats for the indexes weren't
updated since the 12th of January and when oracle re-parsed the query it
chose the index based on the size of that day's data in the index
histogram. 

As to why oracle seemed to reparse the query after the tomcat restart
instead of keeping the shared sql in place, we still don't know. No
database structures changed and the query was textually identical. At
this point it seems to be a chance coincidence that oracle aged out that
sql right when the instance was restarted.

I just closed out a thread on an oracle forum regarding this, and got
plenty of input on why oracle might have reparsed, exploring all of our
optimizer settings, but nothing regarding why it happened after a tomcat
restart.

-----Original Message-----
From: Jeffrey Janner [mailto:Jeffrey.Janner@PolyDyne.com] 
Sent: Tuesday, January 26, 2010 11:55 AM
To: Tomcat Users List
Subject: RE: Tomcat/JDBC Thin Client and Oracle SQL Parsing

Dan -
I'd look into why Oracle took an "improper" index (whatever that means).
There is nothing in Tomcat/JDK that would mess with your queries that
I'm aware of.
I'd also investigate all the standard reasons why Oracle recomputes
explain plans for queries - most of which are related to the query.  Are
all your varying values passed via bind variables?  What are your DBs
optimization settings? Stuff like that.
Otherwise, the question is probably better presented on an Oracle forum.
Jeff

-----Original Message-----
From: Dan Denton [mailto:ddenton@remitpro.com] 
Sent: Tuesday, January 26, 2010 11:41 AM
To: users@tomcat.apache.org
Subject: Tomcat/JDBC Thin Client and Oracle SQL Parsing

Hello all. Several days ago we saw some strange behavior with an Oracle
database after the restart of a tomcat webapp. Immediately after
restart, Oracle was processing the query for a certain page using an
improper index that caused the page/query to hang. We've taken a look at
the configuration for our CBO and come to the conclusion that Oracle
should not have reparsed the query unless the query were somehow
different, but we can't find anything to indicate the query changed.

While it's possible there's something we missed, I would like to know if
anyone out there knows why a webapp restart would cause the database to
reparse and choose a new execution plan. We're running Tomcat 5.5.12
with JDK 1.5.0_12. This is on an RHEL4 server running a 2.6.9 kernel.
The tomcat instance is using a JDBC thin client to connect to the
database. I know of no queries that are made to the database immediately
upon startup, and it's certainly not this one since the query is page
specific.

Can anyone point out any instances they've had where Tomcat or Tomcat
with the Oracle JDBC driver has exhibited similar behavior?

Many thanks in advance,

Dan

*******************************  NOTICE
*********************************
This message is intended for the use of the individual or entity to
which 
it is addressed and may contain information that is privileged, 
confidential, and exempt from disclosure under applicable law.  If the 
reader of this message is not the intended recipient or the employee or 
agent responsible for delivering this message to the intended recipient,

you are hereby notified that any dissemination, distribution, or copying

of this communication is strictly prohibited.  If you have received this

communication in error, please notify us immediately by reply or by 
telephone (call us collect at 512-343-9100) and immediately delete this 
message and all its attachments.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org



*******************************  NOTICE  *********************************
This message is intended for the use of the individual or entity to which 
it is addressed and may contain information that is privileged, 
confidential, and exempt from disclosure under applicable law.  If the 
reader of this message is not the intended recipient or the employee or 
agent responsible for delivering this message to the intended recipient, 
you are hereby notified that any dissemination, distribution, or copying 
of this communication is strictly prohibited.  If you have received this 
communication in error, please notify us immediately by reply or by 
telephone (call us collect at 512-343-9100) and immediately delete this 
message and all its attachments.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message