7 Using SPA Quick Check
Oracle Enterprise Manager Cloud Control (Cloud Control) includes the SQL Performance Analyzer Quick Check (SPA Quick Check) feature. On some Cloud Control database management pages, SPA Quick Check can validate the impact of a system change to the database workload before you make the change.
You can use SPA Quick Check to validate what the impact to your database workload will be for the following changes:
-
Changing the value of an initialization parameter
-
Gathering pending optimizer statistics
-
Implementing key SQL profiles
Note:
SPA Quick Check is available starting with Cloud Control Release 12.1.0.4 Bundle Patch 8 and later.
SPA Quick Check is supported for any database running Oracle Database 10g Release 2 (10.2) and later. However, not all the SPA Quick Check features are supported in Oracle Database 10g Release 2 (10.2).
For example, optimizer pending statistics and Automatic SQL Tuning Advisor features are available starting with Oracle Database 11g Release 1 (11.1), so SPA Quick Check workflows for these features are only supported for databases running Oracle Database 11g Release 1 (11.1) and later.
This chapter describes how to use SPA Quick Check and contains the following topics:
7.1 About Configuring SPA Quick Check
Before you can use SPA Quick Check to validate the impact of an initialization parameter change or of gathering pending optimizer statistics, you must specify default settings for SPA Quick Check.
You will specify a default SQL tuning set for SPA Quick Check to use as one of the settings, and this SQL tuning set should include SQL statements used in the database application you are trying to tune.
Note:
It is not necessary to set default values for SPA Quick Check before using SPA Quick Check to validate the impact of implementing one or more key SQL profiles.
7.2 Specifying Default Values for SPA Quick Check
You specify default settings for SPA Quick Check on the SQL Performance Analyzer Setup page in Cloud Control.
7.3 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.
7.4 Validating the Impact of Pending Optimizer Statistics
Before you gather pending optimizer statistics, you can validate the impact of gathering those statistics on your database workload by using SPA Quick Check.
Note:
You can use SPA Quick Check to validate the impact of gathering pending optimizer statistics in databases running Oracle Database 11g Release 1 (11.1) and later.
7.5 Validating the Impact of Implementing Key SQL Profiles
Before you implement key SQL profiles for SQL statements, you can validate the impact of using those profiles by using SPA Quick Check. You can validate the impact of key SQL profiles on the Automatic SQL Tuning Result Summary page. Key SQL profiles are profiles verified to yield at least a 3 times performance improvement, and which would have been implemented automatically if auto-implementation had been enabled for Automatic SQL Tuning Advisor.
Note:
You can use SPA Quick Check to validate the impact of implementing key SQL profiles in databases running Oracle Database 11g Release 1 (11.1) and later.
7.6 Validating Statistics Findings from Automatic SQL Tuning Advisor
You can validate the impact of statistics findings from Automatic SQL Tuning Advisor using SPA Quick Check.
Note:
You can use SPA Quick Check to validate the impact of validating statistics findings from Automatic SQL Tuning Advisor in databases running Oracle Database 11g Release 1 (11.1) and later.