When we implement the sql profile for the statement, we would also like to confirm if the sql has started using the sql profile or not. We can use the below query to find out if the sql is using the sql profile
How to find if the sql is using the sql profile
col sql_profile for a30 col sql_text for a60 wrap col child_number format 99999 heading CHILD col avg_etime format 9,999,999.99 col avg_lio format 999,999,99 col etime format 9,999,999.99 select sql_id, child_number, plan_hash_value plan_hash, sql_profile, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where sql_id like '&sql_id' order by 1, 2, 3 /
You may want to use the Gv$ view if you are working on an Oracle RAC Database
col sql_profile for a30 col sql_text for a60 wrap col child_number format 99999 heading CHILD col avg_etime format 9,999,999.99 col avg_lio format 999,999,99 col etime format 9,999,999.99 select inst_id, sql_id, child_number, plan_hash_value plan_hash, sql_profile, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from gv$sql s where sql_id like '&sql_id' order by 1, 2, 3 /
- The above queries will give results only if the sql_id is in the cursor cache. It will not provide any results if it is not present.
- In case the SQL_ID is not in the cursor cache, we can use the DBA_HIST_SQLSTAT view to find out the answer
Query to show the 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 the 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;
Find using DBMS PLAN
select * from table(dbms_xplan.display_cursor('&SQL_ID'));
This will display the SQL profile being used at the bottom.
It is important to know if the sql profile implemented is being used or not. I hope this article on how to find if the sql is using the sql profile helps you in the tuning effort.
Read more about the sql profile at the below links
How to move the sql profiles in Oracle
How to check sql profile in oracle
sql tuning advisor