Query related to SQL Profiles in Oracle

Query related to SQL Profiles in Oracle

Find SQL profiles in database

SELECT NAME,type, SQL_TEXT, CATEGORY, STATUS FROM DBA_SQL_PROFILES;

How to check the Sql profile content

SELECT CREATED, PROFILE_NAME, SQL_TEXT, 
extractvalue(VALUE(hint), '.') AS hint
FROM DBMSHSXP_SQL_PROFILE_ATTR h, DBA_SQL_PROFILES p, TABLE(xmlsequence(extract(xmltype(h.comp_data), '/outline_data/hint'))) hint
WHERE p.name = h.profile_name;

select hint as outline_hints
from (select p.name, p.signature, p.category, row_number()
over (partition by sd.signature, sd.category order by sd.signature) row_num,
extractValue(value(t), '/hint') hint
from sqlobj$data sd, dba_sql_profiles p,
table(xmlsequence(extract(xmltype(sd.comp_data),
'/outline_data/hint'))) t
where sd.obj_type = 1
and p.signature = sd.signature
and p.category = sd.category
and p.name like ('&&profile_name'))
order by row_num;

Sql-id related to Oracle Sql profile

select distinct 
p.name sql_profile_name,
s.sql_id
from 
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile;

if in memory

select distinct(s.sql_id)
from dba_sql_profiles p, v$sql s
where p.name=s.sql_profile
;

Enable the SQL Profile in Oracle Database:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS', value=>'ENABLED');

Disable the SQL Profile in Oracle Database:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS', value=>'DISABLED');

Drop the SQL Profile if not needed:

Exec dbms_sqltune.drop_sql_profile(name=>'profile_name_value');

Sql profile for sql id in oracle

select s.name, s.type, s.SQL_TEXT, s.CATEGORY, s.STATUS
from dba_sql_profiles s,DBA_HIST_SQLSTAT d
where s.name=d.sql_profile
and d.sql_id='&sqlid';

If you like please follow and comment