Unterstützung der Projektplanung mit Allocatus

Situation:

Es gibt unterschiedliche Möglichkeiten, die Projekttermine an das Projektteam auszusenden. Jede dieser Möglichkeiten hat Ihre Vor- und Nachteile:

  1. Kopie des Projektplans als PDF an die betreffenden MitarbeiterInnen senden – Nachteile:
    1. Mit dem Ausdruck ist der Inhalt schon wieder alt
    2. schwierig, das richtige Format zu finden, damit möglichst alles gut lesbar ist.
  2. Alle haben MS-Project installiert und sehen dort die aktuelle Taskplanung – Nachteile:
    1. Höhere Lizenzkosten
    2. Nicht alle können und wollen mit dem Tool umgehen.
  3. Project Web App einsetzen – Nachteile:
    1. Lizenzen für Project Essentials notwendig
    2. Es sind leider viele Informationen, die zwar in der Client-Version von MS-Project dargestellt werden können, nicht aber in der Webapp.
  4. Allocatus

Vorteile:

    1. Einbindung der Tasks als Outlook-Termine ins MS Project durch ein Plug in
    2. Die Termine müssen von den MitarbeiterInnen nicht bestätigt werden.
    3. Es können auch gezielt Informationen aus den Tasks (z.B. Notizen) in diese Termine übernommen werden.
    4. E-Mail-Adressen der Mitarbeiter werden auf dem Server hinterlegt und die Mitarbeiter müssen kein neues Tool einsetzen. Die Termine werden mit Allocatus eingetragen bzw. aktualisiert.

 Nachteile:

      1. Es kostet auch Lizenzen und Wartung – ist aber überschaubar
      2. Bei der Projektplanung muss man genau unterscheiden, was bereits veröffentlicht werden kann und was noch nicht.

 

Lösung bei DBConcepts mit Allocatus:

Allocatus ermöglicht, gezielt die Termine von MS Project in die Outlook Kalender der jeweiligen Mitarbeiter zu synchronisieren.

Planänderungen (z.B. im Zeithorizont, Ressourcenzuteilung, etc.) werden im Projektteam abgesprochen und durch unsere Projektmanager im Projektplan entsprechend geändert.

Anschließend mit einem Klick wieder mit Outlook synchronisiert. Dies erspart unseren Technikern enorm viel Zeit und nimmt zugleich die Last der Terminorganisation weg.

 

Kommt es einmal zu einer kurzfristigen Planänderung sind die Outlook Kalender aller Beteiligten mit nur wenigen Änderungen aktualisiert und veröffentlicht.  Der Status „veröffentlich“ bedeutet, ob dieser Task vom Projektplan im Kalender sichtbar sein soll oder nicht.

Die Veröffentlichung kann gezielt verhindert werden, wenn die Planung einzelner Tasks oder Teilprojekte erst in der Entwurfsphase ist.

Terminplanung im MS-Project

Terminplanung im MS-Project

 

Die Termine können als ganztätig eingetragen oder mit genauer Zeitangabe. Dies eignet sich besonders für ein stundengenaue Planung (z.B. in Cut-Over Situationen). Die eingetragenen Termine durch den Allocatus müssen nicht vom Bearbeiter bestätigt werden, weil Sie in der Planung bereits abgestimmt worden sind. Werden im Projektplan Notizen hinterlegt – so werden diese bei der Veröffentlichung vom Allocatus auch berücksichtigt und mit dem Termin eintragen (siehe Bild oben).

Ansicht im Outlook Kalender

Ansicht im Outlook Kalender

 

Terminansicht vom Allocatus im Outlook

Terminansicht vom Allocatus im Outlook

Terminansicht vom Allocatus im Outlook Kalender mit den Projektinformationen wie:

  • Name
  • Uhrzeit
  • Notizen
  • Arbeitspaketnummer

Auch die Abwesenheiten lassen sich optimal mit den Allocatus verbinden. Im MS Project wird die Abwesenheit der Mitarbeiterinnen eingetragen und mit Allocatus zugleich in den Outlook Kalender. Somit hat der Projektleiter und auch die Kollegen einen Überblick über die Abwesenheit des Kollegen.

Fazit

  • Die Einbindung von Allocatus hat die Terminorganisation enorm verbessert und erleichtert.
  • Allocatus erspart uns Zeit bei der manuellen Pflege von Projektterminen.
  • Mitarbeiterinnen sehen sogar Ihre Termine am Mobiltelefon (Voraussetzung Outlook Client ist eingerichtet).

Installation und Inbetriebnahme

Um einen Allocatus Dienst einzurichten sind folgende Komponenten bzw. Schritte nötig:

  1. Allocatus Lizenzen erwerben und den Allocatus Server installieren
  2. Abstimmung der Inhalte für die Outlooktermine mit dem Hersteller.
  3. Konfiguration der E-Mail-Adressen im MS-Project (Ressourcenpool) und im Allocatus
  4. Plugin MS-Project installieren und konfigurieren
  5. Allocatus-Settings im MS-Project
Allocatus-Settings

Allocatus-Settings

  1. Projektplan veröffentlichen:

Schritt 1: Tasks zur Veröffentlichung kennzeichnen

Schritt 2: Button „Update“ aktivieren

Quellen:

https://www.allocatus.com/home

Oracle APEX: Custom error messages mit APEX_ERROR

Die Haupt-Anwendungsfälle im Zusammenhang mit der Ausgabe von Fehlermeldungen in APEX-Applikationen dürften die meisten Entwickler nach einer verhältnismäßig kurzen Einarbeitungszeit kennen:

  • Entweder werden Validations ausgeführt, schlagen an und sollen den Endanwender darüber informieren, dass eine entsprechende Bedingung verletzt wurde.
  • Oder während der Ausführung eines Page Processes tritt ein Fehler auf, über den der Endanwender in Kenntnis gesetzt werden soll.

 

Wollten Entwickler in älteren APEX-Versionen eigene Fehlermeldungen ausgeben, geschah dies häufig über die Nutzung der Variable apex_application.g_print_success_message unter zusätzlicher Angabe von Style-Informationen:

apex_application.g_print_success_message := '<span style="color:red;">Achtung: Bei der Aufbereitung der Datei-Inhalte sind insgesamt 23 Fehler aufgetreten, die Daten konnten daher nicht importiert werden.</span>';

Mittlerweile jedoch stellt das APEX-eigene Package APEX_ERROR viel elegantere Möglichkeiten für den Umgang mit eigenen Fehlermeldungen zur Verfügung:

 

Die überladene Prozedur ADD_ERROR bietet verschiedene Varianten an um eigene Fehlermeldungen auf den Error Stack zu legen, die Darstellung übernimmt dabei APEX, so dass die Notwendigkeit zu expliziten Style-Angaben entfallen kann. Ein entsprechender Prozess im Page Processing könnte damit beispielsweise so aussehen:

DECLARE
 -- define some variables
 v_status NUMBER;
 ...
BEGIN
-- do some fancy stuff
...
 v_status := my_schema.my_package.my_function(p_var => '[MY_VALUE]');

IF v_status > 0 THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Es ist ein Fehler aufgetreten, so dass…');
 END IF;
EXCEPTION
WHEN OTHERS THEN
-- do some error logging
...
RAISE;
END;

Das eigentliche Potential entfaltet dieses Package aber dann, wenn man auf auftretende Fehler speziell reagieren will, beispielsweise weil gewisse Fehler häufiger auftreten und die Seite speziell für bestimmte Fehlerfälle bestimmte zusätzliche Button-Aktionen oder Auswertungen zur Verfügung stellen soll. Dafür kann man statt der Variable v_status im obigen Code ein Hidden Item P12_STATUS setzen und nach dem Ausführen der Prozesse über einen Branch die Seite neu aufrufen und das Item dabei übergeben. Der Aufruf von APEX_ERROR geschieht nun nicht mehr im Page Processing sondern im Page Rendering und nur dann wenn P12_STATUS einen Wert > 0 enthält

IF :P12_STATUS = '1' THEN
apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
 p_message => 'Achtung: Das Hochladen der Daten war nicht erfolgreich, so dass…');
ELSIF :P12_STATUS = '2' THEN
 apex_error.add_error(p_display_location => apex_error.c_inline_in_notification,
p_message => 'Achtung: Das Parsen der Daten ist fehlgeschlagen, so dass…');
ELSE
...
END IF;

