Author: Maria Colgan

Explaining the Explain Plan – How to Read and Interpret Execution Plans

 

Examining the different aspects of an execution plan, from cardinality estimates to parallel execution, and understanding what information you should glean from it can be overwhelming even for the most experienced DBA.

That’s why I’ve put together a series of short videos that will walk you through each aspect of the plan and explain what information you can find there and what to do if the plan isn’t what you were expecting.

What is an Execution Plan?

The series starts at the very beginning with a comprehensive overview of what an execution plan is and what information is displayed in each section. After all, you can’t learn to interpret what is happening in a plan, until you know what a plan actually is.

How to Generate an Execution Plan?

Although multiple different tools will display an Oracle Execution Plan for you, there really are only two ways to generate the plan. You can use the Explain Plan command, or you can view the execution plan of a SQL statement currently in the Cursor Cache using the dictionary view V$SQL_Plan. This session covers both techniques for you and provides insights into what additional information you can get the Optimizer to share with you when you generate a plan. It also explains why you don’t always get the same plan with each approach, as I discussed in an earlier post.

How to use DBMS_XPLAN to FORMAT an Execution Plan

The FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function is the best tool to show you detailed information about a what’s happened in an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.  I’ve also covered a lot of the content in this video in a previous post.

Part 2 of the series will cover Cardinality Estimates and what you can do to improve them!

Remember you can always get more information on the Oracle Optimizer on the Optimizer team’s blog.

How to read a Parallel Execution Plan in Oracle

The volume of data being stored in databases has grown exponentially in recent years. So too has the need to rapidly generate value or business insights from that data.

Parallel execution is the key to processing large volumes of diverse data quickly, as it subdivides complex tasks into a number of small tasks allowing multiple processes to accomplish a single complex task.

However, the use of parallelism can complicate the execution plan displayed. Oracle not only displays the operations needed to complete the SQL statement in the plan but all of the communication steps between the parallel server processes.

So, how should you go about interpreting a parallel execution plan?

In the video below, I give you a step by step guide on how to read parallel plans and what additional information you can glean from them!

How to setup VNCServer on an OCI VM?

Now we are all working from home, I’ve noticed that my MAC laptop is severely overloaded when I do live demos during webinars. After all, it’s running my camera, Zoom, PowerPoint, my Java app, and monitoring tools.

So, I decided it was time to move my demo environment to the Oracle Cloud, where I quickly provisioned a 2-OCPU VM running Linux following the instructions in my previous blog post.

Screen Shot 2020-08-11 at 12.56.23 PM

Once I had my VM up and running, I wanted a proper desktop experience, so I needed VNC.

After a quick google search, I found the video below, which provides a very easy to follow, step by step guide to installing and configuring TigerVNC VNCServer on OCI infrastructure. I followed all of these steps except for the final stage where they describe adding the VNCServer to your firewall.

I’m married to a security expert, who strongly advised against this approach. He told me it would be far more secure to use an SSH tunnel instead of opening the firewall for the VNC port.

Below the video are the full set of commands I used in my setup, including how to establish the ssh tunnel, so you can quickly cut and paste them.

Continue reading “How to setup VNCServer on an OCI VM?”

How to Provision a VM in the Oracle Cloud?

This blog post outlines the 10 simple steps necessary to provision, and connect to, a VM on Oracle Cloud Infrastructure (OCI).

          1. Confirm you have an SSH Public Key on your laptop or localhost.
            $ cd ~/.ssh
            — check if you have an existing if you have a key already
            $ ls
            id_rsa    id_rsa.pub    known_hosts

            You’re looking for a file named either id_dsa or id_rsa and a matching file with a .pub extension. The .pub file is your public key, and the other file is the corresponding private key. If you don’t have these files or you don’t remember your passphrase, you will need to complete the steps outlined here.NOTE: You can’t move on unless you have your SSH Public key.
          2. Connect to the OCI console to begin the provisioning process.
            From the hamburger menu in the upper left-hand corner select, the Compute menu item followed by the Instances option.
          3. On the Instances page, click the Create Instance button.
            Step3_begin_Create_instance_2
          4. Specify a unique name for your instance and accept the default Oracle Linux image.
            Step4_name_instance
          5. Scroll down and click on the Change Shape button.
            Step5_change_shape
          6. To run my demos I typically use Swingbench, which needs a minimum of 2 OCPUs (4 OCPUs if you use the JSON workload). So, I select a Virtual Machine with Intel Skylake processors and  2 OCPUs. Then click the Select Shape button.
            Step6_pick_shape
          7. I use the automatic defaults in the Configure Networking and Boot Volume sections and move on to the SSH Key section.Here I select Paste SSH Keys and cut and paste my public key from my .ssh directory into the window provided.
            Picture7_ssh_key
          8. Finally hit the Create button at the end of the page.
          9. Instantly you will see a new VM is being provisioned for you. Once available, you can connect to the machine using the public IP address and the user OPC. You will find the IP address, on the main Instance console page.Step9_connect
          10. Simply ssh into the machine from your laptop using the supplied OPC user.

            $ ssh opc@XXX.XXX.XX.XXX
            Enter passphrase for key '/Users/sqlmaria/.ssh/id_rsa':
            Last login: Tue Jul 28 16:39:35 2020 from XXXXX

How to use Oracle Database In-Memory for free!

Oracle recently announced a new free base level for the Oracle Database In-Memory Option, their real-time analytics capability. With the base level, you can allocate up to 16GB of memory to the In-Memory column store, per instance without having to have an addition In-Memory license. Larger column stores will still need the additional license. Continue reading “How to use Oracle Database In-Memory for free!”

Oracle Database 19c Upgrade Virtual Classroom

