Beiträge

Datenbank Performance Problem und Lösung

Performance Problem: Eine parallele Abfrage stellt ihre Arbeit ein

Kürzlich traf ich bei einem Kunden auf einen ungewöhnlichen Bug der Oracle Version 12.2.
Es handelt sich um eine parallele Abfrage, die scheinbar einfach anhält und ihre Arbeit einstellt. 🙁

Bei näherer Betrachtung erkennt man eine interne Verklemmung zwischen den Koorditinatorprozess und einem der Parallelprozesse. Das Ganze ähnelt einem Dead Lock!

Problem Analyse

Beide Prozesse warten auf „table queue“ Kommunikation.

Der Query Coordinator wartet mit „PX Deq: Execute Reply“ und der blockiernde Parallel Process wartet mit „PX Deq: Table Q Normal“.

Der Rest der Parallelprozesse warten mit dem event „PX Deq: Execution Msg“.

Damit es zum besagten Problem kommt, muß auch eine analytic_function beteiligt sein.

Im Kern geht es darum, wie Oracle den Window Sort parallelisiert, der mit einer analytic function zwangsweise verbunden ist.

In früheren Oracle Versionen war dieser Sort of weniger effizient als ein regulärer Sort und daher entsprechend langsamer.

In dem sehr gute Post von Phythian’s Christo Kutrovsky wird das Thema im Detail beschrieben: Oracle parallel query hints reference – part 5: PQ_DISTRIBUTE_WINDOW

Problem Lösung

Für unsere Zwecke genügt es zunächst festzuhalten, dass es drei Methoden gibt, wie ein Window Sort parallelisiert werden kann.

Methode 3 ist die bisher verwendete Methode, Methode 1 und 2 sind neu in Version 12. Wenn Methode 2 verwendet wird, kann es zum oben beschriebenen Bug kommen.

Mein Kollege Andreas Schlögl hat einen Testcase erstellt und gezeigt, dass man mittels des neuen  PQ_DISTRIBUTE_WINDOW Hint den Bug umgehen kann, in dem man auf Methode 1 umstellt.

Den Code des Testcases finden Sie hier. Viel Spass beim ausprobieren!

rem ##################################
rem # Objects                        #
rem ##################################

alter session set optimizer_adaptive_plans = false;
alter system flush shared_pool;

drop table asc_dmy1;
drop table asc_dmy3;

create table asc_dmy1
parallel 8
as 
select 'AAA' f001
  from xmltable('1 to 300');
  
--note: this table has no parallel degree
create table asc_dmy3
as
select 'AAA' f001, 1 acc206
  from dual;

rem #############################################
rem # SORT then distribute by HASH (Bug)        #
rem #############################################  
/*
   leads to a HASH JOIN in Line 7, which imo must be a HASH JOIN BUFFERED (due to 2 active PX SENDs at 9 and 13) 
   This SQL hangs and never finishes 
   
   https://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html
   "At most one data distribution can be active at the same time"
   
   "Since it doesn't seem to be supported to have two PX SEND operations active at the same time, 
    some artificial blocking operation needs to be introduced, in this case the HASH JOIN BUFFERED, 
	that first consumes the second row source completely before starting the actual probe phase"
*/
select /*+ pq_distribute_window(@"SEL$1" 2) */
       max(v.acc206) over (partition by v.f001) max_bew
  from asc_dmy3 v,
       asc_dmy1 e
 where e.f001 = v.f001
   and v.f001 = e.f001;  

/*   
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |   419 |     6  (17)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR              |          |        |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10003 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    WINDOW CONSOLIDATOR BUFFER|          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE               |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH            | :TQ10002 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       WINDOW SORT            |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN             |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE           |          |      1 |   415 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH        | :TQ10000 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |
|  10 |           PX SELECTOR        |          |        |       |            |          |  Q1,00 | SCWC |            |
|  11 |            TABLE ACCESS FULL | ASC_DMY3 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | SCWP |            |
|  12 |         PX RECEIVE           |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  13 |          PX SEND HASH        | :TQ10001 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  14 |           PX BLOCK ITERATOR  |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  15 |            TABLE ACCESS FULL | ASC_DMY1 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------   
*/

rem #############################################
rem # distribute by HASH then SORT  (Success)   #
rem #############################################  
/*
   leads to a HASH JOIN *BUFFERED* in Line 6, which is inevitably necessary imo
   This SQL finishes immediately
*/ 
select /*+ pq_distribute_window(@"SEL$1" 1) */
       max(v.acc206) over (partition by v.f001) max_bew
  from asc_dmy3 v,
       asc_dmy1 e
 where e.f001 = v.f001
   and v.f001 = e.f001;    

