A quick and easy way to know if a SQL Profile is being used is through the following query.
SELECT SQL_ID, SQL_PROFILE, SQL_TEXT FROM V$SQL WHERE SQL_PROFILE IS NOT NULL;
This query will give us all the statements that are in the library cache and are currently using a SQL Profile in its execution plan.
Remember that a problem with the SQL profileS is that they can stop used by the optimizer without notice.
HTH – Antonio NAVARRO