Probably the most common usage for large objects (CLOBs and BLOBs) is to store them in a database table. In this circumstance, it feels intuitive that you won’t have a lot of concerns about memory, because the database will simply store those objects in datafiles like it would any other kind of data.

But BLOBs and CLOBs can also be declared as local variables in a PL/SQL block. We typically expect local variables to be housed within the memory for that session (the PGA). There are explicit calls in the DBMS_LOB package to create a temporary large object, but what if we do not use that API? What if we just start bludgeoning a local variable with more and more data? Is this a threat to the session memory and potentially the database server ?

Time for a test!

To see what happens when we keep growing a BLOB, I’m going to monitor two attributes for a session:

I had an AskTOM question come in with an issue trying to PL/SQL on an Active Data Guard (ADG) database (which of course is running in read-only mode). The PL/SQL block seems innocuous; it does not DML and yet refuses to run:

SQL> DECLARE
  2    tst_row  dual%ROWTYPE;
  3  BEGIN
  4    null;
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

I did a trace on the code, and made an interesting discovery, and hence the cause of the error. When we reference a ROWTYPE definition, we need a mechanism to refer to and compile against that definition of that type. Hence we temporarily “create” a type definition to handle that. Tracing the above anonymous block, you’ll see the following entries in the trace file

Summary

Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes.

The missing index opportunities outlined here are

1. Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows
2. Joins that are missing index on the join field
3. Index range scans that could be made more efficient by addition of a field to existing index

Also for future consideration, but not yet outline here

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.

Most customers I encounter nowadays are generally sticking to the defaults when creating their databases from 12c onwards. This means that they’ll be using the UTF8 class of charactersets when configuring their database. As a quick segue from the topic at hand, I think this is a good thing. Having a UTF8 characterset avoids a lot of hassles later when applications encounter a need to store extended characters, and let’s face it, the moment you need to store someone’s name, then at some stage you are going to hit this.

But back to large objects. It is important to realise that a UTF8 characterset has implications with how LOBs will be stored in the database. In a single byte characterset database, then if you need to store a 1 megabyte text file in a CLOB, then it will consume approximately 1 megabyte. But lets look at what happens when you are using a multi-byte database.

MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn’t have Hash Join.

Here is the query, like on the Oracle example from the last blog post

select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;

So there are only 4 examples below which all do NL joins

1. full table scan on T1 and T2
   1. 7.83 secs
2. index on T1 predicate filter column
   1. 7.39 secs
3. index on T2 join column
   1. 0.49 secs
4. index on both T2 join column and T1 predicate filter column
   1. 0.06 secs

There isn’t an idea of “explain analyze” on MySQL until MySQL 8.0.18 and I did my testing on 8.0.17, so the explain plans costs are estimates not actual values, but the elasped time of the query is an actual value.

If you cannot wait for a fully autonomous offering, and you’ve jumped into the 20c preview release on Oracle Cloud, obviously the first thing you will probably be installing is Oracle Application Express.

Unlike autonomous, you’ll be installing it manually, which is a quick and easy process, and either in that installation or when adding ORDS later, you’ll be wanting to set the passwords for the public access accounts (typically APEX_PUBLIC_USER and APEX_REST_PUBLIC_USER).

Here’s what that looks like in Oracle Database 19c

Looking at ways to programmatically analyze explain plans to see if adding indexes would help.

The analysis is straight forward for for full table scans with strong predicate filters. If there is a strong predicate filter on an explain plan node with a full table scan then it’s a good candidate for an index.

Now a similar situation exists for a join.

Again if the join has a strong predicate filter on a full table scan, that is a candidate.

On the other hand when the actual join fields look like they will benefit from an index, it’s less obvious.

In the queries below T1 and T2 have the same data.

Here is the query

select  max(t1.data) from  t1, t2 
where 
      t1.id = t2.id  
   and t1.clus   = 1  ;

First we try with no indexes.

Then we try with an index on the predicate filter on T1.

Then we add a index on the join column on T2.

Let me start by saying that by no means am I a working from home expert; I do not have a degree in ergonomics or human resources or time management. I am just your normal IT worker that happens to have been working from home for a number of years now.

If you have just had that email from your manager or your Human Resources Department that says “Guess what! Time to grab a laptop and set up shop from home”, I thought I would share a couple of my experiences with you to ease the transition, and who knows? Maybe this will be the launch pad for you to pursue a remote working position full time with your current employer (or even a new one )

Today’s video demonstrates how to configure Kata Containers on Oracle Linux 7 (OL7), allowing you to run containers inside lightweight virtual machines (VMs).

This video is is based on an article of the same name, but relates to a bunch of other articles and videos on the subject of containers.