/*
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |        |       |     6 (100)|          |        |      |            |       |       |          |
|   1 |  PX COORDINATOR            |          |        |       |            |          |        |      |            | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)      | :TQ10003 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |       |       |          |
|   3 |    WINDOW SORT             |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            | 20480 | 20480 |     8/0/0|
|   4 |     PX RECEIVE             |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,03 | PCWP |            |       |       |          |
|   5 |      PX SEND HASH          | :TQ10002 |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |       |       |          |
|*  6 |       HASH JOIN BUFFERED   |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |  3400K|  3091K|     8/0/0| 
|   7 |        PX RECEIVE          |          |      1 |   415 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |
|   8 |         PX SEND HASH       | :TQ10000 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |       |       |          |
|   9 |          PX SELECTOR       |          |        |       |            |          |  Q1,00 | SCWC |            |       |       |          |
|  10 |           TABLE ACCESS FULL| ASC_DMY3 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | SCWP |            |       |       |          |
|  11 |        PX RECEIVE          |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |
|  12 |         PX SEND HASH       | :TQ10001 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |       |       |          |
|  13 |          PX BLOCK ITERATOR |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |       |       |          |
|* 14 |           TABLE ACCESS FULL| ASC_DMY1 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
*/
Oracle Datenbank Upgrade 12c

Upgrade auf Oracle 12c

Vieles scheint sich zu verändern: Ganze Datacenter wandern in die Cloud, neue Lösungsangebote wie Database as a Service betreten den Markt, der Trend zur Virtualisierung scheint ungebrochen, Solid State Drives werden herkömmliche Festplatten wohl bald gänzlich ersetzen, Big Data und Datamining sind in aller Munde.

Doch manches verändert sich nicht so schnell: Genauso wichtig wie unternehmerische Innovationen und technologische Paradigmenwechsel ist die Fortführung von bewährten Technologien. Entgegen mancher kurzzeitiger Moden bildet das relationale Datenmodell immer noch das stabile Fundament für viele Anwendungen.

Die IT-Landschaft verändert sich in rasendem Tempo, und Oracle bildet hier keine Ausnahme.
Im Gegenteil: Mit dem Release der Datenbankversion 12c ebnet Oracle diesen Ent-wicklungen den Weg und eröffnet neue und vielversprechende Perspektiven für den Betrieb von Datenbanken, ohne dabei die Stärken einer relationalen Datenbank aufzugeben.

Mit 12c ist es Oracle gelungen, beide Anforderungen zu erfüllen:
Mit der Entwicklung von Features wie Pluggable Database und InMemory wird ein zukunftsweisender Weg aufgezeigt, mit der kontinuierlichen Weiterentwicklung der Stärken der Release 11gR2 eine Erfolgsgeschichte weitergeschrieben.

Wie sich auch entscheiden, wir möchten Sie mit unserem Know-How auf ihrem Weg unterstützen. Doch bevor die Reise beginnt, werfen wir noch einen Blick auf die Gegenwart.

Roadmap 11gR2

Wie bereits über verschiedene Kanäle angekündigt, neigt sich die Unterstützung von Release 11gR2 dem Ende zu. Die Version 11gR2 wurde im Jahr 2009 erstmals veröffentlicht, seitdem erschienen mehrere Subreleases. Momentan ist die Version 11.2.0.4 aktuell, wobei diese Version auch die letzte sein wird (Oracle spricht hier vom terminal patchset).

Das Release 11gR2 wird zwar in Form von PSUs (Patch Set Updates) noch weiter gepflegt, aktuelle Entwicklung findet aber keine mehr statt.

Release  Patching End Date

11.2.0.1  13.09.2011
11.2.0.2  31.10.2013
11.2.0.3  27.08.2015
11.2.0.4  31.01.2018

Wie aus der Tabelle ersichtlich wird, endet das Patching für die Version 11.2.0.3 noch dieses Jahr, bei 11.2.0.4 bleibt noch ein wenig mehr Zeit. Dies betrifft jedoch nur die Bereitstellung von Patches und PSUs, nicht den Support. Hier sieht es anders aus.

 

Premier Support Ends

Achtung: Der Premier Support für 11gR2 ist mit Ende Jänner 2015 ausgelaufen!

Oracle bietet die Möglichkeit, für ein zusätzliches Jahr den sog. „Free Extended Support“ in Anspruch zu nehmen.

