The Art of Being a Successful DBA – Application Design Review Meetings, Part 1

Let’s continue our discussion on the Art of Being a Successful DBA. The intent of this blog 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 life cycle allows them to be more easily addressed than if they were to be identified during later stages.  That last statement bears repeating.   Like any other issue you face, no matter what it is (personal, professional, whatever), the sooner you identify it, the easier it is to correct.

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 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, TSQL, 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 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 (Agile, Waterfall, 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 modeling effort is finished. 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 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 or “lets collapse those 49 tables into one big one for fast read access”
• Table access requirements and predicate usage
• Database objects used to increase SQL performance including:
  • B-Tree Indexes
  • Bitmap Indexes
  • Function-Based indexes
  • Materialized views
  • Index organized tables
  • External tables
  • Data partitioning algorithms (range, hash, list, etc.)
• 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

Read The Art of Being a Successful DBA – Application Design Review Meetings, Part 2.

Thanks for Reading,

Chris Foot
Oracle Ace
Director Of Service Delivery