| Approach for Oracle Profiles to tune queries [message #654398] |
Tue, 02 August 2016 03:20  |
orausern
Messages: 826
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  |
John Watson
Messages: 8964
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 |
orausern
Messages: 826
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
