Home » RDBMS Server » Performance Tuning » How to Tune Long running queries? (Oracle 10.2.0.4)
How to Tune Long running queries? [message #550612] Wed, 11 April 2012 01:16 Go to next message
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 #550614 is a reply to message #550612] Wed, 11 April 2012 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: How to Tune Long running queries? [message #550741 is a reply to message #550614] Thu, 12 April 2012 01:38 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Thanks for the links

I have read the article posted by Ross few months ago. Of course I am reading it again and it is useful

I am sure it would guide me for getting good execution plan in many cases but it won't completely eliminate trial runs of the SQLs

Is it possible to ensure performance of a sql statement by partial or no execution of a sql statement?

Thanks and Regards
Orapratap
Re: How to Tune Long running queries? [message #550746 is a reply to message #550741] Thu, 12 April 2012 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to ensure performance of a sql statement by partial or no execution of a sql statement?


The only way is to force only one type of operation: nested loops
But in this case, you will often have suboptimal plans.

Regards
Michel
Re: How to Tune Long running queries? [message #551038 is a reply to message #550746] Sat, 14 April 2012 23:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not sure what you are getting at when you say avoid execution of a query so you can explain that and I'll consider it. But as for how to tune long running queries, that can be a big topic (as seen by Ross's article).

I'll give you my 30 second lowdown:

95% of the time, a long running query will have an optimal query plan that takes one of two forms:

1) NESTED LOOP w/ INDEX LOOKUPS
2) FULL TABLE SCANS and HASH JOINS

You just have to coerce your query to give you the better plan. This sounds simplistic and certainly it is but overall this is a very good explanation of where the Oracle performance technology is right now. I am amazed every day by how I can take a slow running query and get orders of magnitude improvement by simply removing a NESTED LOOP JOIN from the plan. Many people are hung up on using indexes but technology has changed dramatically in the last five years to the point where if you are not fetching less than 2% of the data from your table, then you should seriously consider the FULL TABLE SCAN. On EXADATA it is even more extreme.

HASH JOINS outperform NESTED LOOP almost everytime, if former is OPTIMAL or ONE-PASS.

Remember also that there are two basic costs to a query:

1) fetching the rows from each table
2) stitching rows you fetch together via joins

You need to optimize both to get the best plan but 80% of the time the primary cost is in the joins not the fetching.

Try to optimize each of these two with respect to each table, and don't be afraid of the FTS and HASH JOINS.

Good luck, Kevin
Re: How to Tune Long running queries? [message #551815 is a reply to message #550741] Fri, 20 April 2012 18:40 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
You could make smaller copies of your tables and try various plans against them. You could use hints to force the plan. Then each of your test cycles would be shorter. It would be important to keep the relative sizes of the tables the same and make sure you didn't choose some where conditions that didn't return any rows.

But, you would have to include the hints in your production SQL otherwise it might use a totally different untested plan against the real data.

The performance tuning guide has a section about giving a full set of hints. If you specify them fully (not always possible) then your tests on a small subset could be valid when run on production.

- Bobby
Re: How to Tune Long running queries? [message #551816 is a reply to message #551815] Fri, 20 April 2012 18:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You never get a second chance to make a first impression.

Other folks need to keep in mind that with free advice, you get what you paid for it.

SQL may work at acceptable level against "smaller" tables &
perform at unacceptable level when the number of rows increase by three order or magnitude.
Only a fool who thinks he smarter than the CBO attempts to use hints in any production application.
Re: How to Tune Long running queries? [message #551821 is a reply to message #551816] Fri, 20 April 2012 20:23 Go to previous messageGo to next message
bobbydurrettdba
Messages: 15
Registered: April 2012
Location: Phoenix, Arizona
Junior Member
Oracle corporation uses hints throughout its internal code and in its applications.

There are risks of testing with smaller data sets but in some cases it is the only practical option.

The key would be to to have the same plan in testing as in production however you make that happen.

- Bobby
Re: How to Tune Long running queries? [message #551823 is a reply to message #551821] Fri, 20 April 2012 20:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Oracle corporation uses hints throughout its internal code and in its applications.
Oracle also continues to use LONG datatype, while advising everyone to avoid LONG datatype.
Excusing one's bad behavior based upon other's bad behavior makes for a lousy justification, IMO.
Just because something can be done, does not mean it should be done.
You can embed hints in SQL. You can poke yourself in the eye with a sharp pencil.
I suggest that either would be foolish act to do.
Re: How to Tune Long running queries? [message #552298 is a reply to message #551823] Wed, 25 April 2012 00:02 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

Kevin, thanks for advice. In fact before execution of a SQL statement I am doing the same check only I am yet to know 'how to check one pass' etc before the actual execution

Bobby, Thanks for the suggestion, I shall try what you have suggested. I will think of providing cardinality hint and set stats in addition to populating small tables

BlackSwan, thanks for your comments
you said Quote:
Just because something can be done, does not mean it should be done

I think you have suggested this so that nobody shall direcrtly jump to the option of Hints before looking for other better options like trying other joins, changing query structure, checking/ gathering stats etc.

you have also said Quote:
Only a fool who thinks he smarter than the CBO attempts to use hints in any production application

Though sounds little harsh I am sure you want to advice on the same line I mentioned above..i.e.use hint only if everything else fails

I have seen queries run better by providing hints in many cases

Is it possible to take a "Yes / No" poll in this forum for "If hints were useful to you?"

Thanks and Regards
Orapratap
Re: How to Tune Long running queries? [message #552304 is a reply to message #552298] Wed, 25 April 2012 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have seen queries run better by providing hints in many cases


But are these queries well written?

Regards
Michel
Re: How to Tune Long running queries? [message #552319 is a reply to message #552304] Wed, 25 April 2012 02:26 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

In many cases, I know, I would say "Yes"

Hints were tried as last option and it worked

Even expert like Ross has many times suggested hints and it has helped me as well as many others

May be some of the hints are not much helpful but I think it would be difficult to generalize their usage or non-usage.

For example parallel hint I found much useful in many cases

Thanks and Regards
Orapratap
Re: How to Tune Long running queries? [message #552323 is a reply to message #552319] Wed, 25 April 2012 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
For example parallel hint I found much useful in many cases


This is not the hint I thought about nor FIRST_ROWS%, ALL_ROWS that indicates your environment, nor LEADING or ORDERED for queries with too much tables but those that wants to force the optimizer to go in a way it does not want.

Regards
Michel
Re: How to Tune Long running queries? [message #552373 is a reply to message #552323] Wed, 25 April 2012 05:40 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Apart from what you have mentioned I have seen performance improvement for sql statements using hints (mainly) index, use_hash, ordered, full, materialize etc, which influence the optimizer

Thanks and Regards
Orapratap
Re: How to Tune Long running queries? [message #552378 is a reply to message #552373] Wed, 25 April 2012 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have seen performance improvement for sql statements using hints


And I say this is mainly because (in order of likelyhood) 1) the query is badly written 2) the statistics are not correct 3) there is a bug in the optimizer

Regards
Michel
Re: How to Tune Long running queries? [message #552389 is a reply to message #552378] Wed, 25 April 2012 07:35 Go to previous messageGo to next message
John Watson
Messages: 8922
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
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
Previous Topic: how do oracle filter data?
Next Topic: Query on the performance of JOIN on indexed columns
Goto Forum:
  


Current Time: Fri Mar 29 10:39:20 CDT 2024