SQL Tuning Refresher for Data warehousing Programmers

ID-100107403SQL tuning is very critical in the context of data ware housing. Before tuning, the following factors we need to consider to take decision.

Isolating the Problem

  • The first challenge is to isolate exactly where the performance problem lies. For example, in the case of a SQL statement which 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.

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 which arise from database configuration, host system configuration and network design. 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.

About 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.

Identifying 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.

Application & Logical Database Design
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


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.