Basic SQL Tuning Tools in Oracle
The purpose of this module is to address the issues surrounding the development and tuning of SQL statements in an Oracle database shop.
Historically, the development of SQL has been fragmented with the development staff writing SQL and the DBA staff tuning the SQL statements.
Now, with the evolution of Oracle and the use of SQL in stored procedures, the DBA is taking a more active role in the approval and tuning of all SQL statements that run against the database because the tuning of SQL statements can have more positive impact than almost any other tuning activity.
This module will show the various roles of the developer and the DBA as well as discuss methods and standards that the DBA can use to ensure quality and consistency of SQL development. Topics in this module include:
- Identifying the roles of the DBA in SQL tuning
- Enforcing SQL standards
- Working with SQL developers
- Using SQL in stored procedures
Let us get started by identifying the roles of the DBA in SQL tuning.
About SQL Tuning
SQL tuning is the iterative process of improving SQL statement performance to meet specific, measurable, and achievable goals.
SQL tuning implies fixing problems in deployed applications. In contrast, application design sets the security and performance goals before deploying an application.
Guidelines for Designing Your Application
The key to obtaining good SQL performance is to design your application with performance in mind. This section contains the following topics:
Guideline for Data Modeling
Data modeling is important to successful application design. You must perform data modeling in a way that represents the business practices.
Heated debates may occur about the correct data model. The important thing is to apply greatest modeling efforts to those entities affected by the most frequent business transactions. In the modeling phase, there is a great temptation to spend too much time modeling the non-core data elements, which results in increased development lead times. Use of modeling tools can then rapidly generate schema definitions and can be useful when a fast prototype is required.
Guideline for Writing Efficient Applications
There are two types of parse operations:
During the design and architecture phase of system development, ensure that the application developers understand SQL execution efficiency.
To achieve this goal, the development environment must support the following characteristics:
- Good database connection management: Connecting to the database is an expensive operation that is not scalable. Therefore, a best practice is to minimize the number of concurrent connections to the database. A simple system, where a user connects at application initialization, is ideal. However, in a web-based or multitiered application in which application servers multiplex database connections to users, this approach can be difficult.
With these types of applications, design them to pool database connections, and not reestablish connections for each user request.
- Good cursor usage and management: Maintaining user connections is equally important to minimizing the parsing activity on the system.
Parsing is the process of interpreting a SQL statement and creating an execution plan for it.
This process has many phases, including a) syntax checking, b) security checking, c) execution plan generation,
and d) loading shared structures into the shared pool.
Effective use of bind variables: Application developers must also ensure that SQL statements are shared within the shared pool. To achieve this goal, use bind variables to represent the parts of the query that change from execution to execution.
If this is not done, then the SQL statement is likely to be parsed once and never re-used by other users. To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements.
- Hard parsing: A SQL statement is submitted for the first time, and no match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse.
- Soft parsing: A SQL statement is submitted for the first time, and a match is found in the shared pool. The match can be the result of previous execution by another user. The SQL statement is shared, which is optimal for performance. However, soft parses are not ideal, because they still require syntax and security checking, which consume system resources.
Because parsing should be minimized as much as possible, application developers should design their applications to parse SQL statements once and execute them many times. This is done through cursors. Experienced SQL programmers should be familiar with the concept of opening and re-executing cursors. For example:
Statement with string literals:
WHERE last_name LIKE 'KING';
Statement with bind variables:
WHERE last_name LIKE :1;
The following example shows the results of some tests on a simple OLTP application:
Test #Users Supported
No Parsing all statements 270
Soft Parsing all statements 150
Hard Parsing all statements 60
Re-Connecting for each Transaction 30
These tests were performed on a four-CPU computer. The differences increase as the number of CPUs on the system increase.
Oracle Tuning Reference