A New Version of an Old Trick

A long, long time ago, people started using the following SQL idiom:

with t as (select /*+ materialize */ …..)

select …. from t

If my memory serves me right, Jonathan Lewis was the first person whom I saw using this idiom. The use of that idiom was to speed create a global temporary table and reuse the query results, without having to re-execute query again. Here is an example:

SQL> set autotrace on


After startup trigger on RAC

Recently, a colleague of mine had some problems with pinning application procedures into the shared pool. His database was upgraded to RAC and he wanted to pin the application procedures and packages into shared pool upon the database startup.
The non-RAC version of the database was using a shell script, something like $ORACLE_HOME/local/scripts/pin_procs.sh in the script that was starting DB when the system was booted
However, RAC starts the database automatically and no user (more...)

ASM 12c, OCR and voting devices II

I finished the installation of 12c cluster and noticed that I haven’t been asked for voting and registry devices as was previously the case. I had to create a single disk group. John Franklin, from LinkedIn RAC SIG suggested running ocrcheck command, so I did:

[grid@rac1 ~]$ ocrcheck -details
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1612
Available space (kbytes) : (more...)

ASM 12c – no voting/quorum device and OCR?

I installed my first 12c ASM (database is still and I made the 11G disk layout: 2GB for voting/quorum, 2 GB for OCR. However, the installation procedure did not ask me for the voting and OCR devices. ASM came right up without them. So, the devices are not needed for 12c? What about upgrading from 11g, which did need the devices? The “crsctl” utility confirms that voting and OCR devices are (more...)

Oracle Database Transactions and Locking Revealed, by Tom Kyte and Darl Kuhn

I recently came across the book called “Oracle Database Transactions and Locking Revealed” by Tom Kyte and Darl Kuhn. Usually, any new book by Tom Kyte is a big deal and acquires a huge following very quickly. Strangely enough there was only a single review on the Amazon, before I added my own.

For the reasons unknown to me, this book is literally ignored. I haven’t seen posts on the oracle-l about it, on the (more...)

PostgreSQL Hints and DBMS_STATS

For those who don’t know,‭ ‬it is now possible to use hints on PgSQL.‭ ‬Here
is how things work:

-‭ ‬Download and install the extension from the home page:

‭  ‬http://sourceforge.jp/projects/pghintplan/

If you are using Red Hat derivative,‭ ‬like me,‭ ‬it’s a simple RPM package.‭ ‬Check the content of the package,‭ ‬like this:

[‬mgogala@pg91‭ ~]‬$‭ ‬rpm‭ ‬-qa|grep pg_hint

[‬mgogala@pg91‭ ~]‬$
‭[‬mgogala@pg91‭ ~]‬$‭ ‬rpm‭ ‬-ql pg_hint_plan93-1. (more...)

Opatch Integration

Oracle 12c came up with one nice new feature: Opatch, the tool known to all database administrators is now integrated into the database in the form of DBMS_QOPATCH package. If the database contains pluggable databases, the package must be executed from the root (CDB$ROOT) level, as user sys.
Also, a minor inconvenience is that there are no tabular results, results are returned as XML. It is a bit cumbersome to work with and I do (more...)

Poor Man’s In-Memory Caching


It is common knowledge, or at least it should be, that Oracle has
released version 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, Linux x86_64 was:

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


Directory tmp was created as /tmp

Result was the following:

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

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



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