Performance deployment is a critical aspect of software deployment that is often undervalued. While it's important to ensure that software is deployed correctly, it's equally important to optimize its performance in the production environment. By recognizing the significance of performance deployment during the software deployment process, developers can ensure that their software performs well and meets the needs of its users. Focusing on performance deployment can help prevent performance issues and improve user satisfaction with the software. The Missing Link of Performance Deployment Between Testing Database and Production Database Despite extensive pre-deployment testing, there is still a chance of encountering performance problems in specific development environments during software performance deployment. The following issues may arise: 1. Inability to copy production data to the testing database. 2. Significant differences in hardware and software configuration between the testing and production databases. 3. Inability to test software in the production database due to security restrictions. 4. The utilization of DML SQL statements in the new software that may damage the data integrity of the production database. It is not unusual for users to face performance issues or encounter application errors following a release of new application code. Ensuring Performance Deployment with a Pre-Deployment Process The subsequent instructions present a novel approach for guaranteeing performance reliability while deploying software. The idea is uncomplicated: since it is not feasible to run the new application code on the production database, why not obtain query plans for every SQL statement in the production database? This way, we can assess the performance of each SQL statement in the application code that is intended for deployment on the production database. Suppose there are 10 SQL statements in the new application code that we need to identify in the testing database. In that case, we need to clear the shared pool and execute the new application in the testing database first to isolate these 10 statements. This process will enable us to capture and analyze the 10 SQL statements and obtain their query plan from the production database. The table below presents various potential outcomes resulting from the query plan comparison. Explain Plan error in the production database The SQL statements requiring access to objects not present in the production database. Query plan changes Significant statistical differences between the testing and production databases, including differences in the database schema. These schema differences may involve missing or new partitions and other changes affecting the database's structure and organization. Benchmarking the SQL may be necessary due to the potential significant changes in performance. Unused indexes Some indexes used in the testing database are not used in the production database. Benchmarking the SQL may be necessary due to the potential significant changes in performance. New used indexes Some indexes used in the production database are not used in the testing database. Benchmarking the SQL may be necessary due to the potential significant changes in performance. Total cost changes Changes in the overall query plan cost for the 10 SQL statements. If the production database has a larger data volume than the testing database, the cost change will be higher. DBAO SQL Performance Tracker - YouTube Tosska DB Ace Enterprise for Oracle - Tosska Technologies Limited
