During development phase, the database undergoes continuous changes as designers and developers build different components of the application (schemas, codes, user roles and privileges); experiment with table sizes, indexes (indices), and partitions; create the necessary data feeds and migration procedures; and introduce new constraints for data validation through referential integrity checks; among other activities. Developers and designers (IT staff) may use database tools, such as report generators, screen painters, and menu generators to prototype the applications during the coding process. These changes end when the application is ready for the test phase.

Objectives of the Development Phase should include:
  • Creating development databases using a template configuration
  • Setting up and monitor development schema exports
  • Creating application users and roles and grant privileges
  • Creating application schema(s)
  • Creating and coding all database procedures and scripts
  • Creating data migration procedures and scripts
  • Creating necessary data feeds and/or loads
  • Documenting database configuration
  • Implementing a backup strategy for testing. Etc...

Tuning the Development Design phase
If the database implementation fails to meet some of the system’s evaluation criteria, several options may be considered to enhance the system. The DBA in conjunction with the database developers, work to resolve the performance issues including response time, turnaround time, and throughput that might be unacceptable to users.

Therefore, the development phase can be tuned through the following activities:
  • Use code compilers such as CASE tool, Erwin data modeler, or any similar tool to detect syntax errors, which the developers must correct until the application programs run properly.
  • Perform desk checking or structured walk-through, a process of program code review to spot logic errors, which produce incorrect results.
  • In addition to analyzing logic and application program code, the project team should hold a session with users to perform “design walk-through” to review the interface with a cross-section of users of the system.
  • Modify the logical design by de-normalizing application schema to reduce the number of joins in ad hoc queries and reports, thus improving performance.
  • Modify the physical design where appropriate, by creating new indexes or modifying the existing ones to facilitate indexed search of tables instead of full table scans. Indexes also facilitate effective pointer movements, thus enhancing performance.
  • Integrate views into queries to avoid long-running SQL (SELECT) statements involving views.
  • Partition very large tables to take advantage of parallel full table scans or to access only the required partitions instead of accessing the entire table(s).
  • Implement data replication strategy for copying data to multiple locations to reduce query-processing time and avoid failed updates when communication links to some of the remote sites goes down. CASE tool, Erwin data modeler, or any similar tool should be used to automatically migrate changes to the relevant scripts for schema creation.
  • Work with designers to fine-tune specific application systems and operating system configurations.
  • Upgrade or change the DBMS software and/or the hardware.
When the database system passes the development phase requirements, it is migrated to the test phase.



References:

Afyouni, A. H. (2004). Oracle 9i Performance Tuning: Optimizing Database
Productivity. Thompson Course Tech.

Alapati, S. R. (2008). Expert Oracle Database 11g Administration. Apress.

Burleson, D. K. & Danchenkov, A. B. (2005). Oracle Tuning: The Definitive Reference.
Rampant Techpress

Connolly, M. T, & Begg, E. C, (2002). Database Systems: A Practical Approach to
Design, Implementation, and Management. Addison-Wesley.

Donar, T. (2002). Tru64 UNIX-Oracle9i cluster quick reference (HP Technologies)

Freeman, R. G. (2008). Oracle Database 11g New Features: Maximize the New
Capabilities of the Latest Database Release. McGraw Hill-Osborne.

Johnson, J. C. (2002). OCP: Oracle9i performance tuning study guide. Illustrated Edition.
John Wiley and Sons.

Mittra, S. S. (2002). Database Performance Tuning and optimization: Using Oracle.
Illustrated Edition: Springer.

Oracle8i Designing and Tuning for Performance Release 2 (8.1.6). Retrieved on September 02, 2009 from
http://www.mcs.csuhayward.edu/support/oracle/doc/8.1.7/server.817/a76992/ch19_mem.htm

Stephens, R., Plew, R., & Sams, A. J. (2003). Teach Yourself SQL in 24 Hours, 3rd
Edition. Sams Publishing.

Whalen, Edward. (2005). Oracle Database 10g Linux administration. McGraw-Hill-
Osborne/Oracle Press.