Building Blocks Of SQL Plan Management 12c

Ever found yourself in a situation when you had to pretend you understand and know everything that is going on in a conversation about some database feature? Maybe a SQL Plan Management conversation?

Or maybe, a DBA was trying to explain to you how he implemented a baseline, for a SQL Statement, to prevent a bad execution plan from running, and the only words that left your mouth were “Huh?”.

Maybe you do know what SQL Plan Management and SQL Plan Baselines are, and forgot some of the details, such as how does it work in 12c.

If this sounds familiar, then today’s post is for you.

You are here, reading about Baselines and Plan Management, eager to know more! Kudos for you!

There is tons of information written about SQL Plan Baselines and SQL Plan Management, and still there are tons of DBAs that are not familiar with them, and are afraid of using them.

Why?

Because the documentation is long, complicated, and requires extra research. Long blog posts, even with great content, that require the user to scroll and go to different pages will not capture the attention of the reader.

Let’s take a different spin on the SQL Plan Management! Today, I will lay the foundation, the key concepts. If you get this, then the rest is much easier!

Instead of starting at the top (SQL Plan Management), I am starting from the bottom, and building on each piece. I find it easier to understand it this way. By the end of the post you’ll really understand all the components and their role in the SQL Plan Management.

What Is An Execution Plan?
What Is SQL Plan History?
What Is SQL Plan Baseline?
What Is SQL Statement Log?
What Is SQL Management Base(SMB)?
What is SQL Plan Management(SPM)?

What Is An Execution Plan?

Did you ever had a discussion with a user (who is not a DBA) and had to explain why a report is running slower? Did you say: “…Well the report is slower because the Oracle optimizer has changed the execution plan.” ? The user was just starring at you, not understanding a word you just said.

If you would have to explain what an execution plan is, to someone that has no Oracle database knowledge, how would you do it?

Here is my version, feel free to use it:

The Execution Plan is series of steps or instructions performed by the database to get or retrieve the data for the report or the sql statement. There it is, in plain English.

Each step within the execution plan retrieves data from the database, or prepares the data for the next step.
The execution plan includes an access path (how it accesses the table – think: full table scan, index scan) and it also includes the join order with a join method (think: nested loops, hash joins)

An execution plan has a Plan Hash Value associated, in order to identify it. The Plan Hash Value, many times referred to as PHV, is a number.

What Is SQL Plan History?

The SQL Plan History is a collection of all the generated execution plans for a specific SQL statement, if the statement has been executed at least 2 times (it has to be a repeatable statement).

By default, SQL Plan History is not created for SQL Statements. You need to enable it by setting the following parameter to TRUE:

optimizer_capture_sql_plan_baselines = TRUE

Once that parameter is set to true, the database will automatically create a plan history for each repeatable SQL statement.

The first execution plan that is generated for a SQL statement, becomes part of the plan history, and it is labeled as ACCEPTED, when Plan History is enabled. A plan is accepted when the database verifies that the plan will not lead to performance regression, compared to other plans from history.
The very first plan is accepted, as there is nothing to compare it to.

The state of plans in the plan history can be ACCEPTED or NON ACCEPTED.
A NON-ACCEPTED plan can become ACCEPTED, after it is verified that it will not cause performance regression (doesn’t perform worse than the accepted plans).

What Is SQL Plan Baseline?

The SQL Plan Baseline is a set of all the ACCEPTED plans from the Plan History for a specific SQL Statement.
Just like the Plan History, SQL Plan Baseline is not created by default. It is enabled by setting the following parameter to TRUE

optimizer_capture_sql_plan_baselines = TRUE.

The very first execution plan that is generated for a repeatable SQL statement becomes part of the SQL Plan Baseline (and of course the Plan History).

What Is SQL Statement Log?

SQL Statement Log contains the signature of the SQL statements the optimizer evaluated. The signature is created when the statement is parsed, and is represented as a number.
The data for the SQL Statement Log is stored in SQLLOG$. This table is populated only when the SQL Plan History and capture is enabled.

What Is SQL Management Base (SMB)?

The SQL Management Base is a logical repository, stored in the data dictionary, in the SYSAUX tablespace. There are four components
that make up the SQL Management Base:

• SQL Management Log
• SQL Plan History
• SQL Profiles
• SQL Patches

The last two components will be discussed in a different blog post.

All these components take up space in the SYSAUX tablespace. Thus you can configure how much space (in percentage) SMB can use in SYSAUX, and how many weeks can SMB retain the execution plans.
To check for the current configuration, query DBA_SQL_MANAGEMENT_CONFIG view.

What is SQL Plan Management (SPM)?

We are getting towards the end of this post, and arriving to the last piece, which puts together all the pieces from above.
SQL Plan Management, SPM for short, was introduced in 11g, and the purpose of it was to preserve the performance of SQL statements during major system changes, such as database upgrades, optimizer parameter changes, system settings changes,changes in schema definitions and others.

The SPM has three main components:
Plan Capture ( SQL Plan Baselines)
Plan Selection (how the plans are selected)
Plan Evolution (how plans are verified and evolved from NON-ACCEPTED to ACCEPTED plans)

The goal of the SPM is to avoid plan regressions.

Most likely you have tons of questions, on how all these pieces work. Come back next week for some more good stuff!

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