Query Tuning

UDA
Enthusiast

Query Tuning

Guys,

Can u plse. help me with basic Query tuning Steps..I have been provided with 2 queries and need to improve the performance of the Queries..

I have collected stats on all base tables,  reviewed the Query for cross joins.

What all steps can I further work on to make it run at faster rate..

Any basic guidelines will be appreciated.

Thanks.

4 REPLIES
Enthusiast

Re: Query Tuning

Hello there,

Query and perfomance tuning is not something you can get off the shelf. It depends upon the query. But since you asked basic query tuning steps I will lay out few things. But keep in mind that over collection of your statistics or unnecessary stats collection will have a negative impact.

Pick those queries that are worthy tuning. For instance queries that consume lots of CPU, IO and spool consumption, skewed (PJI and UII greater than 3). Also check if these queries are run frequently.

Checkpoints:

1. Having state and obsolete statistics is much worse than having no stats.

2. Refresh your stats and make sure you have stats collected on the where and ON criteria. Make sure you have stats collected on your NUSI.

3. Check in the queries if a join condition is missed.

4. if there are UNION operations as opposed to UNION ALL and also disntinct on the top which is redundant.

5. Assuming that you have done all this and even then your query is performing poorly, I would recommended to check your explain plan and look for some key indicators showing poor performance of your queries: redistribution, no confidence, product joins, updating of primary indexes (if any, I've seen at different sites doing this, data redistribution is costly in an update operation).

6. If in your explain plan you have some redistributions going on then make sure you have a proper join condition in place, Implicit data type conversion in joins or you might have missed a join criteria or you are not using a PI or at least proper column.

7. if you have a run away query chewing up resources then make sure your join criteria are atleast same data type with stats in place. Make sure you have a equi join rather than cartesian and try to make the query simple rather than huge constraint criteria (predicates) this will throw-away the estimation with the row retrieval.

I only listed a very few there are number of ways of looking and tuning. you can implement global or join indexes hash indexes etc. But one common start point is your explain plan. Hope this helps to further in your perfomance tuning efforts.

Enthusiast

Re: Query Tuning

Few others:

1) Check if there are any functions being used in the joins. Makes stats ineffective.

2) Use DIAGNOSTICS HELP STATS ON FOR SESSION to make sure you are not missing any single column stats

3) Use GTTs where huge amount of data might be in the spool, through a derived table or a volatile table. The advantage of GTT is that you can collect stats on the join columns

4) If all of the above do not help then check the possibility of using a JI.

Enthusiast

Re: Query Tuning

What is the alternate of distinct, by what we can replace distinct and tune the query? 

didnt get this point

 4. if there are UNION operations as opposed to UNION ALL and also disntinct on the top which is redundant.

Enthusiast

Re: Query Tuning

I think what he means to say is that we can use  windowed functions like max(xxxxxx) over(partition by yyyy order by ....)  and qualify with a condition.So rows are restricted. These are applied based on business condtions.