no peek in PL/SQL

Connor McDonald wrote a blog about differences between bind peeking and SYS_CONTEXT in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values (feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.

In the following discussion he stated (more/less
I should not have this problem at all, as (more...)

OTN Appreciation Day : ONLINE

This post is inspired by Tim Hall.

Thinking about "what is my single favourite feature of Oracle", when all the products of Oracle (or at least all I know about) can be covered sounds stupid or impossible. There are far to many features which could be interesting, worth or made my life easier over my years in IT.
So I decided to pick something more generic.

The "feature" I like most is ONLINE

With (more...)

Oracle DIRECTORY access on OS layer

Oracle DIRECTORY objects are very nice to handle access for external tables or other procedures.
It just can be challenging to enable proper access to those files for other users than the database user (I call it oracle here).

If there are any concerns to allow any access to the databases host (except for DBAs and OS admins) it get's tricky fast.

One possibility is to use a NFS mount and make the directory available (more...)

ORA-08176 with DB-link, create table and isolation level SERIALIZABLE

Today I had some fun identifying how a ORA-08176 can happen.
It started with a ticket similar to "we get an ORA-08176 during a select, please fix the database."
After some questions it refined to "we do a CREATE TABLE x AS SELECT in one session, and a SELECT * FROM X@db_link throws the ORA-08176.

That's enough for me to do a test-case, but I needed to change the isolation level to produce the (more...)

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
              FROM   table2)

This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid (more...)

access to CHM raw data – without manipulating the -MGMTDB

In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository (GIMR) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be (more...)

Instance parameters derived from cpu_count –

About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's without any PSUs/patches. The machine is the same class as previous, so it took some time.

the (more...)

handling disks for ASM – when DB, Linux and Storage admins work together

A proper management of ASM Disks can be a complicated task.

On DOAG2015 I discussed with Martin Bach about the concept in my current company, where we implemented a setting which is consistent, robust and enables Storage, Linux and DB admins to work together easily.

As we started to think about ASM when 10.1 was out we tried to evaluate our possibility. asmlib was discarded quite early as it only increased complexity without additional (more...)


In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to
display information about database feature usage statistics.
That's all I can find in The (12.1) Docu.

Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:

Still for me it's interesting what's behind the view, and how it's content is populated.

So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
SELECT  (more...)

12c datapatch can fail with manual out of place patching

datapatch can fail if doing manual out of place patching, when the new ORACLE_HOME is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME, which is included in something else (like a PSU) in the new ORACLE_HOME.
In my specific situation the old O_H had PSU + some one-off + Patch:21355879.
This Patch is not needed anymore in my new O_H (more...)

12c datapatch – take care of parallel patching

datapatch is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.


Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress (more...)

SQL Plan Directives and result cache affects performance

In my current company we are preparing a migration of our billing application to a new version. During this migration there are the ordinary changes of infrastructure as well. Application servers from HPUX to Linux, database servers from RH5 to RH6, different storage subsystem, Oracle from 10.2 to 12c, different application partitioning, and so on ...
At least from the management perspective the expectation is clear: everything is shiny new and costed a lot of (more...)

Poor mans getTracefile

As you might know already, I'm a big fan of Oracle Traces. Consequently I'm a fan of Method-R tools to work with these files as well.
A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to (more...)

SQL Developer 4.1 with Method R Trace 3

These days SQL Developer 4.1 GA was released. If you use SQL Developer, I'd recommend an update.

Nearly at the same time Method R Trace extension for SQL Developer was released as well. Especially for developers that's a very important news - at least if they concern about performance. Method R Trace
A new Version of this extension is needed as SQL Developer changed it's extension framework at the upgrade from version 3 (more...)


this is just an online docu of ORADEBUG DOC in
The general comments from Tanel Poder apply to this version as well.

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - (more...)

enhanced SQL trace

As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits.
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)

Instrumentation still needed

Recently I received an email with an simple content:
"It" is slow again :-)
The email explained a package to be slow.

Ok, I started with my "not enough information" reply and an email conversation began.

We are still not ready to identify the problem, but as there is no (more...)

No Adaptive Cursor Sharing for collections

Recently I was hunting a performance problem at work.

A developer read about adaptive cursor sharing and therefore guessed, the optimizer would know about the number of rows when he passes a collection for a table(:bind) function. I can totally understand it, as there where no limitations in the statement (more...)

Resetting Your Oracle User Password with SQL Developer on OSX

This post started as a shameless copy of Jeff Smiths Resetting Your Oracle User Password with SQL Developer - but it ended in a trilogy about OSX, Oracle and SQLDeveloper.
In my first two posts I made myself comfortable with Oracle instant client on my MacBook. Now I want to use it for something more serious, like changing passwords.

Even with all the modifications from previous post SQLDeveloper still does not show the Reset Password... option. The reason: it just don't know about the library correctly.

First the PATH as shown in previous post is not set in an (more...)