Transcript

Introduction

Stabilizing Oracle 12c Performance with Baselines Part 1

>> John:  Today I want to talk about instability in Oracle databases with regard to SQL performance. All DBAs know that Oracle databases can have dreadful problems with stability ever since release 7 introduced the cost-based optimizer. A query runs well today and tomorrow it’s a disaster. Why does this happen? Because of dynamic parsing.

 

Dynamic parsing will produce perhaps a different execution plan from one day to another. You could try to stabilize the situation with hints but not seriously without using a huge amount of work at the time and potentially all performance with [00:48 inaudible] later. 

 

The end result is the managing change in Oracle environment can be a terrifying process. You’ll never know what’s going to happen. Even something as basic as gathering statistics which should do nothing but good can sometimes cause problems to degrade accordingly. I’ve seen sites where the DBAs refuse to gather statistics simply because they didn’t dare take the risk of performance dropping.   

 

[pause]

 

The problems of instability should be history for all new upgrades to 12c. SQL baselining is actually introduced in 11 but it’s greatly enhanced in 12. It can give you a guarantee that performance will never degrade. 

 

Now what happens to the databases? Changes with statistics, parameters, patches – not a problem – even platform migrations. We can guarantee the performance caused by execution plans will never degrade.

 

However, performance isn’t necessarily frozen. It takes a while to get better. All changes in execution plans are held back until tested. Then changes that are proven to be good at the method but changes for the worse remain blocked. The bad news is that you need Enterprise Edition licenses for this. We can do something similar for you in Standard Edition but that is a bit more work. But the good news is that this is completely automatic.

 

[pause]

 

What I love about baselines is that it is a declarative technique. Just let Oracle get on with it. All the DBA needs to is set two parameters and then make sure that the auto-task system is enabled.

 

Copyright SkillBuilders.com 2017

×
Transcript

Baseline Demonstration

>> John:  So, let’s do it. First, set two parameters. This parameter will allow the capture_sql_plan_baselines. This parameter will bring the mechanism into use. 

 

Now we need to ensure the auto task system is running, the query against DBA auto task clients, and there it is. The SQL tuning advisor will run by default every night during the maintenance response. 

 

[pause]

 

So I’m going to enable the auto trace facility so we can get some idea what will the execution plans are. And on the simple query, 

 

select * from emp natural join dept where job=’PRESIDENT’ 

 

And note the way it was run, full scale of emp where we apply the filter job=’PRESIDENT’ and there you see a baseline is being created that will be used for the statement for more. 

 

If I try to tune the statements by making a simple change, here I’m creating an index. I’m trying to tune the thing, make the statement run a bit better by indexing the job column. What happens when my users reruns the statement? 

 

[pause]

 

Absolutely no change by still using a full scan of emp. The index is being ignored. Why? Because we’re using the baseline. We’ve stabilized the system. 

 

Today happens to be Thursday. Overnight, the job shall run which should test any new plans that were developed. So let us simulate the move to Thursday night by telling the scheduler to open the Thursday night maintenance window, the Thursday window. How we opened the window – now this precise moment, the auto tasks will be running including the task which should identify and change execution plans and test it. Were they are actually okay? And now we can advance from Thursday night to Friday morning by closing the Thursday night maintenance window. 

 

[pause]

 

And now that this is Friday morning, what happens when my end users run the code? 

 

select * from emp natural join dept where job=’PRESIDENT’ 

 

Hey, we are now using the index and a new baseline is being created. So we were using this baseline. We’ve now tested a new plan, found the plan was good, and created another baseline. 

 

There you have it, a totally self-managing mechanism. All changes are held back until they’re tested overnight. And if the change is proven to be good, it’s implemented. Any changes that will degrade the performance are blocked. 

 

[pause]

 

This should be a powerful driver for 12c upgrade and indeed the reason for investing on the tuning pack. 

 

What if you can’t do that? In 11g, the mechanism is there but it is largely manual. However, we can automate the process for you. It just requires a bit more work. If you do not have Enterprise Edition, we can configure similar functionality for you but again it requires more work. The beauty of the baseline mechanism is that it’s fully automatic and if you have the correct environment, you should certainly consider using it.

 

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×