Zusätzlich besteht nun natürlich die Möglichkeit status-abhängig zusätzliche Items etc. anzubieten – zum Beispiel ein Button um die Daten erneut hochzuladen für P12_STATUS = ‚1‘ oder ein Report mit den Parsing-Fehlern für P12_STATUS = ‚2‘.

Setup IPSec VPN between Oracle Cloud Infrastructure and Customer On-Premise Equipment

Eine Möglichkeit zur Verbindung Ihres On-Premise-Netzwerks und Ihres virtuellen Cloud-Netzwerks (VCN) besteht in der Verwendung von VPN Connect, einem IPSec-VPN. IPSec steht für Internet Protocol Security oder IP Security. IPSec ist eine Protokollsuite, die den gesamten IP-Traffic verschlüsselt, bevor die Pakete von der Quelle zum Ziel übertragen werden.

Voraussetzungen:

  • vorhandenes VCN (Virtual Cloud Network) in der OCI (Oracle Cloud Infrastructure)
  • vorhandenes VPN-Gateway / IP-Netz auf der Customer On-Premise Site

*Wichtig: die Netze in der OCI und On-Premise dürfen sich nicht im gleichen Subnetz befinden (z. B. 192.168.0.0/24 und 192.168.0.0/28192.168.0.0/24 und 192.168.99.0/28)

 

Konfiguration in der OCI:

  1. Erstellen Sie unter OCI -> Networking -> Dynamische Routinggateways ein neues „Dynamische Routinggateway (DRG)“ (Wählen Sie das gewünschte Compartment und einen freiwählbaren Namen)
  2. Verknüpfen Sie das Dynamische Routinggateway mit dem vorhandenen VCN: OCI -> Networking -> Virtual Cloud Network -> Dynamische Routinggateways -> „Dynamische Routinggateway anhängen“ -> Wählen Sie das zuvor erstelle Dynamische Routinggateway
  3. Erstellen Sie ein Customer Premises Equipment: OCI -> Networking -> Customer Premises Equipment -> „Customer Premises Equipment erstellen“ (Wählen Sie hier einen Namen, das Compartment, den Hersteller (ggf. Sonstige) und die öffentliche IPv4-Adresse des CPE-Gateways)
  4. Erstellen der VPN-Verbindung: OCI -> Networking -> VPN-Verbindungen -> IPSec-Verbindung erstellen (Wählen Sie hier einen Namen und das Compartment für den VPN aus, anschließend wählen Sie bitte das „CPE“ und das „DRG“ aus dem richtigen Compartment aus und geben Sie das Customer Netz unter „CIDR mit statischer Route“ bei statischen Routen an)
  5. Anpassen der VPN-ID: OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> „Bearbeiten“ -> CPE-IKE-ID-Typ kann zwischen IP und FQDN gewählt werden
  6. Auslesen der Informationen für den VPN (öffentliche IPs der OCI, PreSharedKeys, …) OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> CPE- und Tunnelinformationen

Beispielkonfiguration On-Premise (hier Sophos SG mit UTM 9.7):

  1. Erstellen der IPSec Policies über Sophos UTM -> Site-to-site VPN -> IPSec -> Policies -> „New IPSec Policy…“ die OCI unterstützt folgende Parameter (Stand 10/2020):

Phase 1 (ISAKMP)

Parameter Optionen
ISAKMP-Protokoll Version 1
Austauschart Hauptmodus
Authentifizierungsmethode Pre-Shared Keys
Verschlüsselungsalgorithmus AES-256-cbc (empfohlen)

AES-192-cbc

AES-128-cbc

Authentifizierungsalgorithmus SHA-2 384 (empfohlen)

SHA-2 256

SHA-1(auch als SHA oder SHA1-96 bezeichnet)

Diffie-Hellman-Gruppe Gruppe 1 (MODP 768)

Gruppe 2 (MODP 1024)

Gruppe 5 (MODP 1536)

Gruppe 14 (MODP 2048)

Gruppe 19 (ECP 256)

Gruppe 20 (ECP 384) * (empfohlen)

Gültigkeitsdauer des IKE-Sessionschlüssels 28800 Sekunden (8 Stunden)
* Gruppe 20 wird in Kürze in allen Oracle Cloud Infrastructure-Regionen unterstützt.

Phase 2 (IPSec)

Parameter Optionen
IPSec-Protokoll ESP, Tunnelmodus
Verschlüsselungsalgorithmus AES-256-gcm (empfohlen)

AES-192-gcm

AES-128-gcm

AES-256-cbc

AES-192-cbc

AES-128-cbc

Authentifizierungsalgorithmus Bei Verwendung von GCM (Galois/Counter Mode) ist kein Authentifizierungsalgorithmus erforderlich, da die Authentifizierung in der GCM-Verschlüsselung enthalten ist.

Wenn GCM nicht verwendet wird, werden folgende Algorithmen unterstützt:

HMAC-SHA-256-128 (empfohlen)

HMAC-SHA-196

Gültigkeitsdauer des IPSec-Sessionschlüssels 3600 Sekunden (1 Stunde)
Perfect Forward Secrecy (PFS) aktiviert, Gruppe 5
  1. Erstellen Sie unter „Remote Gateways“ ein, bzw. zwei neue Remotedesktopgateways, die IPs und PSKs finden Sie in der OCI unter: OCI -> Networking -> VPN-Verbindungen -> angelegten VPN wählen -> CPE- und Tunnelinformationen, hier tragen Sie bei Remotenetz das IPv4 des OCI VCN, bzw. Subnetzes aus dem VCN ein
  2. Legen Sie nun die Conncetion an und wählen hier Ihre Policy, das Remotegateway und Ihre lokalen Netzwerke aus
  3. nach erfolgtem Verbindungsaufbau werden die VPN Tunnel in der Sophos, sowie der OCI als UP/Hochgefahren angezeigt:

Oracle Active Dataguard and Cloning of PDBs – the easy way

In diesem Artikel werden wir mehrere Features der 12.1.0.2 Datenbank beschreiben.

Active Data Guard, ein kostenpflichtiges Feature um Reporting auf die Standby Seite zu bringen (und tolle Administrationsfeatures beinhaltet, wie z. B. vereinfachtes Cloning (wie im Artikel beschrieben) oder Global Data Services).
Multitenant, ein Feature, das man für Datenbank-Konsolidierung benutzt und neue Möglichkeiten zur Administration anbietet.
PDB Cloning in einem ADG Environment: Clones kann man als eine Art von Backup sehen, wenn man Applikation Upgrades oder irgendwelche invasiven Tätigkeiten durchführt und einen schnellen Fallback haben möchte.
In höheren Versionen gibt es bessere Möglichkeiten, wie Flashback PDB oder PDB PITR.

Dieser Artikel dient zur Verbesserung des Daily Business, um Kunden zufriedener machen zu können. Wenn man schon Lizenzen besitzt, sollte man auch die zur Verfügung stehenden Features benutzen, nicht wahr?

Manche unserer Kunden haben Multitenant und Active Data Guard lizensiert, wobei man Multitenant sehr selten für Cloning oder verschiedene andere Tätigkeiten benutzt. Es ist auch klar, weshalb dies so ist, denn viele Kunden kennen diese Features nicht. Es gehört zu unseren Aufgaben, unseren Kunden diese Features vorzustellen und deren Einsatzgebiete zu erklären. Ein glücklicher Kunde ist der beste Kunde.

Bis vor kurzen war es so, dass wir entweder Restore Points erstellt haben, die in Wahrheit in einem 12.1.0.2 CDB Environment nicht wirklich benutzbar sind, denn man kann nur den ganzen Container zurückspielen oder wir mussten Duplicates (Restore) erstellen, was Zeit beanspruchte und unnötige Downtime verursachte. Da wir gern diese Tätigkeiten optimieren, ist es nötig Kunden zu informieren wie man es in Zukunft machen könnte, damit die Projekte weitergehen und jeder zufrieden ist.
Dieses Dokument beschreibt die Möglichkeiten, die sehr einfach sind, wenig Zeit kosten, nur freien Storage und einen DBA brauchen, der diese Features kennt.

Active Data Guard erlaubt es den Kunden das Reporting auf die Disaster Systeme zu schwenken, damit die Performance der Produktivsysteme, bei performance-lastigen Reports, nicht beeinträchtigt wird. Es ermöglicht uns weiterhin die Dinge zu tun, die schnell sind, Downtimes vermeiden und Service Agreements leicht erfüllen.

