APPS-ORACLE.RU

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

Here's a note that was prompted by a report about a delete that started up lots of PX processes but apparently did a serial delete even though parallel DML has been forced (alter session force parallel DML parallel 10). The description of “serial” was subsequently refined to explain that “only [...]

A fairly common question about parallel DML is: “Why isn't it happening?” A fairly common (correct) answer to this question is: “Because you haven't enabled it.” Unlike parallel query and parallel DDL which are enabled by default parallel DML could cause strange deadlocking side-effects so you have to enable it [...]

Here's an example of reading an execution plan that appeared on one of the Oracle forums recently. It's a simple example, made harder by the initial (costmetic) presentation of the problem. Essentially the owner has said:

The three laws of Oracle DBAs (with apologies to Isaac Asimov)

Here's a problem that appeared on the Oracle-L list server a little while ago. It features an Exadata system with a “very large” GTT (global temporary table) and a pair of queries that are repeated frequently, using a different literal value on each repetition of the pair.

The queries, with [...]

Here's a little quiz that came to light on a database running 11g. It's one of those anomalies that is likely to get noticed only in fairly extreme cases and it's about DDL rather than DML so it shouldn't be happening very often on anyone's system.

Read through the following script [...]

A historic complaint about the result cache was that it did not scale. Although this complaint was often the consequence of the mechanism was being used inappropriately, there was an underlying issue that imposed a limit on how scalable (in terms of concurrency) the cache could be: it was covered [...]

This note is just a little background for an impending article on the costs and effects of exchanging partitions, splitting partitions, merging partitions – or any other DDL on partitions except dropping them – in the light ofr “deferred global index maintenance”.

Summary Information: while a “typical” rowid is stored in [...]

This is a list of articles I've written that pick up some details (usually problems or bugs) when upgrading. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first but the catalogue is in two sections: Benefits and Bugs and [...]

A question appeared recently in a comment on a blog note I wrote about the new (in 12c) deferred global index maintenance that allowed the execution of a drop partition to return very quickly. The question was in two parts: