Performance recommendations
To optimize database performance:
Use your fastest disk storage for the portal database. When choosing the device for your Oracle database and if you have a choice between RAID1 - RAID5, choose RAID1.
Minimize I/O wait time. Use the command to determine the I/O wait time.
Consistent query execution is the backbone of the performance of any database application. Even though the Oracle Optimizer is self-sufficient to identify the optimal plan without any user intervention, the execution plan of a SQL statement can get impacted by reasons such as regathering optimizer statistics and changes to the optimizer parameters or schema/metadata definitions. SQL Plan Management (SPM) provides a similar framework and enables complete controlled plan evolution.
To prevent unexpected performance issues, Cohesity recommends to use 'AUTOMATIC PLAN CAPTURE' which is controlled using Oracle parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. You can find more details in SQL Plan Management in Oracle Database 19c white paper.
To enable baseline captures when the performance is stable:
- sqlplus / as sysdba alter session set container = scdb;
- Run following statement to find if the parameter is currently enabled/disabled (TRUE/FALSE):
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES;
- If disabled (FALSE), enable sql baseline capture using following:
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
After about 24 to 48 hours or when all the collections have completed at least once, disable the baselines capture using following steps:
- sqlplus / as sysdba alter session set container = scdb;
- Disable sql baseline capture using following: alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;