Transcript
Welcome to the Oracle Troubleshooting Performance Module. This module will cover identifying performance issues with the Oracle agent and steps to address them once identified. When discussing Oracle performance troubleshooting, it is important to understand that both Commvault and Oracle must be investigated. When looking at the Commvault facets, changes can be made to the subclient properties job streams and the backup arguments. At the Oracle level, changes can be made to the RMAN file parameters to affect throughput. Oracle performance logging is done via the CVPerfManager.log with copies located on the client and backup gateway. Let's review an example output here and try to identify a bottleneck. First notice the job ID for the job being examined listed in two locations. There is broad interpretation of the write and read speeds for the job listed here. Breaking this down further, the write speeds, meaning the speed the job is writing to our media is reporting that the write over the pipeline was averaging around 1.4 terabytes an hour. The read from Oracle was roughly 1 gigabyte per hour. Since the job can only write as quickly as it can read the data, the observation can be made that the slowness being experienced on this job pertains to the read side performance. Let's review some of the options available to help improve general job throughput for the reads and writes. Since the majority of changes in Commvault occur at the subclient level, let's quickly review how to get there. After logging into Command Center, expand the sidebar, which can be done using the ellipses button in the upper left-hand corner. From here, navigate down to Protect followed by Databases. On the Database Information page, click on the Instances tab. Once the Instances page loads, locate the Oracle instance to be modified and click on the name. On the Instance Information page, go to the Subclient tab. After this, select the subclient by clicking on the name, at which point we'll arrive at the Subclient Configuration page. The most lightweight change that can be made is to increase the number of data streams. But what exactly is a data stream and what does increasing it do? When you run a backup, data streams are the network streams that begin at the client level and run through to the backup gateway. Increasing these streams can help improve performance by allowing more data to move between the client and the backup gateway at once. Commvault recommends that these increases to data streams be done incrementally since increasing the streams will increase network communication and load on the client and media agent, respectively. With this in mind, let's look at the General section of our subclient, where we can make this change. The identifier for the number of data streams this subclient uses here, and they can be edited by clicking the Edit box on the right-hand side. After this, a new window will appear that will allow the data streams to be changed to a value between 1 and 100. Once the changes are made, click Submit to set it. Please note, once the change is made, this will affect newly submitted jobs only. Now let's review the backup arguments revolving around Oracle's Recovery Manager, also known as RMAN. The options seen here will be reviewed. The options mention BFS, but what exactly is that? BFS stand for Big File Server, and this setting refers the maximum number of data or archive files included in a single Recovery Manager backup set. When backups are submitted from Command Center, the Oracle client is contacted, and then RMAN service is leveraged to facilitate the backup. Let's take a look at the edits that can be made by clicking on the Edit option here. The Edit window is already populated with a number of variables that Commvault sets by default. Let's review them in order and discuss how they can improve your performance. Firstly, the previously mentioned Data Streams parameter can also be increased here. Please keep in mind that this option and the General tab option are linked, so any changes made here will change the value in general and vice versa. Next is the Data Files for BFS setting. This defines the number of data files in each backup set and will allow RMAN to combine the contents of multiple data files into a single buffer. Increasing this setting can improve performance by processing multiple data files more quickly, however, if Commvault deduplication is being used, it is recommended to keep this setting to 1 in order to improve dedupe performance. This is because, during the combination of data files, the DDB can identify these combined files as wholly new chunks, increasing query and insert times which in turn increase backup times. The Archive File setting has the same effect as the previous setting but exclusively for archive files. This can be increased to add more archive files into a single buffer. By processing multiple files together, the overall backup time can potentially be lowered. The maximum number of open files defines the number of open files that can be read by RMAN in a single session. Having multiple open files can cause resource contention and I.O. issues. This number can be lowered to potentially increase the performance. The RMAN backup piece size, which is unlimited by default, can be set to a maximum size in either megabytes or gigabytes. By limiting the size of each RMAN backup piece, the backup process can be improved by making it more efficient and manageable. Smaller pieces can be processed faster, which can reduce the overall backup time and assist with restore performance for large databases. Resource utilization can also be improved since less CPU and I.O. bandwidth is used, which prevents resource bottlenecks and ensures smoother backup operations. Commvault recommends a size of at least 2 megabytes if this setting is utilized. The final two options, Validate, which checks files for corruption during the backup process, and Check Logical, which verifies the logical consistency of the database, are diagnostic steps that would not be used to optimize your backup performance and will not be discussed in this module. If the Oracle Database Administrator does not wish to use Commvault to make changes to the Oracle parameters, the changes can be made at the Oracle level via SQL commands. Let's discuss some of the commands that can be used to tune your performance, but please note, Oracle settings will always supersede setting changes made at the Commvault level and should only be run by a Database Administrator. Files Per Set is the first Oracle command we will review. This is the manual way to adjust the files in an RMAN backup set and can be designated for either database or archive files. Here is an example output of each command specifying the different file types here. Note the second line in each screenshot specifying either database or archive logs. Max Open Files is the setting used to set a maximum number of open files during an RMAN read operation. Here is an example of the command. Please note that a lower number of open files tends to improve performance on most systems. In this example, the number has been set to 1. If Oracle compression is enabled along with Commvault or Storage compression, performance may be affected. Compression in Oracle can be disabled via the command here. Setting the compression algorithm to None will disable it for the targeted backup set. When a table or other database object is dropped in Oracle, it doesn't immediately remove the data. Instead, it renames and stores the object in the Recycle Bin, making it possible to recover it if needed. The issue here is that during a Commvault backup, this Recycle Bin is scanned and if there are a large number of objects, backup time can be increased. To address this, there are two solutions at the Oracle level. The first option is to disable the Recycle Bin when this option is used. Deleted objects in Oracle are permanently deleted. However, the Recycle Bin will be skipped on a browse operation during backups. This can be done via the following command run on the Oracle database. The second option is to purge the Recycle Bin. This option, just like the Recycle Bin on a PC desktop, will permanently delete all Recycle Bin content ensuring a smaller or no backlog of files for the browse process to go through. The database administrator can achieve this via the following command. There's rarely a single correct answer when throughput issues occur, so please experiment with what works best for your environment. Thank you again for your time and attention on this Oracle Performance Troubleshooting Guide. Have a wonderful rest of your week.
