Options for poorly performing SQL
Date: Mon, 4 Feb 2013 08:15:14 -0700
Message-ID: <CAJzM94BMV9HOyXVsK16buB3HXm35KkR=_o94iAbO05__40NnRg_at_mail.gmail.com>
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
Received on Mon Feb 04 2013 - 16:15:14 CET