| Some questions on SQL tuning [message #287121] |
Tue, 11 December 2007 02:30  |
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 #287317 is a reply to message #287126] |
Tue, 11 December 2007 23:07  |
rleishman
Messages: 3728
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:- Table design - normalisation and strategic de-normalisation
- Index selection
- Segment type selection - partitioning, index-organised tables
- Statistics gathering - scope, sample size, and frequency
- SQL design (using appropriate constructs)
- 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 |
 |
BlackSwan
Messages: 26766
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 #287661 is a reply to message #287317] |
Thu, 13 December 2007 01:24 |
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 #287867 is a reply to message #287667] |
Thu, 13 December 2007 20:41 |
rleishman
Messages: 3728
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 |
 |
Kevin Meade
Messages: 2103
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 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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
