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 ZFS Einführungspromotion

Die Oracle ZFS Storage Appliance – Einführungspromotion

Die extrem schnelle Backup- und Restore-Appliance für Oracle EXADATA, Oracle PCA oder wahlweise auch als Online-Storage Erweiterung für Oracle Engineered Systems.

Die Oracle ZFS Storage Appliance bietet gegenüber anderen Lösungen unschlagbare Vorteile, wenn es darum geht, geschäftskritische Anwendungen zu beschleunigen, die Produktivität zu steigern und dabei gleichzeitig Ressourcen zu sparen, Risiken zu reduzieren und die Gesamtbetriebskosten (TCO) zu senken.

Von anspruchsvollen Oracle-Datenbankumgebungen, über große virtualisierte Umgebungen bis hin zu traditionellen Serverumgebungen spannen sich die empfohlenen Einsatzgebiete.

Maximale Performance

Profitieren Sie von der schnellen, vollständig auf Flash basierenden Performance dank des Hybrid Storage Pools für dynamische Workloads, von effizienter Konsolidierung und vielen einzigartigen Funktionen wie zum Beispiel der „Hybrid Columnar Compression“, oder dem einzigartigen „Oracle Intelligent Storage Protocol“, durch die Sie Ihre Oracle Datenbanken schneller denn je ausführen können und gleichzeitig einen besonders attraktiven TCO erzielen können

Konfigurationen

Die Oracle Oracle ZFS Storage ZS7-2 ist in den Konfigurationen „Mid-range“ und „High-End“ erhältlich.

Beide Konfigurationen sind wirtschaftlich überzeugende Enterprise-Speicherlösungen, die für hohe Workloads mit extrem hohen Leistungsanforderungen ausgelegt sind.

Im Backup Umfeld glänzt die Storage Appliance durch unterschiedliche Block basierende „Data Reduction“ Funktionen wie „Inline Compression“ oder „inline Deduplication“, transparent zur Applikation oder der eingesetzten Backup-Software.

Einführungspromotion

Ab sofort bis zum 27.Februar 2019 ist für die neue Generation ZS7 eine Einführungspromotion mit 40% Discount erhältlich.
Dafür gelten besonderen Bedingungen, über die wir Sie gerne persönlich informieren.

 

Veranstaltung: Datenbank Performance Killer aufspüren und beseitigen

Vielleicht kennen Sie das: Ihre Datenbank wird ohne ersichtliche Gründe immer langsamer.
Die Nutzer beschweren sich über immer länger dauernde Abfragen?

In vielen Fällen sind die Gründe für Performance-Einbruche tief in der Datenbank-Logik versteckt, sodass auch Hardware Upgrades nur für kurze Zeit eine Verbesserung bringen können.

Oracle stellt mit dem Diagnostic Pack und Tuning Pack zwei sehr gute Tools zur Verfügung. Allerdings sind diese ausschließlich für die Enterprise Edition der Datenbank erhältlich und müssen analog zur Datenbank lizenziert werden.

Lernen Sie zwei sehr interessante Lösungen kennen

Quest hat seit vielen Jahren mit der Toad DBA Suite und mit Foglight zwei sehr beeindruckende Lösungen im Angebot, die auf allen Oracle Datenbank Editionen eingesetzt werden können und im Leistungsumfang das Diagnostic und Tuning Pack sogar übertreffen.

Noch dazu zu wesentlich geringeren Lizenzkosten!

In diesem Vortrag stellen wir gemeinsam mit einem Spezialisten von Quest die Stärken der beiden Lösungen Toad DBA Suite und Foglight im Detail vor.

Wir zeigen Ihnen, wie Sie damit Performance Probleme sehr einfach aufspüren, analysieren und beheben können, um Ihre Datenbank wieder auf die gewohnte Geschwindigkeit zu beschleunigen.

Details zur Veranstaltung

Ort der Veranstaltung: DBconcepts GmbH, Lassallestraße 7a Unit5, 1020 Wien

Der Eingang zur Unit5 befindet sich auf der Lassallestraße genau zwischen den Eingängen zu den Hotels Ibis und Ibis Budget Hotel.

Datum: 11. Dezember 2018

Beginn: 15:30h

Im Anschluss laden wir Sie sehr herzlich auf heiße (Punsch) Getränke und kleine Speisen zum Adventmarkt im Alten AKH ein. Für den Transport von der Veranstaltung zum Adventmarkt ist gesorgt. Bitte geben Sie bei der Anmeldung bekannt, ob Sie beim Punschtrinken teilnehmen werden.

Die Teilnahme zur Veranstaltung und zum Punschtrinken ist kostenlos.

 

Zur Veranstaltung anmelden:

2 + 5 = ?

 

 

Weiterführende Informationen zu den oben genannten Lösungen:

Toad DBA Suite for Oracle Whitepaper  |  Quest Foglight – DE Webseite