Search

Top 60 Oracle Blogs

Recent comments

January 2020

Index Engineering

This is a case study based on a question that appeared on the Oracle Developer Community forum a few days ago.

What I’m aiming to present in this note is the pattern of thinking that you should adopt in cases like this. The final suggestion in this note isn’t necessarily the best answer to the question posed (at the time of writing the OP hadn’t supplied enough information to allow anyone to come up with a best solution), but the point of the exercise is to talk about the journey and (perhaps) remind you of some of the extreme engineering you can do with indexes.

The (massaged) problem statement is as follows:

I have a table of more than 200 million rows that is used for inserts, updates and queries. I have a query on this table and want to know what index I could create to speed up the query.

Video : Schema Only Accounts in Oracle Database 18c Onward

Today’s video is a demonstration of schema only accounts, introduced in Oracle Database 18c.

This is based on the following articles.

Oracle Database 19c Automatic Indexing: Minimum Number Of Required Indexes (Low)

  As I discussed in my previous posts, Oracle Automatic Indexing will try and create as few indexes as possible to satisfy existing workloads, even if that means reordering the columns in an existing index. To illustrate how Automatic Indexing creates as few indexes as possible, I’ll create the following table which has a number […]

Has my scheduler been turned off?

When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off.  That command was:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

PL/SQL procedure successfully completed.

However, a quick check of the documentation in later releases, no longer makes any reference to this attribute:

image

Postgres@CERN

For once, I was at a conference without being a speaker, but co-organizer. The idea came only 3 months ago at the Swiss PostgreSQL Users Group dinner organized by dbi services in Milan before the PostgreSQL Conference when Laetitia Avrot asked me if it would be possible to organize a meetup at CERN.

SwissPUGOrg dinner in Milan, organized by dbi services

My answer was “yes” of course, but I had just resigned from CERN so it had to happen before February where I come back the consulting life at dbi-services. Laetitia organized everything in a short time: find the sponsors, the speakers, organized the drinks and food, advertise for it,…

How to obtain semaphore information in gdb when the symbols are missing

This post was created when trying to understand how the Oracle executable works. Specifically the logwriter, which, if it is posted by a process, which is done using semop(), signals that process back using semop() if the logwriter happens to be in post/wait mode, and is not using the ‘scalable logwriter mode’, which means it is not using additional worker processes.

To be more specific, I tried investigating something that is not Oracle specific, but specific to the usage of semaphores on linux with an executable for which you do not have the source code and is not compiled with debugging symbols.

I attached to the process using gdb, and put a break on semop:

Retrieve PostgreSQL variable-length storage information thanks to pageinspect

Introduction

In PostgreSQL a variable-length datatype value can be stored in-line or out-of-line (as a TOAST). It can also be compressed or not (see the documentation for more details).

Let’s make use of the pageinspect extension and the information about variable-length datatype found in postgres.h to build a query to retrieve tuples variable-length storage information.

The query

The query is the following:

What’s new with Oracle database 18.8 versus 18.9

For the difference between Oracle database versions 18.8 and 18.9 this too follows the line of a low amount of differences.

As always, there are some parameters that have changed from being undocumented spare to being undocumented with a name.

Also, the DBA and CDB table (DBA|CDB)_REGISTRY_BACKPORTS is back again. The disappearance of this table in 18.8 turned out to be a bug. There is a patch for 18.8 if you need this table.

Group by Elimination

Here’s a bug that was highlighted a couple of days ago on the Oracle Developer Community forum; it may be particularly worth thinking about if if you haven’t yet got up to Oracle 12c as it appeared in an optimizer feature that appeared in 12.2 (and hasn’t been completely fixed) even in the latest release of 19c (currently 19.6).

Oracle introduce “aggregate group by elimination” in 12.2, protected by the hidden parameter “_optimizer_aggr_groupby_elim”. The notes on MOS about the feature tell us that Oracle can eliminate a group by operation from a query block if a unique key from every table in the query block appears in the group by clause. Unfortunately there were a couple of gaps in the implementation in 12.2 that can produce wrong results. Here’s some code to model the problem.

Announcing SLOB 2.5.2.2

SLOB 2.5.2.2 is available via the SLOB Resources Page.

SLOB 2.5.2.2 is a bug-fix release. After announcing the undocumented Obfuscated Column Data Feature, a few SLOB users reported bugs. The bugs have been fixed in this release.