Home » RDBMS Server » Performance Tuning » Perfectly tuning a Query
Perfectly tuning a Query [message #65452] Thu, 23 September 2004 03:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: To get performance log file for improving performanec on oracle server
Next Topic: Exec DMBS_STATS Visual Basic
Goto Forum:
  


Current Time: Thu Mar 28 04:29:08 CDT 2024