Geschichte des Nested Loops

Jonathan Lewis erläutert wie sich der NESTED LOOPS Join und seine Repräsentation im Execution Plan im Laufe der Releases seit 8.1.7.4 verändert haben. Ich verzichte auf eine Wiederholung der Details, da ich die meisten davon schon in älteren Beiträgen erwähnt habe.

Insert Performance mit Bitmap und B*Tree Index

Bitmap Indizes machen eine Menge Ärger, wenn es um DML geht. Diese Erkenntnis ist nicht neu und weitgehend richtig, und ich habe hier auch schon mal gelegentlich einen - eher anekdotischen - Fall vorgeführt, in dem ein Insert in eine Tabelle mit Bitmap Index deutlich langsamer von statten ging als ein entsprechendes Insert in eine Tabelle mit B*Tree Index (wobei ich bei erneuter Betrachtung des Beispiels darüber nachdenke, ob das Ergebnis nicht ziemlich massiv durch (more...)

After TROUG BI&DW 2014

Originally posted on Gurcan Orhan's Oracle Data Integrator Blog:
It was a nice day of TROUG BI&DW 2014 full day meeting. Thanks for all attendees, speakers and everybody who paid attention to this event. Here are some blog posts / articles about event impressions. Emrah Mete’s impressions on his blog. Canburak Tümer’s impressions on his…

Cardinality für IN-Listen mit NULL-Werten in 12.1

Ein sperriger Titel für eine einfache Beobachtung. Vor etwas mehr drei Jahren hatte ich hier überprüft, wie sich die von Jonathan Lewis in Cost Based Oracle beschriebene Berechnung von Cardinalities für IN-Listen unterschiedlicher Struktur in neueren Releases verändert hatte. Zu den Fällen, die im Buch untersucht wurde, gehörten:
  • wiederholte Werte
  • Werte außerhalb des Korridors bekannter Werte (not between low_value and high_value)
  • NULL-Werte
Wobei es zu jedem dieser Fälle unterschiedliche Varianten gab. Bereits in 11.2. (more...)

Speichernutzungsdetails in V$PROCESS_MEMORY_DETAIL

Im Rahmen seiner epischen Oracle Memory Troubleshooting Reihe (die im Jahr 2009 begann) erläutert Tanel Poder die verschiedenen Informationen zur PGA-Nutzung der Prozesse, die die dynamischen Performance-Views zur Verfügung stellen:
  • v$process: liefert in den Spalten pga_used_mem und pga_alloc_mem einen (in vielen Fällen bereits ausreichenden) Überblick über die PGA Nutzung.
  • v$process_memory: liefert Informationen zur Verteilung dieser Ressourcennutzung auf die Bereiche SQL, PL/SQL, Java, Unused(Freeable) und Other.
  • für den Bereich SQL liefert v$sql_workarea_active Details zur Verteilung des (more...)

Adding CSS buttons to your report

I've liked this concept ever since I saw the packaged applications use this technique:


I've finally gotten around to posting this in part due to some feedback on my sample application.

I used Chrome's inspect element tool to extract the CSS required to create this effect.
According to my notes I first did this when the Bug Tracking application used Theme 24.

You can then modify this to suit your requirements. I've changed the buttons (more...)

Korrigierte CBO-Cardinality-Schätzungen für OR-Prädikate

Stefan Koehler weist in seinem Blog darauf hin, dass ein von Jonathan Lewis in Cost Based Oracle beschriebenes Problem in aktuelleren Releases behoben ist: OR-verknüpfte Range-Prädikate, die den kompletten Wertebereich einer Tabelle umfassen, werden nun in ein einfaches IS NOT NULL Prädikat umgewandelt. Das handliche Beispiel dafür ist in Blog und Buch eine Monatsliste mit der dafür definierten Einschränkung:
where MONTH_NO > 8 OR MONTH_NO <= 8
In 10.2.0.5 führte diese Ausgangslage (more...)

AU/X, NeXTSTEP, and Mac OS X

One thing that gets tedious in the IT community and Oracle community is the penchant for Windows only solutions. While Microsoft does an excellent job in certain domains, I remain a loyal Apple customer. By the way, you can install Oracle Client software on Mac OS X and run SQL Developer against any Oracle Database server. You can even run MySQL Workbench and MySQL server natively on the Mac OS X platform, which creates (more...)

DIY Disasters

When people take on home renovation “Do-It-Yourself” projects beyond their skills, disaster ensues. Apparently, this is so common that you can base a whole TV series on this theme – where professionals rescue the disastrous DIY project for the grateful and clueless amateur handyman.

I’ve seen the same thing happen in many IT projects. The people in the organization overestimate their own skills and are unable or unwilling to pay the cost of professional external (more...)

I Love Logs

It occurred to me a few days ago, as I was reading this article on DevOps, that I might actually be a DevOps.

I think of myself as a developer, but my current role is in a small team running a small system. And by running, I mean that we are 

  • 'root' and 'Administrator' on our Linux and Windows servers
  • 'oracle / sysdba' on the database side, 
  • the apex administrator account and the apex workspace (more...)

Zur Semantik der ON clause im ANSI Left Outer Join

Vielleicht wäre es sinnvoller, auf diesen Eintrag zu verzichten, weil er ein wenig peinlich ist - aber was soll's: gestern ist mir im Rahmen eines OTN Threads aufgefallen, dass meine Interpretation der ANSI OUTER JOIN Syntax bislang unzutreffend war. Ich verzichte auf nähere Erläuterungen zu meiner Fehleinschätzung (die für meine übliche Formulierung von ANSI OUTER JOINs ganz plausibel war), und erkläre lieber gleich, was das ON tatsächlich bedeutet:

drop table t1;
drop table t2;

create (more...)

SQL Developer’s Interface for GIT: Interacting with a GitHub Repository Part 2

In this post I’m going to show to synchronize the remote and local repositories after an existing file in local gets modified. What I’ll do is modify the sp_test_git.pls file in our local repository and then push those changes to the remote repository (GitHub).

First, I proceed to open the sp_test_git.pls file using SQL Developer, add another dbms_output line to it and save it. The moment I save the file, the Pending Changes (more...)

Show APEX IR detail view by default

Since the Detail/Icon views were introduced to Interactive Reports in Oracle APEX 4.0, I've used the detail view a number of times to represent information in ways that don't even look like your standard columnar reports.

One example you may have seen was the original apexblogs.info - this was a detail view IR showing aggregated posts of APEX bloggers.
It's now hosted at odtug.com/apex and this appears to use an alternative method (more...)

Unfocused Learning

The manager was proudly showing off his new IT classroom.

“Where is the projector?” I asked.

“Oh, we don’t need a projector. The instructor can just take over everybody’s screens.”

I have been teaching in environments like this, and it does not work well. When I have a piece of information or a computer screen projected in large format in front of everybody, the class has a common focus on the task. If (more...)

Read Committed Isolation Level im SQL Server

Im SQLPerformance.com Blog hat Paul White einige interessante Punkte zur Implementierung des Isolation Levels Read Committed im SQL Server aufgeschrieben. Grundsätzlich gibt es im SQL Server dieser Tage zwei Implementierungen für Read Committed, von denen die ältere (zumindest vom Herrn White) als Locking Read Committed bezeichnet wird. Da der Autor einen weiteren Artikel zum Thema ankündigt, starte ich an dieser Stelle eine Aufzählung:

Best UTL_FILE Practice

In a post a couple days ago, I promised to provide a best practice approach to reading external files with the UTL_FILE package. My first assumption is that you’re reading unstructured data because structured data is best read by external tables because external tables can read data much faster with the PARALLEL option.

My second assumption is that you’re you don’t know how to use or choose not to use the DBMS_LOB package; specifically, (more...)

IT Defence in Depth

The Heartbleed bug has shown that security vulnerabilities can pop up everywhere. Unfortunately, many IT organizations depend on a single security layer to secure their network – and as the ineffectiveness of the Maginot Line proved, that is a risky strategy. You need multiple security layers – what soldiers call Defence in Depth.Security LayersThis illustration is from my weekly Technology That Fits newsletter – sign up here.

Using UTL_FILE

Sometimes I’m surprised. Today, the surprise came when somebody pointed to an error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters:

DECLARE
*
ERROR at line 1:
ORA (more...)

Unique identifiers – but what do they identify

Most of the readers of this blog will be developers, or DBAs, who got the rules of Normalisation drummed into them during some phase of the education or training. But often we get to work with people who don't have that grounding. This post is for them. Feel free to point them at it.

Through normalisation, the tendency is to start with a data set, and by a methodical process extract candidate keys and their (more...)

In Defense of Joins – Recap

Over the past few articles we’ve looked at database joins. It started out with me noticing that joins appear to be getting bad press recently and wondering whether they really are the root of all evil that some people seem to think they are.

We seen that denormalizing removes joins, giving performance benefits to primary key lookups. This benefit can come at the cost of non-primary key lookup queries though – often the extra (more...)