19c

Dead Connection Detection is a useful feature of the Oracle database: it allows for the cleanup of “dead” sessions so they don’t linger around consuming memory and other system resources. The idea is simple: if the database detects that a client process is no longer connected to its server process, it cleans up. This can happen in many ways, in most cases this kind of problem is triggered by an end user.

A dead connection shouldn’t be confused with idle connections: an idle connection still maintains the network link between client and server process, except that there is no activity. Idle connections aren’t maintained/controlled via DCD, there are other tools in the database handling such cases.

As a by product, DCD can also help with overly eager firewalls forcibly removing seemingly idle network connections. I found the following posts and the references therein very useful:

Those are the two exciting new features about the optimizer statistics which arrived in the latest release of 12cR2: 19c. Less exciting is that we are not allowed to use them in any other platform than Exadata:

But let’s cross the fingers and hope that this will be released in the future because they solve real-life problems such as Out-of-Range queries. Here is a little example involving both of them. A table starts empty and is growing during the day. Relying only on the statistics gathered during the maintenance window will give bad estimations. And dynamic sampling may not sample the right blocks.

I’ve very excited to announce the next series of Oracle “Let’s Talk Database” events to be run throughout Australia and New Zealand in October 2019. I’ll be discussing two exciting topics this series, “Oracle Database 19c New Features” and “Oracle Exadata X8“. As always, these sessions run between 9am-1pm, include a networking lunch and are free, […]

In Part I, we explored some options that Oracle might adopt when ordering the columns within an Automatic Index by default, in the absence of other factors where there is only the one SQL statement to be concerned with. A point worth making is that if all columns of an index are specified within SQL […]

The next thing I was curious about regarding Automatic Indexing was in which order would Oracle by default order the columns within an index. This can be a crucial decision with respect to the effectiveness of the index (but then again, may not be so crucial as well). Certainly one would expect the index column […]

One of the first questions I asked when playing with the new Oracle Database 19c Automatic Indexing feature was how many executions of an SQL does it take for a new index to be considered? To find out, I create the following table: I then ran the following query just once and checked to see […]

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: