Search

Top 60 Oracle Blogs

Recent comments

Oracle

Find the SQL Plan Baseline for a plan operation

By Franck Pachot

.
If you decide to capture SQL Plan Baselines, you achieve plan stability by being conservative: if the optimizer comes with a new execution plan, it is loaded into the SQL Plan Management base, but not accepted. One day, you may add an index to improve some queries. Then you should check if there is any SQL Plan Baseline for queries with the same access predicate. Because the optimizer will probably find this index attractive, and add the new plan in the SPM base, but it will not be used unless you evolve it to accept it. Or you may remove the SQL Plan Baseline for these queries now that you know you provided a very efficient access path.

But how do you find all SQL Plan Baselines that are concerned? Here is an example.

I start with the SCOTT schema where I capture the SQL Plan Baselines for the following queries:

What’s new with Oracle database 11.2.0.4.200114 versus 11.2.0.4.200414

This blogpost takes a look at the technical differences between Oracle database 11.2.0.4 PSU 200114 (january 2020) and 200414 (april 2020). This gives technical specialists an idea of the differences, and gives them the ability to assess if the PSU impacts anything.

Functions

VirtualBox 6.1.6

Along with all the other Oracle quarterly patches comes VirtualBox 6.1.6.

The downloads and changelog are in the usual places.

I’ve done the installation on Windows 10, macOS Catalina, and Oracle Linux 7 hosts. All worked fine.

I’ve done some Vagrant builds with it and they worked too.

Video : Invisible Columns in Oracle 12.1 Onward

In today’s video we demonstrate the invisible columns feature introduced in Oracle 12.1.

The video is run through of the examples in this article.

Video : Online Table Move Operations in Oracle 12.2 Onward

In today’s video we demonstrate how to move, or rebuild, a table as an online operation.

This video was done as a response to some questions about the previous video on shrink operations. As usual, the video is based on some stuff I’ve written previously.

Oracle library cache cursor child generation

This post is about library cache SQL cursors, and how these are managed by the database instance.

Oracle multi-tenant and library cache isolation

This post is the result of a question that I got after presenting a session about Oracle database mutexes organised by ITOUG, as a response to the conference cancellations because of COVID-19. Thank Gianni Ceresa for asking me!

The library cache provides shared cursors and execution plans. Because they are shared, sessions can take advantage of the work of previous sessions of creating these. However, by having these shared, access needs to be regulated not to have sessions overwrite each other’s work. This is done by mutexes.

The question I got was (this is a paraphrased from my memory): ‘when using pluggable databases, could a session in one pluggable database influence performance of a session in another pluggable database’?

The Oracle ACE Program : My 14 Year Anniversary

I was checking my calendar, thinking I was about to jack it in for the day, and I noticed it’s April 1st, which means it’s my 14th year anniversary of being an Oracle ACE. Can’t believe I nearly missed that!

As usual I’ll mention some of the other anniversaries that will happen throughout this year.

World Backup Day- Backing up an Oracle Database using RMAN to Azure Blob Storage

A DBA is only as good as their last backup…or more so, their last recovery.

To celebrate #WorldBackupDay on Twitter, I’m blogging on how to backup an Oracle database directly to Azure Blob storage.  Yes, you could backup on a managed disk directly connected to the VM, then copy it off, but Azure Blob Storage is inexpensive and provides considerable speed and opportunity to create an NSF mount to use the backups with other Oracle hosts for cloning, recovery, etc.

Configure Blob Storage in the Azure Portal

In this example, we have an Oracle database running on an Azure IaaS VM and need to backup the database with RMAN.  No RMAN repository catalog will be used, but yes, you can use one-  no problem.

If you don’t already have a database and want to play along, you can perform the following blog post to create one.

Create a Simple Oracle VM on Azure IaaS

Use the following shell script to create your Oracle VM.  I chose the following parameters to create mine:

Using Azure Cloud Shell and with persistent storage, which are linked on the github page, I uploaded the mk_oravm.sh script and run it after changing the permissions.

chmod 744 mk_oravm.sh

Provision the VM

Run the script:

 ./mk_oravm.sh

Anwser the questions from the script: