January, 2020 - alfredokriegdba.com

Do You Trust SQL Tuning Advisor?

Today’s post is about SQL tuning. During my more than 14 years of experience as a Database Administrator I haven’t found a better tool than Enterprise Manager (EM) to monitor Oracle’s DB performance. The “Top Activity” graph has been a constant during all these years.

Note: The EM’s Top Activity page has been deprecated in EM 13.3 PG.

click here

So let’s start from the beginning. If I receive a call or ticket stating that the DB or a process in the DB is slow, the first thing I do is to jump to the “ASH Analytics” page in EM. This page is part of the “Diagnostics” DB pack.

Here I can spot right away SQL_ID 6kd5jj7kr8swv being the top CPU consumer followed by other 3 SQL_ID’s. I’m going to create a SQL Tuning Set (STS) containing all these SQL’s.

From the STS dashboard I’m going to to click on “Create” to launch the wizard.

Step 1 requires a name and owner for the STS.

Note: You can create an empty STS and populate it later.

The next step is to load the STS with SQL’s. I’m going to choose “Load SQL one time only” and select from “Cursor Cache“. This because I’m sure they are loaded in memory. Another possible option is to load them from the AWR repository.

Next is to filter the SQL’s we want top load to our STS. I’m using the SQL ID row along with the IN operator to achieve this.

The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.

And finally the review page. If everything looks right, then click on “Submit“.

Once the STS is created you will be able to see those 4 SQL’s loaded into it. I’m going now to schedule a SQL Tuning Advisor (STA) task to find out if these SQL’s can be tuned for better performance. STA is part of the “Tuning” DB pack.

Let’s review the results of the STA task we just created couple of minutes back. STA examined 4 distinct SQL’s. It only found recommendations for 3 of them with an overall potential benefit of 55 seconds in DB time.

Let’s click on “Show all results” button to see detailed information about the recommendations. The table below shows all 4 SQL’s along with their recommendations. We can spot right away the benefits of tuning SQL ID 6kd5jj7kr8swv. A SQL Profile could be created with a benefit of 57% or an Index with a benefit of 72%.

What do you do here? Do you hit “Implement All SQL Profiles” and cross your fingers hoping the new execution plan are going to be faster?

You don’t know how many times I saw sub-optimal plans generated by SQL Profiles.

That’s actually me fingers crossed hoping everything works!!!

There’s actually a better way to handle this. Real Application Testing (RAT) DB pack has a tool named SQL Performance Analyzer (SPA). This tool allows you to play the SQL before and after the changes (SQL Profile) and gives you a detailed report with the results.

In order to do that you’ll have to click on the “Validate All Profiles with SPA” button.

What is going to happen is that SPA will gather data before and after implementing the SQL Profiles and will produce a report.

Note: SPA will actually implement the SQL Profiles. Keep this in mind in a running Production system. RAT has also the capabilities to replay this workload in a Non-Production system. You can implement the SQL Profiles there without harming the Production performance.

Ooops! The SQL Profiles actually have a regression impact of -824%. Look at the Net Impact for the 1st SQL. It has a -830% impact, this means that the SQL Profile produced a sub-optimal plan. After this I will go and disable the recently created SQL Profiles before they cause a bigger impact.

My next step will be to propose the Index creation. This may have different implications depending on the application you are running, the business needs and operating procedures. I decided to implement the index to measure the performance benefit.

Here you can see how the performance was before the SQL Profile (maroon), with the SQL Profile implemented (turquoise) and with the index created (pink).

In summary, SPA is a great tool that helps you validate that the changes made to the DB system are working as expected. SPA not only validates SQL Profiles, it also validates statistics, DB parameter changes, DB upgrades and also you can verify the performance of your DB system with an Exadata storage server simulation.

Thanks,
Alfredo

How’s Your Data Center Configuration?

Oracle Enterprise Manager 13c (EM) Lifecycle Management pack offers a set of tools to help you manage all your data canter targets from a single pane of glass. All these tools are listed on my previous post.

