APPS-ORACLE.RU

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:

At present this is just a place holder to remind me to finish commenting on (and correcting) a mistake I made when I wrote a note about the number of bytes of data you could get into an “enable storage in row” LOB before it had to be stored [...]

I've commented previously on the “new” cost-based Or-Expansion introduced in 12c to replace the “legacy” Concatenation transformation, and I've been re-running some of my concatenation scripts to see whether the most recent versions of the optimizer will use Or-expansion unhinted in places where I've previously had to use hints [...]

This example appeared quite recently on one of the public Oracle Forums, and the person who posted it had already worked out (the important bit of) what was going on before anyone asked for more information or supplied any suggestions for action (although there was one response pointing to [...]

Here are the introductory comments I made at a recent “Ask me Anything” session about indexing arranged by the All India Oracle User Group:

There are 4 fundamental thoughts that you need to bear in mind whenever you're thinking about what indexes your application needs:

Here's a little extract from one of my “snap instance activity stats” packages picking out the figures where the upper case of the statistic name is like ‘%PARALLEL%'. I was very careful that nothing except one of my SQL*Plus sessions had done anything in the few seconds between the start [...]

I may have given the answers to this little puzzle elsewhere on the blog already, but if so I can't find where, and it's worth a linttle note of its own.

I have a non-partitioned heap table that is a subset of all_objects, and I'm going to execute a series [...]

Here's a query that appeared on the MOS “SQL Performance” forum (needs a support account) with the request: “Can someone please assist me to optimize this query?”

It looks like a nice simple query (though looks can be deceiving) so I thought I'd use it as another example on [...]

This is nothing more than a basic update of a note that I wrote 8 years ago. The update was triggered by a brief comment made by Martin Widlake at the recent UKOUG annual conference “Breakthrough 2022” in Birmingham. In his presentation on “wide tables”, he mentioned row migration and [...]

There's a question on the MOSC forum (needs an account) at present that started with the performance of the datapump API over a database link but moved on to the topic of how to handle a scenario that I've described in the past involving a table where rows [...]

An email on the Oracle-L list server a few days ago described a performance problem that had appeared after an upgrade from 11.2.0.4 to 19c (19.15). A long running statement (insert as select, running parallel 16) that had run to completion in 11g using about 20GB of temporary space (with [...]

I've just discovered that the space management bitmaps for the tablespace I normally use in my 21c tests are broken. In a tablespace that's supposed to be completely empty a query of dba_free_space shows 4 gaps totalling several thousand blocks:

I wrote a batch of notes about the opt_estimate() hint a couple of years ago, including one where I explained the option for using the hint to specify the number of rows in a query block. I've just come across a particular special case for that strategy that others [...]

Oracle 12c introduced Partial indexing and sometimes it works fine for simple partitioned tables with literals, but it has a few problems.

For example, consider the following simple partitioned table:

Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:

For instance, consider the following simple partitioned table: