Top 60 Oracle Blogs

Recent comments


Split Partition

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 the split point is above the current high value Oracle will recognise that it can simply rename the existing partition and create a new, empty partition, leaving all the indexes (including the global and globally partitioned indexes) in a valid state. There are, however, three little wrinkles to this particular request:

Free Space

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 related page where I published the script I pointed out that a query against dba_extents would be expensive because it makes use of structure x$ktfbue which generates the information dynamically by reading segment header blocks.

Parse Solution

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 and the “SQL ordered by Elapsed Time”. My example was modelling a real world anomaly I had come across, but was engineered to exaggerate the effect to make it easy to see what was going on.

Parse Puzzle

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 report is the work done in that brief time interval. The purpose of the exercise is to demonstrate how some Oracle features can make a complete nonsense of the AWR. (I have, as I often do, produced a model that reproduces an affect that can appear in production but exaggerates the effect to make it more clearly visible.)

First the Time Model statistics:

Transaction management in PostgreSQL and what is different from Oracle

TL;DR: Everything is different about transaction behavior. This may also change your ideas about “database independent” applications.

I like to explain some PostgreSQL concepts from an oracle DBA point of view. There are many things that are different in the two RDBMS and it is important to understand them.

Auto commit

Here is a short example where I create a table, insert one row and rollback:

Is a datafile OMF or not?

TL;DR: there’s no flag (only the name tells it), but a function

You want to know which files are Oracle Managed Files or not? Maybe because you like OMF (like I do as I show no interest for file names when I have tablespaces) and you have non-OMF files that you want to move to OMF ones.

There’s no flag in V$DATABASE or DBA_DATA_FILES. Only the name of the file (starting with ‘o1_mf’, like Oracle Managed Files, and ending with ‘.dbf’, like Database File) makes it OMF or not. But I don’t like to rely on name convention so I searched how Oracle is doing it. There’s an isFileNameOMF procedure in the RMAN packages.


Here is a procedure that displays which files are ASM, and which ones are regular user-managed ones:

LOB reads

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 forum. (But the comments in the blog don’t look as if they are relevant to the question.)

The question on Oracle-l was:  Why is the same CLOB datablock read multiple times?

The answer is basically: client programs tend to set a “sensible” fetch buffer size for LOBs and if the buffer size is less than the size of the LOB the block(s) the LOB is in have to be visited multiple times and for nocache LOBs that means the block(s) will be read multiple times.

This can be seen quite easily in SQL*Plus which has two helpful parameters (one dating back to at least v5):

Oracle RMAN Backup Optimization ON and backup archivelog command.


I always mention “not backed up n times” in my “backup archive log” commands because I think it makes sense to precise exactly how many copies of archived log we want to have available to restore from each backup device

I often configure “backup optimization on” when thinking about the tablespaces I put read-only so that they are not backed up each time(given that there is no suppression of old backups that are unknown to RMAN).

But I’ve never realized that BACKUP OPTIMIZATION and NOT BACKED UP actually both work on datafiles, archivelogs, and backupsets. And then the following is redundant:

Oracle & Postgres JDBC Fetch Size

TL;DR — By default PostgreSQL ResultSet is not a server cursor but a client buffer. The whole result is fetched before the first next() call.

It is usually a good idea to start with default settings rather than trying to ‘tune’ any possible parameter. Defaults are often suited to the most common cases. However, there’s a big exception to it when using JDBC drivers: the default fetch size is probably not the right setting. Here is an example with the same Java code to show the fetching behavior in Oracle and PostgreSQL.

Java example

Here is my Java code which is exactly the same for Oracle and PostgresSQL


There are probably better ways, so please let me know (@FranckPachot). This is what I use when I want to get the SQL_ID and the PLAN_HASH_VALUE when looking at the SQL Plan Baselines.

The DBA_SQL_PLAN_BASELINES view does not provide them, probably because SQL Plan Management (SPM) is going from a statement and it’s execution plan to the SQL Plan Baselines, but doesn’t need to navigate in the other way. However, we need it when troubleshooting query performance.