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

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 administration
• 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 GRID toolset monitoring capabilities
• 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 GRID AWR reports, SQL Trace, explain plan, autotrace, Oracle traces, V$ tables and my old favorite – 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 GRID monitoring components
  • 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
    • 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 GRID, SQL Trace, explain plan, 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

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.

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

Thanks for reading,

Chris Foot
Oracle Ace
Director Of Service Delivery