how to check sql profile in oracle

What is Sql profile in Oracle

• A SQL profile is a set of auxiliary information specific to a SQL statement. The Oracle optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.
• During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the OPTIMIZER_MODE initialization parameter setting from ALL_ROWS to FIRST_ROWS for that SQL statement.
• A SQL Profile, once accepted, is stored persistently in the data dictionary. The SQL Profile is specific to a particular query.
• If SQL profile is accepted , the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.

how to check sql profile in oracle

We can check sql profile in oracle using the view DBA_SQL_PROFILES. It stores all the profiles in the Oracle database. You can use the below statement to find out all the sql profiles in the database

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

How to drop Sql Profile in Oracle

you can drop the sql profile using the dbms_sqltune package

BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (‘my_sql_profile’);
END;
/

How to disable the sql Profile in Oracle

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

How to check the Sql profile content

We can use the below query

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;

or below query can also be used

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;

How to show Sql-id related to Oracle Sql profile

We can use the below query for this

select distinct(s.sql_id)
from dba_sql_profiles p,DBA_HIST_SQLSTAT s
where p.name=s.sql_profile

or if the sql is in memory

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

how to check 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 the sql id is in memory

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

Hope you like information on Oracle sql profile and how to check sql profile in oracle

Related Articles
sql tuning : Here are 12 Tips for Oracle SQL Query Writing and performance Tuning, including Avoid poor coding practice, avoiding disabling the index usage
Oracle index clustering factor: Oracle index clustering factor represents the degree to which data is randomly distributed in a table as compared to the indexed column
sql tuning advisor : How to run sql tuning advisor for sql_id in the Cursor cache, how is the sql tuning task created and executed to get the recommendation