CBQT-ORE and its FIRST_ROWS optimization inability

CBQT-ORE and its FIRST_ROWS optimization inability

Terminology:

CBQT: cost based query transformation
ORE: or expansion

Some days ago, I took a look on a customer’s performance issue which was introduced after their upgrade from 12.1 to 19c. Quite fast we could narrow it down to a new optimizer feature „cost based or expansion“.

To get a basic understanding of the feature, I’ll recommend reading Optimizer Transformations: OR Expansion by Oracle’s CBO PM Nigel Bayliss.

As a quick fix we simply disabled the feature by setting „_optimizer_cbqt_or_expansion“ = off and the performance went back to good again.

Afterwards I wanted to understand the root cause and built a model to reproduce the problem.

rem ######################################
rem # set environment                    #
rem ######################################
alter session set statistics_level=ALL;

rem ######################################
rem # prophylactic cleanup               #
rem ######################################
drop table asc_t2;
drop table asc_t1;
drop table asc_t3;

rem ######################################
rem # create testdata                    #
rem ######################################

--will hold 1M rows with a unique ID
create table asc_t1
as
with gen as
(
   select rownum dummy from dual connect by level <= 1e4
)
select rownum id
  from gen, gen
 where rownum <= 1e6;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t2
as
select id, mod(id, 500) t1_id, lpad('*', 250, '*') pad
  from asc_t1;

--will hold 1M rows with 500 distinct values of T1_ID (20000 records per value)
create table asc_t3
as
select id, t1_id, pad
  from asc_t2;

--indexes
create unique index asc_t1_uk on asc_t1 (id);
create unique index asc_t2_uk on asc_t2(id);
create index asc_t2_i1 on asc_t2(t1_id);
create unique index asc_t3_uk on asc_t3(id);
create index asc_t3_i1 on asc_t3(t1_id);

And this is the query to be examined:

select t1.*
  from asc_t1 t1
 where id between 1 and 10
   and exists (select 1
                 from asc_t2 t2, asc_t3 t3
                where t2.id = t3.id
                  and (t2.t1_id = t1.id or t3.t1_id = t1.id)
              );

With no hints or further parameters set, this is the plan including some rowsource execution statistics pulled from memory after its execution:
So at first we can see that ORE was used and the query was split into two disjunct union all branches. What’s really interesting for me at a first sight is the presence of a blocking operation in the VW_ORE block (HASH JOIN at ID 4), despite the fact it is called in an exists clause and therefore has the ability to leave after the first row is found.

So for each row we got from ASC_T1 an in-memory hash table was build after scanning the appropriate index on T2 and visiting the table block (IDs 6 and 5).  In total 20.000 rows on 20.078 buffers were read on these operations. After that index ASC_T3_UK was probed against that hash table and here we see the effects of “exists” very cleary:  despite the index contains 1 million entries just 55 rows over all 10 calls needed to be read to find a first match and therefore be able to quit, because the exists clause was fulfilled. The second union-all branch wasn’t called at all.

Let’s look at two more examples.

First: forbid CBQT-ORE from kicking in.

select t1.*
  from asc_t1 t1where id between 1 and 10 
 and exists (select /*+ no_or_expand */1
                          from asc_t2 t2, asc_t3 t3
                        where t2.id = t3.id
                              and (t2.t1_id = t1.id or t3.t1_id = t1.id)
                            );

And its rowsource execution statistics:
It has got a much lesser cost than the ORE plan and much less buffers were visited to execute the query. It gives also the impression that the CBO is now aware that it has to deal with an “exists” clause here, because that part of the plan was optimized to find a first matching row very quickly. E.g low cost for the FTS on ASC_T2 or the absence of blocking operations are indicators for this strategy.

Second: Switch back to LORE (Legacy OR Expansion)

select /*+ opt_param('_optimizer_cbqt_or_expansion', 'off') */ t1.* 
  from asc_t1 t1
 where id between 1 and 10
    and exists (select 1
                             from asc_t2 t2, asc_t3 t3
                          where t2.id = t3.id
                                and (t2.t1_id = t1.id or t3.t1_id = t1.id)
                            );

Again rowsource execution statistics:
This one now shows the lowest cost and least buffers visited overall. It also seems to be aware of the exists clause and adapts a first_rows strategy in the relevant parts of the execution plan! This is basically what I would have expected from the CBQT-ORE.

So we now have a theory that CBQT-ORE loses track that its query block is called in an “exists” clause and provides an execution plan as if it was a standalone query, where it would need to fetch all the rows.

