Home » RDBMS Server » Performance Tuning » Approach for Oracle Profiles to tune queries (Oracle 11.2.0.4 on LInux)
Approach for Oracle Profiles to tune queries [message #654398] Tue, 02 August 2016 03:20 Go to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Hi,

I have a question on the approach to tune queries using Profiles and will be thankful for your thoughts on it:

We have a situation where a query was taking more than 2 minutes in Production and the application timeout is set to 60 seconds so the users were getting timeouts. We ran the automated tuning from OEM and got suggestion to use profile which was estimated to provide more than 99% benefit. The suggestion was implemented and it did work out and the same query is now taking less than 3 seconds. The question is: would this be the end of the issue or should something more be done to research further and how? meaning:
1)Should we then import the profile in all our lower environment.
2)Should we continue to depend on profile or if not what should we do?

Thanks,
OrauserN
Re: Approach for Oracle Profiles to tune queries [message #654399 is a reply to message #654398] Tue, 02 August 2016 03:42 Go to previous messageGo to next message
John Watson
Messages: 7624
Registered: January 2010
Location: Global Village
Senior Member
Profiles don't fix a problem, they cover it up. They are a quick get-you-out-of-trouble hack. The problem is that the optimizer is coming up with a bad plan; the cover up is to suggest it use a different one. You need to find out why the optimizer is getting it wrong. Probably, inadequate statistics. Correct that, and you will fix a whole range of queries, not just the one your profile patches.
Re: Approach for Oracle Profiles to tune queries [message #654400 is a reply to message #654399] Tue, 02 August 2016 03:49 Go to previous messageGo to next message
orausern
Messages: 819
Registered: December 2005
Senior Member
Thank you John.

In this particular case I had specifically verified that stats are up to date for all tables concerned but with one thing to note that the query has a function based index and the query uses 2 scalar subqueries. Is there anything specific to statistics of function based indexes that I should verify - I don't know how to do that so asking. Also any issue with scalar subqueries in general?

Thanks again,
OrauserN

[Updated on: Tue, 02 August 2016 03:50]

Report message to a moderator

Re: Approach for Oracle Profiles to tune queries [message #654401 is a reply to message #654400] Tue, 02 August 2016 04:03 Go to previous messageGo to next message
John Watson
Messages: 7624
Registered: January 2010
Location: Global Village
Senior Member
Statistics may be up to date, but inadequate. For example, if a predicate uses AND or OR to connect columns, the estimates will be wrong unless you have explicitly created extensions. See this for an example,
http://www.skillbuilders.com/webinars/webinar.cfm?id=97&w=Tuning-Oracle-SQL-with-Extended-Statistics
Re: Approach for Oracle Profiles to tune queries [message #654402 is a reply to message #654401] Tue, 02 August 2016 04:19 Go to previous message
orausern
Messages: 819
Registered: December 2005
Senior Member
Got it. Thanks a lot John! I will explore on this lines.
Previous Topic: consecutive executions
Next Topic: Why rownum is not used in the query
Goto Forum:
  


Current Time: Tue Oct 23 10:48:44 CDT 2018