Search

Top 60 Oracle Blogs

Recent comments

In-Memory

18c: Order by in WITH clause is not preserved

For a previous post I’ve run on 18c a script of mine to get the V$MYSTAT delta values between two queries. This script (new version available on GitHub) generates the queries to store some values and subtract them on the next execution. But I had to fix it for 18c because I relied on some order by in a CTE which is lost in 18c.
The idea was to get the statistic names in a Common Table Expression (CTE):

with stats as (
select rownum n,stat_id,name from (select stat_id,name from v$statname where name in (&names) order by stat_id)
)

and query it from different parts of the UNION ALL which generates the script:

select 'select ' from dual
union all

Ad – Oracle Database 12c Release 1 and 2: New Performance Features

In the past I gave a number of 1-day seminars about the new performance features available in Oracle Database 12c Release 1. On the 22nd of February, for the first time, I’ll give an updated version of that seminar with content about both Release 1 and Release 2. Note that because there is more content, I extended it from one day to two days.

Conjuctive Normal Form

I recently tweeted about a comment I’d picked up at the Trivadis performance days regarding tablescans and performance.

“If you can write your SQL in conjunctive normal form it can help the optimizer to offload more predicates”

Inevitably someone asked me if I had an example to demonstrate this – I didn’t, and still don’t really, but here’s an interesting demo based on an example from the Oracle In-Memory blog showing how the optimizer will rearrange your filter predicates before passing them to the tablescan code for evaluation against an inmemory table.

InMemory Bonus

It should be fairly well known by now that when you enable the 12c InMemory (Columnar Store) option (and set the inmemory_size) your SQL may take advantage of a new optimizer transformation know as the Vector Transformation, including Vector Aggregation. You may be a little surprised to learn, though, that some of your plans may change even when they don’t produce any sign of a vector transformation as a consequence. This is because In-Memory Column Store isn’t just about doing tablescans very quickly it’s also about new code paths for doing clever things with predicates to squeeze all the extra benefits from the technology. Here’s an example:

Index Usage

There are some questions about Oracle that are like the mythical Hydra – you think you’ve killed it, but for every head you cut off another two grow. The claim that “the optimizer will switch between using an index and doing a tablescan when you access more than X% of the data” re-appeared on the OTN database forum a little while ago – it doesn’t really matter what the specific value of X was – and it’s a statement that needs to be refuted very firmly because it’s more likely to cause problems than it is to help anyone understand what’s going on.

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 compare different kinds of processing and to give you a feel for their impact. Since every system and every application has its own characteristics, the relevance of using each technique might be very different, depending on where it’s applied. Simply put, the scripts were engineered to clearly show specific behaviors.

In case you want to run the scripts, to setup the environment run imcs_prepare_schema.sql.

Population

Oracle 12c in-memory option and IO

This article is about the Oracle 12c in-memory option, and specifically looks at how the background worker processes do IO to populate the in-memory column store.

Hardware: Apple Macbook with VMWare Fusion 7.1.3.
Operating system: Oracle Linux 6.7, kernel: 3.8.13-118.el6uek.x86_64.
Database version: Oracle 12.1.0.2
Patch: opatch lspatches
19392604;OCW PATCH SET UPDATE : 12.1.0.2.1 (19392604)
19303936;Database Patch Set Update : 12.1.0.2.1 (19303936)

But first things first, let’s setup the in-memory option first with a test table. The first thing to consider is to create the in-memory area to store the objects. I only want a single table stored in the in-memory area, so I can very simply look at the size of object:

Demos do fail.

I am an ardent believer of “show me how it works” principle and usually, I have demos in my presentation. So, I was presenting “Tools for advanced debugging in Solaris and Linux” with demos in IOUG Collaborate 2015 in Las Vegas on April 13 and my souped-up laptop (with 32G of memory, SSD drives, and an high end video processor etc ) was not responding when I tried to access folder to open my presentation files.

Sometimes, demos do fail. At least, I managed to complete the demos with zero slides </p />
</p></div>

    	  	<div class=

Presentation material & E-learning videos – In-Memory Column Store Workshop with Maria Colgan

You can now download and have another look at the presentations used during the In-Memory…