In the last days, I am running a number of tests based on the TPC-DS benchmark against Oracle’s Autonomous Data Warehouse Cloud service (ADWC). One of the first thing I did is of course to create the TPC-DS schema and populate it. The aim of this blog post is to share some observations related to […]
Partition-Wise Operations – New Features in 12c and 18c
Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c. The new features […]
Activating and Deactivating Performance Feedback
Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration. The parameters that control performance feedback in 12c are the following: OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set […]
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 […]
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 […]
Parallel Full Table Scans Do Not Always Perform Direct Reads
Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception. For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an […]
Parallel Processing With Standard Edition
As clearly stated in the Licensing Information guide, all features related to parallel processing (parallel backup and recovery, parallel query/DML, parallel statistics gathering, parallel index build/scans, parallel Data Pump export/import, in-memory parallel execution, parallel statement queuing and parallel spatial index builds) are only available with the Enterprise Edition. However, as of Oracle Database 11g Release […]
Exadata Storage Server and the Query Optimizer – Part 3
In the first and second post of this series I shared with you some basics about smart scan and gave some details about projection and restriction. The aim of this post is to cover the third basic technique: join filtering. Join filtering is not something specific to the Exadata Storage Server. In fact, it is […]
Does the Query Optimizer Cost PX Distribution Methods?
The short answer to this question is “yes”, it does. Unfortunately, the distribution costs are not externalized through the execution plans and, as a result, this limitation (yes, it is really a limitation in the current implementation, not a bug) confuses everyone that carefully look at the information provided in an execution plan of a […]
System Managed Extent Size – 11g Improvements
Oracle Database provides two extent management options: locally managed and dictionary managed. Today, when creating a new database, most DBAs use the former for all tablespaces. This is a good thing. What is less obvious is the choice between uniform extent size and system managed extent size. Both of these options have pros and cons. […]