APPS-ORACLE.RU

This is another of the blog notes that have been sitting around for several years – in this case since May 2014, based on a script I wrote a year earlier. It makes an important point about “inconsistency” of timing in the way that Oracle records statistics of work done. [...]

A recent thread on the Oracle Developer Community starts with the statement that a query is taking a very long time (with the question “how do I make it go faster?” implied rather than asked). It's 12.1.0.2 (not that that's particularly relevant to this blog note), and we have [...]

I've got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It's worth taking note of these tricks when you spot one [...]

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables. In that article I suggested (before testing) that you could [...]

It's strange how one thing leads to another when you're trying to check some silly little detail. This morning I wanted to find a note I'd written about the merge command and “stable sets”, and got to a draft about updatable join views that I'd started in 2016 in [...]

What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata' option ?

Here's what my system stats look like (12.2.0.1 test results) after doing so. (The code to generate the two different versions is at the end of the note).

Here's a problem (and I think it should be called a bug) that I first came across about 6 years ago, then forgot for a few years until it reappeared some time last year and then again a few days ago. The problem has been around for years [...]

Here's a question to provoke a little thought if you've got nothing more entertaining to do on a Sunday evening. What threats do you think of when you see a statement like the following in (say) an AWR report, or in a query against v$sql ?

An anecdote with a moral.

Many years ago – in the days of Oracle 7.2.3, when parallel query and partition views were in their infancy and when RAC was still OPS (Oracle Parallel Server), I discovered a bug that caused parallel queries against partition views to crash (Maxim – mixing two [...]

This is a little case study on “pre-emptive trouble-shooting”, based on a recent question on the ODC database forum asking about splitting a range-based partition into two at a value above the current highest value recorded in a max_value partition.

The general principle for splitting (range-based) partitions is that if [...]

Several years ago I wrote a note about reporting dba_free_space and dba_extents to produce a map of the space usage in a tablespace in anticipation of messing about with moving or rebuilding objects to try and reduce the size of the files in the tablespace. In the [...]

In the “Parse Puzzle” I posted a couple of days ago I showed a couple of extracts from an AWR report that showed contradictory results about the time the instance spent in parsing and hard parsing, and also showed an amazing factor of 4 difference between the DB Time [...]

Here are some details from an AWR report covering a few minutes in the lifetime of an instance of 18.3. It's a carefully constructed demonstration and all I've done is take an AWR snapshot, execute a single SQL statement, then take another snapshot, so the only thing captured by the [...]

This is a blog note that started life in September 2001 (which was before I started blogging, I think), got drafted as a blog in January 2014 because of a question on the Oracle-L list server, and has finally got to publication following a question on the ODC database [...]

If you've been wondering why I resurrected my drafts on the opt_estimate() hint, a few weeks ago I received an email containing an example of a query where a couple of opt_estimate() hints were simply not working. The critical features of the example was that the basic structure of the [...]

Over the last few days I've been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

Practical Oracle 8i p.29

"If you are going to depend on a technological feature of Oracle, you need to make sure that you have tried to break [...]

PL/SQL functions in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.

A little elaboration is in order here.

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It's a good question as it's fairly common knowledge that distributed queries can be very much slower [...]

In the previous article in this series on the opt_estimate() hint I mentioned the “query_block” option for the hint. If you can identify a specify query block that becomes an “outline_leaf” in an execution plan (perhaps because you've deliberately given an query block name to an inline subquery and [...]

This is just a quick note to throw out a couple of of the lesser-known options for the opt_estimate() hint – and they may be variants that are likely to be most useful since they address a problem where the optimizer can produce consistently bad cardinality estimates. The first is [...]

Here's a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn't quite finished a bug fix or enhancement. Here's the problem – which starts from the “scott.emp” table (which I'm not going to create in the code below):