Query to show sql_ids related to SQL Profiles

I have a number 0f SQL Profiles on a database I’m working on and I wanted to know the sql_id of the SQL statements that they relate to.  Here is what I came up with:

select distinct 
p.name sql_profile_name,
s.sql_id
from 
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile;

Here is the output on my system:

SQL_PROFILE_NAME               SQL_ID
------------------------------ -------------
coe_5up1944u1zb7r_1979920644   5up1944u1zb7r
coe_b9ad7h2rvtxwf_3569562598   b9ad7h2rvtxwf
coe_9f21udzrfcygh_2815756405   9f21udzrfcygh
coe_366brswtxxuct_10194574     366brswtxxuct
coe_2261y37rp45py_2815756405   2261y37rp45py

These are all profiles I created manually with the SQLT scripts so the profile name includes the sql_id but not all sql profiles are like this.  I have more entries in dba_sql_profiles than these five but these are the only rows that matched a row in dba_hist_sqlstat so I guess this won’t work for queries that are not used very often or are so fast that they don’t get pulled into the tables that are behind dba_hist_sqlstat.

– Bobby



About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

7 Responses to Query to show sql_ids related to SQL Profiles

  1. Brian Motzer says:

    Bobby, that was a question i had up until today also. One way i thought of was to run the following select. This should give you all the sql profiles and sql_ids associated with them from dba_hist_sqlstat.

    select sql_id,sql_profile from dba_hist_sqlstat where sql_profile is not null

  2. Brian Motzer says:

    ahh, i missed the part in your statement about queries not being run very often and being dropped out of dba_hist_sqlstat.

  3. oracleman consulting says:

    great, thanks

  4. Rupesh says:

    The is the best as dba_hist_sqlstat excludes those profiles that are not in AWR:
    select SQL_PROFILE,SQL_ID from v$sql where SQL_PROFILE is not null;
    When data available in present, why to go for history!!!!

    • Bobby says:

      I wanted to see the history but I think you make a good point. Maybe I should combine the query in this blog post with some sort of query against v$sql. Then I would get the history and any recent queries that might not be in the AWR. Maybe I could union the query above with the v$sql equivalent.

      Bobby

Leave a Reply