APPS-ORACLE.RU

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

Here's a silly little detail about execution plans on (interval) partitioned tables that I hadn't noticed until it showed up on this thread on a public Oracle forum: it's an execution plan that claims that Oracle will be visiting a partition that clearly won't be holding the data requested.

Here's [...]

Here's a little detail that I discovered recently when I wanted to do something a little exotic in 23c on the official Virtualbox VM. There's been a hidden parameter to disable oradebug since (at least) 12.2.0.1. The clue is in the name:

The valid values for this parameter are none, restricted, [...]

A recent (Mar 2024) question on the MOSC DB Admin forum (needs an account) asks why a query which runs every few minutes and executes 25 times in 2 hours according to an AWR report never shows up in v$session when you query for the SQL_ID.

This is a note that will probably be of no practical use to anyone – but someone presented me with the question so I thought I'd publish the answer I gave:

Do you know how object ID is assigned? It doesn't look as if a sequence is used

I'm fairly sure the [...]

This is another example of defects in the code to handle descending columns in indexes, with the added feature that the problem is addressed somewhere between 19.4 and 19.10 (it's present in 19.3, gone in 19.11) – which means that if you upgrade to a recent RU of from some [...]

I sketched the first draft of this note on 7th Feb 2010, then forgot about it until some time in July 2019 which is when I expanded enough of it to publish as a demonstration of how the session data unit ( sdu / default_sdu_size) parameters in SQL*Net affected [...]

When you specify that a column used in an index should be a “descending” column Oracle uses the internal, undocumented, sys_op_descend() function to derive a value to store for that column in the index leaf block.

For many years I've claimed that this function simply takes the one's-complement of a character [...]

This is a note that's been awaiting completion for nearly 10 years. It's about a feature (or, at least, a hint for the feature) that appeared in 10.2.0.5 to control some of the inter-process messaging that takes place in parallel execution.

It's a very simple idea that can make a significant [...]

A little detail to remember when trouble-shooting at the session level – some of the information summarised in the Instance Activity figures (v$sysstat) is not available in the Session Activity figures (v$sesstat / v$mystat). The difference goes right down to the x$ objects, and here are two versions of a [...]

This is just a short note (I hope) prompted by a conversation on the Oracle-L list server. A query from a 3rd party application was doing a very large full tablescan with hash join when it should have been doing a high precision index driven nested loop join, and the [...]

I've written a few notes about problems with “descending” indexes in the past (the word is in quotes because it's not the index that's defined as descending, it's a proper subset of the columns of the index). A silly example came up recently on twitter where the cost of [...]

Jump to summary.

A new optimizer feature that appears in 23c (probably not 21c) was the ability to push group by clauses into union all set operations. This will happen unhinted, but can be hinted with the highly memorable [no_]push_gby_into_union_all() hint that appeared in 23.1.0.0 according to v$sql_hint. and [...]

That's not the 23rd article I've written on sql_trace, it's just that there's a little “catch-up” detail about sql_trace that I recently discovered in 23.2 (though it might also be working in 21c or even in recent versions of 19c).

Although level 4 and 8 (binds and waits) of the SQL [...]

This is a note that echoes a feature (dating back at least as far as 10.2.0.4) that I've described in a previous post on Simpletalk. I'm raising it here for three reasons

This is a second follow-up to the video Connor McDonald produced a few days ago about the risks of depending on “current tricks” to control the order of predicate operation, then showing how some requirements for the pattern “check condition B only for rows which have already satisfied condition A” [...]

As the years roll by, Oracle Corp. introduces new procedures (and functions) to replace older procedures that were inefficient, or gave away too much privilege, or simply had used bad naming standards. In relatively recent versions Oracle Corp. has introduced a pragma in the declaration of such procedures/functions that [...]

The “common table expression” (CTE) also known as “with clause” or “factored subquery” has been the target of an optimizer upgrade in recent versions of Oracle that may cause a problem for some people – including, possibly, a few of my former clients and readers who may have adopted a [...]

A recent post on the Oracle SQL and PL/SQL forum posted the following query with a complaint that it produced the wrong results:

A recent post on one of the forums (needs a MOS account) asked if it was possible to change a batch of SQL Plan Profiles from “exact match” to “force match” without going through the hassle of dropping and recreating them. This took me back a couple of [...]

This is a follow-up to a comment on an earlier post about LOBs and sizing. The comment raised a question about finding the stored size of xmltype data (which is essentially a LOB enhanced to include “methods”) particularly when it's declared with compression.

This is one of those questions where [...]