At this point, the database has passed the test environment and ready to become fully operational to serve its purpose or it’s already fully operational. In transition to this phase, a lot of effort has been put into achieving the higher quality and effectiveness in application, and ensuring that all information and documentation needed to complete production phase is available. Such information and documentation must be clear, complete and consistent. This is the most costly phase in SDLC, and depending on the different data structures and different transaction loads, database tuning at this phase can maximize the return on investment (ROI) on system hardware, software, and other application programs. As a result, minimizing the overall total cost of ownership (TCO) and providing users the maximum performance a database system can deliver.

Objectives of the Production Phase should include:
  • Setting up production database and schemas.
  • Managing allocation of machine resources among production databases.
  • Migrating data to the production database.
  • Migrating PL/SQL packages and compile into database.
  • Using tools to monitor the database and install scripts.
  • Tuning and improve database performance as required.
  • Verifying backups and perform random recovery testing.
  • Verifying hardware, software, and network configurations.
  • Verifying network connectivity, specifications, and capability, including operational and technical requirements for communications hardware and software.
  • If necessary, upgrading network resources to support the new system, but only after testing the platform rigorously before system installation begins.

Post-Production Evaluation
The post-production phase examines all aspects of the development effort and the end product, and the feedback should include the following areas:
  • System reliability and maintainability
  • Accuracy, completeness, and timeliness of information output
  • End-user satisfaction
  • Adequacy of system controls and security measures
  • Hardware efficiency and platform performance
  • Effectiveness of database implementation period
  • Performance of the IT team
  • Completeness and quality of the documentation
  • Quality and effectiveness of user training
  • Accuracy of cost-benefit estimates and development schedules.
Tuning the Production Phase
(a). Identify performance goals:
Work with business analysts to establish a reasonable and definable performance goals that will help you successfully tune your system. Tuning goals also help you determine when to tune, what to tune, how to tune it, and when you are done tuning. Without defined goals, you may never be done tuning. It’s not enough to say that “performance of this application must be faster”, it must defined in terms of a quantifiable response time.

(b). Tune application code:
Application code tuning should involve working with developers to tune the data model when SQL code tuning does not resolve the production database performance problem. However, this is the most expensive because it’ll require changes to the SQL code, application code, and the production database. (Please refer to section 2.2.1 Tuning the logical design phase).

(c). Optimize application design:
Application tuning should involve working with system designers and developers to tune SQL statements and indexes in order to achieve better execution plans and data access patterns; and taking advantage of databases features, such as partitioning and parallel query. Applications and processes that utilize a lot of CPU resources or other shared resources should be monitored and eventually eliminated. (Please refer to section 2.2.2 Tuning the physical design phase).

(d). Configure optimal Hardware specification:
Hardware tuning is the act of modifying the hardware to perform optimally in your configuration. This involves setting optimal hardware specifications for database server configuration and connection management; configuring the system to use the CPU resources efficiently; and configuring storage media technologies that hold the database files, such as RAID disk arrays, storage area networks (SAN), and network file systems (NFS). Hardware can be tuned by modifying the driver parameters and firmware configurations depending on the type of the hardware, and reconfiguring or adding new hardware to the existing database system to improve performance. This is necessary when the hardware resources, such as I/O subsystem and system RAM have been exhausted, and when efforts to tune the application and database instance to reduce resource use have not improved performance enough.

(e). Allocate data files and file structure according to OFA standards:
OFA standards are intended to improve database performance by spreading administrative tasks, improving detection and preventing fragmentation in datafiles, spreading I/O functions across separate devices, and improving performance by separating products into distinct directories. For example, the OFA standard can be used to organize the tablespaces in a certain way so that the Oracle database can have maximum performance, thus improving response time in query or transaction processing. In accordance to OFA standards, disk Striping provides significant performance advantages because all the space from the striped drives will appear as a single logical drive. Hence, each file can be spread over all of the striped disks allowing multiple CPUs to access data randomly without conflict.

(f). Develop optimal memory configuration:
Fetching data from memory is a lot faster than fetching data from storage disk (disk I/O). Hence, the most frequently or recently accessed data is kept in the shared global area (SGA) in addition to shared parsed SQL code and data dictionary information for quick access. The correct memory size allocation depends on the nature of your application, the nature of users and the size of transactions. If there isn’t enough memory, the application will have to perform time-consuming disk I/Os. However, over-allocating memory takes away memory resources from the operating system that is needed by other applications.

(g). Tune I/O:
Excessive I/O is usually caused by unwise tablespace or table allocation, an improper setting of Oracle block size, bad memory configuration, or poorly written queries. For example, proper sizing of the shared pool can reduce resource consumption in at least three ways according to Oracle documentation:
·    Parse time is avoided if the SQL statement is already in the shared pool. Thus saving CPU resources.
·    Application memory overhead is reduced, because all applications use the same pool of shared SQL statements and dictionary resources.
·    Disk I/O resources are saved since dictionary elements are in the shared pool.

(h). Prevent and reduce contention:
Performance problems stem form many causes, such as improper design code resource configuration, flawed data model design, lack of scalability, improper sizing of the memory structure or tablesspaces, issuing queries that require extensive sorting, network traffic congestion, among other contention issues. Use the various Oracle tools available to identify and troubleshoot the problem areas. For example for locking issues, uses Lock Monitor, V$LOCK view, or DBA_WAITERS view to determine the locked objects and sessions blocked from obtaining locks, and find out which user, application, or process is locking the object and kill it off.

(i). Configure optimal setup of the Operating system:
Some operating systems provide the capability to collect and record relevant performance statistics for individual programs. This important for identifying those programs, other than Oracle processes that are utilizing a lot of resources. Oracle Enterprise Manager Diagnostics Pack can be used to capture and analyze operating system, application, middle tier, as well as database performance data, and display the results graphically or alerts administrators via email or paging.

(j). Configure optimal Network specification:
The performance of the interconnected network system is very important to the overall system performance. The main concern is often performance problems that arise due to heavy network traffic generated by the system or insufficient bandwidth that is incapable of handing normal traffic demands. Network tuning means making sure that the network can handle the expected traffic as well as keeping a pool of connections open to handle future client requests. Oracle provides the Transparent Network Substrate (TNS) and Oracle*Net for distributed communication between databases. Hence, network administrators can control much of the network performance tuning remotely by adjusting the size and frequency of packets shipping across the network. Or alternatively use multiple listeners to perform load balancing by setting the SDU, TDU and queuesize parameters in the listener.ora or tnsnames.ora.



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.

2. 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

Page, W. G. (1999). Using Oracle8/8i, Special Edition. Chapter 20: Que Books.
Retrieved on September 28, 2009 from http://docs.rinet.ru/O8/index.htm

Piedad, F. & Hawkins, M. (2001). High Availability: Design, Techniques, and Processes.
Prentice Hall PTR, New Jersey.

Rob, P. & Coronel, C. (2004). Database Systems: Design, Implementation, &
Management. 6th Edition. Thompson Course technology

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

Shelly, G. B., Cashman, T. J., & Rosenblatt, H. J. (2000). System Analysis and Design.
4th Edition. Shell-Cashman Series. Course Technology.

Turban, E., Aronson, J. E., Liang, T. P. (2005). Decision Support Systems and Intelligent
    Systems, 7th Editon. Pearson Prentice Hall.

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