($$) Why is SQL tuning important?
First in the series of steps undertaken for tuning the database.
One of the least expensive steps.
Generally highest possible performance gain.
($$) Some things to know about SQL tuning
The DBA & Developers should work together when tuning statements. During design and development, the application Developers can determine which combination of system resources and Oracle features best meet the performance goals and requirements as established by the business rules. The best possible execution time occurs with the usage of least amount of resources, which include I/O (logical and physical) to and from the database, CPU usage.
There are many tools that can assist in the tuning endeavor such as TKPROF, the SQL trace facility, SQL Analyze, Oracle Trace, and the Enterprise Manager Tuning Pack. But tuning SQL statements remains a trial and error process.
SQL tuning should be done prior to looking at database tuning effort. Until it is certain that all the applicable SQL statements are tuned, the system parameters should not be modified to adjust for poorly written SQL.
SQL tuning involves the least expense compared to hardware changes to increase the memory capacity of the systems.
For SQL tuning to be accomplished, it is necessary to know what the current execution time is and what resources are needed to successfully execute the statement there by improving its performance.
It is very essential to write optimal queries so that the CPU resources can be equally shared among the various users connected to the systems in a fair manner.
Oracle execution path may not be the source of the performance problem; it is very likely the wrong approach to the problem has been followed. This may not even be the best path to follow. It is helpful if alternative ways of accomplishing the same tasks are looked into. High performance improvements may be achieved by making a simple statement work from a different angle.
The initial focus should be on the most offending SQL, as they will yield the best immediate returns.
There are many tools that can assist in the tuning endeavor such as TKPROF, the SQL trace facility, SQL Analyze, Oracle Trace, and the Enterprise Manager Tuning Pack. But tuning SQL statements remains a trial and error process.
SQL tuning should be done prior to looking at database tuning effort. Until it is certain that all the applicable SQL statements are tuned, the system parameters should not be modified to adjust for poorly written SQL.
SQL tuning involves the least expense compared to hardware changes to increase the memory capacity of the systems.
For SQL tuning to be accomplished, it is necessary to know what the current execution time is and what resources are needed to successfully execute the statement there by improving its performance.
It is very essential to write optimal queries so that the CPU resources can be equally shared among the various users connected to the systems in a fair manner.
Oracle execution path may not be the source of the performance problem; it is very likely the wrong approach to the problem has been followed. This may not even be the best path to follow. It is helpful if alternative ways of accomplishing the same tasks are looked into. High performance improvements may be achieved by making a simple statement work from a different angle.
The initial focus should be on the most offending SQL, as they will yield the best immediate returns.
Posts
