OraFAQ Forum: Performance Tuning » How to Tune Long running queries?

Home » RDBMS Server » Performance Tuning » How to Tune Long running queries? (Oracle 10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Tune Long running queries? [message #550612]

Wed, 11 April 2012 01:16

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

Report message to a moderator

Re: How to Tune Long running queries? [message #550614 is a reply to message #550612]

Wed, 11 April 2012 01:25

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Tune Long running queries? [message #550741 is a reply to message #550614]

Thu, 12 April 2012 01:38

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

Report message to a moderator

Re: How to Tune Long running queries? [message #550746 is a reply to message #550741]

Thu, 12 April 2012 01:54

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Tune Long running queries? [message #551038 is a reply to message #550746]

Sat, 14 April 2012 23:57

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

Report message to a moderator

Re: How to Tune Long running queries? [message #551815 is a reply to message #550741]

Fri, 20 April 2012 18:40

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

Report message to a moderator

Re: How to Tune Long running queries? [message #551816 is a reply to message #551815]

Fri, 20 April 2012 18:47

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.

Report message to a moderator

Re: How to Tune Long running queries? [message #551821 is a reply to message #551816]

Fri, 20 April 2012 20:23

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

Report message to a moderator

Re: How to Tune Long running queries? [message #551823 is a reply to message #551821]

Fri, 20 April 2012 20:37

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.

Report message to a moderator

Re: How to Tune Long running queries? [message #552298 is a reply to message #551823]

Wed, 25 April 2012 00:02

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552304 is a reply to message #552298]

Wed, 25 April 2012 00:49

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552319 is a reply to message #552304]

Wed, 25 April 2012 02:26

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552323 is a reply to message #552319]

Wed, 25 April 2012 02:48

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552373 is a reply to message #552323]

Wed, 25 April 2012 05:40

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552378 is a reply to message #552373]

Wed, 25 April 2012 06:08

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to Tune Long running queries? [message #552389 is a reply to message #552378]

Wed, 25 April 2012 07:35

John Watson
Messages: 8964
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.

Report message to a moderator

Re: How to Tune Long running queries? [message #552391 is a reply to message #552378]

Wed, 25 April 2012 07:41

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

Report message to a moderator

Previous Topic:	how do oracle filter data?
Next Topic:	Query on the performance of JOIN on indexed columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 14:34:27 CST 2025