RE: Options for poorly performing SQL
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 4 Feb 2013 09:25:11 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
