🖥️ Database Environment:
🔹 Operating System: HP-UX 11.31
🗄️ Database Environment: Oracle Real Application Clusters (RAC)
⚙️ Database Version: Oracle Database 19c Enterprise Edition (19.26)
🎯Problem Statement:Object Movement of 7.4 TB table and index partitions took approximately 4.5 hours in database testing environment, while the production maintenance window allows only 90 minutes of downtime. The objective was to identify performance bottlenecks and determine whether the activity could be completed within the available maintenance window. This troubleshooting exercise focuses on: - Oracle RAC parallel execution. - Session and SQL execution analysis. - Table partition movement and index rebuilds. - Performance benchmarking and production readiness. |
❓ Key Technical Questions: Q 1. Archive Log Generation Across multiple RAC Instances : When Oracle Parallel Execution is enabled with 'parallel_force_local = FALSE', will Oracle generate archive logs on different RAC instances where parallel execution (PX) processes are running? Q 2. Parallel Process Distribution Across RAC Instances: When 'parallel_force_local = FALSE', will Oracle automatically spawn and distribute parallel execution (PX) processes across multiple Oracle RAC instances, or will the processes remain on the instance where the SQL statement is executed? Let's troubleshoot the issue step by step. Step 1: Examine Session Activity and Execution Time The first step was to review session activity and execution times to understand how the table partition movement and index rebuilds were performed. Object Details:
The table and index partition movement was performed using parallel execution in a lower environment. - Table partition movement was executed separately on the local node. - Index partition rebuilds were subsequently executed concurrently from different cluster nodes. Parallel Parameters (Test DB Server): parallel_force_local=TRUE parallel_max_servers=96 Executed On Node1: SQL> alter session force parallel DDL parallel 96; Session altered. SQL> alter table APPUSER.TRAN move partition TRAN_390 tablespace TBS_DATA_APP parallel 96; Table altered. Elapsed: 01:17:36.71 After completion of above command, the following index rebuild commands executed concurrently across different cluster nodes. SQL> alter session force parallel DDL parallel 96; Session altered. SQL> alter index APPUSER.TRANPK rebuild partition TRANPK_390 parallel 96; Index altered. Elapsed: 02:06:20.42 SQL> alter session force parallel DDL parallel 96; Session altered. SQL> alter index APPUSER.TRANUK_02 rebuild partition TRANUK_02_390 parallel 96; Index altered. Elapsed: 03:19:49.28 SQL> alter session force parallel DDL parallel 96; Session altered. SQL> alter index APPUSER.TRANUK_04 rebuild partition TRANUK_04_390 parallel 96; Index altered. Elapsed: 03:09:45.87 Observation: The table partition and all three index partition rebuilds completed successfully with a requested parallel degree of 96. Execution times ranged from 2 hours, 6 minutes to 3 hours, 20 minutes, with TRANUK_02_390 taking the longest. Combining the table movement time with the longest index rebuild time, the second index, TRANUK_02_390 gives a total execution time of 4 hours, 37 minutes, and 25 seconds (04:37:25). Here, one thing we noticed that the sessions executed and running on local nodes only where the command were executed. Each rebuild individually exceeded the maintenance window. Further analysis of session activity, actual parallelism, and resource utilization is needed to identify the bottlenecks. The objective is to identify the causes of the extended runtime and determine how to complete the activity within the permitted (90 Minutes) window. 💡 Can Oracle RAC use parallel execution across multiple instances to reduce the total maintenance time? Is there a way to utilize parallel processing across all Oracle RAC nodes? The initial results suggested that further investigation of PX distribution, actual parallelism, and resource utilization was necessary. Note for Dataguard: When Multi-Instance Redo Apply (MIRA) is configured in a standby environment, uneven redo generation across the primary RAC nodes can lead to variations in redo apply performance and delays on the standby database. For example, if the redo generation rates differ across the primary RAC Nodes - Node1(20 logs), Node2(30 logs), Node3(50 logs), and Node4(70 logs). This imbalance may affect the parallel redo apply process in the standby environment. As a result, the archive log gap between the primary and standby databases may continue to increase during periods of uneven workload or high redo generation. This gap may persist until the corresponding workload or activity window subsides and the standby database processes the accumulated redo logs. Once the redo apply process catches up with the incoming redo stream, the standby database can gradually return to a synchronized state with the primary database. Step 2: Enable Parallel Execution Across Oracle RAC Instances. Let’s examine the PARALLEL_FORCE_LOCAL parameter, which controls whether parallel execution is restricted to the local instance. FALSE (default): Parallel execution processes can run on any or all RAC nodes. TRUE: Parallel execution processes are restricted to the local node only where the query is executed. #Check the current parallel parameter values: parallel_force_local=TRUE parallel_max_servers=256 #Repeat the table partition movement with the following settings: parallel_force_local=FALSE parallel_max_servers=64 SQL> ALTER SYSTEM SET parallel_force_local=FALSE SCOPE=BOTH SID='*'; SQL> ALTER SYSTEM SET parallel_max_servers=64 SCOPE=BOTH SID='*'; 04:34:19 SQL> alter table APPUSER.TRAN move partition TRAN_390 tablespace TBS_DATA_APP parallel 256; Table altered. Elapsed: 00:21:03.29  SQL_ID : fc82cffyqkkvk SQL_TEXT : alter table APPUSER.TRAN move partition TRAN_390 tablespace TBS_IDX_APP parallel 256 The session snapshot shows SQL_ID: fc82cffyqkkvk active across RAC instances 3, 5, 6, and 7, indicating that activity for this operation was distributed across multiple instances. The table partition move completed in 21 minutes and 3.29 seconds. Although the command requested PARALLEL 256, PARALLEL_MAX_SERVERS was configured as 64 per instance. With PARALLEL_FORCE_LOCAL=FALSE, parallel execution could span multiple RAC instances. After the offline table partition movement, rebuild any associated indexes or index partitions marked UNUSABLE. Step 3: Rebuild Index Partitions Using Cross-Instance Parallel Execution After the table partition movement, the three index partitions were rebuilt sequentially, with each rebuild using parallel execution. #Let's start by rebuilding the first index. parallel_force_local=FALSE parallel_max_servers=64 04:57:00 SQL> alter index APPUSER.TRANPK rebuild partition TRANPK_390 parallel 256; Index altered. Elapsed: 00:11:16.42  SQL_ID : cv5w32krkj1hv SQL_TEXT : alter index APPUSER.TRANPK rebuild partition TRANPK_390 parallel 256 Why can an index rebuild use more PX workers than the requested degree? Certain parallel index operations use two PX server sets: one for reading and distributing index entries and another for sorting and building the index. Based on the session logs, parallel execution spanned all eight RAC instances, with 64 workers per instance, totaling 512 parallel worker sessions. Why parallel 512 for index rebuild ? This is because one set of 256 parallel workers read and distributes index entries, while another set of 256 workers sorts the entries and builds the index. The index partition TRANPK_390 rebuild completed in 11 minutes and 16.42 seconds. Although the command requested PARALLEL 256, PARALLEL_MAX_SERVERS was configured as 64 per instance. With PARALLEL_FORCE_LOCAL=FALSE, parallel execution could run across multiple RAC instances. #Let's rebuild the second index with the following parallel execution settings: parallel_force_local=FALSE parallel_max_servers=64 05:09:29 SQL> alter index APPUSER.TRANUK_02 rebuild partition TRANUK_02_390 parallel 256; Index altered. Elapsed: 00:15:33.80  SQL_ID : 9ufzhppan3mhu SQL_TEXT : alter index APPUSER.TRANUK_02 rebuild partition TRANUK_02_390 parallel 256 The rebuild of partition TRANUK_02_390 for index APPUSER.TRANUK_02, identified by SQL_ID: 9ufzhppan3mhu, completed successfully in 15 minutes and 33.80 seconds. The command requested PARALLEL 256, while PARALLEL_MAX_SERVERS was configured as 64 per instance. With PARALLEL_FORCE_LOCAL=FALSE, Oracle could distribute parallel workers across multiple RAC instances. #Let's proceed with rebuilding the third index. parallel_force_local=FALSE parallel_max_servers=64 05:25:52 SQL> alter index APPUSER.TRANUK_04 rebuild partition TRANUK_04_390 parallel 256; Index altered. Elapsed: 00:14:16.66  SQL_ID : 9kkrm92wwnqxu SQL_TEXT : alter index APPUSER.TRANUK_04 rebuild partition TRANUK_04_390 parallel 256 The third index rebuild, for partition TRANUK_04_390 of APPUSER.TRANUK_04, identified by SQL_ID: 9kkrm92wwnqxu, completed successfully in 14 minutes and 16.66 seconds. The command requested PARALLEL 256, with PARALLEL_MAX_SERVERS set to 64 per instance. The PARALLEL_FORCE_LOCAL=FALSE setting allowed parallel execution to span multiple RAC instances. ❓ Step 4: Technical Questions and Answers:Q.1. Will Oracle generate archive logs on different RAC instances where parallel processes are running with PARALLEL_FORCE_LOCAL = FALSE? ✅Yes. In Oracle RAC, when 'PARALLEL_FORCE_LOCAL = FALSE', parallel execution (PX) processes may run on multiple RAC instances, depending on resource availability, configuration, and execution requirements. When a PX process performs a database change, redo is generated in the redo thread associated with the instance where that change is performed. For example, If a parallel operation runs across RAC instances 1 and 2, both instances generate redo. Q.2. When 'parallel_force_local = FALSE', will Oracle automatically spawn and distribute parallel execution (PX) processes across multiple Oracle RAC instances, or will the processes remain on the instance where the SQL statement is executed? ✅Yes. Oracle distributes parallel execution processes across multiple RAC instances, depending on factors such as parallel degree, available PX servers, resource management, and instance configuration. 🎯 Performance Improvement:The reported elapsed time decreased from 4 hours, 37 minutes, and 25 seconds to 1 hour, 2 minutes, and 10.17 seconds. This represents an approximately 77.6% reduction in elapsed time, based on the recorded totals. The revised test finished approximately 27 minutes and 50 seconds before the 90-minute limit.  🏁 Final Conclusion:The initial table and index partition movement took approximately 4 hours and 37 minutes, exceeding the production maintenance window of 90 minutes. After changing PARALLEL_FORCE_LOCAL to FALSE and revising the parallel server configuration, the test showed cross-instance PX execution. The table partition movement and three sequential index rebuilds completed in 1 hour, 2 minutes, and 10.17 seconds. The results demonstrate the potential benefit of cross-instance parallel execution and benchmarking different execution strategies in Oracle RAC. The revised approach met the target maintenance window in the test environment, leaving approximately 28 minutes for additional maintenance and validation. Production deployment should proceed only after confirming resource capacity, object consistency, recoverability, and performance under representative workload. 🎉 Enjoy the troubleshooting journey! 📝 Stay tuned for more Oracle Database performance tuning case studies. 💬 Thanks for reading! Share your experiences, questions, and suggestions in the comments. |
Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get next blog updates !
📢Join Me on:
