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
- [ 1 ] Küchler, Uwe, Historical SQL Plan from Statspack using DBMS_XPLAN in 12c, https://oraculix.com/2016/06/21/historical-sql-plan-from-statspack-using-dbms_xplan-in-12c/
- [ 2 ] Colgan, Maria, Using SQL Patch to add hints to a packaged application, https://blogs.oracle.com/optimizer/using-sql-patch-to-add-hints-to-a-packaged-application
- [ 3 ] Gelling, Kevin, Any_value Set function, https://community.oracle.com/ideas/13039#comment-1069168
- [ 4 ] Oracle , User-Defined Aggregate Functions Interface, https://docs.oracle.com/database/121/ADDCI/ext_agg_ref.htm#ADDCI4220
