Re: Options for poorly performing SQL

From: Job Miller <jobmiller_at_yahoo.com>
Date: Mon, 4 Feb 2013 14:39:33 -0800 (PST)
Message-ID: <1360017573.27240.YahooMailNeo_at_web126102.mail.ne1.yahoo.com>



You don't say whether this is a bad SQL statement or a bad plan from a SQL statement. If you rewrote the SQL to get the same result set smarter, that's different than you re-writing the statement so that the same statement is optimized better and gets a better plan.

bad plans can be fixed with profiles, but bad SQL with a good plan can only be fixed with a query rewrite or a materialized view/query-rewrite technique to make the bad sql more efficient.

Job



 From: "Coll-Barth, Michael" <Michael.Coll-Barth_at_VerizonWireless.com> To: sbecker6925_at_gmail.com; oracle-l <oracle-l_at_freelists.org> Sent: Monday, February 4, 2013 10:24 AM
Subject: RE: Options for poorly performing SQL  

> -----Original Message-----
> From: Sandra Becker

[mcb >]
> Oracle - EE 11.2.0.2
> OS - SUSE11

> 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.
>

I suspect that what they are doing is building the SQL code based on choices that are being made on a web site.  I've done enough of those. What I did to greatly improve performance was to build a proc and then call it with passed parms.  Even without the proc, just using bind variables will improve response time.

--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 04 2013 - 23:39:33 CET

Original text of this message