Wenn man in einem Active Data Guard Environment eine lokale PDB klont, werden die Standby PDBs automatisch erstellt und in einer 12.1.0.2 sind dazu nur 5 Kommandos notwendig (denn man muss die PSORGER PDB wieder READ WRITE öffnen).
In höheren Versionen kann man einen Hot Clone erstellen, wenn man Local Undo benutzt, das heißt Zero Downtime.

 

SYS@CDB1 SQL> alter pluggable database psorger close immediate instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> alter pluggable database psorger open read only instances=all ;
Pluggable database altered.
SYS@CDB1 SQL> create pluggable database psorgerc from psorger ;
Pluggable database created.


[oracle@exa2vm01 ~]$ tail -f $al
  Mem# 0: +DATAX6C1/E2CDB1/ONLINELOG/group_22.2191.970881745
  Mem# 1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
1: +RECOX6C1/E2CDB1/ONLINELOG/group_22.9224.970881745
Thu Sep 10 20:36:27 2020
Recovery created pluggable database PSORGERC
Thu Sep 10 20:36:54 2020
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/system.1628.967329427
Datafile 23 added to flashback set
Successfully added datafile 23 to media recovery
Datafile #23: '+DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/system.2920.1050784587'
Thu Sep 10 20:37:23 2020
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATAX6C1/E2CDB1/AEFAD6D19935CDF7E0537D001BAC3B4F/DATAFILE/sysaux.2929.1050784615 from +DATAX6C1/E2CDB1/647EDCDA7FB28F8DE0538B001BACEE69/DATAFILE/sysaux.1629.967329427
Datafile 24 added to flashback set

Wie man sieht reichen 3 Kommandos aus, um eine Pluggable Database zu klonen (EE 12.1.0.2 mit ADG). Eine 100GB Datenbank kann auf einer Exadata in wenigen Minuten geklont werden und somit verfügt man über eine „Point in Time“ Kopie der PDB, die man im Fall eines schnellen „Restores“ wieder benutzen kann. Es handelt sich hierbei nicht um einen Restore, aus Kundensicht sieht es jedoch so aus, als würde man etwas aus der Vergangenheit zurückbringen. Letztendlich wird hierbei nur ein mächtiges Feature der Enterprise Edition von Oracle genutzt.

Natürlich gibt es aber auch Situationen, in denen ein Restore erforderlich wird, da keine Clones erstellt wurden. In diesem Fall ist es am besten einen DUPLICATE vom Backup zu erzeugen. Dies ist einfach umzusetzen, braucht nur wenige Kommandos und ist automatisiert, so dass der DBA wenig Zeit mit der Kommandoeingabe verliert. Mehr Informationen zu diesem Thema folgen in einem weiteren Artikel.

Jetzt werden wir uns aber dem Thema cloning weiter widmen und zeigen in wenigen Kommandos, wie wir aus dem Duplikat die Pluggable Database wieder in den korrekten Container einfügen können.

Das Scenario beinhaltet:
1x Active Data Guard Configuration (12.1.0.2.200114) – CDB1 mit mehreren PDBs, PDB1, PDB2, PDB3
1x Duplikat der CDB Point in Time Recovered (12.1.0.2.200114) – CDBDUP mit PDB1

Die technischen Schritte um die PDB1 aus der CDBDUP in die CDB1 zu kopieren sind folgende:

1. In der CDB1 löschen wir die PDB1

alter pluggable database pdb1 close immediate instances=all ;
drop pluggable database pdb1 including datafiles ;

2. In der CDBDUP der PDB1 erstellen wir einen Benutzer mit Berechtigungen zum Klonen

ALTER SESSION SET CONTAINER=pdb1 ;
CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

3. In den tnsnames.ora beider DB Systeme definieren wir einen Connect String der im Scenario benutzt wird. Es ist gut nicht produktive Connect Strings zu benutzen, so weiß man zu 100% wo man sich einloggt

PDB1_PS=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost)(PORT=1521))(CONNECT_DATA=(SID=CDBDUP)(SERVICE_NAME=pdb1)(INSTANCE_NAME=CDBDUP)))

4. In der CDB1 auf der Standby Seite setzen wir einen Parameter damit die PDB automatisch in das Standby Environment kopiert wird.

alter system set standby_pdb_source_file_dblink='pdb1_ps' ;

5. In der CDBDUP setzen wir die PDB1 in READ ONLY Modus (nach Duplicate ist die DB im MOUNT Zustand, in höheren Versionen ist diese Read Only Restriction nicht mehr da, da könnte man dann einen Relocate zB. machen)

alter pluggable database PDB1 open read only ;

6. In der CDB1 erstellen wir einen Database Link zu der PDB1 im CDBDUP

create database link pdb1_ps connect to remote_clone_user identified by remote_clone_user using 'pdb1_ps' ;

7. Wir erstellen einen Klon der PDB in der CDB1 und warten bis es fertig wird. Auf einer Exadata X6-2 dauerte eine 110GB PDB 10 Minuten. Im Background wird der Database Link für den Klon auf die Standby Seite benutzt.

create pluggable database PDB1 from pdb1@pdb1_ps ;

8. Wir öffnen die PDB1 und sind mit der Bereitstellung der PDB1 fertig. Wir öffnen die PDB mit dem Application Service damit es auf den korrekten Instanzen startet (im RAC kann es 1 Node sein oder auch 10)

alter pluggable database PDB1 close immediate instances=all ;
srvctl start service -db CDB1 -pdb pdb1_svc

Somit haben wir die PDB1 wieder in Betrieb genommen und die Welt ist für heute gerettet.
In einem Data Guard Environment geht es leider nicht so einfach, man muss manuell die Datafiles auf die Standby Seite transferieren. Um mehr Informationen darüber zu erfahren bitte den Oracle Note durchlesen.
Using standby_pdb_source_file_dblink and standby_pdb_source_file_directory to Maintain Standby Databases when Performing PDB Remote Clones or Plugins (Doc ID 2274735.1)

Oracle SQL Tuning: Unmögliche Optimizer Hints

Englisch Version here: https://www.dbconcepts.at/oracle-sql-tuning-impossible-optimizer-hints/

Query Blöcke

Ich war bis jetzt der Meinung, dass ein Optimizer Hint nur innerhalb seines Query Blocks wirkt, außer man qualifiziert den Hint mit dem Query Block Namen [1].

Man kann beispielsweise Viewnamen in einem Hint nicht wirksam angeben.

In meiner Untersuchung zur Verbesserung des Statspack [2] habe ich gesehen, dass das nicht immer stimmt und wollte diese genauer untersuchen.

Ausgangslage

Meine Testbeispiele erstelle ich im Scott Schema, so dass jeder meinen Test nachvollziehen kann.

Mittels des Optimizer Hints werde ich einen schlechten Plan erzwingen, damit eindeutig ist, dass der Optimizer den Plan unfreiwillig und durch den Hint gewählt hat.

Für die Tests habe ich Oracle Version 19c verwendet, um gleich die neue Hint Report Funktion nutzen zu können.

Die Pläne habe ich mit dem folgenden Statement angezeigt:

select * from dbms_xplan.display_cursor(format=>'TYPICAL +hint_report')
;

Hier ist unser Beispiel Statement:

SELECT ename,
       dname,
       sal,
       grade
FROM salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Ohne weitere Angabe erzeugt der Optimizer den folgenden Plan:

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                 |          |     1 |    94 |     7   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|*  6 |   TABLE ACCESS FULL           | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ENAME"='SCOTT')
   5 - access("E"."DEPTNO"="D"."DEPTNO")
   6 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))

Versuchen wir es nun mit einem Hint.
In unserem Hint erzwingen wir, dass der Optimizer mit der Tabelle dept beginnt, was er freiwillig nicht machen würde.

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Gegen meine Erwartung wird der Hint befolgt, obwohl er sich auf einen Alias in einem anderen Query Block bezieht.

Diesmal ist auch ein Hint Report dabei, da es einen Hint gibt.

