Tuning Goals for Queries and Transaction Processing - Oracle

Changes to the application design may have significant impact on the application’s performance. Application design choices may also directly affect your ability to tune the application. Because application design has such a great effect on the DBA’s ability to tune its performance, the DBA must be involved in the design process.

You must identify the performance goals of a system before it goes into production. The role of expectation in perception cannot be overemphasized. If the users have an expectation that the system will be at least as fast as an existing system, anything less will be unacceptable. The estimated response time for each of the most-used components of the application must be defined and approved.

It is important during this process to establish two sets of goals: reasonable goals and “stretch” goals. Stretch goals represent the results of concentrated efforts to go beyond the hardware and software constraints that limit the system’s performance. Maintaining two sets of performance goals helps to focus efforts on those goals that are truly mission-critical versus those that are beyond the scope of the core system deliverables. In terms of the goals, you should establish control boundaries for query and transaction performance; the application performance will be judged to be “out of control” if the control boundaries are crossed.

The development team must specify the account structure the application will use, including the ownership of all objects in the application and the manner in which privileges will be granted. All roles and privileges must be clearly defined. The deliverables from this section will be used to generate the account and privilege structure of the production application (see Chapter 10 for a full review of Oracle’s security capabilities).

Depending on the application, you may need to specify the account usage for batch accounts separately from that of online accounts. For example, the batch accounts may use the database’s autologin features, whereas the online users have to manually sign in. Your security plans for the application must support both types of users.

Like the space requirements deliverable, security planning is an area in which the DBA’s involvement is critical. The DBA should be able to design an implementation that meets the application’s needs while fitting in with the enterprise database security plan.

The methods for data entry and retrieval must be clearly defined. Data-entry methods must be tested and verified while the application is in the test environment. Any special data-archiving requirements of the application must also be documented because they will be application specific.

You must also describe the backup and recovery requirements for the application. These requirements can then be compared to the enterprise database backup plans (see Chapter 12 for guidelines). Any database recovery requirements that go beyond the site’s standard will require modifying the site’s backup standard or adding a module to accommodate the application’s needs.

Execution plans are the steps that the database will go through while executing queries. They are generated via the explain plan or set autotrace command, as described in Chapter 8. Recording the execution plans for the most important queries against the database will aid in planning the index usage and tuning goals for the application. Generating them prior to production implementation will simplify tuning efforts and identify potential performance problems before the application is released. Generating the explain plans for your most important queries will also facilitate the process of performing code reviews of the application.

If you are implementing a third-party application, you may not have visibility to all the SQL commands the application is generating. As described in Chapter 9, you can use the STATSPACK utility to monitor the most resource-intensive queries performed between two points in time. You can take STATSPACK “snapshots” before and after a test period and then evaluate the execution paths for the most common or most resource-intensive queries during a test period. See Chapter 9 for details on STATSPACK implementation and the use of the repository introduced with Oracle Database 10g.

Developers and users should very clearly define what functionality and performance goals must be achieved before the application can be migrated to production. These goals will form the foundation of the test procedures that will be executed against the application while it is in the test environment.

The procedures should also describe how to deal with unmet goals. The procedures should very clearly list the functional goals that must be met before the system can move forward. A second list of noncritical functional goals should also be provided. This separation of functional capabilities will aid in both resolving scheduling conflicts and structuring appropriate tests.

NOTE

As part of acceptance testing, all interfaces to the application should be tested and their input and output verified.

You can use stored outlines to migrate execution paths between instances, and you can use the Database Resource Manager to control the allocation of system resources among database users. Stored outlines and resource management are important components in a managed development environment. The Database Resource Manager gives DBAs more control over the allocation of system resources than is possible with operating system controls alone.

NOTE