Saturday, September 16, 2017

Test SQL Profile In Restricted Environment


The CATEGORY attribute determines which sessions can apply a profile. By default, all profiles are in the DEFAULT category, which means that all sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.

By altering the category of a SQL profile, you determine which sessions are affected by profile creation. For example, by setting the category to TEST, only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to TEST can use the profile. Other sessions do not have access to the SQL profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.

Perform below steps after accepting the profile and verify it's impact on system.

Assumption :
  • The name of the SQL profile is my_profile.
1. Use the ALTER_SQL_PROFILE procedure to set the attribute_name.


  VARIABLE pname my_profile
   BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE
     (
       name                =>  :pname, 
       attribute_name  =>   'CATEGORY', 
       value                =>   'TEST'     
     );
   END;
   /


2. Change the initialization parameter setting in the current database session.


  ALTER SESSION SET SQLTUNE_CATEGORY = 'TEST';


3. Test the profiled SQL statement and verify if you are getting expected benefits after accepting profile.

4. Use the ALTER_SQL_PROFILE procedure to set the attribute_name and make this profile accessible to all sessions.


  VARIABLE pname my_profile
   BEGIN
     DBMS_SQLTUNE.ALTER_SQL_PROFILE
     (
       name            =>  :pname,   
       attribute_name  =>  'CATEGORY',
       value           =>  'DEFAULT'  
     );
   END;
   /


No comments:

Post a Comment