Manage SQL Profiles

Managing SQL Profiles

Purpose of Profiles

SQL Profiles help the query optimizer find an efficient execution plan for a given SQL statement. The SQL Profile is applied to a specific SQL statement, without any modifications to the SQL statement. This is especially helpful when dealing with sql statements from third party software which cannot be modified.

Profiles provide hints to the cost based optimizer, which provide adjustments for cardinality estimates, object statistics and initialization parameters allowing the optimizer to find an efficient execution plan. The hints can be used to adjust the number of rows returned by a query on a table or index, adjust the number of rows returned from a join, gather missing or stale object statistics for a table or table column or index, alter the executed sql statement, and create new indexes.

Profiles can also disable sql statement hints, change optimizer mode and set optimizer_features_enable.

SQL Profiles have the ability to match multiple statements that vary only by a literal, through the use of the FORCE_MATCH attribute in the IMPORT_SQL_PROFILE procedure.

When are profiles created:

Profiles are created by the SQL Tuning Advisor when it is run either automatically, or as part of a maintenance Window, or manually using the dbms_sqltune package.

SQL Tuning Advisor runs the optimizer in tuning mode (Automatic Tuning Optimizer) which does additional analysis to check if an execution plan can be improved. The Optimizer employs various techniques to verify that its estimates are correct.

After its estimates have been verified the optimizer generates a recommendation to the user to accept the profile. If the user accepts the profile the generated hints will be stored in the Data Dictionary. Whenever the statement is called, the Profile's hints will be used to generate a more efficient execution plan.

Assign Privileges

a. Access SQL Tuning Advisor API - grant advisor to scott;

b. Administer sql management - grant ADMINISTER SQL MANAGEMENT OBJECT to scott;

1.) Check the settings of Automatic SQL Tuning Advisor

COL PARAMETER_NAME FORMAT a25

COL VALUE FORMAT a10

SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE", DESCRIPTION

FROM DBA_ADVISOR_PARAMETERS

WHERE ( (TASK_NAME = 'SYS_AUTO_SQL_TUNING_TASK') AND

( (PARAMETER_NAME LIKE '%PROFILE%') OR

(PARAMETER_NAME = 'LOCAL_TIME_LIMIT') OR

(PARAMETER_NAME = 'EXECUTION_DAYS_TO_EXPIRE') ));

Example output:

PARAMETER_NAME VALUE DESCRIPTION

------------------------- ---------- ------------------------------

LOCAL_TIME_LIMIT 1200 Time limit per statement in a

SQL Tuning Set

ACCEPT_SQL_PROFILES FALSE TRUE if SQL Profiles should be

created by the task, FALSE

otherwise

MAX_SQL_PROFILES_PER_EXEC 20 Maximum number of SQL Profiles

that can be created by the

system in one run

MAX_AUTO_SQL_PROFILES 10000 Maximum number of system SQL

profiles that are allowable at

any one time

EXECUTION_DAYS_TO_EXPIRE 30 Specifies the expiration time

in days for individual

executions of the current task

2.) Setting parameter for Automatic SQL Tuning Advisor

When the Automatic SQL Tuning task (SYS_AUTO_SQL_TUNING_TASK) is configured to implement SQL profiles automatically, Advisor behavior depends on the setting of the ACCEPT_SQL_PROFILE tuning task parameter.

- If set to true, then the advisor automatically implements SQL profiles.

- If set to false, then activation by user is required

- If set to AUTO (default), then the setting is true when at least one SQL statement exists with a SQL profile, and false when this condition is not satisfied.

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',

'ACCEPT_SQL_PROFILES', 'true');

— Implementing other parameters:

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',

'LOCAL_TIME_LIMIT', 1200);

DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER('SYS_AUTO_SQL_TUNING_TASK',

'MAX_SQL_PROFILES_PER_EXEC', 50);

Note: SQL Profiles should not be set for automatic implementation in production. The SQL Profile should be exported from production and tested on a test database.

3.) Create tuning task

a) Using text of a single statement

VARIABLE tuning_task VARCHAR2(64);

EXEC :tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( -

sql_text => 'select quantity_sold from sales s, times t where s.time_id = t.time_id and s.time_id = TO_DATE(''24-NOV-15'')');

b) Using SQL_ID

i.) (for present user session)

VARIABLE tuning_task VARCHAR2(64);

DECLARE

l_sql_id v$session.prev_sql_id%TYPE;

BEGIN

SELECT prev_sql_id INTO l_sql_id

FROM v$session

WHERE audsid = sys_context('userenv','sessionid');

:tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);

dbms_sqltune.execute_tuning_task(:tuning_task);

END;

ii.) (for another user session)

VARIABLE tuning_task VARCHAR2(64);

select PARSING_SCHEMA_NAME, sql_id, sql_text, to_date(LAST_ACTIVE_TIME.'DD-MM-YY,HH:MI')

