Chris Antognini's picture

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for, or, but not for I successfully tested it on

The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

Chris Antognini's picture

Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT

The database engine determines the maximum disk I/O size used during multiblock reads (for example, full table scans or index fast full scans) by multiplying the values of the db_block_size and db_file_multiblock_read_count initialization parameters. The db_file_multiblock_read_count initialization parameter can be set explicitly, or, as of version 10.2, it’s also possible to instruct the database engine to automatically configure it. For the latter, simply don’t set it.

About the value which is automatically determined by the database engine the Oracle Database 12c Reference Guide gives us the following information:

mwidlake's picture


I think I’ve found an (admittedly obscure) bug with DBMS_RANDOM, group functions, PL/SQL and/or SQL.

Have a look and see if you also think this is odd – or have I missed the totally obvious?

(This is all on

{Update – my conclusion is, and thanks to Joel and Sayan for their comments, that this is not a “bug”. Oracle do not promise us how PL/SQL functions are executed due to the way SQL can be re-written by the parser. I think most of us stumbling over something like this would treat it as a bug though. You have to look at the column projection, again see the comments, to see how Oracle is deciding to get the columns derived by a naked call to DBMS_RANDOM.VALUE (by naked I mean no inclusion of parameters passed in and, significantly, no reference to columns). It’s just the way it is}

oraclebase's picture

PDB Logging Clause… Again…

About 14 months ago I spotted a problem with the PDB Logging Clause. I opened an SR and several months later I got a patch, which unfortunately didn’t fix the issue, just altered the symptom somewhat. I wrote about that patch here.

Yesterday I got a new patch, which actually does fix the problem, so now the PDB Logging Clause works as documented!

I’ve updated the PDB Logging Clause article to reflect the change.

Chris Antognini's picture

Bugs Related to SQL Plan Directives Pack and Unpack

SQL plan directives are a new concept introduced in version 12.1. Their purpose is to help the query optimizer cope with misestimates. To do so, they store in the data dictionary information about the predicates that cause misestimates. Simply put, the purpose of SQL plan directives is to instruct the database engine to either use dynamic sampling or automatically create extended statistics (specifically, column groups).

Since the database engine automatically maintains (e.g. creates and purges) SQL plan directives, in some situations it is necessary to copy the SQL plan directives created in one database to another one. This can be done with the help of the DBMS_SPD package.

Here are the key steps for such a copy:

fritshoogland's picture

Reconstructing oratab from the cluster registry

At the Accenture Enkitec Group we have a couple of Exadata racks for Proof of Concepts (PoC), Performance validation, research and experimenting. This means the databases on the racks appear and vanish more than (should be) on an average customer Exadata rack (to be honest most people use a fixed few existing databases rather than creating and removing a database for every test).

Nevertheless we gotten in a situation where the /etc/oratab file was not in sync with the databases registered in the cluster registry. This situation can happen for a number reasons. For example, if you clone a database (RMAN duplicate), you end up with a cloned database (I sincerely hope), but this database needs to be manually registered in the cluster registry. This is the same with creating a standby database (for which one of the most used methods is to use the clone procedure with a couple of changes).

Chris Antognini's picture

The Broken Statistics: "parse count (total)" and "session cursor cache hits"

The values provided by the “parse count (total)” and “session cursor cache hits” statistics are subject to several bugs. And, what’s worse, for years Oracle didn’t care to fix it. This is my impression, at least.

Then, when few weeks ago I read in the Oracle Support note 13837105.8 (Bug 13837105 – statistics “parse count (total)” and “session cursor cache hits” miscounted) that the bug introduced in was fixed, I hoped that others bugs in this area were fixed as well.

Unfortunately, it’s not the case. What a disappointment!

Even though several posts were already wrote about this topic (e.g. here and here; check the comments as well…), I thought it could be useful to summarize what the current status is.

Chris Antognini's picture

Named Notation and SEM_MATCH Table Function

This is a short post about a strange behavior I discovered today…

The fact is that it is not really possible to use the named notation with the SEM_MATCH table function. In fact, even though the parameter’s names can be specified, the order of the parameters overrides the specification done with the named notation!?!

Here is an example:

oakroot's picture

Gathering statistics in and encounters strange waits

It appears that, somewhere in the and patchsets, Oracle introduced some additional unwanted functionality to the “GATHER_*_STATS” procedures in the DBMS_STATS package.

I have been working on a customer’s database supporting a data-mart application where the data loading programs call DBMS_STATS.GATHER_TABLE_STATS as a concluding part of load processing, which means that the procedure gets called a *lot*.

We noticed that some calls to the same procedure were waiting excessively on the event “enq: TX – allocate ITL entry” and being blocked by sessions calling similar DBMS_STATS procedures, themselves in turn waiting excessively on the event “row cache lock” on data dictionary tables like SYS.CON$ (i.e. underlying the DBA_CONSTRAINTS view) and SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$.

Chris Antognini's picture

How Many Children Can a Parent Cursor Have? 1,000,000?

The patch set includes a fix for bug# 10187168 which, in reality, is an enhancement request. Its purpose is to artificially limit the number of child cursors that a parent cursor can have. The concept is quite easy: when a parent cursor reaches _cursor_obsolete_threshold (default value is 100) child cursors the parent cursor is obsoleted and, as a result, a new one is created.

So, as of (or with some PSUs and bundle patches), the answer to the question is: 100.

Syndicate content