18c

I haven’t used Data Pump in a little while but recently needed to do a bit of work involving this very useful utility to export a single table. I know that it is possible to export data in parallel using expdp, but I can’t recall the syntax for doing so off the top of my head when I need it. This post describes a potential approach to exporting a table in parallel. In the next post I will demonstrate an interesting case where using parallelism didn’t help me speed up the export. All of this was tested on 12.2 and 18.4.0, the examples I am sharing originate from my 18.4.0 single instance database (without ASM) running on Linux.

The setup

My lab environment is a bit limited when it comes to storage, so I’ll have to do with small-ish tables. The basic principles should still apply for larger segments though. Please note that my tables aren’t partitioned to keep the individual segment size as large as possible. 

Just a quick blog post from Wellington New Zealand where we have just wrapped up the 2018 APAC Groundbreakers tour. It was a great way to finish the event with a small but enthusiastic crowd here in New Zealand.

One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the potential “baggage” of user nominated hints strewn throughout the code.

This would seem a fairly easy function to implement, namely, as we parse the SQL, simply rip out anything that is a comment structured as a hint. At the Perth Oracle User Group conference yesterday, I had an interesting question from an attendee – namely, if all optimizer hints are being ignored, then does this mean that every hint will be ignored. In particular, what about the (very useful) QB_NAME hint? If we are just stripping out anything that is in a hint text format, we will lose those as well?

So it’s time for a test!

Here’s one I’ve just discovered while trying to build a reproducible test case – that didn’t reproduce because an internal algorithm has changed.

If you upgrade from 12c to 18c and have a number of hybrid histograms in place you may find that some execution plans change because of a change in the algorithm for producing hybrid histograms (and that’s not just if you happen to get the patch that fixes the top-frequency/hybrid bug relating to high values).

Here’s a little test to demonstrate how I wasted a couple of hours trying to solve the wrong problem – first a simple data set:

Here’s a problem that (probably) won’t affect the day to day running of most systems – but it could be a pain in the backside for people who write programs to generate repeatable test data. I’m not going to say much about the problem, just leave you with a test script.

Hopefully you’ve followed my very simple and easy guide to downloading the 18c database software for Windows. But of course, software on its own is not much use – we need a database! So let’s get cracking and create one. Using the Start menu like I’ve done below, or using the Windows panels, locate the Database Configuration assistant and start it.

After a few seconds the initial screen will ask what you want to do.  Choose “Create Database”.

If you’re a Windows enterprise, or you want to run your 18c database on your Windows laptop/desktop for research and education, then there has been some good news this week.  The software is now available to you on the OTN network page.  Here’s a walk through of the software installation process

Head to the standard database downloads page

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Accept the license agreement and choose the Windows version to download

As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature.

Often in a transactional-style system the busiest table (let us call it SALES for the sake of this discussion) is also

• the biggest table, after all, it has all of our sales in it,
• the most demanded for table, in that, almost every query in our application wants to access it in some way shape or form.

This is in effect the database version of the Pareto Principle. Everyone wants a slice of that SALES “pie”, and the piece of that pie that is in most demand is typically the most recent data. Your application may have pages that will be showing:

Well, finally it’s here! 18c for on-premise installation so the world can all get stuck into the cool new features of the latest release on their own laptops   At least that is what I’ll be doing!

Naturally as soon as I heard the news, I downloaded the software and got ready to set aside the day for installation and creation of an 18c database. But I didn’t need that long – I didn’t need that long at all. Just a few clicks and a few commands and there it was – my 18c database up and running.

Check out how easy it is with my three videos.

Software Installation

By Franck Pachot

.
Good news, the latest Patchset for Oracle 12cR2 (which is not named patchset anymore, is actually called release 18c and numbered 18.0.0.0.0) is available for download on OTN. It is great because OTN download does not require access to Support and Software Updates. It is available to anybody under the Free Developer License Terms (basically development, testing, prototyping, and demonstrating for an application that is not in production and for non-commercial use). We all complained about the ‘Cloud First’ strategy because we were are eager to download the latest version. But the positive aspect of it is that we have now on OTN a release that has been stabilized after a few release updates. In the past, only the first version of the latest release was available there. Now we have one with many bug fixed.