Enabling DIAGNOSTIC SET PROFILE
You can control the availability of DIAGNOSTIC SET PROFILE requests with the EnableSetCostProfile DBS Control field. For details, see Utilities and SQL Request and Transaction Processing . This field controls usage of DIAGNOSTIC SET PROFILE statements to dynamically change cost profiles used for query planning.
The following table describes each of the settings:
| Value | Definition |
|---|---|
| 0 | The system default cost profile cannot be changed by a DIAGNOSTIC SET PROFILE request. The default EnableSetCostProfile value. The EnableCostProfileTLE DBS Control field is set FALSE. The DIAGNOSTIC SET PROFILE statement is disabled. The system aborts attempts to submit DIAGNOSTIC SET PROFILE requests and returns an error. |
| 1 | A nondefault cost profile can be activated only at the SESSION and REQUEST levels. |
| 2 | A nondefault cost profile can be activated at all levels. |
Optimizer Cost Profiles and Logon Profiles
The profiles referred to by DIAGNOSTIC SET PROFILE are Optimizer cost profiles, not database logon profiles.
About Optimizer Cost Profiles
- A name
- A type
- A collection of named constant values (the collection varies by profile type)
A profile is identified by name and is an instance of its profile type. Instance definitions in the dictionary can supply alternative constant values.
There are several valid cost profiles defined in the Data Dictionary for special purposes, but the following profile is the candidate system profile that supplies the standard set of calibrated CPU cost parameter values.
- TD15
For more information, see SQL Request and Transaction Processing .
System Response When profile_name Does Not Exist
The system returns an error if the specified cost profile is not found.
If the CostProfileId in the DBS Control record is not a valid cost profile ID, the standard default profile for the system remains unchanged.
Scope Level Precedence
Scope levels have a sequential precedence. The following table lists the precedence ratings for the various scope levels:
| Precedence | Option |
|---|---|
| 1 | REQUEST |
| 2 | SESSION |
| 3 | IFP An IFP-level scope applies only to the current parsing engine. |
| 4 | SYSTEM |
The following table indicates how precedences cascade:
| For this scope definition … | The scope for the profile definition defaults to this level … |
|---|---|
| NOT ON FOR REQUEST | SESSION |
| NOT ON FOR SESSION | IFP An IFP-level scope applies only to the current parsing engine. |
| NOT ON FOR IFP | SYSTEM |
Effect of Setting a Cost Profile ON
The syntax DIAGNOSTIC SET PROFILE profile_name ON FOR scope_level activates the specified cost profile at the indicated scope level as described in this table:
| Scope Level | How the Profile is Activated and Used |
|---|---|
|
applied to the current session and all new sessions started by the system or the specific parsing engine. An IFP-level scope applies only to the current parsing engine. |
| SESSION | used for subsequent requests in the session. |
| REQUEST | applied to all statements of a multistep request. The request DIAGNOSTIC SET PROFILE profile_name NOT ON FOR REQUEST by itself is effectively a no-op because the profile reverts to the SESSION scope level as soon as the diagnostic statement completes. |
For IFP (an IFP-level scope applies only to the current parsing engine) and SYSTEM level settings, propagation to session-local settings occurs only in the session that is handling the DIAGNOSTIC request. Other sessions continue to use their local SESSION-level or REQUEST-level profile values until they execute a DIAGNOSTIC SET PROFILE profile_name NOT ON FOR SESSION request, which forces the active IFP value to propagate to the SESSION and REQUEST levels.
DIAGNOSTIC SET PROFILE Precedence in Multistatement Requests
- Each DIAGNOSTIC SET PROFILE request is processed based on its placement relative to other DIAGNOSTIC SET PROFILE requests.
- All DIAGNOSTIC SET PROFILE requests are processed before requests of another type.
For example, in the following multistatement request, the three DIAGNOSTIC SET PROFILE statements are processed in the order they are specified relative to each other, and before the SELECT statement. As a result, the SELECT statement is optimized using profiles g3 and g2 activated at the SESSION level.
DIAGNOSTIC SET PROFILE g1 ON FOR SESSION ;SELECT emp_num FROM employee_phone WHERE employee_phone=5553229 ;DIAGNOSTIC SET PROFILE g2 ON FOR SESSION ;DIAGNOSTIC SET PROFILE g3 ON FOR REQUEST;