Dies ist für alle Kunden mit einem aufrechten Support-Vertrag möglich.

Um in den Genuss von „Free Extended Support“ zu kommen, ist es erforderlich, dass sie bei Oracle einen Vertrag anfordern. Sie bekommen dann eine Rechnung über den Betrag von 0 Euro ausgestellt, der die Aktivierung des „Free Extended Supports“ ausweist. Nach Ablauf des kostenlosen „Free Extended Support“erfolgt keine automatische Verlängerung des Supportvertrags. Ab dem zweiten Jahr (also nach 31.01.2016) würden die vollen Sup-portgebühren anfallen. Sofern sie den „Free Extended Support“ beantragen, ist nach Ablauf des Vertrags nichts mehr zu machen. Sie brauchen den Vertrag weder kündigen, noch wird dieser zu den regulären Supportpreisen verlängert.

Wie aus der Tabelle (siehe unten) ersichtlich wird, ist der „Free Extended Support“ nur für die Version 11.2.0.4 von Relevanz.

Für die Version 11.2.0.3 endet der Support bereits in wenigen Monaten.

Oracle Support Roadmap

 

Release  Free Extended Support Ends

11.2.0.1 No Extended Support available
11.2.0.2 No Extended Support available
11.2.0.3 27.08.2015
11.2.0.4 31.01.2016

Der „Free Extended Support“ für 11.2.0.4 endet mit Ende Jänner 2016. Ab diesem Zeitpunkt gibt es nur mehr den kostspieligen „Extended Support“, der maximal bis Ende Jänner 2018 verlängert werden kann.

 

Release 12c

Das Datenbank-Release 12c wurde am 22.06.2013 zum ersten Mal veröffentlicht. Mittlerweile (Stand: Februar 2015) steht die Release 12.1.0.2 zur Verfügung. Allerdings betrifft dies nur die Enterprise Edition der Datenbank, für die Standard Edition ist das letzte verfügbare Patchlevel 12.1.0.1.6.

 

Edition      Release and PSU-Level

Enterprise   12.1.0.2.2 (includes PSU Jan2015)

Standard      12.1.0.1.6 (includes PSU Jan2015)

 

Ein „Direct Upgrade“ auf 12c ist nur von folgenden Versionen aus möglich:

•             ≥ 10.2.0.5
•             ≥ 11.1.0.7
•             ≥ 11.2.0.2

Sollten sie eine ältere Version im Einsatz haben, ist ein Zwischenschritt notwendig. Doch auch hier gibt es wie immer mehrere Möglichkeiten.

Wir beraten sie gerne hinsichtlich der möglichen Upgrade-Szenarien und stimmen dieses gerne auf ihre Anforderungen hinsichtlich Downtime und Applikationstests ab.

 

Neben der in Verwendung befindlichen Version des RDBMS spielt das Client-Umfeld eine entscheidende Rolle für ein erfolgreiches Upgrade auf 12c.

  • Welche JDBC-Versionen sind im Einsatz?
  • Welche Client-Versionen verbinden sich zur Datenbank?
  • Wurde am Applikationsserver ein Connection Pool konfiguriert?
  • Und wie authentifizieren sich die Clients gegenüber der Datenbank?

Das sind nur einige der Fragen, die es im Vorfeld zu erheben gilt.

Wir unterstützen sie bei der Erfassung dieser Informationen und der möglicherweise notwendigen Konfigurationsänderungen.

 

Are you ready for Oracle 12c?

Um ihnen den Umstieg auf 12c so einfach wie möglich zu machen, bieten wir ihnen umfassende Prüfung ihrer Datenbanken und ihrer Clients im Vorfeld des Upgrades an.

 

Basierend auf unserer Erfahrung, die wir bei diversen Upgrades auf 12c sammeln konnten, haben wir einen Upgrade-Check erarbeitet, der in nicht-invasiver Weise die vorhandene Datenbank-Landschaft prüft und auf mögliche Stolpersteine beim Upgrade hinweist.

 

Am Ende des Prüfvorgangs erhalten Sie von uns einen detaillierten Bericht, wie sich ein Upgrade-Szenario darstellen würde, was hinsichtlich Datenbank und Client-Umfeld zu tun wäre und mit welchen Serviceeinschränk-ungen während des Upgrades zu rechnen wäre.

Dieser Check bildet die Grundlage für ein erfolgreiches Upgrade auf 12c.

Also: Sind sie dabei? Oder anders gefragt: Are you ready for 12c?