Home » RDBMS Server » Performance Tuning » Some questions on SQL tuning
Some questions on SQL tuning [message #287121] Tue, 11 December 2007 02:30 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I have a couple of questions.

From what I read and hear people say that the order of joining tables and the order of predicates are very important towards SQL tuning. I understand that we need to filter out as many rows as possible using predicates with indexed columns, then joning those small set of rows with Full table scan tables.
Can you please crisply explain the importance of the join & predicate ordering in SQL ? What are the most important things to look for ?

Also my bosses want the SQL to be perfectly tuned at the development stage itself. But from our discussions I find that unless we have the real pattern of data distribution like selectivity, repetitiveness/uniqueness of the column data, it's not possible to accurately tune the query. Also that would leave us only with explain plan to analyze but actual picture is obtained only by TKprof, isn't it ? What should be done in such cases ?
Re: Some questions on SQL tuning [message #287126 is a reply to message #287121] Tue, 11 December 2007 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read How to Identify Performance Problem and Bottleneck and its links.

Regards
Michel
Re: Some questions on SQL tuning [message #287317 is a reply to message #287126] Tue, 11 December 2007 23:07 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Without wanting to sound dismissive or patronising, the only way to design for performance is to have the experience under your belt first. In order to truly understand performance TUNING, you must first see bad-performance first-hand.

For much the same reason, doctors must perform internships before they become qualified to treat patients solo.

Oracle is a complex database and the subject area of performance tuning is quite broad. I think it is overly ambitious to attempt to design a highly performant system without the necessary experience.

The best thing you could do is to hire a recognised tuning expert for the first 6 months of your project to give you a head start. This is precisely what my former employer did when I was a junior in charge of the development of a major new strategic system. I learned probably half of what I know now about Oracle in just 9 months.

As for the question of whether it is possible to build a perfectly tuned system without understanding the data distribution; that's only partly true.

Assuming adequate hardware, network, and configuration of the Oracle database itself, the most important factors (for performance) are in my optinion:
  1. Table design - normalisation and strategic de-normalisation
  2. Index selection
  3. Segment type selection - partitioning, index-organised tables
  4. Statistics gathering - scope, sample size, and frequency
  5. SQL design (using appropriate constructs)
  6. Hints and optimiser control
Understand of the data has a very small impact on all these, but a large impact on the last - but least important - one.

What I'm saying is that you can get it 95% of the way without a deep understanding of the data (some idea is required obviously). The other 5% can be achieved with minor impact closer to the end of the project.

Ross Leishman
Re: Some questions on SQL tuning [message #287318 is a reply to message #287121] Tue, 11 December 2007 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Also my bosses want the SQL to be perfectly tuned at the development stage itself.

Will the data architecture be designed for OLTP or data warehouse?

Does this mean that there will be NO ad hoc reporting done against this new database?

I think your PHB needs a mauve database.

[Updated on: Tue, 11 December 2007 23:15] by Moderator

Report message to a moderator

Re: Some questions on SQL tuning [message #287324 is a reply to message #287317] Tue, 11 December 2007 23:34 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for your valuable advice, Ross.
Re: Some questions on SQL tuning [message #287325 is a reply to message #287318] Tue, 11 December 2007 23:35 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
It is for a datawarehousing project. Adhoc reporting is a part of next phase of the project.
Re: Some questions on SQL tuning [message #287661 is a reply to message #287317] Thu, 13 December 2007 01:24 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
One of my seniors insists that Table join order can make a big difference in SQL performance. He says that in fact the ORDERED hint is one of the most widely used hints for just this reason.

Also he opines that with the Oracle CBO it will generally find the most efficient join order (based on column histograms). and we need to be just sure to gather stats with the dbms_stats package.
But another guy contradicts that table join order dependancy existed only during Rule based optimizer was dominant but with CBO, this dependancy has since been eliminated.

I'm trying to figure out which is more relevant. Can you help ?

Re: Some questions on SQL tuning [message #287663 is a reply to message #287661] Thu, 13 December 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't trust your DBA.
Ask the ones that know: read T. Kyte and J. Lewis books.

Regards
Michel
Re: Some questions on SQL tuning [message #287667 is a reply to message #287121] Thu, 13 December 2007 01:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Also he opines that with the Oracle CBO it will generally find the most efficient join order (based on column histograms). and we need to be just sure to gather stats with the dbms_stats package.
>But another guy contradicts that table join order dependancy existed only during Rule based optimizer was dominant but with CBO, this dependancy has since been eliminated.

So have each provide benchmarks that supports their position!
Which is the most likely for for your system?
Re: Some questions on SQL tuning [message #287867 is a reply to message #287667] Thu, 13 December 2007 20:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You're mixing up 2 problems:

The order of tables in the FROM clause was important for the RULE based optimizer. If there is no indexed access path into any of the tables the RBO will start with the last table listed.

This is not a problem with the CBO, which uses statistics to find not only the best starting table, but the optimal join order.

What IS a problem is that with 5 or more tables in a join, CBO does not evaluate all of the execution options. Because it misses some (many) out, it may miss the optimal one. Worse, it may not evaluate ANY good plans.

The ORDERED and LEADING hints mitigate this behaviour.

Ross Leishman
Re: Some questions on SQL tuning [message #288101 is a reply to message #287121] Fri, 14 December 2007 16:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
nice one Ross.

Also I would add that there as always been a wive's tale about the order of predicates in the where clause. Here is what I know:

1) it is true, the order of predicates can have an impact on the CPU cost of a query.

2) it is also true that the CBO does not use this ordering for anything except a last resort and thus if you are tuning based on order of predicates then you are wasting your time.

Years ago, an inventive fellow presented a paper at an Oracle conference in which he demonstrate how #1 above was so. I forget if he then went on to mention that the order was only relevent after all other join paths were determined ( driving table, order of tables, indexes used, join methods used, etc.) and thus in almost every situation it means nothing.

Consider this however:

select *
from T1
where a = 1
and b = 2
and c = 3
and d = 4
and e = 5
/
T1 has no indexes.
T1 has 100 million rows
column A is always 1
column B is always 2
column C is always 3
column D is always 4
column E is NEVER 5

if order of predicates is such that Oracle checks a first, then b, then c, then d, then e, it does five checks per row to figure out it does not want the row,

if order of predicate is such that Oracle checks e first, then it does not check anything else because a check of e eliminates each row and thus checking d,c,b,a is unnecessary. Oracle does only one check per row.

Thus you can do 500 million checks to get no rows
or.. you can do 100 million checks to get no rows

This has in the past made a difference in some rare situations. But, if you spend any time trying to optimize your system by re-ordering predicates, you are wasting time that could have been spent doing some kind of intelligent tuning.

This is one of the many forms of "short circuiting" that Oracle does to give us better performance in some situations.

So the basic rule, is, forget about order of predicates in the where clause and concentrate on creating better data models, better sql, and learning and using new features.

As for Order of tables in the From clause, I defer to Ross.

Good luck, Kevin
Re: Some questions on SQL tuning [message #288217 is a reply to message #288101] Sat, 15 December 2007 22:39 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
We're getting a little off-topic now. But predicate order is "important" for the same reason as table order in the FROM clause.

Kevin is right - the CBO works out the best order in which to apply predicates for both access, joining, and filtering. The order you use is irrelevent.

However just like the FROM clause, the CBO sometimes gets it wrong. Like the /*+ODERED*/ hint for the FROM clause, you can also use the /*+ORDERED_PREDICATES*/ clause to control the order of predicate execution.

So, if you're happy that CBO is getting it right, table and predicate sequence in the SQL is unimportant. If you want to help the CBO out, it IS important.

Ross Leishman
Previous Topic: STATSPACK
Next Topic: To improve execution time of query
Goto Forum:
  


Current Time: Fri Dec 09 17:53:48 CST 2016

Total time taken to generate the page: 0.51764 seconds