APPS-ORACLE.RU

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:

All Oracle specialists know that any predicate X=NULL can't be true and we should use “X is NULL” in such cases. Oracle optimizer knows about that, so if we create a table like this:

All Oracle specialists know that a predicate X=NULL can never be true and we should use “X is NULL” in such cases. The Oracle optimizer knows about that, so if we create a table like this:

A tweet from Franck Pachot [July 2021] about fully qualified names in Postgres prompted me to highlight a note I wrote a few years ago about using the label mechanism in Oracle's PL/SQL to avoid collisions between variable names and table names. This led to a brief twitter [...]

There's a question on the Oracle developer forum at the moment asking how a tablescan could be reported as taking 94,000 seconds so far when a count(*) shows that it holds only a couple of hundred thousand rows (and it's not storing megabytes of LOB per row if that's [...]

I don't really do tips because I often see simple tip that have a specific purpose being mis-used abused; but it's a special day in the Oracle community, so here's a quick tip in tribute to a great sharer.

I've been doing some work with datapump recently, and needed to get [...]

A recent question on the Oracle Developer Community forum asked for help with a statement that was taking a long time to run. The thread included the results from a trace file that had been passed through tkprof so we have the query and the actual execution plan with [...]

In the past I've sometimes had to dump the contents of the redo log to a trace file when I needed to find out what work Oracle was doing behing the scenes. To minimise the volume dumped by the “alter system dump logfile” command and make it easier to find [...]

“Why did my query do parallel?”

It's a question that crops up from time to time, usually followed by a list of reasons why it shouldn't have gone parallel – no hints in the query, table is not declared parallel, parallel_degree_policy is set to manual etc.

When the question appeared recently [...]

If you want to do something about “wasted” space in an index what are the differences that you need to consider between the following three options (for the purposes of the article I'm ignoring “rebuild” and “rebuild online”):

Looking [...]

First if you want don't know what an Exadata Quarantine is read this.

Someone asked whether you can create your own Exadata Cell quarantine and, if you can, why you might ever want to do it?

The first step when you don't know how to do something is [...]

I posted a while back on how to use Tracing Hybrid Columnar Compression in an offload server so this is a quick follow up.

Exadata is designed to present a well-balanced system between disk scan rates, cell CPU processing rates, network bandwidth, and RDBMS CPU such that on average across a lot of workloads and a lot of applications there should not be no one obvious bottleneck. Good performance engineering means avoiding a design [...]

I had noted in my first post that using the highest level of tracing caused timeout issues with the offload server heartbeat monitor. Heartbeat issues can also occur with expensive (and badly formed) regexp expressions. By default the heartbeat monitor is set to 6 seconds which is the maximum permitted [...]