One of the overlooked duties of an Oracle DBA is to inform, educate and assist the application development staff during the application development process. Although these responsibilities may not be formally assigned to the DBA, the DBA unit often finds that they are providing these services by default. The DBA is often considered to be a seasoned veteran who spends most of their time learning the subtle eccentricities of the Oracle database management system.

It is the DBA’s responsibility to ensure that the overall design and implementation of the new application is proceeding according to expectations. Although application developers may be experts in SQL, procedural languages (Java, PL/SQL, C variations, etc.), they still turn to the DBA for help with standards, procedures, performance design, error recovery and complex SQL.

A continuous and effective dialogue between the DBA unit, system support personnel and application developers is of utmost importance throughout the entire application design and implementation process. One method to foster effective communications is for the DBA unit to create a series of new application design review meetings. These meetings can be organized in a manner that promotes efficient application usage of the Oracle database environment. The design review meetings can be scheduled during logical break points between the different stages of the application development process.

The intent of this article is to help administrators design and standardize on a formalized design review process. The goal of the design review process is to identify and address application design, process flow, program logic and SQL statement problems early in the development lifecycle. Identifying these issues early in the development lifecycle allows them to be more easily addressed than if they were to be identified during later stages.

The database administration team should invite representatives from the development and business units to assist in the creation (and enforcement) of the standardized review process. Application development teams will be able to help tailor the design review process to satisfy their specific design and implementation needs. Business users who will be impacted by the production turnover will provide valuable input on implementing new applications for their specific business areas. Customized checklists are created to ensure that all application and business specific issues are addressed during each meeting of the design review process.

It is recommended that the database administration team then communicate to all application development and business areas that any internal applications created without following the standardized review process will not be migrated to production. Having the application teams and business units participate during the creation of the standardized review process allows the DBA team to enforce the policy without being viewed as dictatorial or making rules in a vacuum. In addition, every effort should be made to “sell” the standardized design review process in all communications to application development teams and business units.

The following list of meetings can be used as a starting point in the creation of a structured application design review process:

Initial Overview of Proposed System

The kickoff meeting is held with datacenter operations support managers, operating system administrators, application team leaders, system analysts, end-user management and data administrators to discuss the application’s general purpose and function. This information will allow support technicians to determine the impact the new application will have on existing systems and allow them to begin planning the application design implementation process. The following information should be covered in this meeting:

• General description of purpose and function
• Application scope (enterprise wide application that affects all business units, intra-departmental that affects several business units or departmental)
• General application size (estimated number of programs, data objects)
• Criticalness of the application (mission critical, application critical, non-critical)
• Application availability requirements and downtime windows
• Application type (decision support, business intelligence, data warehouse, data mart, online transaction processing)
• Architecture design (web server, application server, N-tier, distributed database environment)
• Advanced features required (replication, advanced queuing)
• Data feeds required from other architectures
• Load processing
• Batch processing
• Online transaction processing
• Development tools used to build front-end application screens
• Third-party tools used to provide Ad-Hoc query access
• Procedural language used to build business logic (Java, PL/SQL)
• Application development methodology to be used (JAD, RAD)
• Number of concurrent users
• Disk storage and data growth estimates
• Highly available architecture discussion (RAC, Oracle Fail Safe, Data Guard, hardware vendor clustering and failover)
• Performance expectations
• Criteria used to judge performance
• Security and auditing requirements
• Hardware platform, O/S preferences/selection and sizing discussion
• Hardware platform, O/S installation, operation and administration
• Division of duties between the DBA, application development and business units

Logical Data Model Review

This meeting is convened as soon as the logical data model is complete. The major emphasis of this meeting is to determine if the logical data model is complete and correct. The application’s process model (if one is available) can also be verified at this time. Volume statistics, object growth rates, purge criteria, referential integrity needs and application-naming conventions are also discussed. Knowing your data before hand is essential to designing processes to manipulate that data. The following topics are covered in this meeting:

• Determine if the data model is fully documented (entities, attributes, relationships)
• Attributes have correct datatype, length, NULL status, default values
• General discussion of business rules that are to be enforced by database level constraints
• Not null constraints
• Check constraints
• Unique constraints
• Primary key constraints
• Foreign key constraints
• Business rules to be enforced by triggers and procedures
• Business rules to be enforced by application code
• Logical/process model comparison
• Volume statistics
• Growth rates and purge criteria

Designing for Performance

This meeting is held with the application development units before any physical DDL is generated by the DBA. Proper transaction design and efficient SQL coding results in less performance-oriented database alterations being made during the latter stages of the design and implementation process. Determining predicate usage will allow the DBA to create additional database objects (indexes, materialized views, index organized tables) to increase SQL, and subsequently, application performance. The following information is discussed:

• Normalization vs denormalization
• Table access requirements and predicate usage
• Database objects used to increase SQL performance including:
• B-Tree Indexes
• Bitmap Indexes
• Bitmap join indexes
• Function-Based indexes
• Materialized views
• Index organized tables
• External tables
• Data partitioning algorithms
• Range partitioning
• Hash partitioning
• List partitioning
• Composite partitioning
• Process parallelism (parallel query, parallel DML, parallel load)
• Transaction modeling
• Oracle SQL performance features
• Full table scan vs index access
• Hash joins
• Star joins
• Index skip scans
• Index fast full scans
• Cursor sharing (SQL statement reuse and soft parse vs hard parse)
• Bind variables
• Reinforcement of effective SQL coding techniques

Setting up a Successful Test System in Oracle

This meeting is held as soon as the application developers are ready to begin the actual coding process. The ultimate goal of this meeting is for application developers to have a firm understanding of what is required to create and maintain a successful Oracle test environment. Discussions on Oracle utilities, SQL statements, procedural code, Oracle security, batch script testing, benchmarking, testing and monitoring are of prime importance at this time. The DBA must make it clear to all involved that the DBA unit is always available to answer questions and troubleshoot poorly performing SQL throughout the entire application development life cycle. It is imperative that the DBA make every effort to find and correct problems during this stage of the development process. Maintaining a proactive stance instead of a reactive one will always prove to be beneficial when it comes to correcting design problems. There are always enough potential problems lurking in the system for the DBA to solve without adding any additional ones through faulty application design. Topics to be discussed in this meeting include:

• Division of duties between DBA, systems, application development and business units
• Test hardware platform, O/S installation, configuration and administrationD
• Data feeds required from other architectures
• Data load and update processing and scheduling
• SQL Loader control cards
• Test system availability requirements
• Test system backup and recovery requirements
• Oracle security authorization
• Tools to aid in application development
• Oracle Enterprise Manager toolset
• In-house monitoring tools
• Third-party vendor products
• Benchmarking and performance measurements

Monitoring Performance During Testing

Because this meeting and the previously discussed Setting Up a Successful Test System are closely related, they may be discussed together to save time and provide greater clarity. The DBA and developers need to have an understanding of what information is required to effectively monitor the performance of the Oracle application throughout the entire testing process. The DBA can offer to show developers how to use the various toolsets provided by Oracle (Oracle Enterprise Manager, SQL Trace, explain plan, BSTAT/ESTAT, autotrace, Oracle traces, V$ tables and Statspack) so that developers can play an active role in performance measurement. Suggested discussion topics are as follows:

• Names of all SQL Loader control cards and batch scripts
• Performance measurement goals and expectations
• Determine the test load volume (data volume, concurrent users) required to accurately predict production performance
• Comparison of test system load to estimated production load
• Oracle performance measurement tools. Suggested tools include:
• Explain
• SQL*Plus autotrace
• Oracle SQL trace
• Statspack
• Oracle Enterprise Manager with Tuning Pack option
•  V$ performance tables
• Index utilization monitoring via ALTER INDEX MONITORING statement
• Third party performance monitoring tools
• LAN performance monitoring tools
• Operating system performance monitoring tools

