After doing a number of 12c installations, I decided it was time to write something new. I figured I’d pick something easy to start off with, so here is the first thing off the press.
Hola (that’s all my Spanish used up).
Olá (and my Portuguese too).
Apologies for the English spelling of names.
Afgelopen dinsdag is Oracle Database 12c beschikbaar gekomen, de nieuwste generatie van de Oracle database. AMIS biedt Oracle professionals de eerste mogelijkheid om de vernieuwingen in Oracle Database 12c in actie te zien. Op maandag 15 juli organiseert AMIS het gratis seminar “Oracle database 12c revealed”. De AMIS specialisten die meer dan een jaar bètatesten
Oracle 12c certainly has some great features, but for the performance guy like myself, performance monitoring features are particularly interesting. There are three new v$ tables that track anomalies in the IO path. The idea is to provide more information about really poorly performing IO that lasts more than 500ms.
These two tables are going to be useful to monitor when performance issues occur. I can already see the SQL scripts to monitor this activity starting to pile up. But, there is one little extra table that dives even further into the IO stack using Dtrace.
Following a comment from Marcin Przepiorowski on my last post, it crossed my mind to check whether “with” functions can be deterministic – the answer seems to be “not yet”. Here’s a simple script that you can run from end to end to check current and future releases – it compares inline (with) and standalone functions when the “deterministic” keyword has been used.
Here’s a quirky little thing I discovered about 5 minutes after installing 12c Beta 1 (cut-n-pasted from SQL*Plus):
create or replace view v$my_stats as select ms.sid, sn.statistic#, sn.name, sn.class, ms.value from v$mystat ms, v$statname sn where sn.statistic# = ms.statistic# 14 ; create or replace view v$my_stats * ERROR at line 1: ORA-00999: invalid view name
You can’t create views with names that start with V$ or GV$ in the sys schema. Presumably to eliminate the risk of someone’s clever view definition from overwriting and disabling one of the distributed dynamic performance views by accident.
I finally got my server problems sorted out yesterday, so I was able to do a 12c virtual RAC installation. You can see what I did here:
The setup is pretty much the same as the 11gR2 setup. So there’s no real drama at all.
With the basic installation articles out of the way I can start having a play with the functionality.
The title is a bit of a joke, really. It’s mirroring a title I used a little over a year ago “Logical Tuning” and reflects my surprise that a silly little trick that I tried actually worked.
If you don’t want to read the original article, here’s a quick précis – I started with the first query, which the optimizer executed as a filter subquery, and rewrote it as the second query, which the optimizer executed as two anti-joins (reducing the execution time from 95 seconds to 27 seconds):
XMLIndex support for Hash Partitioning is now also supported in Oracle 12c. In Oracle 11 there was already support for RANGE and LIST partitioning, but not yet for HASH partitioning. Some examples for the later two: LIST Partitioning on XMLType -- -- Create partitioned LIST partitioned XMLType table -- DROP TABLE list_part_xml PURGE;