| Re: Query regarding Tuning [message #59623] |
Tue, 09 December 2003 04:42  |
Daya shankar
Messages: 2
Registered: December 2003
|
Junior Member |
|
|
Dear sir,
Well for the SQL tuning of those report which are not performing well what i should do? can you sugeest some document on sql tuning which i should follow.
Regards
Daya Shankar
|
|
|
|
|
|
| Re: Query regarding Tuning [message #59625 is a reply to message #59623] |
Tue, 09 December 2003 05:19  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi Again,
Just check the queries if there is any problem in code or not? like r ur queries using sub-queries if yes, so upto wht extend, wht is the role of available indexes in ur query, wht is the coding standard, how much sorting they need, and where this sorts are taking place i.e. in memory or in disk if it is in disk this may be the reason of performance bottleneck. Also analyze the tables which have been used by ur queries. To identify all this just user explain plan and autotrace feature.
And as per sudhir u should consider oracle doc too to identify the performance bottlenecks properly on ur own basis.
Just start with process and gather the statistics. On the basis of that statistics u will be able to take further steps.
Good Luck.
|
|
|
|
| Re: Query regarding Tuning [message #59631 is a reply to message #59623] |
Wed, 10 December 2003 07:06 |
scott
Messages: 73
Registered: September 1999
|
Member |
|
|
The first thing I would suggest is check your query.
There is a list of things to look for. I cannot write all of them but here are a few that you might want to start with.
Check to see stuff like NOT IN and replace with EXISTS
Instead of using UNION try using UNION ALL. Reduce usage of DISTINCT, ORDER BY and GROUP BY if not required. As Sudhir said read the performance tuning manual and check for SQL query tuning chapter. Once you are satisfied with SQL Query tuning you might want to involve your DBA to check Indexes on the tables that you are querying. Use the AUTOTRACE command and generate a report using the PLAN_TABLE. Things that you might want to look at would be Full table scans. They usually kill query performance. At this point you will be close to reducing query run time at least by 50 %. Your DBA can you help you tune any further.
Scott
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
