Where does the log writer spend its time on?

Uncategorized
| Jan 2, 2020

The Oracle database log writer is the process that fundamentally influences database change performance. Under normal circumstances the log writer must persist the changes made to the blocks before the actual change is committed. Therefore, it’s vitally important to understand what the log writer is exactly doing. This is widely known by the Oracle database community.

The traditional method for looking at log writer performance is looking at the wait event ‘log file parallel write’ (more...)

Oracle wait event ‘log file parallel write’ change

Uncategorized
| Dec 31, 2019

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 (more...)

dbnodeupdate.sh appears to be stuck

Uncategorized
| Dec 20, 2019

I was patching an Exadata db node from 18.1.5.0.0.180506 to 19.3.2.0.0.191119. It had been more than an hour and dbnodeupdate.sh appeared to be stuck. Trying to ssh to the node was giving “connection refused” and the console had this output (some output removed for brevity):

[  458.006444] upgrade[8876]: [642/676] (72%) installing exadata-sun-computenode-19.3.2.0.0.191119-1...
<>
[  459.991449] upgrade[8876]: Created  (more...)

Exadata storage indexes

Uncategorized
| Nov 19, 2019

We had a question on AskTOM inquiring about how to handle the issue of only 8 storage indexes being possible on an Exadata engineered system. If you are unfamiliar with what a storage index is, they are part of the suite of features often referred to as the “secret sauce” that can improve query performance on Exadata systems by holding more metadata about the data that is stored on disk. You can get an introduction (more...)

Oracle database and grid home patches to install

Uncategorized
| Nov 7, 2019

This blogpost is about Oracle database and grid infrastructure software homes, which patches should be applied to which homes, and what it then looks like. This is fully documented by MyOracleSupport notes, but you will see that with version 18 and up this is unclear.

I keep a script-set that automatically installs and patches the Oracle database software and creates a database. This script-set is called vagrant-builder, and it can install any version with (more...)

Oracle internal data dictionary oddity

Uncategorized
| Oct 31, 2019

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. (more...)

What’s new with Oracle database 18.8 versus 18.7

Uncategorized
| Oct 30, 2019

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:

DATABASE_VERSION                                                                                     NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------
18.8                                                                                                 _ldap_password_oneway_auth
19.2                                                                                                 _ldap_password_oneway_auth
19.3                                                                                                 _ldap_password_oneway_auth
19.4                                                                                                 _ldap_password_oneway_auth
19.5                                                                                                 _ldap_password_oneway_auth

And it seems the DBA view DBA_REGISTRY_BACKPORTS is corrupted with 18.8, there only is a synonym with that name, which (more...)

What’s new with Oracle database 19.5 versus 19.4

Uncategorized
| Oct 29, 2019

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).

One DBA view was (more...)

Autonomous Health Framework (AHF) available

Uncategorized
| Oct 28, 2019

Bildschirmfoto 2019-10-28 um 19.36.24

Oracle released the new Autonomous Health Framework since one week. It is very interesting to go through the list of new and changed features.

Yes after a very long time we had one Tool which bring everything together:

  • one single Interface
  • all diagnostics tools in one bundle makes everything easier
  • Automatic proactive compliance checks helps to fix problems
  • Diagnostic when the failure occur ensures you get everything for the resolution

 

To get familiar go (more...)

Oracle memory troubleshooting using analysis on heapdumps, part 2

Uncategorized
| Oct 25, 2019

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 (more...)

Oracle memory troubleshooting using analysis on heapdumps

Uncategorized
| Oct 24, 2019

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 (more...)

OGB Appreciation Day : Exadata X8M

Uncategorized
| Oct 14, 2019

What is OGB Appreciation Day?

The Oracle Groundbreakers (OGB) Appreciation Day formally known as OTN Appreciation Day and ODC Appreciation Day, is a great initiative by Tim Hall aka Oracle-Base.com.  Where we take the opportunity to say thanks to the Oracle Community which includes but not limited to ACEs, Java Champions, Ambassadors and all those who have the Groundbreakers spirit #ThanksOGB 🙂

