Poor Man’s In-Memory Caching

Introduction

It is common knowledge, or at least it should be, that Oracle has
released version 12.1.0.2 in June 2014. which has a feature called
“Oracle In-Memory”. The name is a tad confusing, since many
features were called “in-memory” before, from “Very Large
Memory” to Times 10 product.

This “In Memory” option is a licensed product featuring
columnar storage, in addition to the normal buffer storage, almost
identical to IBM’s “Blu acceleration” (more...)

Christian Antognini, Troubleshooting Oracle Performance, 2nd Edition (Review)

This is an excellent book with an encyclopedia-like approach to Oracle performance tuning, concentrated around the optimizer. It doesn’t cover architectural changes in the database in the newer releases, like the new redo mechanism with the private strands or the new implementation of the latches and pins. In my opinion, Oracle performance is more than just the optimizer.
Also, the book is very long and without the sections for new features in 12c, which makes (more...)

12c and export/import from a lower version

There is a serious problem with export/import utilities between versions.

The first command, executed on Oracle 11.2.0.4, Linux x86_64 was:


[oracle@oradb tmp]$ expdp system directory=tmp dumpfile=oe.dmp schemas=oe

logfile=oe.log

Directory tmp was created as /tmp

Result was the following:

Export: Release 11.2.0.4.0 – Production on Mon Jul 28 22:57:03 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights

reserved.

Password:

Connected to: Oracle Database 11g (more...)

Gimmick with offset and fetch N rows first in 12c

I was looking into some new features of 12c and I didn’t need to dig deep, in order to figure out how does the “fetch first” feature work:

QL> set autotrace on
SQL> select ename from emp offset 5 rows fetch next 5 rows only;

ENAME
———-
BLAKE
CLARK
SCOTT
KING
TURNER

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 3611411408

——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (more...)

Still the same cluster limitations

I am slightly disappointed to report that the new extended data types cannot be used in the clustered tables. CLOB columns have never been allowed, and are still not allowed in 12c, but I expected the new data types to pass seamlessly. That is not the case:

SQL> create cluster testclu(intkey number(30,0));

Cluster created.

Elapsed: 00:00:00.03
SQL> create index testclu_ind on cluster testclu;

Index created.

Elapsed: 00:00:00.03

Now, let’s create a normal table (more...)

SYSBACKUP travails, part II

The SYSBACKUP connectivity problems described in the previous post are caused by unpublished bug 15828768.

There is a way to connect, using SYSBACKUP privilege:

 

RMAN> connect target ‘system@local as sysbackup’

target database Password:
connected to target database: ORA12C (DBID=214280212, not open)

Please, note the quotes around the connection string. Without the quotes, this will not work. It works with both single and double quotes:

RMAN> connect target “system@local as sysbackup”

target database Password:
connected (more...)

Quirks with the SYSBACKUP privilege in 12c

Oracle 12c has a great new privilege, tailor made for the paranoid oracle DBA guys like me. However, there are still some quirks: SYSBACKUP privilege doesn’t work over Oracle*Net. If the authentication method is SID based local authentication, all is well:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
(more...)

Missing Information

Since the version 11G it is possible to exclude committed blocks from specific tablespace from being written to flashback recovery area. FBDA process writes committed blocks to flashback area and is quite IO intensive. There are, among other things, flashback checkpoints as well. They occur when when flashback logs fill (more...)

Multi-threaded Oracle 12c architecture on Linux

Introduction

Oracle has long had so called multi-threaded architecture on Windows. On the Unix platforms, Oracle was using multi-process architecture until version 12c. The difference is that Oracle server processes in the threaded architecture become threads of a single process. The reason for such an architecture is lower overhead caused (more...)

Pidstat

Recently, I’ve been faced with a strange problem: around 10AM every morning, my database was showing signs of IO contention. Wait times for db file sequential and multipblock reads were increasing for no apparent reason and there were no signs of increased activity reported by the users. Fortunately, my system (more...)