Analyse von Parsing-Effekten mit ASH

In seinem jüngsten Artikel stellt Jonathan Lewis einen ziemlich extremen Fall von Parsing in einem System mit zahlreichen sehr ähnlichen - und recht komplexen - Queries vor. Eine Query, die im Testsystem in 7 Sekunden optimiert werden kann, benötigt im Produktivsystem 415 Sekunden für das Parsen. Dafür verantwortlich ist offenbar die Tatsache, dass der Server unter extremer Last steht und mehr CPU verwenden will als verfügbar, und dass zahlreiche sehr ähnliche Queries gleichzeitig optimiert werden (more...)

PowerQuery mit SSAS

Viel habe ich nicht mehr zu SSAS zu sagen, aber der Hinweis von Chris Webb, dass PowerQuery in der aktuellen Version jetzt auch SSAS Multidimensional und Tabular Quellen als Datenbasis verwenden kann, sollte nicht unerwähnt bleiben. Dabei wird intern MDX erzeugt - "while it’s a bit strange in places it’s basically good and should perform well."


The questions in the quiz were, of course, all trick questions.

Question 1: Answer D



The second example shows the correct syntax for a natural outer join.  In the first example, LEFT is interpreted as a table alias for T1 and so an inner join is used.

QUESTION 2: Answer E


Oracle Mobile Day: It’s a Mobile Revolution and ODTUG’s Leading the Charge


“The BIGGEST Fears of the Current Generation”

Dec. 2, 2014 marked the first ODTUG Oracle Mobile Day, hosted at Oracle Headquarters in Reston, VA. This is a format we hope to replicate in different cities across the US and in Europe in the future. We were really excited to have with us Suhas Uliyar, Oracle’s VP of Mobile Strategy and Product Management, and the visionary behind Oracle mobility. I had the honor of introducing him to (more...)

Going to UKOUG Tech14

I’m on my way to UKOUG Tech14, where I will present Database 12c MATCH_RECOGNIZE: Beating the Best Pre-12c Solutions on Tuesday at 3 P.M., room 4B. With all the Oaktable ACE Directors presenting at the same time, I don’t expect a big crowd but I’ll do my best for those who do turn up. This is my […]


First things first: Thou shalt not explicitly set AQ_TM_PROCESSES=0 ! Unless, of course, you want to disable the Queue Manager Process (QMNC in Oracle 11.x). Which you may want to do during database upgrades to prevent Streams or Advanced Queueing from interfering with the upgrade process. However, if you don’t reset this parameter afterwards, you might run […]

Keine adaptiven Pläne für Queries mit LOB-Elementen

Zu den interessanten neuen Errungenschaften des Optimizers in 12c gehören die adaptive plans, die es möglich machen, die endgültige Entscheidung darüber, ob ein Join als Nested Loops oder als Hash Join erfolgen kann bis zum Abschluss der ersten Ausführung aufzuschieben und dann in Abhängigkeit von der ermittelten Cardinality zu treffen (Stichwort: inflection point). Allerdings gibt es eine Reihe von Rahmenbedingungen, die erfüllt sein müssen, damit ein adaptive plan verwendet werden kann. Eine Einschränkung, die (more...)


Learning, relearning or unforgetting…

What value is there in a DISABLEd constraint?

This was a question on the OTN forums this week and a) my first reply was wrong and b) I couldn’t find a clear demonstration elsewhere.

The documentation is clear.

RELY Constraints

The ETL process commonly verifies that certain constraints are true. For example, it can validate all of the foreign keys in the data coming into the fact table. This means that (more...)

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as
create materialized view apx_application_pages
as (more...)

The Future of Oracle Forms Straight From the Source’s Mouth

At Kscope14 ODTUG had a “Social Media Lounge” where they interviewed leaders and visionaries in various technology fields. They have just released a great one where John King, a renowned Oracle ACE Director for middleware and Oracle development tools,  interviewed Michael Ferrante, the Oracle Forms Product Manager. Of course the hot topic was the future of Forms. When asked about this Michael responded:

“I’m smiling because I hear that [Forms is dead] a lot as well. I (more...)

Early Christmas Present: Oracle SQL Developer Data Modeler 4.1 Beta is here! (Still FREE)

Just saw this tweet from Oracle Product Manager Ashly Chen: Happy Wednesday! Data Modeler 4.1 Beta is available for download. — Ashley Chen (@ashleychen5) December 3, 2014 Get thee to the site and download away! Did I say, it is FREE? (Do remember it is BETA as use it accordingly) Cheers! Kent The Oracle […]

