Overview

Customers may experience performance issues with their Valero database, where the transaction time is longer than expected. This could be due to multiple potential causes such as multiple 'Updating' operations, potential resource constraints on the database server, and the absence of active sessions in related databases.

Solution

The solution to this issue involves adjusting the backup process to reduce CPU usage. The high CPU utilization is often linked to the logical backups being taken during the daytime and then compressed using the gzip utility. This process can cause the system to work harder, leading to additional CPU consumption. The following steps are recommended:

1. Avoid taking backups during peak hours and compressing the backups.
2. Move the backups from previous days to a different partition before the backups are taken.
3. Monitor the I/O speed during the RMAN backup to ensure it doesn't go below 100Mbs.
4. Modify the RMAN backup script to eliminate the compression feature due to high CPU usage caused by it and update the database path.
5. Create a directory on oracle (connected to sqlplus) using the following script:

   CREATE DIRECTORY logical_backup AS '/ORAFRA/backup'; GRANT READ, WRITE ON DIRECTORY logical_backup TO SYS;

6. Monitor the system's CPU usage and ensure that the CPU is idle for a significant percentage of the time, indicating that there is more than enough processing power available, and there are no signs of CPU bottlenecks or resource constraints.
7. Check the IO speed on the RMAN backup to ensure it doesn't go below 100Mbs.
   • Use the following script to check the speed:

     COLUMN input_GB FORMAT 999999999.99 COLUMN output_gb FORMAT 999999999.99 COLUMN start_time FORMAT A20 COLUMN ob FORMAT A15 COLUMN stt FORMAT A10 COLUMN ttd FORMAT A15 SELECT start_time AS st, round(input_bytes_per_sec/1024/1024,2) input_GB, round(output_bytes_per_sec/1024/1024,2) output_gb, OUTPUT_BYTES_DISPLAY AS ob, status AS stt, time_taken_display AS ttd FROM V$RMAN_BACKUP_JOB_DETAILS WHERE trunc(start_time) = trunc(sysdate-3);

Summary

Performance issues in Valero databases can be resolved by adjusting the backup process to reduce CPU usage. This involves avoiding backups during peak hours, moving old backups, monitoring I/O speed, modifying the RMAN backup script, creating a new directory in Oracle, and monitoring CPU usage.

FAQ

1. What causes high CPU usage in Valero databases?
   High CPU usage can be caused by logical backups being taken during the daytime and then compressed using the gzip utility.
2. How can I reduce CPU usage in my Valero database?
   You can reduce CPU usage by adjusting the backup process, which includes avoiding backups during peak hours, moving old backups, monitoring I/O speed, modifying the RMAN backup script, and creating a new directory in Oracle.
3. How can I monitor the I/O speed during the RMAN backup?
   You can monitor the I/O speed by using the provided script in the solution section, which checks the speed and ensures it doesn't go below 100Mbs.