When we implement the sql profile for the statement, we will 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 /
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
Leave a Reply