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.
