RE: Options for poorly performing SQL
Date: Mon, 4 Feb 2013 09:25:11 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01FEE3C4_at_WIN02.hotsos.com>
Dynamically created SQL is always hard to work with. I'm far from a Java expect (I drink Java, I don't write Java), I think profiles and even stored outlines might help. But really your issue is more political then technical. I'd suggest you need to work up the food chain to find a manager or director type person to get involved in this process to get it fixed. You already have a solution, now you need the backing to make it happen.
Ric Van Dyke
Education Director
Hotsos Enterprises LTD.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker
Sent: Monday, February 04, 2013 10:15 AM
To: oracle-l
Subject: Options for poorly performing SQL
Oracle - EE 11.2.0.2
OS - SUSE11
I have a particularly egregious SQL statement that causes problems in my
production OLTP. It's bad enough when only one session is running it,
but at times there may be 5 to 10 sessions running it. Then I end up on
a conference call explaining the problem is the application and unless
they want me to kill customer sessions, I can't address the performance
issues.
I have been trying for 5 years to get the development team to address
it.
I have provided a properly coded statement that retrieves the identical
results yet performs about 95 percent faster with significantly less
I/0, along with trace data and explain plans of both the current
statement and the optimized statement. They refuse to even look at the
statement because it is dynamically created in the application using
javaScript. Not knowing javaScript, I'm not sure why it makes a
difference. Perhaps other dynamically created statements would benefit
from changes here as well.
Questions: Do I have any other options to corral this statement? I haven't used profiles and will be reading up on them this week, but would a profile even be a appropriate for this situation?
--
Sandy
Transzap, Inc.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 04 2013 - 16:25:11 CET