Statement-level PARALLEL Hint
UPDATE 2017-08-23: because of the confusing meaning of the term “force” (the meaning that most people give to it isn’t the one of the optimizer traces), I edited the text to make it more clear. Given the number of changes I completely removed the old text. From version 11.2 onward, the PARALLEL hint supports two […]
When 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. […]
Ad: The Method R Guide to MASTERING ORACLE TRACE DATA
The second edition of Cary Millsap‘s MASTERING ORACLE TRACE DATA (MOTD) is finally available. You can order it through amazon.com. I had the pleasure not only to review MOTD while Cary was working on it, but also to write a foreword that summarizes what I think about the book. So, if you are asking yourself […]
Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in 12.1.0.2
One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version. To reproduce it you simply need […]
Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages – ...
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 […]
Oracle Database In-Memory Demos
In the last 14 months I delivered a dozen of presentations covering the In-Memory Column Store. During many of them, I spent most of the time showing the audience several demos. The aim of this post is to share with you the scripts and a recording (MP4) of those demos. Warning about Demos The recordings […]
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 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. UPDATE 2016-11-08: The patch related to […]
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 […]
Online Training Ad: Oracle Database 12c – New Performance Features
On 10 December 2015 I’ll give an online training entitled Oracle Database 12c – New Performance Features. This short post provides key information about it. Description With every new release of Oracle Database, a number of features aimed at improving performance are introduced. It goes without saying that 12.1 is no exception to the rule. […]
Foreign 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 […]