Validating the Impact of an Initialization Parameter Change

Before you change the value of a session-modifiable initialization parameter, you can validate the impact of that change on your database workload by using SPA Quick Check. Session-modifiable parameters are initialization parameters whose values can be changed using the ALTER SESSION statement.

Note:

You can use SPA Quick Check to validate the impact of an initialization parameter change in databases running Oracle Database 10g Release 2 (10.2) and later.

To validate the impact of an initialization parameter change:

  1. On the Database Home page in Cloud Control, from the Administration menu, select Initialization Parameters.The Initialization Parameters page appears.

  2. Use the filter on the Initialization Parameters page to identify the session-modifiable initialization parameter whose value you want to change, and click Go to display that parameter in the table at the bottom of the page. Most of the parameters in the Optimizer category are session-modifiable.

  3. In the table, change the current value of the parameter to the new value whose impact you would like to validate using SPA Quick Check.

  4. Click Validate with SPA.
    Description of the illustration spc_init_param.gif follows
    Description of the illustration spc_init_param.gif
    An Information message appears at the top of the page, and says that a SPA task for validating the impact of the initialization parameter change has been submitted.

  5. Click the link for the SPA task in the Information message.The SQL Performance Analyzer Home page appears.

  6. In the SQL Performance Analyzer Tasks section at the bottom of the page, select the task for the initialization parameter job, and click View Latest Report.The SQL Performance Analyzer Task Report page appears.

  7. View the table at the bottom of the page to see what the result of changing the initialization parameter's value would be on the most impactful SQL statements in the workload.