Uncategorized

Recently, I have been working with customers that have been concerned about the performance of various UUID formats. Other products have various performance characteristics for inserting, generating and presenting UUID data.

For this blog, I ran a quick series of tests using jmeter insert data along with some simple SQL tests to generate UUID values. Hopefully, this will be helpful to better your understanding of UUID with CockroachDB.

UUID formats

Cockroach DB has four different ways data can be formatted for use with the UUID data type.

String format
'63616665-6630-3064-6465-616462656564'

Curly Brace format
'{63616665-6630-3064-6465-616462656564}'

One of the very cool features we’ve been talking about for 20c is SQL Macros. But you no longer need to wait for a future release of the database to get access to all the goodness of SQL Macros.

Why? Because much of the functionality has now been backported to 19c, and is also now officially in the documentation so there’s no ambiguity as to whether you are supported to use them or not. They’ll be coming soon to an RU near you

• 19c
• Autonomous
• livesql
• Macros
• SQL
• Uncategorized

Any database application that stores information about people will very likely store a person’s date of birth as part of that data. Whether it be for marketing purposes, or part of the security checks for a password reset, or part of the authentication when the inevitable call centre calls you with their “latest, greatest offer”, the date of birth is common.

But when it comes to reporting, typically it is not the date of birth that we present on our screen or hard-copy, but the person’s age. Clearly we cannot store a persons age in the database because it changes every day, month or year depending on the granularity with which you present it. Which leads to the ponderance – Surely it cannot be that hard to calculate someone’s age from their date of birth?

Oh yes indeed, I have dug deep into the well of cheesy blog post titles for this one . But hey, I have two teenage children so I figure that I have reached the age where you’ll need to permit me my share of terrible Dad-joke style puns.

Consider the two dates below (both of which include a time component):

04-SEP-2020 00:00:00
05-SEP-2020 00:08:02

Let us do some quick arithmetic in our heads. How many minutes are there between those two dates? To avoid any reader headaches I’ll throw in the fact that there are 1440 minutes in a day. So there is one day between them (1440) plus an additional 8 minutes past midnight bringing us to 1448, plus a couple of seconds left over.

The tech world is a big and varied place, so whilst there are some database customers running on 19c and have applied the latest RUs etc to get them to (at of time of writing) 19.8, there are plenty of customers running on 18c, plenty running on 12.2, plenty on 12.1 and so forth all the way down the version tree.

What this means is that we have a plethora of versions of the documentation available to serve those customers, and since all of those versions are in active use, the various search engines of the world have to decide which versions of the documentation best match the search items you have entered.

Thus if you search for “DBMS_JOB”, then your search engine of choice might show you the 12c version, or the 10.2 version, or the 19c version, all dependent on the multitude of things that search engines use to decide what you should be shown first.

Here’s a “gotcha” brought to my attention by one of our AskTOM readers. I’ve mentioned in the past that DBMS_JOB, having been deprecated in favour of DBMS_SCHEDULER, got a new lease of life in 19c because under the covers we translated calls to create a job under DBMS_JOB to the same mechanism in DBMS_SCHEDULER.

The benefit of that is that we don’t need to maintain our older DBMS_JOB code base, but your existing code is fine to keep running. However, as I said in the other post, you do need to alter your privileges, but here is another discovery that might impact you as well.

Frequency histograms in any version of Oracle are pretty cool things, so whenever you have a distribution of data that potentially skewed and the number of distinct values fall under the limit of how many histogram buckets you can have, then a frequency histogram is pretty much a no-brainer. In particular, if you don’t have a large number of distinct values, the nice thing is that you can nominate the largest bucket size possible, and we’ll only create the buckets required to contain the frequency information.

For example, I’ll create table with only 3 distinct values (1,3 and 5) and the distribution of the data is skewed. Then I’ll ask for a 254-bucket histogram, but you can see by querying USER_HISTOGRAMS that only 3 buckets were required to hold the histogram.

When you connect to your Autonomous Database, you get to choose from some predefined services. The services available depends on whether you are using a transaction processing (ATP) or a data warehouse instance (ADW) of the database, but for example, for an ATP database you get the following:

Note: This is a screen of the docs as of time of writing this post. Over time, that may change so always be sure to consult the docs directly in future.

The other day as my PC popped up its usual “Hey, you really should look at buying a bigger disk” message, I was motivated to see if I could clear down some of the space utilization on the myriad of databases on my machine. On the majority them I encountered no issue doing that, but on one of them, the query to DBA_FREE_SPACE revealed an odd performance issue:

TL;DR: There is no schema grant command in Oracle, but this routine lets you get close.

Why no schema level grant?

A very common request on the various forums that exist for the Oracle Database is a “schema level grant”. The idea is that you could do something like

grant select on HR to SCOTT;

The concept sounds relatively straightforward but there would ne some nuances to cover off, for example: