Manage SQL Profile in Oracle Database

Manage SQL Profile in Oracle Database

SQL Profile is used by Optimizer for use more efficient execution plan for the SQL Query to make in accepted State. It is found in DBA_SQL_PROFILES view. It used to choose better plan by optimizer if the SQL profile plan is having low cost then optimizer use it.

Check the SQL Profile in the Database.

COLUMN category FORMAT a10
COLUMN sql_text FORMAT a20
SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

Check the SQL Profile internal hint information

SELECT
a.name
,b.comp_data
FROM dba_sql_profiles a
,dbmshsxp_sql_profile_attr b
WHERE a.name = b.profile_name;

Create the SQL Profiles
Two ways:
1. Creating the SQL Profiles while running the manual SQL tuning task
For manually creating with SQL Tuning task follows the link:
SQL Tuning Advisory

2. Automatic SQL Tuning job runs on a daily basis (in Oracle Database 11g or higher).
Check SQL profiles that have automatically been created having the value “AUTO” in the TYPE column of the DBA_SQL_PROFILES views

--Check
select name, type, status, sql_text from dba_sql_profiles;

-- Enable
exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');

-- Disable
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');

Disable the SQL Profiles

BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SYS_SQLPROF_789tfag56hjli0004',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/

Drop the SQL Profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_9824ryfg6f7d78653');

Drop all profiles in a database, using PL/SQL block:

declare
cursor c1 is select name from dba_sql_profiles;
begin
for r1 in c1 loop
dbms_sqltune.drop_sql_profile(r1.name);
end loop;
end;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.