BAAS: Battle Against Archiver Stuck – ALTERNATE Archive Location

If you are managing volatile systems, you might have encountered Archiver-Stuck in the past.

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available destinations

If this is the case, you might be interested in a rarely used feature called “ALTERNATE Archive Locations”. This is a second independent archive log destination, that is only used in case the primary destination is failing (full). In that case, the dest_2 is automatically (more...)

Playing with Oracle Cloud – Creating a Hybrid DR Deployment

On this article, we will create a Hybrid Disaster Recovery (DR by using Data Guard) Oracle Database Cloud Service database deployment with a primary database on-premises and a standby database in the cloud. This can be used to implement an improve the high

Playing with Oracle Database Cloud – Migrating to Cloud using an Oracle Cloud Backup

The idea for this scenario is to learn how to migrate an Oracle Database from on-premises to Oracle Cloud DBaaS by using a previously done backup using Oracle Cloud Backup Services. First, we will create an Oracle Database Cloud Services database

Playing with Oracle Database Cloud Backup Service

This article will show you how easy is to make use of Oracle Cloud to securely store all our on-premises Oracle Database Backups. Note that backups stored in Oracle Cloud can also be used to create new Oracle Database Instances

Playing with CrashSimulator

Hi everyone,   Today I will show you how CrashSimulator (Shell Script – current version 1.04) works in detail. When unzipping the zip file you will find the following files within it: CrashSimulator.sh.x – For high-resolution ssh emulator screens, it

No Asynchronous I/O When Using Shared Server (Also Known As MTS)

I've recently had a case at a client where it was questioned why a particular application was seemingly not making full use of the available I/O capabilities - in comparison to other databases / applications using similar storage.

Basically it ended up in a kind of finger pointing between the application vendor and the IT DBA / storage admins, one side saying that the infrastructure used offers insufficient I/O capabilities (since the most important application (more...)

Oracle Database Physical I/O IOPS And Throughput Benchmark

General Information

I've used a similar set of scripts quite a few times now to provide feedback to customers that wanted to get a more realistic picture of the I/O capabilities from a database perspective, rather than relying on artificial benchmarks performed outside - or even inside, think of I/O calibration - the database. Although there are already several Oracle benchmark toolkits available, like Swingbench and in particular SLOB, that run inside the database I (more...)

ORA-4031 Troubleshooting – Issue with durations in 11gR2

I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.  Being curious, I digged into the trace files again to confirm and understand the issue here. This blog post describes the method to analyze the issue and (more...)

Enabling Flashback – Be Cautions

Enabling flashback on 11.2.0.4 database can take minutes and cause multiple database wait events degrading database performance.

On a quite busy system, during the low peak hours, I tried to enable flashback on 11.2.0.4.6 version database and multiple sessions waited on

1. buffer exterminate
2. enq: SQ – contention
3. latch: cbc contention
4. SGA: allocation forcing component growth

PRAKANAN_DBA@:1> show parameter sga_target
 
NAME                           TYPE        VALUE
------------------------------ -----------  (more...)

Queue-based Concurrent Stats Prototype Implementation

This is just a prototype of a queue-based concurrent statistics implementation - using the same basic implementation I've used a a couple of years ago to create indexes concurrently.There are reasons why such an implementation might be useful - in 11.2.0.x the built-in Concurrent Stats feature might turn out to be not really that efficient by creating lots of jobs that potentially attempt to gather statistics for different sub-objects of the (more...)

"Cost Based Optimizer: Grundlagen – mit Update für Oracle 12c" Artikel (German)

Seit gestern steht auf der "Informatik Aktuell"-Seite mein Artikel "Cost Based Optimizer: Grundlagen – mit Update für Oracle 12c" zur Verfügung.

Er stimmt auch inhaltlich auf meinen Vortrag bei den IT-Tagen 2016 am 13. Dezember ein.

Sollte Sie das Thema interessieren, lade ich Sie hiermit also herzlich zu meinem Vortrag ein, bei dem ich das Thema auch mit Live-Demonstrationen vertiefen werde.

Outer Join with OR and Lateral View Decorrelation

Use of ANSI SQL is a personal thing.

Historically I have not been a fan apart from where it makes things easier/possible.

This reticence was mainly due to optimizer bugs and limitations in the earlier days.

Recently I have been using it much more because I find that the developers I interact with prefer it / understand it better.

You might/should be aware that Oracle will rewrite ANSI SQL to an Oracle syntax representation, this (more...)

INSERT ALL caveat

Why you might want to think twice about using INSERT ALL.

One of those things I knew and then forgot.

So, let’s say you’ve got three tables or a partitioned table or something like that.

Let’s use regional tables for simplicity.

drop table t1_r1;
drop table t1_r2;
drop table t1_r3;

create table t1_r1
(col1 varchar2(2) not null
,col2 number not null
,check( col1 in ('R1')));

create table t1_r2
(col1 varchar2(2) not null
,col2 number not  (more...)

Oracle ASMLib

This blog post is more of a note for myself on configuring ASMLib.

ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices.

ASMLib consists of the following components:

An open source (GPL) kernel module package: kmod-oracleasm
An open source (GPL) utilities package: oracleasm-support
A closed source (proprietary) library package: oracleasmlib

On my Oracle VMBox, I performed the below steps –

1. Check the installed packages related to (more...)

Side-effect of ALTER SESSION SET CURRENT_SCHEMA

For most of our database set-ups we use a different TEMP space for application users than for end-user/support/developer/reporting usage.
The intention is to minimise the potential impact of a rogue ad-hoc query on the main applicaiton.

However, turns out this is ineffective IF you use:

ALTER SESSION SET CURRENT_SCHEMA = main_app_schema;

This is documented but I wasn’t previously aware.
No surprise that this learning opportunity was presented as a direct result of a rogue query (more...)

Did it really fix it 2: Plan flip, unprofileable, baseline OFE round 2 and “stability”

Snappy title, huh?

Aka: Why a sql plan baseline may be no guarantee of stability.

The other day, a problematic plan flip…

Tangent 1:
Cue much discussion about plan flips etc.
My thoughts on stability are that the priority for most application owners is stability and predictability but that does not tally with the defaul CBO behaviour and potentially you have to turn off a helluva lot to even get close.

Tangent 2:
I have (more...)

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on 11.2.0.4

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

What AWR isn’t telling you

It is well-known that AWR, and Statspack before, take snapshots of V$ views (or rather the underlying objects) to produce the data in AWR.

It is also well-known that, when considering sql and its statistics in the shared pool, if something big hitting happens but the big-hitter is no longer in the shared pool by the time of the snapshot, then it can’t be recorded in your AWR picture of activity.

But like many things (more...)

Quick Cross-Period AWR Time Model Comparison

Here’s a query which I find useful in order to have a very quick comparison across AWR snapshots of the high level time model statistics.
The numbers should match those in the associated section in the AWR report.

If you feel compulsed, obsessively, with tuning then you may see some blips here and there which then encourage you to dive into the AWR detail for that snapshot.

Or quite often I get in in the (more...)