I wonder what will be the name will be next year 😉

(more...)

impdp with parameter „cluster and parallel“ for a RAC Cluster

Uncategorized
| Jul 12, 2019

For using the „parallel“ parameter during an import (impdp) on a Oracle RAC Cluster you need to prepare your environment.

The „parallel“ parameter works correctly when you do the following:

– mount point were the export dump resides must be available on ALL cluster members

– create a Service on the database for the impdp job

srvctl add service -s impdp_service -d xdb1 -pdb xpdb1 -preferred xdb11,xdb12 -available xdb13

srvctl start (more...)

Online Redo Log Switch Frequency Map

Uncategorized
| May 31, 2019

A query I find myself often running is the online redo log switch frequency map query, which queries the v$log_history/gv$log_history (for cluster databases) view and show the historical log switch frequency.

Why you might ask? Well it’s important to see how frequent log switches are occurring as Oracle’s rule of thumb is to not switch more then 3 logs per hour (20 minutes of redo) at peak DML activity to prevent excessive checkpoints.  The (more...)

Exadata and WebLogic CVE-2019-2725

Uncategorized
| May 14, 2019

I can still remember the surprise when Frits Hoogland pointed out that Oracle had replaced the old OC4J instance on Exadata storage servers with a WebLogic service. I wasn’t quite sure what to make of this, but it’s become apparent as things move cloudward that it would enable an entire set of REST operations for Exadata on both storage and compute tiers. This becomes interesting due to the recent 0-day vulnerability for WebLogic, CVE-2019-2725. According (more...)

Bloom Filter Efficiency And Cardinality Estimates

Uncategorized
| Apr 23, 2019
I've recently came across an interesting observation I've not seen documented yet, so I'm publishing a simple example here to demonstrate the issue.

In principle it looks like that the efficiency of Bloom Filter operations are dependent on the cardinality estimates. This means that in particular cardinality under-estimates of the optimizer can make a dramatic difference how efficient a corresponding Bloom Filter operation based on such a cardinality estimate will work at runtime. Since Bloom (more...)

12.2 Grid Patching lesson learned

Uncategorized
| Mar 12, 2019

What happend?

During the last month I updated manually the TFA Software.

I  do this update while the TFA release installed via the Patchset is an older Version. This happens while Oracle Support adds the TFA release which is available while they create the Patchset.

Last weekend I start Patching GI Software 12.2 to RU Oct 2018 on a 4 Node Exadata Cluster

As best practice I do the installation manually and not via (more...)

Checking Flash Cache Mode in Exadata Cloud

Uncategorized
| Feb 28, 2019

While looking at an exachk report for an Exadata rack running in Oracle Cloud Infrastructure, I saw an interesting finding:

Since this is an Oracle cloud environment, we don’t have any access to view the storage servers – they are managed and maintained by Oracle.  For several reasons, I believed this to be a false alert, but wanted to see if I could determine what the flashcachemode setting is.

On some flavors of Exadata (more...)

Changing IP Addresses on InfiniBand Network with 12.2 GI on Exadata

Uncategorized
| Feb 26, 2019

I was tasked a while back with modifying the IP addresses assigned to the InfiniBand network on an X6-2 rack.  We were configuring a ZFS appliance to share between 2 Exadata clusters, and we wanted to keep them on separate network ranges.  In this case, we were moving the interfaces from 192.168.10.x/22 (the Exadata default network range) to 192.168.14.x/22. The process is documented very well by Oracle (more...)

Understanding grid disks in Exadata

Uncategorized
| Feb 18, 2019

Use of Exadata storage cells seems to be a very poorly understood concept. A lot of people have confusions about how exactly ASM makes uses of disks from storage cells. Many folks assume there is some sort of RAID configured in the storage layer whereas there is nothing like that. I will try to explain some of the concepts in this post.

Let’s take an example of an Exadata quarter rack that has 2 db (more...)