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

4/27/21

Current version profiles.sql

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.

22 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

  5. DATTA KUMAR K SURYANARAYANA RAO says:

    Hi

    There is a view which had lots of UNION ALL, sort merge and what not? When the user runs the report it’s taking long time. Instead I thought of creating a MV and it took almost 14 hours to get created and now I have it again, the refresh is taking a toll. Do you have any suggestion for me. Thanks in advance!

  6. Shashi Kaushik says:

    Bobby, your posts are always nice.

  7. Sunil says:

    I use below SQL for the same :

    select distinct(s.sql_id)
    from dba_sql_profiles p,DBA_HIST_SQLSTAT s
    where p.name=s.sql_profile
    union
    select distinct(s.sql_id)
    from dba_sql_profiles p,v$sql s
    where p.name=s.sql_profile
    ;

    • Bobby says:

      Thank you for sharing your query. It looks good. I tend to operate only on the SQL that is already in the AWR so I use the DBA_HIST views. I probably need to look at ones that are in v$sql also as your query does.

      Bobby

    • damirvadas says:

      p.name=s.sql_profile(+)
      this is safest method

      • Bobby says:

        In my case I only wanted to see the SQL statements that had SQL Profiles. With an outer join I think you would see all the SQL statements which could be a long list. If that is what you want that is great.

        Thanks for your comments.

        Bobby

  8. damirvadas says:

    correct query is:
    p.name=s.sql_profile(+)
    AWR history may not have this sql_id in its data!

  9. Vishal Sood says:

    How about the below SQL

    select sql_id,SQL_PROFILE,SQL_PLAN_BASELINE,sum(EXECUTIONS) from v$sql where SQL_PROFILE ‘ ‘ or SQL_PLAN_BASELINE ‘ ‘ group by sql_id,SQL_PROFILE,SQL_PLAN_BASELINE

    Because when SQL Profile or base line plan is fixed at a SQL , so that SQL expected not to run run long, and wont be there at top at AWR.

    • Bobby says:

      Thanks for your comment. You have a good point about the SQL Profile making the SQL so efficient that it no longer shows up in the AWR.

      Bobby

  10. Pingback: A Step-by-Step Guide for SQL Tuning for Beginners

Leave a Reply to Brian Motzer Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.