3 Basic Things DBAs Need To Know About SQL Profiles – Part 1

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!

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