Blog Feed

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.


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.

ASH Analytics page

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.

SQL Tuning Sets Menu

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

SQL tuning Sets Dashboard

Step 1 requires a name and owner for the STS.


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


Create STS – Options

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.

Load SQL’s

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.

Filter SQL’s

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

Schedule STS Creation

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

STS Creating Review

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.

Review SQL Tuning Sets

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.

SQL Tuning Advisor Results

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%.

SQL Tuning Advisor Results Detail

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.

Validate SQL Profiles with SPA

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.


SPA Report

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.


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.

Configuration Menu

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.

Configuration Search Library

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

Run Configuration Search

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

Initialization Parameter Settings Search

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.

Configuration Search Using SQL

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.

Configuration Data for testdb Target

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.

Latest Configuration

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.

Target Relationships

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.

Latest Configuration Actions

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

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.

Comparison Results

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

  • 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

  • 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:


Thanks,
Alfredo

Oracle DB Patching with EM 13.3

I got several interesting questions last week during my presentation about Oracle Enterprise Manager (EM) 13.3 and Exadata at the East Coast Oracle Conference 2019.

One of them was related on how to patch a DB home using EM 13.3 and I’ll try to show it on this post.

First, keep in mind that in order for EM to download patches it needs to be able to connect to the My Oracle Support (MOS) site using valid credentials. There’s an offline patching option well, I won’t cover that setup on this post though.

Once EM is able to communicate with the MOS site, it will automatically (EM job) download the latest patch recommendation for the managed targets. Let’s navigate to Enterprise -> Provisioning and Patching -> Patching and Updates.

The Patches and Updates home page is divided in 4 sections.

  • A) Quick Links will open a new page linked to MOS site
  • B) Patch Recommendation for the managed targets in EM
  • C) Patch Search allows you to search for specific patches
  • D) Plans created are shown in this section

Let’s take a look at the patch recommendations. They can be viewed by classification or target type.

Click on the bar for Database Instance patches to go to the Patch Details page. As shown below the Details page provides an easy to read table with all the recommendations for that target type.

Select the patch from the table and automatically the patch details is populated below. In this example we are looking at the latest OJVM PSU for the 12.1.0.2 version.

At the top of the table you have several options:

  • Create a patching plan
  • Add the patch to an existing patching plan
  • Print the patching information
  • Download the patch (to you computer or to the EM Software Library)

I’m going to download this patch to the EM’s Software Library to apply it later.

Next, we need to create a Patch Plan. This plan will contain all the selected patches and will be analyzed against the Oracle Homes in order to find conflicts or potential issues.

Once the Patch Plan is created we can then click on it to navigate to the Patch Wizard. The first page page of the Wizard is the Patch Plan Information page, it allows us to see and amend the Patch Plan name and to grant access to other users and/or roles.

The Patches page provides information about the patches currently added to the Plan. You can add and/or remove patches here.

The Deployment Options page let’s you choose if you want to perform an “out of place” (recommended) or an “in place” patching. There are several caveats here:

  • You have to provide the new Oracle Home (OH) name if “out of place” patching is selected
  • You need to have enough free space for the new OH and for the staging directory
  • You need to provide both the normal and the privileged credentials for the host

The next step is the Patch Plan Validation. Basically EM will validate all the patches against the Oracle Home.

Analysis in Progress

If the validation is successful, you can proceed to deploy the patches to the Oracle Home.

Ready for Deployment

Finally, you have to select if you want to deploy the patch immediately or at a different schedule.

Deploy Confirmation

Wait for the procedure to apply the patches. Once the procedure finishes you will see the success page along with a link to view the details of the deployment.

Deployment Successful

Note that after the patches are successful deployed, the Plan will be locked. Also, you have the option of creating a template of the Plan so you can deploy it to different targets later.

Important note: This procedure is available for 12.1.0.x and prior versions. If you want to patch 12.2, 18c and 19c versions then you must use Fleet Maintenance feature.

Patching the Oracle Database 12.2 using Enterprise Manager Requires the EM DB Plugin 13.2.2 and Fleet Maintenance (Doc ID 2237521.1)

Thanks,
Alfredo

OEM 13c Database Plugin 13.3.2 Caveat

I have done some testing on the OEM 13c DB Plugin 13.3.2 (PG) for the last several months. I highlighted some of the changes in this new version in a previous post:


During the last Dallas Oracle User Group (DOUG) conference I got couple of questions though. I will try to answer them as best as possible.

  • OEM 13c Top Activity page has been deprecated

I don’t know the reason why, but is a fact. Oracle is moving towards getting rid of the Top Activity page and replacing this with ASH analytics. As I keep mentioning… we are so used to Top Activity page and this will be a shocking factor for users.

We know that Internet browsers such Chrome, IE or Firefox will no longer support Adobe Flash starting next year. Oracle is now using JET instead of Flash. But the Top Activity page was solely using Flash.

There’s a trick though. Top Activity link will show at the end of the Performance Home page. So you can still access it (while your browser permits it).

  • ASH Analytics can’t be used for our monitoring screens

This was a really good question. There are many companies that have big screens in their command centers to monitor mission critical databases. The Top Activity page is always shown in those screens and is really handful by having a quick glance on the DB’s performance.

Top Activity page will refresh every 15 seconds but the highlighted time frame (5 minutes) always moves to the very end. This means that we only monitor the last 5 minutes every single time. We notices a different behavior with ASH analytics though.

When ASH analytics refreshes, the highlighted time frame won’t move to the very end. This means that we are only monitoring the same 5 minutes (static). See the picture below taken from my lab running 13.2 version:

ASH Analytics EM 13.2 version