Over the last couple of months, the wonderful folks from Oracle Australia have been running a FREE virtual training event on what to expect from Oracle Database 19c and how to prepare to upgrade to the latest long-term support release of the Oracle Database.

The first 5 sessions covered everything you would want to know about how to upgrade and what you can expect from Oracle Database 19c including an in-depth look at the new Multitenant architecture. The sessions so far have been presented by some of the best Oracle speakers around including Mike Dietrich, Julian Dontcheff, Martin Bach, Richard Agnew, and Alex Blyth. All of the sessions so far have been recorded and are available for replay.

Under the Hood of an Autonomous Database

The final session in series is scheduled for August 11th at 10am AEST ( August 10th at 5 pm PST) and will be presented by Alex Blyth and myself. In our session, we plan to give you a peek under the hood of the Oracle Autonomous Database and provide you with a clear understanding of how this unique autonomous database works. We’ll share details on the exclusive combination of Oracle Database 19c features, best practices, and machine-learning algorithms used to deliver this family of cloud services. We’ll use live demos to show you how it can simplify your approach to data management with Oracle Database 19c and accelerate your transition to the cloud.

You can registers for this session here. Hope you can join us!

self_driving

Data-Driven Apps – What are they and the easiest way to develop them

At the moment, we hear a lot about how businesses need to become data-driven to remain competitive, how business need to understand their customer’s needs and quickly deliver value to those customers.

But how do you do that?

You take advantage of data-driven apps that allow users to create value or insights from data in real-time. 

What are Data-Driven Apps?

Data-driven apps operate on a diverse set of data (spatial, documents, sensor, transactional, etc.) pulled from multiple different sources, often in real-time and create value from that data in very different ways to traditional applications. For example, they may use Machine Learning to make real-time recommendations to customers or detect fraudulent transactions. Or use Graph analytics to identify influencers in a community and target them with specific promotions or perhaps use spatial data to keep track of deliveries.

These apps are also frequently deployed on multiple platforms, including mobile devices as well as standard web browsers, which means they need a flexible, scalable and reliability deployment platform. Given the demands on these apps, they need to be continuously developed to adapt to new use cases or user needs, and all updates must happen online as they have to be available 24×7.

When building data-driven apps, developers need to leverage an ever-increasing set of data processing and machine learning algorithms to meet these requirements.

So how should you go about developing and deploying data-driven apps quickly, efficiently, and more importantly, in a maintainable way?

Data-Driven Techniques and Technology

You take a data-first approach, or as A. Neil Pappalardo put it ‘A Minimize Code, Maximize Data‘ approach. In other words, you bring the algorithms to the data, not the data to the algorithms.

In the video below, I explain how to take advantage of the built-in features and functionality of the Oracle Database to develop and deploy data-driven apps efficiently. I also share some easy to follow code examples to demonstrate how much simpler your application code can be if you use this approach! Continue reading “Data-Driven Apps – What are they and the easiest way to develop them”

How to implement Data-Driven Apps – Using many Single Purpose Database or with a single Converged Database?

There is an on-going debate in our community about the best approach for developing cloud-native or data-driven apps. On one side, you have folks who say use a single-purpose “best-of-breed” database for each data type or workload you have. While the other half say, you should use a single converged database. So, which approach is right for you and your projects?

Let’s examine some of the pros and cons of each approach.

Single-purpose Databases

Single-purpose databases or purpose-built databases as they are often as known, are engineered to help solve a single or small number of problems. Given their narrow focus, they can ignore the tradeoffs usually required when trying to accommodate multiple data types or workloads. It also allows them to use a convenient data model that fits the purpose and to adopt APIs that seem natural for that data model. They offer less functionality than converged databases, and therefore, fewer APIs, making it easier to start developing against them. Their simplicity means they do a few things very well, but other things not at all. For example, a lot of single-purpose databases scale well, because they offer no strong consistency guarantees.

At first glance, single-purpose databases appear to be a good option. Developers are happy because they get exactly what they need to begin a project. However, when you look at the bigger picture, single-purpose databases can cause a lot of pain and end up costing more in the long run.

Continue reading “How to implement Data-Driven Apps – Using many Single Purpose Database or with a single Converged Database?”

Generating an AWR report for Autonomous Databases

Oracle Autonomous Database automates the lifecycle management of a database, everything from provisioning, scaling, backups and patching, but what it doesn’t do yet is fully tune your application.

You are still on the hook to make sure your app doesn’t have any concurrency bottlenecks or poorly written SQL that Auto Indexing can’t address.

So, what can you do to monitor your app while it’s running on an Autonomous Database?

The first place you can start is the Performance Hub tab on the cloud console. Here you’ll find both real-time and historical performance data in the form of Active Session History (ASH) information for the last seven days, and SQL Monitor reports for the high-load SQL. You can aggregate the ASH data several different ways including by wait class, database service, resource group or SQL ID. The same information is also available in Oracle Management Cloud (OMC), and SQL Developer Web.

But if you are trying to get a holistic view of how your app is behaving on a database, nothing beats an Automatic Workload Repository report or AWR report.

Continue reading “Generating an AWR report for Autonomous Databases”

What are Query Block Names and how to find them

I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.

What are query blocks?

A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.

The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.

Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.

Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.

How do I find the name of a query block?

To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.

The new section will list the Query Block name for each of the lines in the plan.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4c8bfsduxhyht, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno 
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818
---------------------------------------------------------------------------
| Id  | Operation	   | Name | ROWS  | Bytes | Cost (%CPU)| TIME	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	  |	  |	5 (100)|	  |
|*  1 |  HASH JOIN SEMI    |	  |	5 |   205 |	5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   280 |	2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |	1 |    21 |	2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / E@SEL$1
3 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
3 - FILTER("D"."LOC"='DALLAS')

As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.

Continue reading “What are Query Block Names and how to find them”