Top 60 Oracle Blogs

Recent comments


Oracle wait event ‘log file parallel write’ change

This post is about a change in how the time is measured for the event ‘log file parallel write’. This is important for the performance tuning of any change activity in an Oracle database, because with the default commit settings, a foreground session that commits changes waits in the wait event ‘log file sync’, which is a wait on logwriter activity, for which the wait event ‘log file parallel write’ always has been the indicator of the time spend on IO.

Log file sync
First things first: a foreground session normally waits on the wait event ‘log file sync’ when it commits waiting for its change vectors to be written to the online redologfile(s) by the logwriter. It is wrong to always assume a ‘log file sync’ will be present. If, somehow, the logwriter manages to increase the ON DISK SCN to or beyond the foreground session’s commit SCN, there will be no ‘log file sync’ wait event.

Oracle internal data dictionary oddity

This blogpost is about an inconsistency I found in the X$ tables X$KQFTA and X$KQFCO. This is very specific. If you don’t care about that, you can skip this post.

The Oracle database’s “dynamic performance views” are views that are prefixed with “GV$” and “V$”. The “G” with “GV$” stands for “global” and gets you the results from its “V$” equivalent for all instances. In most cases, but that’s not the purpose of this blog. In most cases, the “V$” version simply is the “GV$” view with inst_id (instance id, used in RAC to specify the instance id) set to the current instance. I’ll refer to both simply as “V$” for simplicity.

The “V$” views are mostly build on top of “X$” tables. The “X$” tables are Oracle internal tables, and not officially supported, therefore you should use the “V$” views.

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.

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.

What’s new with Oracle database versus

There are a couple of underscore parameters changed from spare to named ones.
It’s interesting to see that in sysstat, ‘spare statistic 2’ changed to ‘cell XT granule IO bytes saved by HDFS tbs extent map scan’. This obviously has to do with big data access via cell servers. What is weird is that this is the only version where this had happened.

Oracle wait event ‘TCP Socket (KGAS)’

I was asked some time ago what the Oracle database event ‘TCP socket (KGAS)’ means. This blogpost is a deep dive into what this event times in Oracle database

This event is not normally seen, only when TCP connections are initiated from the database using packages like UTL_TCP, UTL_SMTP and the one used in this article, UTL_HTTP.

A very basic explanation is this event times the time that a database foreground session spends on TCP connection management and communicating over TCP, excluding client and database link (sqlnet) networking. If you trace the system calls, you see that mostly that is working with a (network) socket. Part of the code in the oracle database that is managing that, sits in the kernel code layer kgas, kernel generic (of which I am quite sure, and then my guess:) asynchronous services, which explains the naming of the event.


At Trivadis Performance Days 2018 (awesome event by the way) I promised to deliver ODBV3 with support for ASM – and here it is! </p />

    	  	<div class=

ODBV3 – more comfortable usage

It has been crazy few months – organizing POUG2018 took a lot of energy but it was satisfying as hell! </p />

    	  	<div class=

Dumping SGA to read encrypted blocks

After my last article AMM vs security, Martin Berger wrote to me:

even without AMM you can do it:
write your own process which attaches to the same shm segments – and use its memory mapping (?)

My response was that it is also possible with ASMM but AMM makes it extremely easy. And this is because you can treat memory as regular binary files when operating on AMM.

Today I want to show you how dump blocks from SGA which is configured as ASMM to get into encrypted data which is also protected by Oracle Database Vault. To set up the environment I will use examples from a previous blog post.