from v$sql

where parsing_schema_name = 'DEVELOPER1';

:tuning_task := dbms_sqltune.create_tuning_task(sql_id => 'a sql_id from v$sql',task_name =>’atask_name’ );

dbms_sqltune.execute_tuning_task(:tuning_task);

4.) Report Tuning Task

-- display report for a single statement

SELECT dbms_sqltune.report_tuning_task(:tuning_task,’TEXT’,’ALL’) from dual;

— report for Automatic SQL Tuning Task showing all SQL statements analyzed

VARIABLE lastnight_rept CLOB;

BEGIN

:lastnight_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK (

begin_exec => NULL

, end_exec => NULL

, type => 'TEXT'

, level => 'TYPICAL'

, section => 'ALL'

, object_id => NULL

, result_limit => NULL

);

END;

PRINT :lastnight_rept

5.) Drop Tuning Task

dbms_sqltune.drop_tuning_task(:tuning_task);

6.) Accepting SQL Profiles

VARIABLE tuning_task VARCHAR2(30)

BEGIN

dbms_sqltune.accept_sql_profile(

task_name => :tuning_task,

task_owner => user,

name => 'optimizer_estimate',

category => 'TEST',

force_match => TRUE,

replace => TRUE

);

END;

7.) View information on the accepted sql profile

SELECT category, sql_text, force_matching

FROM dba_sql_profiles

WHERE name = 'optimizer_estimate';

8.) Altering SQL Profiles

-- change status ( enabled/disabled) of sql profile

BEGIN

dbms_sqltune.alter_sql_profile(

name => 'optimizer_estimate',

attribute_name => 'status',

value => 'disabled'

);

END;

9.) Dropping SQL Profiles

-- include ignore (TRUE/FALSE) to raise an error if profile does not exist(default is FALSE)

BEGIN

dbms_sqltune.drop_sql_profile(name =>'optimizer_estimate'

ignore => TRUE);

END;

10.) Activating SQL Profiles

Automatic activation of SQL profiles is controlled by the SQLTUNE_CATEGORY initialization parameter at both the session and system level. Its default value is DEFAULT.

This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.This value is the same as DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(category => ‘DEFAULT’).

By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to TEST, only those user sessions where the SQLTUNE_CATEGORY initialization parameter is set to TEST can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile.

-- session level. A session can only activate a single category

ALTER SESSION SET sqltune_category = test;

-- system level

ALTER SYSTEM SET sqltune_category = PROD;

11.) Altering a SQL Profile

You can alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

BEGIN

DBMS_SQLTUNE.ALTER_SQL_PROFILE(

name => 'my_sql_profile',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

12.) Viewing SQL Profile hints

SELECT extractValue(value(h),'.') as HINTS

FROM DBMSHSXP_SQL_PROFILE_ATTR sa,

table(xmlsequence(extract(xmltype(sa.comp_data), '/outline_data/hint'))) h

WHERE PROFILE_NAME='optimizer_estimate';

-- second statement to extract hints

SELECT extractValue(value(h),'.') AS hint

FROM sys.sqlobj$data od, sys.sqlobj$ so,

table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h

WHERE so.name = 'optimizer_estimate'

AND so.signature = od.signature

AND so.category = od.category

AND so.obj_type = od.obj_type

AND so.plan_id = od.plan_id;

13.) Data dictionary Views for SQL Profiles

dba_sql_profiles

cdb_sql_profiles

dba_advisor_tasks,

dba_advisor_executions,

dba_advisor_findings,

dba_advisor_recommendations

dba_advisor_rationale

14.) Moving SQL Profiles between databases

A. Create staging table

BEGIN

dbms_sqltune.create_stgtab_sqlprof(

table_name => 'MYSTGTAB',

schema_name => user,

tablespace_name => 'USERS'

);

END;

B. Copy SQL Profile to the staging table

BEGIN

dbms_sqltune.pack_stgtab_sqlprof(

profile_name => 'my_sql_profile',

profile_category => 'PROD',

staging_table_name => 'MYSTGTAB',

staging_schema_owner => user

);

END;

C. Make changes to the SQL Profile (ie. name, category)

BEGIN

dbms_sqltune.remap_stgtab_sqlprof(

old_profile_name => 'my_sql_profile',

new_profile_name => 'my_new_sql_profile',

new_profile_category => 'TEST',

staging_table_name => 'MYSTGTAB',

staging_schema_owner => user

);

END;

Transfer table to another database such as a Test database using expdp/impdp

D. Copy SQL Profile from staging table to Test database data dictionary

BEGIN

dbms_sqltune.unpack_stgtab_sqlprof(

profile_name =>'my_sql_profile',

profile_category => 'TEST',

replace => TRUE,

staging_table_name => 'MYSTGTAB',

staging_schema_owner => user

);

END;