Extended Statistics und dynamic sampling

Eigentlich ist dynamic sampling eine Standardantwort auf falsche cardinalities für komplexe Queries mit hoher Laufzeit, bei denen das Parsing gegenüber der Gesamtlaufzeit in den Hintergrund tritt. Leider ist das Zusammenspiel zwischen dynamic sampling und statischen Statistiken noch ausbaufähig: aktuell werden z.B. extended statistics beim Einsatz von dynamic sampling komplett ignoriert - wie Franck Pachot in seinem Blog zeigt. Besonders gut gefällt mir das Fazit:
In this case, Adaptive Dynamic Sampling is a good approximation. (more...)

Forms Community Meeting and questions to Michael Ferrante

Next week at the German Oracle User Group (DOAG) conference, we have a Forms community meeting Tuesday night at 6 pm in the Galileo Lounge.

As a preparation, we would like to ask you to send us questions, so that we get them answered by Michael Ferrante.

We are all looking forward to a great conference. Forms is coming again!

Please send the questions to gerd.volberg@forms12c.de.

See you in Nuremberg

Join the Cloud Analytics Academy

Maybe not a cool as Star Fleet Academy, but this is pretty cool. Snowflake and a number of our partners have come together to create the first, self-paced, vendor agnostic, online training academy for analytics in the cloud. This academy will get you up to speed on what is happening today in the cloud with […]

How to create small Docker images

When it comes to space efficiency Docker still isn’t quite as good as it could be. The layered filesystem used by Docker sometimes occupies more space than is really necessary. Over time, a couple of enhancements have made their way into Docker to allow the build of more space efficient images. The ADD instruction for example … Continue reading "How to create small Docker images"

Slides of my JavaOne and OOW 2017 sessions

The slides of my recommended JavaOne 2017 and OOW2017 sessions are available here.

Verhalten der auto_sample_size in 12c

Nigel Byliss erläutert im Oracle Optimizer Blog, die erfreulichen Änderungen, die für die auto_sample_size in 12c eingeführt wurden. Dabei ist die auto_sample_size der default-Wert für den Parameter estimate_percent der dbms_stats.gather_*_stats Prozeduren. Obwohl sie für viele Statistiken tatsächlich bereits seit ihrer Einführung sehr gute Ergebnisse lieferte, gab es einen Bereich, in dem ihre Ergebnisse recht erbärmlich ausfallen konnten, nämlich die Erstellung von Histogrammen, denn dafür wurde stets ein mikroskopisches Sample von gerade einmal 5500 Datensätzen (more...)

CTAS-Statement-Texte in 12.2 Trace-Files nicht mehr gekürzt

Der Titel ist beinahe länger als der folgende Text, aber was tut man nicht alles, um den eigenen Blog nicht völlig einschlafen zu lassen: Martin Bach weist in seinem Blog darauf hin, dass die Texte in der SQL-Trace-Ausgabe für "create table as select" (aka CTAS) in 12.2 nicht mehr auf 20 Zeichen gekürzt werden, was in älteren Releases der Fall war und die Analyse solcher Operationen über Trace erschweren konnte. Darüber war ich in (more...)

Comparing queries…

How do you compare a rewritten query to its original version? Most of the time I just run a MINUS operation on the original and new query. Actually I execute two. Old query (A) MINUS New query (B) and vice versa (B) MINUS (A). Both should result in no rows. That way I thought I had proven that the resultsets for both queries are equal.
But there is a flaw in this assumption.

What if (more...)

Oracle XE 12c becomes Oracle XE 18c

Oracle Database, Express Edition (XE) is a free version of the Oracle Database, currently available as version 11g (11.2) for Windows and Linux.
A planned new version of Oracle XE based on 12c (12.2) was first mentioned back in 2013. Now we are in 2017 and Oracle has changed its version numbering, aiming for yearly releases of the database, which means that after 12c comes 18c (in 2018) and 19c (in 2019) and (more...)

TPC-DS at 100TB and 10TB Scale Now Available in Snowflake’s Samples

