SQL tuning is much needed for data warehousing projects. The point is how to fix the issue when you identify the problem. These six steps will help you to fix the performance issue.
Before tuning, the following factors we need to consider to take a decision.
Steps to Fix SQL Query Performance When You Identify an Issue
Step 1: Identify the problem
- The first challenge is to isolate exactly where the performance problem lies. For example, in the case of a SQL statement that executes poorly, the performance of the statement may be only a symptom, not the actual problem.
- The underlying problem may lie within the overall database configuration or operation. Or it may be the PL/SQL application in which the SQL statement is found. Conversely, one may begin with a focus on the database or a PL/SQL program unit only to find that an embedded SQL statement is the true source of the problem.
Step 2: May be issue in database
Furthermore, SQL statement performance cannot be separated from the performance of the database in general.
This often indicates that a collaborative approach to performance tuning is required. Database, system and network administrators typically address performance issues that arise from database configuration, host system configuration, and network design.
Step 3: Role of Senior Developers
Senior database developers generally address issues related to SQL statement construction and database-resident program design using PL/SQL and Java. And both groups must work together in the diagnosis effort.
Step 4: Check the response time
In the context of SQL statement execution, response time is the time to receive a response from the database for a SQL statement, as the response perceived by the user or the application.
The ultimate goal of a tuning effort will primarily seek to improve the response time.
Step 5: Identify External Factors
Other, more distant, external factors can also have a dramatic impact on performance. One of the most important is the hardware and software environment within the overall systems infrastructure.
Just as a SQL statement is affected by the database environment in which it executes, on an even larger level the entire database installation is itself affected by the execution environment.
Therefore, tuning, configuring, troubleshooting the overall systems architecture is also critical. Similarly, adding additional hardware resources may be a necessary step.
Database installations, in turn, exist within a systems area (SAN), local area (LAN) or wide area network (WAN). Network design and configuration is another important factor.
This is especially true with modern 3-tier and n-tier application environments. In such cases SQL statements may be issued from application servers, clients and users may be connected to web and HTTP servers, and so on.
Step 6: Database Design issues
One of the most fundamental problems which can contribute to poor systems performance is poor application design.
This could be the design of the logic within the application modules or the logical data model upon which the database application is based.
It is nearly impossible to tune an application when its design is poor. Thus, the best database design and the most expensive hardware resources can all be squandered by poor application design.
Ref: Oracle 11g R2
Srinimf 