Search

Top 60 Oracle Blogs

Recent comments

Oracle

IOT Hash

It’s another of my double-entendre titles. The optimizer can turn a hash join involving an index-organized table into a real performance disaster (though you may have to help it along the way by using a silly definition for your primary key columns). This post was inspired by a question posted on the Oracle Developer Community forum recently so the table and column names I’ve used in my model reflect (almost, with a few corrections) the names used in the post.

We start with a simple requirement expressed through the following SQL:

The Complexity Defense and Other DBA Crimes

No matter how far my career and role has shifted, I will always view myself as a database administrator.  I know this because when I fill out any form asking me what role I fulfill in IT, I still choose “DBA” from the list.  No matter what claims the media and leading sources state, DBAs are an important role, even as technology shifts and traditional database tasks move to the cloud.  Deeper relational database skills are still quite relevant when working in complex technical scenarios.

The Experts

Companies work to ensure they hire the right technical specialists.  They don’t understand database and other deep technology, so they hire someone who does.  The challenge arises when the technologist may fear responsibility for a problem or an incident, so decides to use the technical complexity to disqualify their technical area, such as a database, suspect.

What’s new with Oracle database 18.8 versus 18.7

The amount of changes between version 18.7 and 18.8 is truly minimal. There’s one spare parameter renamed to an underscore parameter, which seems to be a back port of functionality created version 19:

Strange Estimates.

A question came up on the Oracle-L list server a couple of days ago expressing some surprise at the following execution plan:

What’s new with Oracle database 19.5 versus 19.4

It seems that the most eye-catching difference between Oracle database versions 19.5 and 19.4 is three underscore parameters spare parameters being changed to specifically named underscore parameters, two of them have a bug number in them which is not publicly visible.

In v$sysstat/v$sesstat, a group of statistics are renamed from ‘OS %’ to ‘Server %’. All these statistics are about networking. One changed parameter directly points to networking (tcpinfo). These statistics are added in version 19.

One DBA view was added: DBA_DV_COMMON_OPERATION_STATUS. This view is specific and owned by to DVSYS.

Video : Oracle REST Data Services (ORDS) : First Party (Basic) Authentication on Tomcat

In today’s video we demonstrate first party cookie-based authentication, or basic authentication, for Oracle REST Data Services when run on Tomcat.

For those of you that prefer to read, this is one of the authentication and authorization methods discussed in this article.

You can get more information about ORDS here.

match_recognize()

A couple of days ago I posted a note with some code (from Stew Ashton) that derived the clustering_factor you would get for an index when you had set a value for the table_cached_blocks preference but had not yet created the index. In the note I said that I had produced a simple and elegant (though massively CPU-intensive) query using match_recognize() that seemed to produce the right answer, only to be informed by Stew that my interpretation of how Oracle calculated the clustering_factor was wrong and that the query was irrelevant.  (The fact that it happened to produce the right answer in all my tests was an accidental side effect of the way I had been generating test data.

Oracle memory troubleshooting using analysis on heapdumps, part 2

In the article oracle memory troubleshooting using analysis on heap dumps I introduced heap_analyze.awk.

The reason the tool exists is because I am using it myself. Therefore, I ran into additional things that I wanted the tool to do. I added some stuff, which is that significant, that I decided to make another blogpost to introduce the new features.

1. Percentages
In order to get an idea of the relative size of the summarised topic, I added a percentage. For example:

Oracle memory troubleshooting using analysis on heapdumps

This blogpost is about analysing Oracle heap dumps. It is an extension to earlier work, Tanel Poder’s heap dump analyzer. So hat tip to Tanel, he’s done the hard work, I merely increased the presentation options. The heap analyser script that I wrote analyses Oracle heapdumps from the trace file that the dump was written to by the Oracle database. Because the heap dump representation is the same between PGA and SGA memory, it can work on both. The reason for this is that memory management is done by the same memory manager, and is commonly called ‘kgh’ (kernel generic heap) managed memory.

Please mind that for PGA analysis, not all memory is managed by the kgh memory manager. For example memory used for networking (sqlnet) is allocated totally outside of the kgh memory manager.

Clustering_Factor

A few days ago I published a little note of a script I wrote some time ago to estimate the clustering_factor of an index before it had been built. At the time I pointed out that one of its limitations was that it would not handle cases where you were planning to set the table_cached_blocks preference, but a couple of days later I decided that I’d write another version of the code that would cater for the new feature – and that’s how I made an embarrassing discovery.