We are happy to announce that a full 100 TB version of TPC-DS data, along with samples of all the benchmark’s 99 queries, are available now to all Snowflake customers for exploration and testing.

New Snowflake features released in Q2’17 

It has been an incredible few months at Snowflake with the introduction of self-service, the data sharehouse, and numerous other features added in the last quarter.

AUSOUG Connect 2017 – see you in Perth

I’ll be presenting a talk about some APEX applications I’ve been building gradually over the past five years at the AUSOUG Connect 2017 conference in Perth, Western Australia on 29 November.

My hope is that you’ll get some ideas on how best to set up the overall architecture for your APEX applications that may need to serve more than one customer – whether different organisations or different business groups within your organisation.

Although it’s just (more...)

ODC Appreciation Day: EMP #ThanksODC

Here is my very short entry for the Oracle Developer Community Appreciation Day 2017.

Very often I use the good ol' EMP table to demonstrate even the latest features of SQL and PL/SQL.
Everybody seems to know the EMP table, and some even know some of the content off the top of their head (yes, I'm guilty of that too). Whenever I need to write a hierarchical query and am not really sure what which (more...)

ODC Appreciation Day: Mike Hichwa’s team #ThanksODC

I don't know Oracle's internal organization, but I get the impression that people like Joel Kallman, Jeff Smith and Kris Rice work for or with Mike Hichwa. By naming him, I get to show appreciation for a bunch of technologies, and for the people behind them.

ODC Appreciation Day: Collections in SQL

Here’s my contribution to the ODC Appreciation Day.


Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in this session.
My 5-minute presentation was about Collections in SQL.

Collections are very useful in PL/SQL development. This is (more...)

ODC Appreciation Day: The PL/SQL Language

Like last year, Tim Hall of oracle-base.com fame suggested we should all do an "ODC Appreciation Day" in honor of the Oracle Developer Community (ODC), by blogging about our favorite Oracle product or feature.

My personal favorite, after the database itself, is the PL/SQL language that runs inside the database.

Here's what's great about it:
  • Simple (and therefore easy to understand and quick to learn)
  • Runs everywhere the Oracle database runs (any operating (more...)

ODC Appreciation Day: Cursor Variables

You know everything is Tim Halls fault, right? Including this blog post? Oh yes it is, he came up with the idea of OTN ODC Appreciation Day - a day where Oracle Bloggers all over the world show their appreciation of the Oracle Developer Community by blogging about some favorite Oracle feature.

So what should I pick this year of the multitude of possible favorites? Something brand new? No, I think I'll write about (more...)

RETURNING INTO – Enhancement Suggestion

The RETURNING INTO clause is one of my favorite features.
It returns data from the rows that have been affected by the DML statement, and as I wrote in this previous post:
For INSERT it returns the after-insert values of the new row’s columns.
For UPDATE it returns the after-update values of the affected rows’ columns.
For DELETE it returns the before-delete values of the affected rows’ columns.

For INSERT there are no before-insert (more...)

Mehrere SQL_IDs zu einem Query-Text

Jonathan Lewis erläutert in seinem Blog ein - zumindest für mich überraschendes (sprich: bisher unbekanntes oder wieder vergessenes) Phänomen: zum gleichen SQL Text kann es mehrere unterschiedliche SQL_IDs geben. Eine relativ bekannte Ursache dafür ist offenbar, dass die Länge der in einer Query verwendeten Bindevariablen einen Einfluss auf die Erzeugung der SQL_IDs hat, was vermutlich ein Effekt des internen library Mechanismus ist und keine Design-Entscheidung.

Darüber hinaus erwähnt der Autor die Möglichkeit, ein Statement über (more...)

Database is the marquee feature again

So every year before and during Oracle Open World we all complain about how data and database is brushed to the side.

This year it is the feature. It is the one thing the big sign on Moscone West screams. “The Autonomous Database”

Here is a picture from showing it. It is all about database.

Every year there is talk about how Oracle needs to return to data and databases. Now that they do, I (more...)