Click here

One of this tools is Configuration and Drift Management. What is it and how it works? Well, pretty simple. EM 13c collects configuration data from all the configured managed targets and stores it in the Management Repository database. You can use this configuration data to track, search and compare configuration changes for below targets:

Hosts
Database
Middleware
Elastic Cloud Infrastructure
VM Server Pool
Client
Non-Oracle Systems

The Configuration tool is located under Enterprise -> Configuration. Let’s take a look at the Search section.

This will take you to the Configuration Search Library. Take a look at the pre-defined searches available for all the target types already mentioned. You can run these searches against your managed targets, but also you can create new custom searches based on your requirements.

Let’s select the first Configuration search from the table and click Run.

The Initialization Parameter Settings search will show all the results of Configuration data collected for all the targets with target type as “Database Instance”.

You can export or print this search results. One important feature to mention is that you can customize this search on the fly. Look at the upper right corner for a button called “Search Using SQL”. This basically allows you to use SQL to customize this search. Let’s click on it.

In the Query section you can customize your search as needed. In this example, I’m removing the “emrepo” target from the search. You can save this customized search if needed.

Another way to access Configuration data is to go to the target’s home page and navigate to Configuration -> Latest. This provides you with just this target’s Configuration data and not for all the managed targets.

This page shows all the Configuration data collected for the target based on the pre-defined search. Any changes made to the search will have an impact on these results.

Configuration Properties tab shows all the configuration items along with the values at the time they were collected. See “Last collected at” for more information. You can navigate to “Immediate Relationship” tab to have insights about the relationship of this target with other managed targets. In this example you can see the DB system, listeners, agent and host related to this target.

The rest of the tabs provide information about membership and usage of the target. Now let’s take a look at the “Actions” button. You can save, export, see history and compare this data against another target of the same type.

Let’s click on “Compare” to make a one-time comparison.

I’m going to select another example database from the list. Keep in mind that this database has DataGuard configured, so I’m expecting to see a lot of differences as result.

Here you go! 89 differences between my 2 example databases. Again, some of the parameters are expected to differ but what about:

the interconnect parameter for instances running on the same host?
SGA and PGA size parameters for instances of the same cluster database?
the compatible parameter for instances of the same cluster database?

This is just an small example on how this tool works and how powerful it can be in order to drive consistency in the configuration of all your managed targets.

But wait a minute… Can you automate these comparisons and send me a report? The answer is yes, that will be part of my next post.

Stay tuned!
Alfredo

Lifecycle Management Automation Series

Today’s infrastructure management is getting complex. We now have hybrid datacenters, multi-cloud solutions, virtual machines and so on. With all this complexity is difficult to manage configuration consistency, changes and to keep up with security vulnerabilities and patches.

Enterprises realize that is not possible to engage more human resources to overcome these issues that easy. Here is where Oracle Enterprise Manager (EM) can help to meet all these challenges by automating discovery of targets, provisioning, patching, cloning and upgrading databases. But more important, doing all these in a consistent manner and following compliance rules.

Lifecycle Management Pack in EM 13c contains a set of tools that provide:

Automated Discovery of hosts and targets
Provisioning DBs, Oracle GI, Linux VMs and Fusion Middleware applications
Patching of DB targets through the patching workflow and patch plans

Click here

Out-of-place patching using fleet maintenance
Full multitenant management support allows you to create, clone, plug/unplug and delete PDBs
Change management which captures and compares object definitions, parameters and data
Configuration management searches and compares configuration data across the enterprise

Click here

Target inventory tracker
Compliance management evaluates targets and systems as they relate to your business best practices for configuration and security
Enterprise data governance identifies DB’s that potentially contain sensitive data
Change activity planner enables you to to plan, manage and monitor operations in your datacenter

This EM pack is under an additional license covered under the Lifecycle Management pack. I’ll be covering all these tools one by one in future posts.

Additional information can be found in the Oracle EM Lifecycle Management administration guide:

click here

Thanks,
Alfredo