There’s some more evidence to this theory.

1.) The transformed query from the VW_ORE query block shows the same cost and execution plan when it is costed as a standalone query. The correlated value from the outer rowsource was replaced with a bind variable in the following example:

select 0
  from (
             select 1
                 from asc_t3 t3, asc_t2 t2
              where t2.id = t3.id and t2.t1_id = :b1
            union all
           select 1
               from asc_t3 t3, asc_t2 t2
            where t2.id = t3.id      
                 and t3.t1_id = :b1
                 and lnnvl (t2.t1_id = :b1)               );

generates this plan, which matches the “VW_ORE_82971ECB” from our first query.



2.) The 10053 traces for both the disabled CBQT-ORE and the old-style OR-Expansion (LORE) show lots of references that a first_rows optimization approach was chosen for the query blocks in question.

Like:

On the other hand when looking at the 10053 trace of the first query, interestingly before the ORE checks kicked in, the same plan was already found which was used in query 2, where I explicitly disabled the transformation.

Final cost for query block SEL$2 (#2) – First K Rows Plan:
  Best join order: 1
  Cost: 506.142990  Degree: 1  Card: 2.000000  Bytes: 71964.000000
  Resc: 506.142990  Resc_io: 506.000000  Resc_cpu: 4233492
  Resp: 506.142990  Resp_io: 506.000000  Resc_cpu: 4233492

Later in the same tracefile, the ORE transformation and costing was performed and produced a final cost (8591) which was (way) higher than the formerly calculated plan with a final cost of 506. But however at the end it was not picked and the CBO stayed with the cheapest ORE-plan.

Summary:
CBQT-ORE does not seem to be aware when its query block resides in an “exists” clause and therefore doesn’t optimize for first rows access patterns. The query block gets optimized as if all rows would be needed to fetch. Additionally in certain scenarios it seems to “forget” if cheaper plans were found during the whole optimization process. If this effects you in a negative way, as a first action the cost based transformation can be turned off and the legacy version used instead. Additionally an SR was raised to tackle this issue.

Further Reading:
Excellent articles on the topic of CBQT Or-Expansion can also be found on the blogs of Patrick Joliffe, Mohamed Houri and Nenad Noveljic

Update 1:
After playing with the same testcase in my 21c lab I noticed the that the problem went away. After quick look into v$system_fix_control I found bug 28414968 “expansion with some constant branches;fkr1 in (NOT)EXISTS subque” which is first available in 19.11 and has to be activated proactively. That plan now gets produced in in 21c and after setting alter session set „_fix_control“=’28414968:3′; in >=19.11
10 buffers less visited compared to the LORE plan, as ID 8 was superfluous in the LORE plan.

Funnily, if I code the union-all instead of the or-predicate myself, I still get the old plan. But that’s one topic for another post I guess.

select t1.id id
  from demo.asc_t1 t1
 where t1.id >= 1
   and t1.id <= 10
   and exists (select 0
                            from demo.asc_t3 t3, demo.asc_t2 t2
                         where t2.id = t3.id
                               and t2.t1_id = t1.id 
                         union all
                         select 1
                             from demo.asc_t3 t3, demo.asc_t2 t2
                          where t2.id = t3.id
                                and t3.t1_id = t1.id
                                and lnnvl (t2.t1_id = t1.id)
                            );

Sommerparty Banner

Das war die DBConcepts Sommer-Party 2021

Zum vierten Mal fand heuer wieder unsere legendäre DBConcepts Sommerparty in der La Creperie an der Alten Donau statt. Nach einem langen Jahr der Zwangspausierung haben wir eine Rekordzahl an Anmeldungen für unsere Sommer Party entgegengenommen und über 70 TeilnehmerInnen begrüßen können.

Doch diese Party war nicht nur besonders da es unsere erste nach dem Corona Jahr sein sollte. Gemeinsam mit unseren Kunden, Partnern und Kollegen haben wir das 20jährige Jubiläum der DBConcepts gefeiert, zwar nachgefeiert.. aber zumindest gebührend.

Gemeinsam haben wir einen entspannten Abend mit einigen Highlights verbracht. Als Willkommensgeschenk und gab es heuer nicht nur unsere sehr beliebten Sonnenbrillen, sondern auch ein großartiges Strandtuch mit Unterstützung der Arrow.

Nach Meteorologischer Ansage sollte unsere Tretbootchallenge auch dieses Jahr eher ins Wasser fallen, doch dieses Mal war auch für eine Alternative gesorgt – das erste DBConcepts PubQuiz.

Insgesamt 13 Teams haben es sich zur Aufgabe gemacht, die fünf verschiedenen Themenblöcke in kürzester Zeit zu beantworten. Die Themen rangierten von DBConcepts Wissen über Popkultur bis hin zu geografischen Kenntnissen. Die glorreichen Sieger durften sich über tolle Preise freuen!

Sehr besonders freut uns das begeisterte Feedback aller Gäste, die uns zur gelungen Sommer-Party gratuliert haben.

Nach der Sommer-Party ist immer vor der Sommer-Party.

In diesem Sinne freuen wir uns schon auf nächstes Jahr!

Business Continuity Management

Business Continuity Management

Postgresql Cheat Sheets

PostgreSQL Cheat Sheets – Sammlung

Für PostgreSQL finden sich einige sehr nützliche PostgreSQL Cheat Sheets, um im Alltag mit der Datenbank eine schnelle und nützliche Referenz zur Hand zu haben.

Die korrekte Übersetzung von Cheat-Sheet lautet „Schummelzettel“.

Allerdings zielt die Verwendung in erster Linie nicht auf das Schummeln ab, sonder es handelt sich vielmehr um eine übersichtliche Zusammenstellung von wichtigsten Details zu einem ganz bestimmten Thema.

Im Idealfall sollte die Länge von einem A4 Blatt nicht überschritten werden, aber es gibt natürlich auch umfassendere Cheat-Sheets.

Zum Thema PostgreSQL haben wir folgende nützliche Cheat-Sheets gefunden:

[Link ] PostgreSQL Cheat Sheet von postgresqltutorial.com – 3 Seiten

In diesem Cheat Sheet werden folgende PostgreSQL Themen behandelt:

  • quering data from table
  • quering from multible tables (verschiedene Joins – left join, right join, outer join, cross join, usw..)
  • SQL operatoren – union, intersect, except, like – not like, in – not in, between and, is null – is not null
  • Table Management – create, drop, alter, truncate, etc…
  • SQL Constraints – primary key, foreign key, unique, check, usw..
  • Data Modification – insert into values, insert into select * from table, update set, delete from, etc…
  • View Management – create view as select, create recursive view, create temporary view, etc…
  • Index Management – create index, create unique index, drop index
  • SQL Aggreate Functions – avg, count, sum, max, min
  • Trigger Management – create trigger when event, etc…

[ Link ] Postgresql Cheat Sheet von alberton.info – 1 Seite

In diesem Cheat-Sheet werden folgende PostgreSQL Themen behandelt:

  • Data Types
  • Internal Functions
  • Usefull Queries
  • Information Shema

[ Link ] PostgreSQL Sting Functions Cheat Sheet von SQLBackupAndFTP.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL String Functions behandelt:

  • Conversion
  • Measurement
  • Modification

[Link ] Postgresql terminal commands – cheatography.com – 1 Seite

In diesem Cheat-Sheet werden PostgreSQL terminal commands behandelt:

  • Connecting
  • PSQL
  • Roles and database management
  • Database backup

Informationen zu unseren professionellen PostgreSQL Managed Services finden Sie hier 

 

 

Webservices direkt aus der DB (XMLDB)

Übersicht

Web Services werden heutzutage immer öfter benutzt um Daten unabhängig vom Standort, zwischen zwei Applikationen auszutauschen und Funktionen aufzurufen.

Oracle stellt mit den Native Oracle XML DB Web Services eine Möglichkeit zur Verfügung, die es erlauben SQL und XQuery Abfragen an einen Host zu senden.

Außerdem kann auf PL/SQL Stored Procedures und Functions zugegriffen werden.

Dabei unterstützt Oracle XML DB das Netzwerkprotokoll SOAP 1.1. Durch die HTTP POST Methode werden die SOAP Requests an die Oracle XML DB Web Services übermittelt.

Der Standort der Web Services und WSDL Dokumente können in der Oracle XML DB Datei xdbconfig.xml konfiguriert werden.

Konfiguration

Um die Web Services zu aktivieren, ist es zuallererst notwendig dem Datenbankbenutzer als SYS User die XDB_WEBSERVICES Rolle zuzuweisen.

Durch die Zuweisung der Rolle können die Web Services benutzt werden. Standardmäßig ist die Benutzung via HTTPS freigeschalten.

Dazu können noch weitere Rollen vergeben werden:

XDB_WEBSERVICES_OVER_HTTP – Benutzung via http

XDB_WEBSERVICES_WITH_PUBLIC – Zugriff auf PUBLIC Datenbank-Objekte

Hat ein User die Zugangsberechtigung auf eine Datenbank mittels Web Services so kann er nur auf die ihm zugewiesenen Datenbank-Objekte zugreifen.

Mit XDB_WEBSERVICES_WITH_PUBLIC kann er nun auch auf PUBLIC Objekte zugreifen.

Zugriff

Das Web Service für Datenbank-Abfragen befindet sich auf http://host:port/orawsv.

Bei host:port handelt es sich um den Datenbank-Host und HTTP(S)-Port. Der Pfad enthält eine WSDL-File, die eingehende und ausgehende Dokumente in XML spezifiziert.

Um auf Stored Procedures und Functions zugreifen zu können, muss http://host:port/orawsv/dbschema/package/fn_or_proc angewählt werden. Host:port enthält wieder den Datenbank-Host und  HTTP(S)-Port. Fn_or_proc gibt dabei die Procedure bzw. Function an.

Mit dbschema wird das Datenbank-Schema angegeben.

Sollte eine Procedure oder Function außerhalb eines Packages sein, so kann package ausgelassen werden.

Das war die Sommer-Party 2018 mit Tretboot-Challenge

Circa siebzig Kunden, Partner, Freunde und Kollegen feierten am 30. August mit uns bei coolen Grooves und köstlichem Grill-Buffet auf unserer Sommer-Party.

Aus der Sommer-Party wurde zur späteren Stunde durch leichten Regen leider eine Sommer-Abschieds-Party.

Die Location an der Alten Donau bot aber auch Indoor genügend Platz für alle Party-Gäste und so nahm die Feier trotz Regen keinen Abbruch.

Auch heuer wurde wieder unsere DBConcepts-Tretboot Challenge ausgetragen, wo 14 tapfere Teams um den Sieg kämpften.

Der Wettergott zeigte sich gnädig mit allen Athleten, sodass erst pünktlich nach dem letzten Team die ersten Regentropfen fielen.

Da es bei der Tretboot-Challenge nur zwei Regeln gibt, blieb für jedes Team genügend Spielraum um die eigene Taktik zu optimieren. Daher sahen wir heuer zum Ersten Mal auch einen waghalsigen Les-Mans Start und viele abenteuerliche Anlegemanöver.

Der Kampfgeist wurde auf jeden Fall bei allen Teilnehmern geweckt und die Pedale bis zum Wadlbrennen getreten.

Schließlich wurden zwei Teams mit der exakt gleichen Siegerzeit von 1:46 gemessen!

Wir gratulieren den beiden Siegerteams „Cyclist“ und “ France“ und auch allen anderen Wettkämpfern, die keine Herausforderung scheuen und den Wettkampf auf sich genommen haben.

Unglaublich wie knapp die Abstände der einzelnen Teams waren. Letztlich lag es meistens nur am Ab- und Anlegemanöver wo wertvolle Sekunden verloren gingen.

Die Rangliste der Challenge finden Sie weiter unten in diesem Blog Beitrag.

Wir freuen uns, dass so viele Kunden, Partner, Freunde und Kollegen trotz Schlechtwetter den Weg zu uns gefunden haben und schönen und lustigen Abend mit uns verbracht haben!

Aus dem Feedback der Teilnehmer können darauf schließen, dass auch nächstes Jahr eine Sommer-Party stattfinden soll 😉

Ein paar Impressionen zur Sommer-Party 2018:

 

 

Rang Team Name Zeit
1 Cyclist 01:46
1 France 01:46
3 Alex&Alex 01:47
4 Deine Mudda 01:54
5 Kittl & Kittl 01:58
6 TurboKurbler 01:59
7 Titanic 02:02
7 Nix überlegt 02:02
9 Spontan 02:06
10 Vergessen 02:07
11 Borgs 02:17
12 Old Stars 02:24
13 AKH01 02:35

see you next year!

Workaround: Unpivot mit ORA-00942 oder ORA-00904

Ausgangslage

Kürzlich trat ein Problem mit einem UNPIVOT Operator in der Entwicklung einer Statistik-Abfrage auf

Select [...]
 from VIEW1 unpivot(val for year in ([...]);
ORA-00942: table of view does not exist

Die Abfrage wurde im gleichen Schema ausgeführt wo auch der View sein sollte. Folgendes Select funktionierte zum Beispiel problemlos:

select * from VIEW1;

Lösung

Eine Recherche ergab anfangs wenig Lösung. Die üblichen Lösungsvorschläge bezogen sich auf Problem mit Arbeiten in unterschiedlichen Schemas oder über Datenbank-Links. Da die Tabelle aber grundsätzlich als vorhanden erkannt wurde musste der Grund woanders liegen. Im Oracle Support habe ich die Lösung gefunden, im Dokument Doc ID 22339954.8.

Laut diesem Dokument handelt es sich hierbei um einen Bug in potentiell allen Datenbankversionen vor 12.2.0.1. Laut diesem Dokument gibt es keinen Workaround. Das Problem kann ich außerdem in langen Laufzeiten äußern selbst wenn keine Fehler auftreten. Die Probleme treten lt. Dokument auf wenn eine Abfrage eine UNPIVOT Operation auf Views mit mehrfachen verschachtelten Abfrageblöcken und/oder einer großen Anzahl von Tabellen macht.

Wir haben einen Workaround gefunden der in unserem Fall das Problem recht leicht und zufriedenstellend gelöst hat:

with base as (select /*+ materialize */ * from VIEW1)
Select [...]
from base unpivot(val for year in ([...]);

Problem und Lösung mit Klick auf URL in Outlook 2010 unter Win10

Ich hatte nach dem Umstieg auf Windows 10 das Problem, dass ich keine URLs aus Outlook 2010 mehr öffnen konnte. Ich bekam immer folgende Fehlermeldung:

Fehlermeldung Outlook 2010 bei Klick auf URL

Nachdem mir unsere Admins versichert haben, dass es hier keine Gruppenrichtlinie gibt, die das verhindert, musste ich mich auf die Suche nach einer Lösung machen.

Ein bisschen googeln und man findet folgenden Artikel:

https://support.microsoft.com/de-de/help/310049/hyperlinks-are-not-working-in-outlook

Dieser Artikel ist schon sehr lösungsschwanger, aber:

Es gibt zwar einen Button im KB Artikel zu einem Fixit Tool für Windows 10, 8.1 und 8, aber der führt nur zu der allgemeinen Beschreibung, wie man ein FixIt Tool benutzt. Das Fixit Tool für Windows 7 oder früher lässt sich nicht unter Windows 10 ausführen.

Na gut, es gibt ja auch eine Beschreibung, wie das manuell erledigt werden kann, aber:

Methode 1: Zurücksetzen der Internet Explorer-Einstellungen

Problem: Es gibt keinen Internet Explorer in Windows 10!

Methode 2: Exportieren des Registrierungsschlüssels von einem anderen Computer

Tja, keinen im Zugriff. Was jetzt?

Nach längerem Recherchieren kam ich auf folgende Lösung:

  1. Registrierungseditor öffnen (regedit.exe)
  2. Zu folgenden Schlüssel navigieren: HKEY_LOCAL_MACHINE\htmlfile\shell
  3. An dieser Stelle folgende Unterschlüssel erstellen (falls nicht vorhanden – was wahrscheinlich der Fall ist, weil sonst das Problem ja nicht auftreten würde): \open\command
  4. Hier unter „(Standard)“ oder „(Default)“ folgendes eintragen:
    „%systemroot%\system32\rundll32.exe“ „%systemroot%\system32\url.dll“,FileProtocolHandler „%1“

So sollte es im Registrierungseditor schlussendlich aussehen:

Registrierungseditor mit korrekten Key Eintrag

Jetzt Outlook neu starten.
Ich kann natürlich nicht versprechen, dass diese Lösung bei jedem funktioniert, aber bei mir tat es das.

Ich hätte mich gefreut, wenn ich diese Lösung früher gefunden hätte. Es gibt zwar ein paar Artikel, die diese Lösung fast vorschlagen, allerdings unter folgenden Registrierungsschlüssel:

HKEY_LOCAL_MACHINE\htmlfile\shell\opennew\command

Bei mir hat es aber nur unter HKEY_LOCAL_MACHINE\htmlfile\shell\open\command  funktioniert.

 

Auftakt der JavaOne 2015: Geburtstagsfeier ohne große Neuigkeiten

Oracle bot während der Eröffnungs-Keynote der weltweit größten Java-Konferenz eine Zusammenfassung dessen, was von Java 9 zu erwarten sein wird und was danach kommen wird.

Quelle: Auftakt der JavaOne 2015: Geburtstagsfeier ohne große Neuigkeiten | heise Developer