Performance Design Reviews

Information collected from the various performance monitoring tools is discussed at this time. One meeting may be sufficient, but large application development efforts usually require several discussions. If the DBA has maintained good communications throughout the initial stages of application design, there should be few surprises when these meetings are held. SQL tuning and tweaking recommendations are covered in this meeting. Depending on the length of the development process, follow-up meetings can be held to ensure that the application is performing as expected. Some suggested topics include:

• Load testing assessment: Is the load being placed on the test system large enough to accurately predict production performance?
• Review performance statistics collected during the testing process
• Assess SQL coding techniques by reviewing explain plan output for performance critical transactions
• Index usage
• Local/join predicates
• Join methods
• Subselects
• View materialization
• Determine if additional Oracle objects need to be created to enhance SQL performance
• B-Tree Indexes
• Bitmap Indexes
• Bitmap join indexes
• Function-Based indexes
• Materialized views
• Index organized tables
• External tables

Preparation for Production Turnover

This meeting is held to determine if there are any last-minute questions and to make certain that everyone knows what is expected of them during the final turnover process. All units that have participated in the application design or application design review process are invited to attend. To ensure that all steps necessary for a successful migration to production are executed, the use of a standardized Oracle migration checklist is highly recommended. This document will allow the DBAs and developers to concern themselves with topics that are related to this specific turnover rather than spending time on the more mundane turnover tasks that are just as important, but easily forgotten. Having a complete, well thought-out migration checklist produces a less stressful and less error prone production migration process. Topics include:

• Division of duties between DBA, systems, application development and business units
• Production hardware platform, O/S installation, configuration and operation
• Data feeds required from other architectures
• Data load and update processing and scheduling
• SQL Loader control cards
• Backup and recovery
• Oracle security authorization
• DBA forms and turnover procedures
• Contact information and escalation procedures
• Post-production monitoring tools to be used

Post-production Turnover

This final set of meetings is held after the production turnover is complete. Application logic errors and performance problem resolution are the prime topics of discussion. A comparison of the actual performance to the anticipated performance of the application is also discussed. The review and correction process, by its nature, is iterative. The problems are identified, a plan of attack to solve them is agreed upon and additional meetings are scheduled to review the results. As additional problems are identified, they are added to the list of problems to be solved. This iterative process continues until all major performance issues and application logic errors are addressed. The post production turnover meetings should include discussions on:

• Review performance statistics collected (Oracle Enterprise Manager, SQL Trace, explain plan, BSTAT/ESTAT, autotrace, Oracle traces, V$ tables and Statspack)
• Assess SQL coding techniques by reviewing explain plan output for transactions experiencing performance problems
• Index usage
• Local/join predicates
• Join methods
• Subselects
• View materialization
• Determine if additional Oracle objects need to be created to enhance SQL performance
• B-Tree Indexes
• Bitmap Indexes
• Bitmap join indexes
• Function-Based indexes
• Materialized views
• Index organized tables
• External tables

Oracle Database Design Review Meetings - Conclusion

These recommendations are not intended to coerce readers into using the Oracle application design review meeting examples verbatim but to emphasize the importance of a structured approach to the design review process. The seemingly endless combinations of application architectures and software products used for application development may require the creation of a fully customized design review process for each application development project. The overall goal of design review meetings is to ensure the involvement of technical support units during the application’s design and implementation. When Oracle design issues are addressed early in the development lifecycle, problems are minimized and the migration from test to production is more easily accomplished.

--

Christopher Foot has been involved in database management for over 18 years, serving as a database administrator, database architect, trainer, speaker, and writer. Currently, Chris is employed as a Senior Database Architect at RemoteDBA Experts, a remote database services provider. Chris is the author of over forty articles for a variety of magazines and is a frequent lecturer on the database circuit having given over a dozen speeches to local, national and international Oracle User Groups. His book titled OCP Instructors Guide for DBA Certification, can be found at http://www.dba-oracle.com/bp/bp_book14_OCP.htm.