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
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
Brian,
I think that’s a good idea. Maybe do a select distinct to eliminate duplicates.
– Bobby
ahh, i missed the part in your statement about queries not being run very often and being dropped out of dba_hist_sqlstat.
Yes. It only gets sql_ids for queries that are in the AWR.
great, thanks
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!!!!
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
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!
It’s hard to give any useful advice based on what you have told me, but I have some general advice that has helped me and others.
If you have a complex query that is too slow why not break it up into a series of smaller queries and tune each one? Here are a couple of my posts about this idea:
https://www.bobbydurrettdba.com/2013/09/05/how-to-break-a-large-query-into-many-small-ones/
https://www.bobbydurrettdba.com/2012/08/14/breaking-up-query-to-force-join-order/
I often have developers come to me with huge queries that are slow. I have no idea how to speed them up as one big query. But, I can make them faster in an almost mechanical way by breaking them up into smaller pieces.
Thanks for your question and I hope this is helpful.
Bobby
Thanks for your quick reply. I really appreciate the same. Thanks for few suggestions. I will try and keep you posted in case if I make any progress.
Regards
Datta
Bobby, your posts are always nice.
Thanks!
Bobby
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
;
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
p.name=s.sql_profile(+)
this is safest method
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
correct query is:
p.name=s.sql_profile(+)
AWR history may not have this sql_id in its data!
Thanks for your suggestion!
Bobby
Thank you very mush Damirvadas and Bobby , it helped alot.
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.
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
Pingback: A Step-by-Step Guide for SQL Tuning for Beginners