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:
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.
The key to obtaining good SQL performance is to design your application with performance in mind.
This section contains the following topics:
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
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.
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 syntax
checking, security checking, execution plan generation, and loading shared structures into the shared pool.
There are two types of parse operations:
- 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.
- 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.
Because parsing should be minimized as much as possible, application developers should design their applications to parse SQL statements once and execute them
This is done through cursors. Experienced SQL programmers should be familiar with the concept of opening and re-executing cursors.
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.