APPS-ORACLE.RU

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

Oracle error 942 translates, for most people, into: “table of view does not exist”. This note is based on a conversation that I had with Mikhail Velikikh on the Oracle-l list server over the last couple of days while looking at the question: “How do I find out which table [...]

Here's a silly little puzzle that baffled me for a few moments until I spotted my typing error. It starts with a small table I'd created to hold a few rows, and then deletes most of them. Here's a statement to create and populate the table:

What to do when you hit a problem (possibly after an incomplete recovery) that reports an “ORA-00001 unique key violation” on sys.wrm$_snapshot_pk – as reported recently in this thread on the MOSC SQL Performance forum (needs a MOS account.)

Snapshot ids are carefully sequenced, without gaps, so somehow the thing [...]

Here's a detail about dbms_output that is probably overlooked because (in most cases) it's ignorable, except that it can lead to unexpected response times when you try using it to debug “busy” operations.

A question on the Oracle SQL and PL/SQL forum asked: “Why is a PL/SQL ‘for loop' [...]

In the second part of this series I described some of the technicalities of Index Usage Tracking and showed an example of what I was doing to test the feature. In this episode I'll describe some of the index access methods I've tested and report the results. I've listed [...]

In the first part of this series I introduced Index Usage Tracking and the view dba_index_usage – a feature that appeared in 12.2 as a replacement for index monitoring and the view dba_object_usage. In this note I'll give a quick sketch of the technicalities of the implementation and comments [...]

In 12.2 Oracle introduced Index Usage Tracking to replace the previous option for “alter index xxx monitoring usage”. A recent post on the Oracle database discussion forum prompted me to look for articles about this “new” feature and what people had to say about it. There didn't seem to [...]

Despite their continued presence in the Oracle data dictionary, LONG columns are not an option that anyone should choose; you can't do much with them and they introduce a number of strange space management problems. Nevertheless a recent thread on the Oracle database forum started with the following question: [...]