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 from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). (more...)
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 (more...)
Trivadis, the company I work for, just opened a new branch in Denmark (the press release, in German, is available here). On the 14th of April (09:00-15:00) takes place, in the Oracle’s office in Ballerup, the opening event. Two colleagues of mine and I will be there to deliver the following presentations:
Big Data versus Conventional Techniques – Decision Criteria (Peter Welker, Senior Principal Consultant)
Better use Big Data technologies in the Business (more...)
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
max functions efficiently, two specific operations are available with B-tree indexes defined on the column referenced in the
max function. The first,
INDEX FULL SCAN (MIN/MAX), is used when a query doesn’t specify a range condition. In spite of its name, however, it performs no full (more...)
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 the application should be adapted (optimized) for the new database version.
The issue is that not all new features are (more...)
If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Expert Oracle SQL by Tony Hasler AND a free copy of Troubleshooting Oracle Performance (2nd edition) by myself.
If you want to know more, click here.
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 can be allocated for that purpose. As a result, in some situations no adaptive plans can be used because according (more...)
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 data is duplicated across RAC instances
The aim of this post is not to describe these attribues in detail. Instead, (more...)
I strive to keep the number non-technical posts on this blog to the minimum. However, there are days when a non-technical post has to be written. This is one of such days.
Oracle Corporation, concerned about legal and ethical issues, not only provides to its employees a document entitled Code of Ethics and Business Conduct, but also expects that every employee adheres with the standards that the document sets. For the purpose of (more...)
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 with several numerical columns (the name of the column shows how many distinct values it contains), loaded 100 million (more...)