Join Cardinality und Sanity Checks

In seinem heutigen Blog-Artikel Upgrades - bisweilen denke ich, der Autor könnte sich wiedererkennbarere Titel ausdenken - schreibt Jonathan Lewis über die Veränderungen der Join-Cardinality für unterschiedliche Oracle-Realases seit Version 9.2. In seinem Beispiel verbindet er zwei identische Tabellen, die jeweils 1.000.000 Datensätze enthalten, über unterschiedlich häufig auftretende Spaltenwerte und zeigt dabei, dass es mindestens drei unterschiedliche Ergebnisse für die Cardinality-Schätzungen gibt. Ich wiederhole hier nicht den Versuchsaufbau des Artikels, sondern ergänze (more...)


If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Troubleshooting Oracle Performance (2nd edition) by Christian Antognini



Troubleshooting Oracle Performance, 2nd Edition provides a systematic approach to addressing the underlying causes of poor database application performance.


AND a you can also win a free copy of Expert Oracle SQL by Tony Hasler


Expert Oracle SQL provides a systematic approach for tuning individual SQL (more...)

DOAG 2014 – Oracle Forms is alive and well in Germany

Last week I did a quick hop, skip, and a jump across the pond to Nuremberg, Germany for the DOAG usergroup conference. Although I was only there for a total of 54 hours, I’m so glad I made the effort to attend. I was especially honored to go to represent ODTUG as we further attempt to increase cooperation and connect with fellow user groups around the world.

Screen Shot 2014-11-25 at 2.34.34 PM

Michael Ferrante preparing for his session. I’m glad I made it just in time to (more...)

Why Kyte Tolerates Poor Questions

I have Tom Kyte’s “Ask Tom” in my feed reader, and every now and then it opens for questions and the flood pours in. Quite often there is an interesting question, but more often than not there are quite a lot of “poor” questions: some just poorly worded, some too-easily-googleable, others are could-have-answered-from-the-docs questions. It’s not dissimilar to StackOverflow, which doesn’t suffer so much because of the army of people who work together to either improve or (more...)

The mess that is fast-refresh join-only Materialized Views

Every now and then you come across a feature or a combination of features which has turned into such a dog’s dinner that you wonder how many people can possibly be using it.

This week – fast fresh materialized views.
I would have thought that this was a very commonly used feature.

This is quite a long article so I will do a top-level TOC first

  1. Why am I looking at a fast-refresh, on-commit, join-only (more...)

Privilege Analysis mit 12c

Eine kleine persönliche Beichte, die regelmäßige Leser vielleicht nicht allzu sehr überrascht: Performance-Fragen interessieren mich deutlich mehr als solche der Security. Und noch ein Geständnis: ich habe gelegentlich Applikationen ausgeliefert, deren Berechtigungen größer waren als erforderlich - weil es mir zu mühsam und zu öde war, mich bis ins Detail mit der Privilegienvergabe zu beschäftigen. Für Entwickler, die unter ähnlichen Charakterschwächen leiden, gibt es in 12c jetzt ein großartiges neues Feature: die Privilege Analysis, bei (more...)

Datapump Bug: Scheduler Jobs Not Imported in 11.2

Just the other day I hit the unpublished Bug 16445132: DBMS_SCHEDULER PROBLEM WITH DATABASE MIGRATION VIA EXPDP/IMPDP. It affects Scheduler Jobs that were created to end with a corresponding Scheduler Window. Here’s an excerpt of a Data Pump Logfile: ORA-39083: Object type PROCOBJ failed to create with error: ORA-27469: STOP_ON_WINDOW_EXIT is not a valid job […]

Instanz-Crash bei Latch-Analyse

Stefan Koehler analysiert bei SAP on Oracle eine Situation, in der das manuelle Setzen und Freigeben von Latches über Oradebug zu einem Instanz-Crash führt. Relevanter als das Ergebnis ist für mich die methodische Vorgehensweise bei der Analyse - sollte ich mich mal wieder mit Latches beschäftigen wollen, dann wäre es nützlich, diesen Artikel noch mal intensiver anzuschauen.

Chrome Device Mode and Mobile Emulation

Recently a colleague suggested I take a fresh look at the mobile emulation button in Chrome's developer tools.

Apparently this was upgraded earlier in the year and it's a shame I didn't have this a year ago!

Chrome Developer Tools Mobile Emulation
Some immediate advantages I see for APEX development:

  • Specify the device you'd like to emulate from a decent range of contemporary devices
  • This viewport can then be easily scaled to suit whatever monitor (more...)