How to Tune Long running queries? [message #550612] |
Wed, 11 April 2012 01:16 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f71d1fec42c5929468e37dbc03ea9e46?s=64&d=mm&r=g) |
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello
How do we tune long running queries, reports?
I mean when each execution may take few hours how we compare and test performance of a query with various options?
What is the most usual method for this?
Thanks and Regards
Oraratap
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to Tune Long running queries? [message #552389 is a reply to message #552378] |
Wed, 25 April 2012 07:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Tweo contributions:
First, the parallel hint. I have found that in 11.2, it is sometimes best to remove all parallel hints and remove the parallel decoration from tables, and instead rely on the automatic tuning possible through parallel_degree_policy=auto.
Second, in an Exadata environment, I often have to hint statements to force what the optimizer considers to be a bad plan, because the CBO does not understand that Smart Scan can mean that a plan which needs zillons of blocks may be more efficient than one which needs fewer.
These may be sepecial cases, but they serve to show that hints should always be considered on a case-by-case basis.
|
|
|
Re: How to Tune Long running queries? [message #552391 is a reply to message #552378] |
Wed, 25 April 2012 07:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/172194.jpg) |
bobbydurrettdba
Messages: 15 Registered: April 2012 Location: Phoenix, Arizona
|
Junior Member |
|
|
One document I've found useful related to the need for hints is
Limitations of the Oracle Cost Based Optimizer [ID 212809.1]
This is on Oracle's support site. It points out some of the reasons the CBO can't correctly optimize certain queries.
The section on correlation between columns is profound. There are complex relationships between the data in your columns that the optimizer's statistics can't capture.
But, of course you should design your application so you don't need hints on every single query. Otherwise the optimizer isn't gaining you anything.
- Bobby
|
|
|