When it comes to SQL Profiles are you afraid to deal with them? Do you secretly wish you never heard of them, and that you will never ever need to touch them?
Or, do you jump up with excitement when a performance problem needs to be addressed, and a SQL Profile is the solution?

If you think you belong to the first group of DBAs you are not alone.
Many DBAs have heard about SQL Profiles, but don’t know how to use them, or don’t exactly know how they work and what they are.
Many DBAs are confused between SQL Profiles and SQL Plan Baseline, and I don’t blame them…it is very easy to get mixed up.

Do you know why some DBAs don’t know much about SQL Profiles? I can think of at least 3 reasons:

  • SQL Profiles require Diagnostic and Tuning Pack license, and not all companies pay for licensing, thus DBAs cannot use it.
  • lack of experience. DBAs don’t use SQL Profiles on a daily/weekly/monthly basis
  • lack of training. Some DBAs are not even aware that SQL Profiles exists, and what they can do.

If you aren’t very familiar with SQL Profiles, then this post is for you!
If you need a refresher on SQL Profiles, then this post is for you!
If you are an expert on SQL Profiles, then you can share in the comments, some of your knowledge and experience with the rest of the readers!

1. What Are SQL Profiles?
2. What Do SQL Profiles Contain?
3. What Data Dictionary Views To Check?

1. What Are SQL Profiles?

SQL Profiles are database objects, stored in the data dictionary.
The purpose of the SQL Profile is to improve performance of the SQL statements. In order to fulfill its purpose, the SQL Profile is helping the optimizer find a good execution plan for a specific SQL statement.
The SQL Profile can influence the optimizer on choosing an execution plan, without adding hints to the query, without modifying the query.
This is a great deal, especially in cases where you cannot modify the application.

2. What Do SQL Profiles Contain?

Before I tell you what SQL Profiles contain, let me tell you what they don’t. They do not contain a specific execution plan.

We don’t know all the details of what they contain, but here are a few things they do:

  • information about the execution environment
  • object statistics
  • estimation corrections
  • cardinality information
  • set of hints with instructions to the optimizer

3. What Data Dictionary Views To Check?

In order to get more information about SQL Profiles, query DBA_SQL_PROFILES view. This view will not contain the “content” of the SQL Profile, or the hints passed to the optimizer.

That detail can be obtained from the following table, sqlobj$data, comp_data column.

If you are curious to see what hints are passed to the optimizer, here are some sample lines:

IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$07BDC5B4")
MERGE(@"SEL$4")
...

To better understand and remember SQL Profiles, and because I love Mind Maps, I am sharing with you a Mind Map on SQL Profiles!
Feel free to print it and use it!

SQL Profiles

Stay tuned for more SQL Profiles AWESOMENESS next week!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana


13 thoughts on “3 Basic Things DBAs Need To Know About SQL Profiles – Part 1

  1. Thanks a lot for your post. I have gone through almost all of your post that you have written in the past. in fact, I have gone ahead and subscribed to your posts.

    I use SQL Profile occasionally and I find them really useful. I make sure I educate my application guys about repercussions of it. I also use sqltrpt.sql for getting the recommendations. Would appreciate if you can include something about sqltrpt in your blog.

    Most of the customers that I support (~ 95%) do not have Diagnostic and Tuning pack license. So, I do not have the luxury of using this feature.

    1. Thank you Vijayendra for your feedback! Indeed it is a real challenge when customers don’t have the Diagnostic and Tuning pack license.
      We need to go back to SQL Outlines, those are still available, without the license.

  2. Hi Diana,

    Please share with us how change SQL profiles for a particular statements ? and how to know the best profile in case we have more than one.

    Regards,
    Mohammed

  3. Selecting from sqlobj$data to to obtain the details of a profile requires SYSDBA-rights.

    As normal user you can gather the same content via View DBMSHSXP_SQL_PROFILE_ATTR if you have the SELECT_CATALOG_ROLE.

Leave a Reply

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