Oracle DBA

An SQL Profile in Oracle is a set of optimizer directives created to improve the execution plan for a specific SQL statement. It contains statistical information or corrections to cardinality and cost estimates, allowing the optimizer to generate a more efficient plan without changing the SQL code itself.

SQL Profiles are typically generated by the SQL Tuning Advisor and are stored in the DBA_SQL_PROFILES view. Unlike hints or stored outlines, SQL Profiles are dynamic and adaptive, providing flexibility as database statistics or data distribution changes. They are particularly useful for tuning complex queries where the default optimizer estimates may lead to suboptimal execution plans. SQL Profiles are not mandatory but act as recommendations that the optimizer considers during query execution.

Sample Queries

Query to show all SQL_IDs that have a history of running without and with a Profile:

select sql_id from DBA_HIST_SQLSTAT
where sql_profile is null and sql_id in 
(select sql_id from DBA_HIST_SQLSTAT where sql_profile is not null)
group by sql_id order by sql_id;

Query to show all SQL_IDs and snapshots that they ran without a profile:

select sql_id,snap_id from DBA_HIST_SQLSTAT
where sql_profile is null and sql_id in 
(select sql_id from DBA_HIST_SQLSTAT where sql_profile is not null)
order by sql_id,snap_id;

Query to show the Most recent SQL_IDs which ran without a profile:

select sql_id,snap_id from DBA_HIST_SQLSTAT
where sql_profile is null and sql_id in 
(select sql_id from DBA_HIST_SQLSTAT where sql_profile is not null)
order by snap_id desc;

Query to show most recent calls of a prompted SQL_ID with a profile:

select * from DBA_HIST_SQLSTAT where sql_id='&&sql_id_val' 
and sql_profile is not null order by snap_id desc;

Query to show Most recent calls of a prompted SQL_ID without a Profile:

select * from DBA_HIST_SQLSTAT where sql_id='&&sql_id_val' 
and sql_profile is null order by snap_id desc;