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 show the results of running the scripts on an Exadata system. The performance figures are intended only to explain and (more...)

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

What is new in 12c is that a SQL plan directive can instruct DBMS_STATS to create (more...)

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 to automatically configure it. For the latter, simply don’t set it.

About the value which is automatically determined by (more...)

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. Notably, it introduces key improvements in three areas.

  • The query optimizer has been enhanced not only by the introduction (more...)

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 from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). Schema used for the tests (more...)

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 dynamic sampling or automatically create extended statistics (specifically, column groups).

Since the database engine automatically maintains (e.g. creates (more...)

Ad: Trivadis DK Opening Event

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...)

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 used when a query doesn’t specify a range condition. In spite of its name, however, it performs no full (more...)

Restrict the query optimizer through OPTIMIZER_FEATURES_ENABLE and Top-Frequency/Hybrid Histograms

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...)

UKOUG TECH14 SQL Quiz

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.

Expert Oracle SQL

Troubleshooting Oracle Performance, 2nd Edition