Oracle 19c Database Switching from RULE based Optimizer to COST Based Optimizer in VERITAS APTARE IT Analytics

Description

Future releases of Veritas APTARE IT Analytics will by default use a different Oracle query optimizer - the Cost based optimizer. Previous installs on version 10.6 or upgrades to future versions can continue to use the old Rule based optimizer, but we recommend manually changing to the Cost based optimizer. The steps to make that change are documented here.

Also, please note, before changing the optimizer_mode to ALL_ROWS, please upgrade your environment to 10.6 P6 or later. Otherwise, the SDK reports will fail when you upgrade later.

1.     What is the Oracle optimizer?

Veritas stores the Veritas APTARE IT Analytics data inside an Oracle 19c database. The data is retrieved from the Database using SQL (Structured Query Language). The efficiency of retrieval of the data by querying the database tables is the job of the Oracle Optimizer, and it does that using a query execution plan.

As there is more than one possible way to retrieve the data, the optimizer compares the plans and chooses the plan with the lowest cost. The output from the optimizer is an execution plan that describes the optimum method of execution. The plans show the combination of the steps Oracle Database uses to execute a SQL statement.

2.     What are the two types of Oracle Query Optimizers, and their differences?

RULE and COST  Based Optimizers.

Rule based optimizer was the initial Oracle optimizer. Later Oracle came up with a more intelligent Cost based optimizer.

The main difference is that the Rule based optimizer chooses the best execution plan based on Oracle’s pre-defined rules. The Cost based optimizer on the other hand, rather than going by some pre-defined rules, chooses the best Query execution plan more intelligently, based on the available Statistics regularly collected for Oracle database tables and indexes.

3.     Why Switch to cost-based optimizer?

The benefits of Cost based Optimizer over the Rule based in selecting best possible execution plan based on Statistics tend to benefit the overall Query performance. The Cost based optimizer is the more popular and more modern approach and can result in reduced query execution times and system CPU load.

4.     What are the potential risks?

a.     There should be NO risks in general for the OOB (Out-Of-the Box) Reports in Veritas APTARE IT Analytics Portal as those reports have gone through an internal QA performance certification

b.     On some rare occasions a Custom developed report MAY run into potential slowdowns and may require tuning to work more efficiently with the Cost based optimizer.       

5.     How to minimize the risks of this change in the Production Environment?

a.     If you have a TEST environment, refresh it with a copy of your Production database to ensure the same size of data.

b.     Perform a thorough test of your key reports, including the OOB and Custom Reports. If any performance issue is found, resolve the issues in the TEST environment first.

When doing the testing, first identify key reports and run them while the optimizer is still set to RULE, noting down the response times.  The “Report Activity Detail” report under the  “Systems Administration Reports” folder will report on the database response times for each report run.

c.      Make the Optimizer Change from Rule to Cost in the TEST environment as per the instructions provided below and run the exact same reports and note down / compare the response times.

6.     What are the steps to switch the Oracle Optimizer from RULE to COST?

1)    Shutdown the Veritas APTARE IT Analytics Portal, data receiver and Oracle Services

2)    Make a copy of the file  /opt/aptare/oracle/dbs/initscdb.ora file. Then edit the file and set the “optimizer_mode” parameter to ALL_ROWS

Note: This changes the “optimizer_mode” at the oracle instance level after the oracle instance is restarted. Any new databases created (ONLY) will inherit this change automatically.

Note: If you are using Windows Veritas APTARE IT Analytics portal, the location of the file may be different, please refer to the product Documentation

3)     Now restart the Oracle service.

4)     Now change the “optimizer_mode” for the pluggable database “scdb” to “ALL_ROWS”.

$sqlplus / as sysdba

SQL>alter session set container=scdb;
SQL>alter system set optimizer_mode=ALL_ROWS;
SQL>show parameter optimizer_mode

The output should show “ALL_ROWS”. 

Note: The “ALTER SYSTEM” command makes the change permanent. You should never have to repeat these steps again, even if the oracle is restarted.

5)     Using SQL*Plus confirm the optimizer_mode is NOT set to RULE anymore when connected as Database User “portal”.

$sqlplus portal/<portal_password>@scdb

SQL>show parameter optimizer_mode

The output should show ALL_ROWS

Note: if for whatever reason, it’s still showing RULE as the value of optimizer_mode, then please STOP and do not proceed, repeat the step 6 from the beginning to ensure you didn’t miss anything accidentally, once you confirm the output shows optimizer_mode of ALL_ROWS, only then proceed).

6)     Collect Fresh Statistics for the "scdb" database (the 4 lines below create a small PL/SQL Block) by running this PL/SQL code,

SQL>begin
       dbms_scheduler.run_job(job_name  => 'GATHER_SCHEMA_STATS');
    end;
    /

Note: This could take a few minutes in some cases, kindly be patient.

7)     Restart the Veritas APTARE IT Analytics Portal and data receiver services and resume working with the Veritas APTARE IT Analytics portal.

7.     In worst case scenario, can I switch back to RULE Based optimizer? 

Yes, you can, but Veritas DOES NOT recommend it, refer to Step 3 for why it's important we switch to the cost-based optimizer.

8.     Oracle documentation Link

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_MODE.html