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 have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.

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