Written by From version 11.2 onward, the PARALLEL hint supports two syntaxes: object-level and statement-level. The object-level syntax, which is the only one available up to version 11.1, overrides the DOP associated to a tables. The statement-level syntax can not only override the PARALLEL_DEGREE_POLICY initialization parameter at the SQL statement level, but also force the utilization of […]
Read MoreWhen CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?
The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals. […]
Read MoreAlso Extensions Created Because of SQL Plan Directives Can Invalidate Packages – Take Two
In a previous post I described a situation where the creation of an extension, independently of whether it’s carried out manually by a person or automatically by the database engine, can lead to the invalidation of PL/SQL objects. In this second post on that subject, I describe, with the help of an example (I love […]
Read MoreAlso 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 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0. The fact that an extension explicitly […]
Read MoreAuto 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 […]
Read MoreForeign Keys and Library Cache Locks
In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way. Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see […]
Read MoreExtension Bypassed Because of Missing Histogram
Today, while tuning a fairly complex query experiencing wrong cardinality estimates, I noticed something I was not aware of. Hence, I thought to write this short post to illustrate how to reproduce the problem I experienced… Create the test table (notice the correlation between the data of the two columns): SQL> CREATE TABLE t 2 […]
Read MoreSystem Statistics Gathered in Exadata Mode – When Are They Relevant?
The aim of this post isn’t to explain what the “exadata mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only thing I would like to add is that the “exadata mode” is available as of 11.2.0.4 or when […]
Read MoreThe 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 […]
Read MoreNamed 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 […]
Read More