APPS-ORACLE.RU

In the previous part, I forgot to mention one important detail: if you want to export or extract RTSM (Real-Time SQL Monitoring) reports directly in XML format for further analysis, you can simply use the following functions:

When you work with a large number of Real-Time SQL Monitor (RTSM) reports in the ACTIVE format (the interactive HTML report with JavaScript), it quickly becomes inconvenient to open them one by one in a browser. Very often you want to load them into the database, store them, index them, [...]

When using the merge command to process small amounts of data, be careful that you don't manage to enable parallel DML or you may find that you add a lot of empty space to the object. This could become very wasteful if you were running a loop that processed a [...]

Here's a little detail about Oracle's calculation of user_tables.avg_row_len that I hadn't noticed before – and I really should have noticed it years ago, so maybe I'm the only person who's surprised by it.

The figure isn't necessarily telling you about the number of bytes stored in the row.

We've [...]

In recent posts I've started with the conclusion so that you can decide very quickly whether or not the article is likely to be relevant to your current requirements.

SQL Developer (I have been told) always uses the character data type to pass bind variables to the database and leaves it [...]

Here's a simple detail about bind peeking (during optimisation) that makes a big difference to “fetch first/next” queries. The optimizer knows about your bind types, and can peek at the bind values – but that doesn't mean it will do something sensible with them. Here's a little model to demonstrate [...]

This note is a follow-on to a note I published a couple of years ago, and was prompted by a question on the MOS community forum (needs an acount) about the performance impact of using bind variables instead of literal values in a clause of the form: [...]

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 [...]