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 […]
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 […]
Exadata Storage Index Min/Max Optimization
Before discussing the Exadata-specific feature, let’s review what the database engine can do independently of whether Exadata is used. To execute queries containing the min or max functions efficiently, two specific operations are available with B-tree indexes defined on the column referenced in the min or max function. The first, INDEX FULL SCAN (MIN/MAX), is […]
Restrict the query optimizer through OPTIMIZER_FEATURES_ENABLE and Top-Frequency/Hybrid...
To make upgrades easier, I regularly see people considering disabling query optimizer features by setting the OPTIMIZER_FEATURES_ENABLE initialization parameter to a non-default value. My general opinion about this “habit” is summarized in TOP with the following two sentences: Changing the default value of the OPTIMIZER_FEATURES_ENABLE initialization parameter is only a short-term workaround. Sooner or later […]
STATISTICS COLLECTOR Row Source Operation and LOBs
The aim of the STATISTICS COLLECTOR row source operation, which is used in adaptive plans, is to buffer all data produced by its child operation until it is known whether the inflection point is crossed. It goes without saying that buffering requires memory and, therefore, Oracle Database has to limit the amount of memory that […]
The Importance of the In-Memory DUPLICATE Clause for a RAC System
With the INMEMORY clause you can specify 4 sub-clauses: The MEMCOMPRESS clause specifies whether and how compression is used The PRIORITY clause specifies the priority (“order”) in which the segments are loaded when the IMCS is populated The DISTRIBUTE clause specifies how data is distributed across RAC instances The DUPLICATE clause specifies whether and how […]
The APPROX_COUNT_DISTINCT Function – A Test Case
The aim of this post is not to explain how the APPROX_COUNT_DISTINCT function works (you find basic information in the documentation and in this post written by Luca Canali), but to show you the results of a test case I run to assess how well it works. Here’s what I did… I created a table […]
RESULT_CACHE Hint in 12.1.0.2
Every new Oracle Database patch introduces not only documented features, but also undocumented ones. In this short post I would like to provide information about an enhancement of the RESULT_CACHE hint. According the documentation, the RESULT_CACHE hint has no parameter. But, as of 12.1.0.2, at least the SNAPSHOT parameter is supported. Its purpose is to […]