APPS-ORACLE.RU

For the Oracle performance tuning and troubleshooting Telegram channel https://t.me/ora_perf, I developed a simple helpful Telegram bot. It simplifies common Oracle database tasks directly within Telegram.

Here's what the bot can do:

The “coalesce subquery” transformation introduced in the 11gR2 timeline can produce wrong results and you will find some bug reports and patches on MOS spread over versions from 12cR1 onwards. If you find that you are hitting a bug for which there appears to be no patch there are several [...]

This is just a reminder and a quick note about the Oracle error:

Since the named tablespace is not a temporary tablespace, the error has occured as a data sesgment was being created, rebuilt, moved, or was trying [...]

I responded to a problem on one of the Oracle forums recently about getting rid of some temporary segments that wouldn't disappear after a call to “alter table move …” command had failed, and in searching for a note I'd written about how smon handled this situation I came across [...]

There is a common misconception that partition pruning does not help in the case of global indexes and only works with local indexes or full table scans (FTS).

It is understandable how this misconception arose: indeed, when operations like PARTITION RANGE ITERATOR, PARTITION RANGE SINGLE, etc., appear in execution plans, partition [...]

Here's a note that's been sitting as a draft for the last 7 years – finally dusted down, retested, and published. Following my new publishing paradigm, I'm going to tell you the conclusion at the start, then show the working that demonstrates the point.

When cloning a table, or subset of [...]

When the dbms_space_usage package came out reporting the “percentage free” space in ASSM blocks with ranges like 0-25%, 25%-50%, etc. with flags FS1 to FS4, I had great trouble remembering whether 0-25% was FS1 or FS4 and it was a strangely long time before I managed to get the obvious [...]

Forums, mailing lists, and StackOverflow are all great resources for Oracle performance discussions, but I've long thought it would be useful to have a dedicated online chat/group specifically for Oracle performance specialists. A place to share news, articles, and discuss performance issues. To test the waters, I've created a group: [...]

In this part, I'll show how to implement Dynamic Range Segmentation (DRS) explained in the previous part using a custom Domain Index, allowing you to apply this optimization with minimal changes to your existing tables.

First, we create a function that will be [...]

In the previous part, I discussed the most efficient known methods for optimizing range queries. In this part, I'll introduce a simple version of my custom approach, which I call Dynamic Range Segmentation (DRS).

As explained earlier, a significant issue with conventional approaches is the lack of both [...]

One of the most common and enduring challenges in database management is performing efficient interval searches, particularly for date intervals such as: WHERE :dt BETWEEN beg_date AND end_date.

In this series of articles, I will explore various strategies for optimizing such searches. We'll delve into well-known standard approaches, analyze their limitations, [...]

Years ago, I had to solve the problem of cluster-wide contention for log table blocks during a massive number of inserts from numerous sessions.
Since the table was essentially insert-only and reads were extremely rare (only during investigations of issues), the obvious solution was for sessions from each cluster node to [...]

I saw a recent tweet (on Bluesky) from SQLDaily highlighting a blog note that Lukas Eder wrote in 2016 with the title: “Avoid using COUNT() in SQL when you could use EXISTS()”. This is a good guideline because it probably maximises the options the optimizer has for producing [...]

Here's a cut-n-paste from a simple SQL*Plus session running under 19.11.0.0 (23.4 produces the same effects):

Part of my effort to clear my long backlog of drafts

This is a trivial script I wrote many years ago – the date on the draft (one of the oldest on my blog) was August 2009 – but the script itself must have started life in 8i if not earlier

All [...]

Many years ago I wrote a short note to introduce the view v$sql_optimizer_env (and the session and system equivalents) which report the current values of optimizer related parameters (some of which are not found in the startup file) for a cursor child, or a session, or the system. I [...]

What would Shakespeare say?

If you thought 1,000 columns was over the top for a table in Oracle, then you'll love 23ai which allows you to stretch that to 4,096 columns if you've set the parameter max_columns = extended in the spfile (or the pdb equivalent).

Naturally I was curious to see if there had [...]

This note is an observation of an odd little detail that's nice to know about the way that Unified Audit (see part 8 of Neil Chandler's series on Fundamental Security) handles deleting old data. I don't have a lot of background experience with Unified Audit, but I was surprised [...]

A question appeared on the Oracle developer forum recently (Oct 2024) that could be answered by a blog note that I started drafting about 18 months ago after answering a very similar question that had appeared in the same forum. Take a look at the following [...]

Some years ago I wrote a note describing how with a query using identical literal values in the predicate, the optimizer was able to produce exactly the same plan body with exactly the same Predicate Information section while producing different cardinality estimates depending on how you expressed the [...]

What could make a “create index” statement fail with an error about a table having too many columns (ORA-01792)? And why would this suddenly happen when the code that raised the error had been dropping and recreating the index every night for the best part of 3 years with [...]

A recent MOS Community forum posting (needs an account) raised the problem of a query with a specific index hint switching between two plans, one using “inlist iteration of a unique index scan” the other using a simple “index range scan with filter predicate”. Here's an example of [...]

This is a small case study from one of the MOS Community Fora (needs a MOS account) that I drafted about a year ago. It started out as a question about a two node RAC system running Oracle 19.17 where an SQL Monitor report showed time spent in a [...]