Top 60 Oracle Blogs

Recent comments


Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part II (Sound And Vision)

  In my previous post, I discussed how the Automatic Indexing task by using Dynamic Sampling Level=11 can correctly determine the correct query cardinality estimates and assume the CBO will likewise determine the correct cardinality estimate and NOT use an index if it would cause performance to regress. However, if other database sessions DON’T use […]

Oracle 19c Automatic Indexing: Data Skew Fixed By Baselines Part I (The Prettiest Star))

In my previous few blog posts, I’ve been discussing some issues in relation to how Automatic Indexes handle SQL statements that accesses skewed data. In this post, I’m going to setup the scenario in which Automatic Indexing can potentially use Baselines to help address some of these issues. BUT, as we’ll see, I’m having to […]

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part II ( Sleepwalk)

As I discussed in Part I of this series, problems and inconsistencies can appear between what the Automatic Indexing processing thinks will happen with newly created Automatic Indexing and what actually happens in other database sessions. This is because the Automatic Indexing process session uses a much higher degree of Dynamic Sampling (Level=11) than other […]

Oracle 19c Automatic Indexing: CBO Incorrectly Using Auto Indexes Part I (Neighborhood Threat)

Following on from my previous few posts on “data skew”, I’m now going to look at it from a slightly different perspective, where there is an inherent relationship between columns. The CBO has difficulties in recognising (by default) that some combinations of column values are far more common than other combinations, resulting in incorrect cardinality […]

Oracle 19c Automatic Indexing: Data Skew Part III (The Good Son)

  I’m going to expand just a tad on my previous posts on data skew and run a simple query that returns a few rows based on a column predicate AND another query on the same column that returns many rows. The following table has a CODE column as with previous posts with the data […]

Oracle 19c Automatic Indexing: Data Skew Part II (Everything’s Alright)

In my previous post, I discussed an example with data skew, in which the Automatic Indexing process created a new index, but somehow the CBO when using the index estimated the correct cardinality estimate even though no histograms were explicitly calculated. In this post I’ll answer HOW this achieved by the CBO. Get some idea […]

When can I use SQL Macros?

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 </p />

    	  	<div class=

Oracle 19c Automatic Indexing: Data Skew Part I (A Saucerful of Secrets)

When it comes to Automatic Indexes, things can become particularly interesting when dealing with data skew (meaning that some columns values are much less common than other column values). The next series of blog posts will look at a number of different scenarios in relation to how Automatic Indexing works with data that is skewed […]

AWR: Multitenant-Specific Initialization Parameters

By default, the database engine automatically takes snapshots in the root container only. Such snapshots cover the root container as well as all open PDBs belonging to it. From version 12.2 onward, you can control whether the database engine automatically takes also PDB-level snapshots through the dynamic initialization parameter AWR_PDB_AUTOFLUSH_ENABLED. In case you want to enable that feature, you have to carry out two operations:

  • Set the initialization parameter AWR_PDB_AUTOFLUSH_ENABLED to TRUE (the default value is FALSE) either in a specific PDB or, if you want to enable it for all PDBs, in the root container.
  • Set the snapshot interval at the PDB level.

Note that to enable automatic PDB-level snapshots it’s necessary to set the snapshot interval because the PDB-level default is 40,150 days! Hence, if you don’t change it, the database engine will never take them.

Docs hack to save you time!

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.