I confirmed this behavior in 12c, 13.1, 13.2 and 13.3 versions of the DB Plugin. But there are good news. This didn’t happen in the latest 13.3 PG version using JET. The highlighted time frame always moves to the very end thus showing information of the last 5 minutes every time.

This is another reason to upgrade to the latest version of EM.

  • You can only use DB 19c as a repository if you are using the latest DB plugin

Yes. OEM 13c PG was released with full 19c support. So, the best thing to do is to upgrade to 13c PG version if you want to monitor or use 19c as your DB repository.

Hope this helps to address your questions.

Thanks,
Alfredo

The Strange Case Of A Missing Agent Gold Image

If you are running Oracle Enterprise Manager (OEM) 13c, you should (must) be using Agent Gold Images (GI). They provide an easy way to manage the lifecycle of you OEM agents from a fancy dashboard.

The problem came while removing a Gold Image Versions in OEM 13.3PG. Guess what? The whole Gold Image disappeared!!!

This is not the expected behavior for this. The point here is that there were agents still subscribed to the GI. So even trying to create a new GI won’t allow us to subscribe those agents as they were still subscribed to the GI that was missing.

Using some Sherlock Holmes’ skills found that the GI was in fact still present in the OMR tables. You can use the query below to see all the GI currently living in your system.

SELECT image_name FROM MGMT$EM_GAI_ENTITIES;

What about the agents still subscribed to this GI?

SELECT * from EM_GI_AGENT_SUBSCRIBE WHERE gold_image_series='<GI Name>';

OK, so there should be something wrong here. After working with Oracle Support they found the issue and provided a patch. Please refer to Patch 28817766.

Apply this patch on top of your OEM 13.3 PG and you should see the GI again. If you are not able to see the GI, then create a new one with the same old name. Everything should look fine after that.

Happy patching!
Alfredo

Bye Bye Flash!

Formerly named Macromedia Flash (now Adobe Flash) player was really popular in the 2000’s. I remember learning it to add animation to Web Sites I was developing during my college years. Flash has also been the target of hackers to insert viruses, hence posing several security concerns to enterprises and home users.

In the IT world, change is the only constant. So, is time to move on to the next thing.

Adobe Flash is widely used in several Oracle sites. My Oracle Support, Oracle Business Intelligence EE, Oracle Enterprise Manager Cloud Control, etc.
Google just announced that it will stop supporting Flash in Google Chrome at the end of 2020.

https://blog.google/products/chrome/saying-goodbye-flash-chrome/

That means that you will loose all those fancy graphs in your browser. Well, not quite. Oracle started to provide patches and upgrades to get rid of Adobe Flash and use JET instead.

We still have more than a year for this to happen, but is the perfect time to start planning to switch. Is this already in your plans?

Thanks,
Alfredo

OEM 13c – Summer 2019 Announcements. You can manage ATP and more!!

Here are some exciting announcements for OEM 13c.

First, keep in mind that you should upgrade your OEM 13c to the latest version (13.3PG). I discussed some new features of this version in a previous post.

Now to the good news.  

1. Oracle Database 19c has been certified as a OEM Repository Database (OMR). This means that is time to upgrade your repository to a long term support release.

  More info : EM 13.3C: Certification of 19c Database as an EM Repository Database and as a Managed Target (Doc ID 2541711.1)    

2. Oracle EM 13.3.2 Bundle Patch (BP) is available. You must keep up with patching if you don’t want to encounter issues down the road.  

More info: Enterprise Manager Cloud Control Database Plugin 13.3.2.0 Bundle Patch Bug List (Doc ID 2554474.1)    

3. The latest EM 13.3.2 BP 3 has support for Oracle Autonomous Database (ATP-D). So, if you are running ATP in dedicated mode and you want to use OEM to manage your DB, is time to patch!!

More info: https://blogs.oracle.com/oem/oracle-enterprise-manager-cloud-control-13c-release-3-db-plugin-133200-with-bundle-patch-3-is-now-available-in-self-update    

Thanks,
Alfredo

Oracle Enterprise Manager 13.3 problems discovering Exadata targets

If you are on the latest Exadata plugin for EM 13.3 (well, even the 13.2 version of the plugin). Be aware that there’s a bug where if the Exadata nodes’ names are longer than 7 characters, you cannot discover them.

EM just throws a weird error with below legend:

SEVERE: makeTargetsXml for each rack#: 1 e=java.lang.StringIndexOutOfBoundsException: String index out of range: -1 

The solution is to apply the latest (March 2019) Bundle Patch to the agents running on the Exadata’s compute nodes.

More info on below MOS note:  

Exadata Discovery Fails With “No New Component Is Discovered. Click Cancel” (Doc ID 2477624.1)  

Another good excuse to keep your EM system up to date.  

Happy patching.  

Thanks,
Alfredo

Oracle Enterprise Manager 13.3.2 Database Plugin New Features (Flash and JET)

Oracle EM 13.3 PG was released April 28th 2019. This new (plugin only) release contains 8 new plugins including a new version of the DB plugin.

After installing these plugins we noticed several changes in behavior for the DB targets.  

  1. All pages that used Adobe Flash now use Oracle JavaScript Extension Toolkit (JET)
  2. Top Activity and Emergency Performance pages were completely removed from the Performance menu  

At the beginning was weird to use just ASH analytics instead of Top Activity page but all the key information is there.   There’s an option to revert 3 JET pages back to Flash if needed.

This can be done through emctl commands. All this information can be found on MOS 2422790.1  

Also, if you really need the old Top Activity page you can still access it using a direct URL link.

For this follow MOS 2528627.1 for more details.  

Thanks,
Alfredo