-------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS       |          |     1 |    94 |     9   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - filter("ENAME"='SCOTT')
   5 - filter(("SAL">="LOSAL" AND "SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
   1 -  SEL$F5BB74E1
           -  leading(d)

Eigentlich hätte der Hint gar nicht funktionieren dürfen.

Der Hint befindet sich in der Hauptabfrage und referenziert ein Objekt in einem anderen Query Block.

Weshalb geht es wohl doch?

Wie man sieht wurde aber die Unterabfrage aufgelöst und mit der Hauptabfrage verschmolzen. Man spricht hier von einem Simple View Merging.

Ein Simple View Merging ist eine sogenannte Transformation. Der Optimizer schreibt die Abfrage um.

Durch die Transformation gibt es nur noch einen Query Block für die Abfrage.

Das könnte der Grund sein, dass der Leading Hint funktioniert.
Wird der obige Hint noch wirken, wenn man die Transformation verbietet?

SELECT /*+ leading(d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

In der Tat wird der Leading Hint jetzt nicht mehr befolgt.

Das ist typisch für das nicht funktionieren von Hints. Die Transformation erfolgt vor der Optimierung.

Dadurch werden zwei unterschiedliche Query Blocks erzwungen.
Das direkte Referenzieren eines anderen Query Blocks funktioniert dann nicht mehr.

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                  |          |     1 |    68 |     7   (0)| 00:00:01 |
|   2 |   VIEW                         |          |     1 |    29 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |          |     1 |    55 |     4   (0)| 00:00:01 |
|   4 |     NESTED LOOPS               |          |     1 |    55 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)|          |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL            | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("ENAME"='SCOTT')
   6 - access("E"."DEPTNO"="D"."DEPTNO")
   8 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2 (U - Unused (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         U -  leading(d)
   3 -  SEL$2
           -  NO_MERGE

Der Hint Report zeigt ebenfalls an, dass der Leading Hint nicht befolgt wird.

Im Statspack sah ich aber diese Variante des Hints:

SELECT /*+ leading(de.d) */ ename,
       dname,
       sal,
       grade
FROM
    salgrade s,
    (
        SELECT /*+ NO_MERGE */  ename,
               sal,
               dname
          FROM emp    e,
               dept   d
         WHERE e.deptno = d.deptno
           AND ename = 'SCOTT'
    ) de
WHERE de.sal BETWEEN losal AND hisal;

Das Qualifizieren mit dem Alias der Unterabfrage sollte eigentlich nicht funktionieren.

Der Alias der Unterabfrage ist kein Query Block Name.

Jedoch, es geht trotzdem:

--------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |       |       |     9 (100)|          |
|   1 |  NESTED LOOPS        |          |     1 |    68 |     9   (0)| 00:00:01 |
|   2 |   VIEW               |          |     1 |    29 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN         |          |     1 |    55 |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| DEPT     |     4 |    88 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| EMP      |     1 |    33 |     3   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS FULL  | SALGRADE |     1 |    39 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("ENAME"='SCOTT')
   6 - filter(("DE"."SAL">="LOSAL" AND "DE"."SAL"<="HISAL"))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   3 -  SEL$2
           -  leading(de.d)
           -  NO_MERGE

Fazit

Die Möglichkeit mit dem Alias einer Subquery zu Hinten erleichtert das Hinten von komplexen Abfragen.

Aufgrund des Hint Reports kann man sehen, dass der Hint korrekt verstanden wird.

Wie so vieles im Kontext von Hints ist auch diese Option nicht dokumentiert.

Ich habe die Abfrage von Version 11.2.0.4. bis 19.0 getestet und sie funktionierte immer.

Statt mit einer Unterabfrage in der From Klausel funktioniert es auch mit einer View.

Jedoch muss auch hier, wenn die View einen Alias bekommt, der Alias im Hint verwendet werden.

Jonathan Lewis hat mich darauf hingewiesen, dass die Art von Hints offiziell erlaubt ist.

Es handelt sich um global table hints [3].

Diese besonders nützliche Art des Hints ist leider noch zu wenig bekannt. Jedenfalls habe ich sie in meiner Praxis noch nicht angetroffen.

Quellen

Oracle Statspack verbessern: Historische Pläne – Teil 2

Grundlegendes: Ändern des Statspack Code

English version here : Improving Statspack: Add Support for Plan Stability

Natürlich sollte man den Statspack Code nicht leichtfertig ändern. Das wird Niemand wollen. Die hier besprochene Änderung erlaubt es, schnell und ohne großes Nachdenken einen Plan aus der Vergangenheit wieder her zu stellen.

Für manche Applikationen und Datenkonstellationen ist diese Möglichkeit wirklich wichtig.
In diesem Fall sollte man es sich gründlich überlegen, ob man die hier beschriebene Veränderung nicht doch machen will.

Schließlich hat man den Source Code des Statspack. Man sollte die originale Codeversion behalten und kann daher jederzeit den Originalcode schnell wiederherstellen. Die zusätzliche optionale Spalte stört da nicht.

Ausgangslage

Bei meinem Webinar „The good plan and the bad Plan” für die DOAG ging es darum, gute Pläne aus der Vergangenheit wieder zu aktivieren.

Kann man die AWR Daten lesen, so kann man beispielsweise aus den DBA_HIST Daten mittels dbms_xplan.display_awr ein Outline erzeugen. Dieses Outline kann man dann in aktuelle Pläne einpflanzen.

Ich wurde damals gefragt, ob eine solche Methode auch auf einer Oracle Standard Edition Datenbank funktioniert.

Mit dem SQL_PATCH [2] gibt es eine legale Möglichkeit, ein Outline in einen SQL Befehl einzupflanzen.

Die Frage ist nur, wie man auf einer Oracle Standard Edition zu einem Outline eines alten Planes kommt.
Gibt es mit Statspack eine Funktion ähnlich dem dbms_xplan.display_awr?

Uwe Küchler, alias Oraculix hat eine sehr kreative Lösung parat [1].

Man verwendet einfach dbms_xplan.display im Zusammenhang mit einer Suchfunktion:

select * from table(dbms_xplan.display(
  table_name   => 'perfstat.stats$sql_plan',
  statement_id => null,
  format       => 'ALL -predicate -note',
  filter_preds => 'plan_hash_value = '|| &&phv
);

Man muss dazu noch eine Spalte in der Tabelle stats$sql_plan ergänzen, die Einzelheiten finden Sie im Oraculix Blog.

Was passiert nun, wenn man jetzt einfach im Format noch +OUTLINE angibt?
Dann müsste man doch das Outline bekommen. Da könnte man dann über einen SQL_PATCH in ein Statement schreiben und voila, hat man den alten Plan zurück.

Oder etwa nicht?

Nein, die Format Anweisung wird scheinbar einfach ignoriert.
Der Grund hierfür ist, dass in den Statspack Plänen einige Spalten fehlen. Konkret geht es hier um die Spalte OTHER_XML, die nicht mitgespeichert wird.

Die Spalte other_xml

In dieser Spalte findet sich unter anderen das Outline. Wer also die sehr wichtige Funktion der historischen Pläne haben will, darf nicht davor zurückscheuen, den Statspack code zu verändern. Wenn Oracle schon die der MOS Note 2182680.1 vorschlägt das Statspack zu verändern, weshalb sollte wir nicht ein wenig weiter gehen?

Wir beginnen, in dem wir die Spalte hinzufügen.

ALTER TABLE perfstat.stats$sql_plan ADD timestamp INVISIBLE AS (cast(NULL AS DATE));
ALTER TABLE perfstat.stats$sql_plan ADD OTHER_XML CLOB;

Danach müssen wir den Code anpassen. Analog zum Vorgehen in MOS Note 2182680.1 habe ich zuerst eine Kopie des aktuellen Codes gemacht.
Wieder ist es das Skript spcpkg, welches angepasst werden muss.

Zu ändern ist das Insert Statement beginnend mit:

insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#

Hier muss die Spalte Other_XML ergänzt werden. Dabei gibt es jedoch eine Schwierigkeit.

Im Select Teil des Inserts steht eine Maximum Funktion, z.B. so:

                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)

Die Maximum Funktion ist ziemlich sicher nur dafür da, um duplizierte Sätze zu vermeiden.

Also schreiben wir doch einfach: Max(other_xml), oder?

Prompt kommt ORA-00932. Der Grund dafür ist, dass CLOB nicht sortierbar sind, daher kann auch kein Maximum berechnet werden.

ANY_VALUE

Natürlich gäbe es noch andere Möglichkeiten, um die Werteliste eindeutig zu machen.
Beispielsweise analytische Funktionen wie row_number ().

Ich wolle jedoch nicht so viel am Original Code verändern.

Mir fiel eine Idee aus dem Oracle Technologie Network ein, bei der es darum ging, für genau solche Probleme eine neue Art von Gruppenfunktion ein zu führen, genannt ANY_VALUE [3.].

Die Idee wird anscheinend mit Version 20c umgesetzt. Zu schade. Mit Version 19 wäre besser.

Als ehemaliger Oracle Mitarbeiter weiß ich jedoch, dass manchmal die Vorgängerversionen undokumentiert schon Änderungen der Folgeversion enthalten.

Und in der Tat: any_value gibt es schon.
Leider wirft auch diese Implementation völlig unnötig ORA-00932.

Ich habe Chris Saxon darauf aufmerksam gemacht und hoffe das Oracle die Implementierung noch ändert.
Nun, wenn Oracle nicht liefert muss ich das selbst machen.

Ich habe mir über das User-Defined Aggregate Functions Interface die Funktion selbst definiert und any_lob genannt.

Den Code finden sie am Ende dieses Blogs. Hier erst einmal der vollständige Insert Befehl. Die alle vom Standard abweichenden Änderungen sind mit Rot gekennzeichnet. Die Hints habe ich im vorigen Blog zum Thema Statspack beschrieben.

            insert into stats$sql_plan
                 ( plan_hash_value
                 , id
                 , operation
                 , options
                 , object_node
                 , object#
                 , object_owner
                 , object_name
                 , object_alias
                 , object_type
                 , optimizer
                 , parent_id
                 , depth
                 , position
                 , search_columns
                 , cost
                 , cardinality
                 , bytes
                 , other_tag
                 , partition_start
                 , partition_stop
                 , partition_id
                 , other
                 , other_xml                 
                 , distribution
                 , cpu_cost
                 , io_cost
                 , temp_space
                 , access_predicates
                 , filter_predicates
                 , projection
                 , time
                 , qblock_name
                 , remarks
                 , snap_id
                 )
            select /*+ leading(spu@np ssp@sq  s sp) */
                   new_plan.plan_hash_value
                 , sp.id
                 , max(sp.operation)
                 , max(sp.options)
                 , max(sp.object_node)
                 , max(sp.object#)
                 , max(sp.object_owner)
                 , max(sp.object_name)
                 , max(sp.object_alias)
                 , max(sp.object_type)
                 , max(sp.optimizer)
                 , max(sp.parent_id)
                 , max(sp.depth)
                 , max(sp.position)
                 , max(sp.search_columns)
                 , max(sp.cost)
                 , max(sp.cardinality)
                 , max(sp.bytes)
                 , max(sp.other_tag)
                 , max(sp.partition_start)
                 , max(sp.partition_stop)
                 , max(sp.partition_id)
                 , max(sp.other)
                 , any_lob(sp.other_xml)
                 , max(sp.distribution)
                 , max(sp.cpu_cost)
                 , max(sp.io_cost)
                 , max(sp.temp_space)
                 , 0 -- should be max(sp.access_predicates) (2254299)
                 , 0 -- should be max(sp.filter_predicates)
                 , max(sp.projection)
                 , max(sp.time)
                 , max(sp.qblock_name)
                 , max(sp.remarks)
                 , max(new_plan.snap_id)
              from (select /*+ QB_NAME(NP)  */  
                           spu.plan_hash_value
                         , spu.hash_value    hash_value
                         , spu.address       address
                         , spu.text_subset   text_subset
                         , spu.snap_id       snap_id
                      from stats$sql_plan_usage spu
                     where spu.snap_id         = l_snap_id
                       and spu.dbid            = p_dbid
                       and spu.instance_number = p_instance_number
                       and not exists (select /*+ QB_NAME(SQ)  */ *
                                         from stats$sql_plan ssp
                                        where ssp.plan_hash_value 
                                            = spu.plan_hash_value
                                      )
                   )          new_plan
                 , v$sql      s      -- join reqd to filter already known plans
                 , v$sql_plan sp
             where s.address         = new_plan.address
               and s.plan_hash_value = new_plan.plan_hash_value
               and s.hash_value      = new_plan.hash_value
               and sp.hash_value     = new_plan.hash_value
               and sp.address        = new_plan.address
               and sp.hash_value     = s.hash_value
               and sp.address        = s.address
               and sp.child_number   = s.child_number
             group by 
                   new_plan.plan_hash_value, sp.id
             order by
                   new_plan.plan_hash_value, sp.id; -- deadlock avoidance

Zusammenfassung

Der Code scheint recht gut zu funktionieren.

Nur eine Sache war etwas ärgerlich: Immer, wenn ich Syntaxfehler im Insert hatte, musste ich auch die Funktion any_lob neu kompilieren.

Hinweis: Man muss den Snap Level auf 6 oder höher schalten, damit das Statspack Pläne sammelt.  Z.B.:

EXECUTE statspack.snap(i_snap_level => 7);

Create or replace type any_lob_type as object
(
  v_clob CLOB,

  static function ODCIAggregateInitialize
    ( sctx in out any_lob_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out any_lob_type ,
      value in     CLOB
    ) return number ,

  member function ODCIAggregateTerminate
    ( self        in  any_lob_type,
      returnvalue out CLOB,
      flags in number
    ) return number ,

  member function ODCIAggregateMerge
    ( self in out any_lob_type,
      ctx2 in     any_lob_type
    ) return number
);
/

create or replace type body any_lob_type
is
  static function ODCIAggregateInitialize
  ( sctx in out any_lob_type )
  return number
  is
  begin
    sctx := any_lob_type( null ) ;
    return ODCIConst.Success ;
  end;

  member function ODCIAggregateIterate
  ( self  in out any_lob_type ,
    value in     CLOB
  ) return number
  is
  begin
    self.v_clob := value ;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate
  ( self        in  any_lob_type ,
    returnvalue out CLOB ,
    flags       in  number
  ) return number
  is
  begin
    returnvalue := self.v_clob;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge
  ( self in out any_lob_type ,
    ctx2 in     any_lob_type
  ) return number
  is
  begin
      return ODCIConst.Success;
  end;
end;
/

create or replace function any_lob
  ( input CLOB )
  return CLOB
  deterministic
  parallel_enable
  aggregate using any_lob_type
;
grant execute on any_lob to public;
create public sysnonym for sys.any_lob;

Quellen

 

Virtuelle Maschinen in zwei Rechenzentren organisieren (VMWare Pinning)

Virtuelle Maschinen in zwei Rechenzentren organisieren (VMWare Pinning)

Da wir im letzten Jahr unsere Infrastruktur um eine zweite Location in einem neuen Rechenzentrum erweitert haben, sind durch diesen Schritt neue Herausforderungen auf uns hinzugekommen.
Eine der Herausforderungen war, die Virtuellen Maschinen in den richtigen Rechenzentren laufen zu lassen.

Das heißt, dass die Virtuellen Maschinen (Config Files) sowie der Datastore am gleichen Standort läuft/liegt, um unnötigen Traffic über die Standleitungen zu verhindern.

Problembeschreibung:

Wir besitzen zwei VM-Farmen mit zugehörigen Storages, die auf zwei Rechenzentren aufgeteilt sind.

Wenn das Config File einer Virtuellen Maschine in Rechenzentrum 1 liegt, der Storage aber im Rechenzentrum 2, dann hat das natürlich extreme Auswirkungen auf das Netzwerk, da der Traffic immer zwischen den beiden Rechenzentren läuft.

Wenn die Virtuelle Maschine aber in einem Rechenzentrum läuft und die Storage auch korrekt definiert ist, dann ersparen wir uns den unnötigen Traffic.

Problemlösung:

Dazu habe ich ein Script entwickelt, dass das überprüft und die Ergebnisse unserem Monitoring System Zabbix meldet.

Ich habe für das Script Powershell verwendet.

Aufbau des Scripts:

  • Um in VMWare via Powershell Daten abzufragen oder Änderungen vornehmen zu können, muss man sich zuerst mit dem Server verbinden:
connect-viserver -Server <Server>
  • Abfrage der Config Files aller Virtuellen Maschinen in beiden Rechenzentren:
[string[]]$rzfmvms = Get-Cluster <Cluster1> | Get-VM | Select Name

[string[]]$zfs1config = Get-VM | Select Name, @{N="VMConfigFile";E={$_.extensiondata.config.files.vmpathname}} 
| Where VMConfigFile -Like "*<Storage1*>" | Select Name

[string[]]$zfs2config = Get-VM | Select Name, @{N="VMConfigFile";E={$_.extensiondata.config.files.vmpathname}} 
| Where VMConfigFile -Like "*<Storage2*>" | Select Name

[string[]]$rzesvms = Get-Cluster <Cluster2> | Get-VM | Select Name

[string[]]$zfs3config = Get-VM | Select Name, @{N="VMConfigFile";E={$_.extensiondata.config.files.vmpathname}} 
| Where VMConfigFile -Like "*<Storage3*>" | Select Name

[string[]]$zfs4config = Get-VM | Select Name, @{N="VMConfigFile";E={$_.extensiondata.config.files.vmpathname}} 
| Where VMConfigFile -Like "*<Storage4*>" | Select Name
[string[]]$zfsvms2 = $zfs3config + $zfs4config
  • Nach einigen notwendigen Zwischenschritten (hauptächlich Formatierungen: [@{Name=“, „“, } „“ entfernt) arbeite ich mit einer for-Schleife dann die Einträge ab und überprüfe, ob der Name der Virtuellen Maschine auf einem Storage eines anderen Rechenzentrum auftaucht und lese mir die „Persistent ID“ der Virtuellen Maschine aus.

Diese Information schicke ich dann an unser Monitoring System.

$<Cluster1> | ForEach-Object{
    
    $id = Get-VM | Where Name -Like $_ | select PersistentId
    C:\zabbix\ZABBIX_SENDER.EXE -z <ZabbixServerIP> -s $id.PersistentId -k <ZabbixTrigger> -o yes
}
#Send the information to Zabbix so we can monitor the wrong Config Files
$<Cluster2> | ForEach-Object{
    
    $id = Get-VM | Where Name -Like $_ | select PersistentId
    C:\zabbix\ZABBIX_SENDER.EXE -z <ZabbixServerIP> -s $id.PersistentId -k <ZabbixTrigger> -o yes
}

Somit kann ich über unser Monitoring System jederzeit überprüfen, welche Virtuellen Maschinen falsch laufen und diese dann entsprechend verschieben.

Anmerkung:

Mit der Version 5.0 von Zabbix kann man die Formatierung auch direkt im Preprocessing ohne JavaScript durchführen.

Oracle Statspack verbessern: Schnellere Snapshots – Teil1

Oracle Statspack verbessern: Schnellere Snapshots – Teil1

Ausgangslage: Langsamer Statspack Snapshot

Auf verschiedenen Standard Edition Datenbanken sieht man immer wieder den Statspack Snapshot lange laufen.

Dies ist für mich als Consultant sehr unangenehm.
Oft muss ich den Kunden sagen, dass das Statspack auf einer Standard Edition Datenbank unverzichtbar ist.

Und dann sieht mein Kunde, wie der Statspack Snapshot minutenlang einen der kostbaren, limitierten Cores blockiert.

Ich beschloss, etwas dagegen zu unternehmen.
Bei näherer Betrachtung erkennt man zwei Statements, die die Datenbank belasten.

In erster Linie ist dies:

INSERT INTO stats$sql_plan

Aber auch dieser Befehl läuft lange:

INSERT INTO stats$seg_stat

Grundsätzliches: Hints im Statspack

Man hört oft, dass man Hints so wenig wie möglich machen soll und lieber andere Mittel verwenden soll, um den Optimizer zu steuern.

Gute Statistiken zum Beispiel.
Ich bin auch dieser Meinung.
Allerdings gibt es auch Ausnahmen.

Bei Tools wie Statspack kann man sich nicht darauf verlassen, dass die Statistiken immer aktuell sind.

Es werden auch interne Tabellen X$ verwendet. Nicht jeder DBA macht fixed table stats und selbst wenn, schwanken diese Statistiken oft stark.

Ein Tool wie Statspack muss immer zuverlässig laufen.
Glücklicherweise ist bei den zwei Statspack Queries die wir betrachten werden, ziemlich klar wie der Plan auszusehen hat.

Hints stellen also kein großes Risiko dar. Jedoch habe ich dennoch so wenig wie möglich festgelegt.

Statement : INSERT INTO stats$sql_plan

Das Problem wird in der MOS Note 2182680.1 behandelt.

In dieser Note wird vorgeschlagen eine alternative Implementation des Statspack Package (SCPPKG.SQL) herunter zu laden und zu implementieren.

Diese Implementation enthält einen geänderten Hint, den ich im folgenden Beispiel mit Rot hervorgehoben habe.

Vom ganzen Insert Statement zeige ich nur den SELECT Teil, weil der Eintrag sonst zu lange wird.
Auch vom Select Teil habe ich die Liste der Spalten gekürzt, damit der Code Teil übersichtlich bleibt.

SELECT /*+ no_merge(new_plan) leading(new_plan s sp) use_nl(s) use_nl(sp) */
            new_plan.plan_hash_value,
            sp.id,
            MAX(sp.operation),
            MAX(sp.options),  
            . . .
            MAX(new_plan.snap_id)
        FROM
            (
                SELECT /*+ index(spu) */
                    spu.plan_hash_value,
                    spu.hash_value    hash_value,
                    spu.address       address,
                    spu.text_subset   text_subset,
                    spu.snap_id       snap_id
                FROM
                    stats$sql_plan_usage spu
                WHERE
                    spu.snap_id = :b3
                    AND spu.dbid = :b2
                    AND spu.instance_number = :b1
                    AND NOT EXISTS (
                        SELECT /*+ nl_aj */
                            *
                        FROM
                            stats$sql_plan ssp
                        WHERE
                            ssp.plan_hash_value = spu.plan_hash_value
                    )

Wie unschwer zu erkennen ist, befinden sie relevanten Suchkriterien auf der Tabelle stats$sql_plan_usage mit dem Alias spu.

Es ist also wichtig, dass im Execution Plan mit dem Lesen dieser Tabelle begonnen wird.

Der Hint Leading(new_plan .. ist also folgerichtig.

Das Problem besteht jedoch darin, dass new_plan keine Tabelle, sondern der Name einer Unterfrage ist.
Obwohl Version 19c den Hint als korrekt meldet, wird er in tieferen Versionen oft nicht akzeptiert.

Der Alias für den Hint wäre eigentlich spu, jedoch ist dieser in der Hauptabfrage nicht zugänglich, weil die Tabelle stats$sql_plan_usage in einer Unterabfrage angesprochen wird. (Achtung: wenn ein Alias vorhanden ist, muss der Alias im Hint angegeben werden und nicht der Tabelle Name.)

Diese Unterabfrage bildet einen eigenen Queryblock und dessen Inhalt ist von der Hauptabfrage nicht direkt referenzierbar.

In der Tat zeigt der Plan des Insert Befehles, dass der Leading Hint ignoriert wird.

Hier ein Beispiel von einem unserer Kunden:

Operation | Name | A-Time 
-------------------------------------------------------------------------------
INSERT STATEMENT | |00:05:07.72
 LOAD TABLE CONVENTIONAL | STATS$SQL_PLAN |00:05:07.72
  SORT GROUP BY | |00:05:07.72
   NESTED LOOPS | |00:05:07.71
    NESTED LOOPS | |00:05:07.71
     FIXED TABLE FULL | X$KGLCURSOR_CHILD |00:00:02.30
     VIEW PUSHED PREDICATE | |00:05:05.33
      NESTED LOOPS ANTI | |00:05:05.23
       TABLE ACCESS BY INDEX ROWID BATCHED| STATS$SQL_PLAN_USAGE |00:03:42.53
        INDEX RANGE SCAN | STATS$SQL_PLAN_USAGE_PK|00:02:22.49
       INDEX RANGE SCAN | STATS$SQL_PLAN_PK |00:01:03.19
   FIXED TABLE FIXED INDEX | X$KQLFXPL (ind:3) |00:00:00.01
-------------------------------------------------------------------------------

Wie man sieht, benötigt der Insert 5 Minuten und 8 Sekunden.

Queryblöcke

Um eine Queryblock in der Hauptabfrage referenzieren zu können, muss man dem untergeordneten Queryblock mit dem QB_NAME hint einen Namen geben.

Dann kann man die Tabellen des untergeordneten Queryblocks mittels „tabellenalias@queryblock“ ansprechen.
Dies ist ein dokumentiertes Vorgehen und sollte in allen Versionen stabil funktionieren.

In unserem Beispiel sieht das so aus:

SELECT /*+ leading(spu@np ssp@sq  s sp) */
            new_plan.plan_hash_value,
            sp.id,
... 
            MAX(new_plan.snap_id)
        FROM
            (
                SELECT /*+ QB_NAME(NP)  */  
                    spu.plan_hash_value,
                    spu.hash_value    hash_value,
                    spu.address       address,
                    spu.text_subset   text_subset,
                    spu.snap_id       snap_id
                FROM
                    stats$sql_plan_usage spu
                WHERE
                    spu.snap_id = :b3
                    AND spu.dbid = :b2
                    AND spu.instance_number = :b1
                            AND NOT EXISTS (
                        SELECT /*+ QB_NAME(SQ)  */
                            *
                        FROM
                            stats$sql_plan ssp
                        WHERE
                            ssp.plan_hash_value = spu.plan_hash_value
                    )
            ) new_plan,

Diesmal hält der Optimzer sich an die Hints, das Ergebnis sieht wie folgt aus:

--------------------------------------------------------------------------------
Operation                                | Name                     |   A-Time  
--------------------------------------------------------------------------------
INSERT STATEMENT                         |                          |00:00:00.05
 LOAD TABLE CONVENTIONAL                 | STATS$SQL_PLAN           |00:00:00.05
  SORT GROUP BY                          |                          |00:00:00.05
   NESTED LOOPS                          |                          |00:00:00.05
    NESTED LOOPS                         |                          |00:00:00.04
     HASH JOIN ANTI                      |                          |00:00:00.04
      TABLE ACCESS BY INDEX ROWID BATCHED| STATS$SQL_PLAN_USAGE     |00:00:00.01
       INDEX RANGE SCAN                  | STATS$SQL_PLAN_USAGE_PK  |00:00:00.01
      INDEX FAST FULL SCAN               | STATS$SQL_PLAN_PK        |00:00:00.01
     FIXED TABLE FIXED INDEX             | X$KGLCURSOR_CHILD (ind:1)|00:00:00.01
    FIXED TABLE FIXED INDEX              | X$KQLFXPL (ind:3)        |00:00:00.01
--------------------------------------------------------------------------------

Also 5 hundertstel Sekunden statt 5 Minuten.

Das ist über 6000x schneller und kann sich sehen lassen.

Nachdem ich die Hints gefunden hatte, habe ich eine Sicherheitskopie des Scriptes SCPPKG.SQL angelegt und den neuen Hint in das Package kopiert.
Durch den Aufruf des Scriptes wurde das neue Package dann installiert.

Statement: INSERT INTO stats$seg_stat

Wie sieht nun das zweite Insert aus?

Diesmal gibt es keine MOS Note.

Auch hier zeige ich wieder den sql code, mit den hervorgehobenen Hints:

SELECT /*+  ordered use_nl(s1.gv$segstat.X$KSOLSFTS) */
            :b3,
            :b2,
...
            SUM(decode(s1.statistic_name, 'row lock waits', value, 0))
        FROM
            v$segstat s1
        WHERE
            ( s1.dataobj#,
              s1.obj#,
              s1.ts# ) IN (
                SELECT /*+ unnest */
                    s2.dataobj#,
                    s2.obj#,
                    s2.ts#
                FROM
                    v$segstat s2
                WHERE
                    s2.obj# > 0
                    AND s2.obj# < 4254950912
                    AND ( decode(s2.statistic_name, 'logical reads', s2.value, 0) > :b10
                          OR decode(s2.statistic_name, 'physical reads', s2.value, 0) > :b9
                          OR decode(s2.statistic_name, 'buffer busy waits', s2.value, 0) > :b8
                          OR decode(s2.statistic_name, 'row lock waits', s2.value, 0) > :b7
                          OR decode(s2.statistic_name, 'ITL waits', s2.value, 0) > :b6
                          OR decode(s2.statistic_name, 'gc cr blocks received', s2.value, 0) > :b5
                          OR decode(s2.statistic_name, 'gc current blocks received', s2.value, 0) > :b4 )
            )
        GROUP BY
            s1.ts#,
            s1.obj#,
            s1.dataobj#
;

Wieder sieht man hier ein sehr ungewöhnliches Hint Format.

Wieder meldet aber die Report Funktion in 19c keinen Fehler und in unseren Test hat der Optimizer den Hint befolgt.

Jedoch bin ich diesmal mit der Wirkung des Hints nicht einverstanden.

Sehen wir uns dazu Laufzeitstatitiken an:

------------------------------------------------------------------------------
Id  | Operation                | Name          | Starts | A-Rows |   A-Time   
------------------------------------------------------------------------------
  0 | INSERT STATEMENT         |               |      1 |      0 |00:01:16.83 
  1 |  LOAD TABLE CONVENTIONAL | STATS$SEG_STAT|      1 |      0 |00:01:16.83 
  2 |   HASH GROUP BY          |               |      1 |    746 |00:01:16.83 
  3 |    NESTED LOOPS          |               |      1 |  19396 |00:01:16.81 
  4 |     VIEW                 | VW_NSO_1      |      1 |    747 |00:00:00.19 
  5 |      SORT UNIQUE         |               |      1 |    747 |00:00:00.19 
  6 |       FIXED TABLE FULL   | X$KSOLSFTS    |      1 |    814 |00:00:00.19 
  7 |     FIXED TABLE FULL     | X$KSOLSFTS    |    747 |  19396 |00:01:16.62 
------------------------------------------------------------------------------

Immerhin läuft auch dieser Befehl noch über eine Minute.

Die Zeit fällt fast zu 100% in der Zeile 7 an und zwar deshalb, weil die Zeile 747 Mal wiederholt wird.

Ein Hash join wäre hier wesentlich Laufzeit stabiler.
Der Nested Loop join hat Vorteile bei kleinen Datenmengen.

Allerdings spielt es ohnehin keine Rolle, welchen Join man nimmt, bei kleinen Datenmengen sind alle schnell.

Ich habe also den use_nl hint gegen einen use_hash hint getauscht.

Das ist das Resultat:

-------------------------------------------------------------------------------
Id  | Operation                | Name           | Starts | A-Rows |   A-Time   
-------------------------------------------------------------------------------
  0 | INSERT STATEMENT         |                |      1 |      0 |00:00:00.39 
  1 |  LOAD TABLE CONVENTIONAL | STATS$SEG_STAT |      1 |      0 |00:00:00.39 
  2 |   HASH GROUP BY          |                |      1 |    748 |00:00:00.39 
  3 |    HASH JOIN             |                |      1 |  19448 |00:00:00.38 
  4 |     VIEW                 | VW_NSO_1       |      1 |    748 |00:00:00.19 
  5 |      SORT UNIQUE         |                |      1 |    748 |00:00:00.19 
  6 |       FIXED TABLE FULL   | X$KSOLSFTS     |      1 |    815 |00:00:00.19 
  7 |     FIXED TABLE FULL     | X$KSOLSFTS     |      1 |    390K|00:00:00.13 
-------------------------------------------------------------------------------

Insgesamt läuft der Snapshot jetzt in 6 Sekunden durch.

Oracle APEX Ein Stolperstein im Upgrade auf Version 5.1

Oracle APEX: Ein Stolperstein im Upgrade auf Version 5.1

Beim Upgrade von Oracle APEX Applikationen auf die schon etwas ältere Version 5.1 wird gerne ein kleines Detail übersehen – vor allem auch deswegen weil die Auswirkungen nur dann sichtbar sind, wenn man alte und neue Version wirklich genau testet und vergleicht oder alternativ die Release Notes aufmerksam liest:

Im Abschnitt 1.4.4 der Release Notes befindet sich der Absatz:

End Date Displayed Inclusive — In release 5.0, the CSS calendar considered the end date of an all-day event as exclusive (similar to the jQuery FullCalendar Plugin).
In release 5.1, the end date is inclusive like all other Oracle Application Express components.

Das bedeutet, dass für die korrekte Darstellung eines mehrtägigen Termins in einer Calendar-Region in Version 5.0 folgende Abfrage notwendig war:

SELECT 'SomeCSSClass'         AS cal_class,
       t1.appointment_start   AS cal_date_from,
       t1.appointment_end + 1 AS cal_date_upto,
       'SomeLabel'            AS cal_label,
       'SomeLink'             AS cal_link
FROM   appointment_table t1

Mit dem Upgrade auf 5.1 entfällt die Notwendigkeit für das un-intuitive „+ 1“ und die Abfrage reduziert sich auf:

SELECT 'SomeCSSClass'         AS cal_class,
       t1.appointment_start   AS cal_date_from,
       t1.appointment_end     AS cal_date_upto,
       'SomeLabel'            AS cal_label,
       'SomeLink'             AS cal_link
FROM   appointment_table t1

Vor allem beim Überspringen von Applikations-Versionen kommt es dann mitunter vor, dass die Zwischenschritte ein wenig stiefmütterlich behandelt werden und sich Probleme wie dieses einschleichen.

Daher bietet es sich bei Oracle APEX Upgrades immer an, noch einmal einen kurzen Blick in die entsprechenden Release Notes zu werfen.

Long Parse aufgrund von Skew Detection in Hybrid Hash Distribution

Für einen detaillierten Überblick wie Skew Detection/Skew Handling in Hybrid Hash Distributionen funktioniert und welche Ziele es verfolgt, empfehle ich den sehr guten Blogeintrag von Randolf Geist zu lesen: https://oracle-randolf.blogspot.com/2014/05/12c-hybrid-hash-distribution-with-skew.html

Im Blog von Herrn Geist werden unter anderem sieben Vorbedingungen genannt, die nötig sind, damit dieses Feature wirksam wird.

Zumindest in Oracle 12.2 konnte ich auf Basis meiner Untersuchung eine weitere Vorbedingung finden:
Dynamic Sampling muss ebenfalls aktiviert sein (d.h auf einem Level > 0 konfiguriert sein; der Default ist 2).

Die rekursive Query die während der Optimierungsphase (=Hard Parse) ausgeführt wird, um einen Skew zu erkennen sieht nun auch ein wenig anders aus.

Zum Großteil wurden hier weitere Hints hinzugefügt.
Auf Oracle 12.2 hat sie nun folgende Form:

--kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH("ID"), COUNT(*) CNT, TO_CHAR("ID") FROM "ASC_SKEW_PART" SAMPLE(99.900000) SEED(1) GROUP BY "ID" ORDER BY CNT DESC, "ID") WHERE ROWNUM <= 2;

Was hier sofort auffällt ist die Größe des gewählten Samples (99.9%).

Nach ein paar Tests mit unterschiedlichen Tabellengrößen, kam ich zum Schluss, dass das Sample so gewählt wird, um ca. 5.500 Rows aus der Tabelle zu selektieren, um auf dieser Datenbasis die Skew Erkennung durchzuführen.

Die Beschränkung auf 5.500 Rows wurde in älteren Releases schon vorgenommen, wenn Histogramme im Zuge der Statistikberechnung erzeugt wurden. Für Tabellen die weniger als 5.500 Rows haben, wird das Sample mit 99,9% gewählt.

Sehen wir uns nun an unter welchen Umständen das zu sehr langen Parse Zeiten führen kann:

--#######################################
--# Small lookup table                  #
--#######################################
drop table asc_pids;
create table asc_pids
as
select 45e6 + rownum id
  from dual
 connect by level <= 13;
 
--#######################################
--# Big, partitioned fact table         #
--#######################################
--5 mio records in each partition from P1 to P9
--only 4 records in partition P10
drop table asc_skew_part;
create table asc_skew_part
nologging
partition by range(id)
(
   partition P1 values less than (5e6),
   partition P2 values less than (10e6),
   partition P3 values less than (15e6),
   partition P4 values less than (20e6),
   partition P5 values less than (25e6),
   partition P6 values less than (30e6),
   partition P7 values less than (35e6),
   partition P8 values less than (40e6),
   partition P9 values less than (45e6),
   partition P10 values less than (50e6)
)
as
with a as
(
    select rownum id
      from dual
     connect by level <= 1e5
)
select /*+ parallel(4) */rownum id, 
       lpad('*', 255, '*') padding
  from a, a
 where rownum <= 45e6
union all
select 45e6 + mod(rownum, 2) + 1 id,
       lpad('*', 255, '*') padding
  from dual
 connect by level <= 4;
 
--#######################################
--# histograms on id on fact table      #
--####################################### 
exec dbms_stats.gather_table_stats(user, 'asc_skew_part', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');


--#######################################
--# clear RC                            #
--#######################################
exec dbms_result_cache.flush();

Ich habe eine große, partitionierte Faktentabelle erstellt. Darin befinden sich jeweils 5 Millionen Rows in den Partitionen P1 bis P9 und nur 4 Rows in der letzten Partition P10 (2 Rows pro Value, das reicht um die notwendige Vorbedingung für die Skew Erkennung zu erfüllen).

Nachdem ich Histogramme auf der Faktentabelle erzeugt und den Result Cache (aufgrund des Hints in der rekursiven Query) geflushed habe, joine ich diese Tabelle nun zur kleinen Lookup-Tabelle.
Dabei schränke ich in der where Klausel aber so ein, dass nur Datensätze der kleinen Partition P10 selektiert werden.

--#######################################
--# note the long parsing time          #
--#######################################
explain plan for
select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH)*/*
  from asc_skew_part a, asc_pids b
 where a.id = b.id
   and a.id between 45000000 and 50000000;

Die rekursive Query die im 10053 Event/Optimizer Trace ersichtlich ist, ist exakt jene die ich schon zu Beginn des Posts gezeigt habe:

--kkopqSkewInfo: Query:SELECT /* DS_SKEW */ /*+ RESULT_CACHE no_parallel dynamic_sampling(0) no_sql_tune no_monitoring */ * FROM (SELECT SYS_OP_COMBINED_HASH("ID"), COUNT(*) CNT, TO_CHAR("ID") FROM "ASC_SKEW_PART" SAMPLE(99.900000) SEED(1) GROUP BY "ID" ORDER BY CNT DESC, "ID") WHERE ROWNUM <= 2;

Hier wurde während des Parsens ein 99,9% Sample über alle Partitionen in einer Tabelle mit 45 Millionen Rows gelesen, eine Hash Funktion auf der Join Spalte angewandt und aggregiert.

Es sieht so aus als wäre die Größe des Samples nur von der kleinen Partition P10 (4 Rows) abgeleitet worden, die ich mit meiner where Klausel treffe.

Angewandt wurde das Sampling dann aber über die gesamte Tabelle, eine where Klausel um auf die entsprechende Partition zu filtern gibt es in der rekursiven Query nicht.

Im konkreten Fall ist das auf einer Produktions-DB mit Milliarden von Rows und hunderten Partitionen aufgetreten, woraufhin das bl0ße Parsen über Stunden gedauert hat.

Update: 

Offensichtlich wird die Anzahl der Zeilen, die der Optimizer nach Anwendung der Filter in der where Klausel schätzt, als Basis für die Größe des Samples herangezogen.

Wenn wir eine Tabelle mit 1 Mrd. Rows hätten, dabei ein Wert besonders oft vorkommen würde (≥30% defaultmäßig) und die anderen eindeutig wären, würde ein Filter auf einen der eindeutigen Werte zu einem Sample von 99,9% in der rekursiven Query führen (da der CBO 1 Row nach dem Filter schätzen würde; 1 ≤ 5,500 deswegen → 99,9%).

Natürlich müssen alle anderen Vorbedingungen (Histogramme, Hash Join, etc.) natürlich weiterhin erfüllt sein.

Um eine Zeile aus einer Tabelle von 1 Mrd. Rows zu lesen, möchte man es wahrscheinlich vermeiden 99,9% aller Rows während des Parsens zu lesen und zu aggregieren 😉

Beispiel:

create table asc_skew
nologging
as
with generator as
(
   select *
     from dual
    connect by level <= 1e4
)
select (case when mod(rownum, 2) = 0 then -1 else rownum end)            
       id, 
       rpad('*', 255, '*') padding
  from generator, generator
 where rownum <= 50e6;

create table asc_pids
as
select rownum id
  from dual
 connect by level <= 13;

exec dbms_stats.gather_table_stats(user, 'asc_skew', method_opt=>'for all columns size 1 for columns id size 255', granularity=>'ALL');

--this will parse very long
explain plan for
select /*+ leading(b a) use_hash(a) parallel(8) pq_distribute(a HASH HASH)  */*
  from asc_skew a, asc_pids b
 where a.id = b.id
   and a.id = 42;

Fazit:

Meiner Meinung nach sollte die Auswahl des Samples angepasst werden auf die Größe des Segments vor der Anwendung der where Klausel.

Weiters sollte auf partitionierte Tabellen besonders Rücksicht genommen werden.

Eventuell wäre es auch sinnvoll bei 99,9% komplett auf die sample Klausel zu verzichten, da diese z.B auch Smart Scans auf Exadata verhindert.