#Accenture #Enkitec Group at #ukoug_tech17

The circus moves on to Birmingham again! You know that this is one of the must-be-there events of the Oracle community, right?

The Accenture Enkitec Group is there with 6 speakers:

Jason Arneil with two talks: Beginners Guide to Oracle IaaS Cloud and An Introduction to Sharding

Martin Bach: The Answer to the Ultimate Question of SQL, Performance Tuning & Everything

Andy Colvin: Deploying Exadata Cloud Machine – What’s it Really Like?

Frits Hoogland: Who Shut my Database Down? Options for a Security Post Mortem Investigation in the Cloud

and on Super-Sunday he delivers all about linux memory usage by the oracle database

David Kurtz: Making Materialised Views Great Again

and myself: Real-Time Materialized Views in 12c and Fast-Start Failover Enhancements in 12c

See you soon 🙂

 

Leave a comment

Why INSERT causes TX – row lock contention or TM – contention in #Oracle

Inserts may cause enq: TX – row lock contention and enq: TM – contention under special circumstances:

Bitmap Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then proceeds without error message.

Unique Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then gets ORA-00001: unique constraint violated.

Parallel Inserts

First session inserts with parallel dml enabled. Second session inserts sequentially or in parallel doesn’t matter. Second session experiences wait event enq: TM – contention. This is also true for partitioned tables unless the partitions are explicitly referred to!

My test cases for the above scenarios have been done with 12.2.0.1 and  look as follows.

Bitmap Index scenario:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
Connected.
SQL> create table t1 (n number);

Table created.

SQL> create bitmap index b on t1(n);

Index created.

SQL> insert into t1 values(1);

1 row created.

Second session inserts the same and waits. You see the wait event in v$session then.
Unique Index

SQL> create table t2 (n number);

Table created.

SQL> alter table t2 add constraint u unique(n);

Table altered.

SQL> insert into t2 values (1);

1 row created.

Second session inserts the same and waits.
Parallel insert

SQL> create table t3 (n number);

Table created.

SQL> alter table t3 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t3 select 1 from dual;

1 row created.

Second session waits for any DML.
Partitioned table parallel insert without partition clause

SQL> create table t4 (n number) partition by list (n) (partition p1 values(1),partition p2 values(2));

Table created.

SQL> alter table t4 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t4 select 1 from dual;

1 row created.

Second session waits for any DML.
Non-blocking parallel insert with explicit partition clause
Same table as above, same parallel degree and parallel DML enabled, but:

SQL> insert into t4 partition (p1) select 1 from dual;

This blocks only partition p1. Second session can do any DML against p2 explicitly.

I have the feeling that this is not widely known – could actually be a good interview question: When do inserts lead to locking waits? As always: Don’t believe it, test it! 🙂

Leave a comment

#Accenture #Enkitec Group at #DOAG2017

Yes, it’s that time of the year again when DOAG (Deutsche Oracle Anwender Gruppe) is about to host its annual conference – probably the largest Oracle event in Europe!

DOAG conference 21 - 24 November 2017 in Nuremberg, Germany

The Accenture Enkitec Group is present with five speakers:

Martin Bach: Profiling & Debugging Problems on Modern Linux in the Cloud

Frits Hoogland: Provisioning the Oracle Database in the Cloud

Patrick Hurley: GoldenGate: a Live Introduction

David Kurtz: Practical Active Session History for Developers

And myself: Real-Time Materialized Views in 12c

The conference planner wrongly still lists me as employee of Oracle – an understandable mistake since I have been with Oracle for so long – but I actually changed to the Accenture Enkitec Group five months ago 🙂

We are really looking forward to this event, it has been always a great and rewarding experience! Hope to see YOU there 🙂

Leave a comment

%d bloggers like this: