19c

I am SOOOO struggling with this nightmare block editor but here goes. Please excuse any formatting issues below: I thought it was time to show the new Oracle 19c Automatic Indexing feature in action and what better way than to go through how I created my first ever Automatic Index. To start, I create a […]

There is an odd contradiction that we all encounter for most databases, especially if they are predominantly used during the business day. Here is how that contradiction comes to be – it is in the way that we obtain and use optimizer  statistics on those databases. The contradiction runs like this:

• To minimize service disruption, we gather statistics at a quiet time, for example, in the middle of the night
• We then use those statistics during the business day whilst user activity is at its highest.
• Highest user activity will typically mean the highest frequency of data changes.
• Hence the statistics are at their peak accuracy when no-one is using them to optimize queries, and they are at their least accurate when everyone is using them to optimize queries!

We can demonstrate this easily with the following script run in 18c.

As a SQL_ID-like base 32 hash on table owner, name, column list

The indexes created by the 19c Auto Indexing feature have a generated name like: “SYS_AI_gg1ctjpjv92d5”. I don’t like to rely on the names: there’s an AUTO column in DBA_INDEXES to flag the indexes created automatically.

But, one thing is very nice: the name is not random. The same index (i.e on same table and columns) will always have the same name. Even when dropped and re-created. Even when created in a different database. This is very nice to follow them (like quickly searching in my e-mails and find the same issue encountered in another place). Like we do with SQL_ID.

Yes, the generation of the name is similar to SQL_ID as it is the result of a 64-bit number from a hash function, displayed in base 32 with alphanumeric characters.

ORA-65532: cannot alter or drop automatically created indexes

Oracle 19c Automatic Indexing is not like the autonomous features that happen without your control. You can decide to enable it (if you are on a platform that allows it) or not, and in report-only or implementation mode.

But when you have enabled it to create new indexes, you are not supposed to revert its effect. What if you want to drop those indexes?

DROP INDEX

If I want to drop an index that has been created automatically (i.e with the AUTO=’YES’ in DBA_INDEXES) I get the following error:

In my previous post I showed you that Oracle’s SQL loader (sqlldr) utility has a built-in timeout of 30 seconds waiting for locked resources before returning SQL*Loader-951/ORA-604/ORA-54 errors and failing to load data. This can cause quite some trouble! Before showing you the enhancement in 12.2 and later, here is the gist of the previous post.

Concurrency in Oracle sqlldr 12.1 and earlier

To show you how sqlldr times out I need to simulate an exclusive lock on the table in sqlplus for example. That’s quite simple:

That’s a very light bug with a very simple workaround, but it may require a little change in scripts. If you use passwordless authentication (external password file or OS authentication) with Data Pump in 19c it will ask for the password. The solution is just to answer whatever you want because the external authentication will be used anyway.

Example

I create the wallet

mkstore -wrl $ORACLE_HOME/network/admin -create <w4ll3t-P455w0rd
w4ll3t-P455w0rd
CREATE

I create a tnsnames.ora entry that I’ll use to connect:

cat >> $ORACLE_HOME/network/admin/tnsnames.ora  <CDB1A_SYSTEM=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=CDB1A))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
CAT

I add a credential for this entry — here SYSTEM user and its password:

While researching the use of JSON in Oracle 19c I came some interesting behaviour that wasn’t immediately obvious (to me). With this post I am hoping to save you a couple of minutes scratching your head when working with JSON_TABLE(). This is Oracle 19.3.0 on Linux and I’m connecting to it using SQLcl 19.1.

Some background

As part of my JSON-support-in-Oracle research I had a good look at JSON_TABLE. Although complex at first sight, it is a lot less intimidating if you know how to use XMLTABLE :) My goal for this post is to convert a JSON document to a relational structure.

This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.

Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:

1) What about new jobs submitted via the old API after the upgrade?

I had a friend point this one out to me recently. They use DBMS_JOB to allow some “fire and forget” style functionality for user, and in their case, the jobs are “best efforts” in that if they fail, it is not a big deal.

So whilst this may sound counter-intuitive, but if you rely on jobs submitted via DBMS_JOB to fail, then please read on.

By default, if a job fails 16 times in a row, it will marked as broken by the the database. Here’s a simple of example that, where the anonymous block will obviously fail because we are dividing by zero each time. I’ll set the job to run every 5 seconds, so that within a couple of minutes we’ll have 16 failures. First I’ll run this on 11.2.0.4

Even if the syntax accepts it, it is not a good idea to write a hint like:

/*+ USE_NL(A B) */ with multiple aliases (‘tablespec’) even if it is documented.

One reason is that it is misleading. How many people think that this tells the optimizer to use a Nested Loop between A and B? That’s wrong. This hint just declares that Nested Loop should be used if possible when joining from any table to A, and for joining from any table to B.

Actually, this is a syntax shortcut for: /*+ USE_NL(A) USE_NL(B) */