| Perfectly tuning a Query [message #65452] |
Thu, 23 September 2004 03:30  |
raghukalyan
Messages: 44
Registered: May 2004
|
Member |
|
|
hi ,
I need some basic information regarding Query Tuning.
When a Query is said to be perfectly tuned..Hw can we decide whether query is perfectly tuned..If it is not tuned then wht are the basic things we need to check for the query..
Wht are the possible ways of tuning the query ??Does cost of the query reflects the performance of the query..?If the cost of the query is less then can we say tht it is perfectly tuned...
Hope i will get quick reponse to my query
Regards
Raghukalyan.G
|
|
|
|
| Re: Perfectly tuning a Query [message #65453 is a reply to message #65452] |
Thu, 23 September 2004 03:48  |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
Hi,
You can say a perfectly tuned query is the query which has the best execution plan and the response time is good.
To perfectly tune a query first step is to identify the execution plan for Full table scan. If there are FTS than identify the Indexes if any and if they exist why the query is not using Indexes.Try to analyze and update the statistics.
The resource cost of the query should be less for better response time.
Some things that you can check in order to Tune it :-
1.) remove the distinct unless really necessary
2.) try changing the not in to not exists
3.) try re-writing the NOT IN as an outer join
4.) use (=) wherever possible instead of Like.
keep the statistics updated in a scheduled manner for better performance.
Always remember first concentrate on writing a good query and then go for other aspects as a badly written query will never give you good performance.
All the best.
Milind
|
|
|
|
| Re: Perfectly tuning a Query [message #65454 is a reply to message #65453] |
Thu, 23 September 2004 04:56 |
raghukalyan
Messages: 44
Registered: May 2004
|
Member |
|
|
hi Milind,
Thnks for your quick response..
I am having another question if u don't mind ..
I am having a Query whn i run the execution plan for the same i am getting full table scans on almost all the tables..So i wht i have done is used primary key indexes of the respective tables as hints and avoided FTS..Initally cost was around 1000 but after using hints it was around 5000 to 6000.So wht should i do to reduce the cost..Do i need to create the index for the columns involved in the where clause.??
And one more thing is in the execution plan i am able to see hight cost due to hash join..can u explain me hw joins improve the performance...
Finally wht i have done is deleted the statistics for the table and computed the statistics for the same and then i was able to reduce the cost of the query.
Is that ok ..if not please suggest me
Regards
Raghukalyan.G
|
|
|
|
| Re: Perfectly tuning a Query [message #65456 is a reply to message #65453] |
Thu, 23 September 2004 05:51 |
raghukalyan
Messages: 44
Registered: May 2004
|
Member |
|
|
hi Milind,
Thnks for your quick response..
I am having another question if u don't mind ..
I am having a Query whn i run the execution plan for the same i am getting full table scans on almost all the tables..So i wht i have done is used primary key indexes of the respective tables as hints and avoided FTS..Initally cost was around 1000 but after using hints it was around 5000 to 6000.So wht should i do to reduce the cost..Do i need to create the index for the columns involved in the where clause.??
And one more thing is in the execution plan i am able to see hight cost due to hash join..can u explain me hw joins improve the performance...
Finally wht i have done is deleted the statistics for the table and computed the statistics for the same and then i was able to reduce the cost of the query.
Is that ok ..if not please suggest me
Regards
Raghukalyan.G
|
|
|
|
| Re: Perfectly tuning a Query [message #65461 is a reply to message #65456] |
Thu, 23 September 2004 20:32 |
Milind Deshpande
Messages: 93
Registered: May 2004
|
Member |
|
|
Hi,
The performance of the query mainly depends upon the cost.If you are getting Full table scan cost of a table less in comparison to the cost when Indexes were applied you should not use indexes then.Full Table scans are sometimes better than using Indexes.
And also you might be using some condition in the query that is making the query go for a Full table scan.Identify that condition try to change it if possible.Are you using any filters for the query.
You can definately try by creating Temporary Indexes for the columns in the where clause I think you will get low cosr and Performance will improve but you cannot go on creating duplicate column Indexes.
what you can do is update the statistics of the Table and try and see the response by using Parallel hint on the table which is having large no. of records and undergoing Full Table scan.
For Hash join related Info. you can try this link and search for Hash Join.
http://h71028.www7.hp.com/erc/downloads/11384qpwpfinal.pdf
Regards
Milind.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
