In manchen Situation ist es sinnvoll und/oder notwendig, dem Optimizer den „richtigen“ weil schnelleren Ausführungsplan mitzuteilen. Dazu sind die mit Oracle 11g eingeführten SQL Plan Baselines gut geeignet. Im Gegensatz zu SQL Profile und Outlines beinhalten diese nicht eine Vielzahl an HINTs, um dem Optimizer einen besseren Ausführungsplan nahe zu legen. In einem SQL Plan Baseline wird einem SQL direkt der entsprechende Ausführungsplan zugeordnet.
Ein Beispiel aus der Praxis:
Der Optimizer hat für ein und dasselbe SQL zu unterschiedlichen Zeiten verschiedene Ausführungspläne. Im vorliegenden Fall handelte es sich um einen Report, der alle 10 Minuten ausgeführt wurde. Dieser Report bestand aus mehreren SQL Anweisungen, von denen jedoch eins besonders hervorsticht – aufgrund seiner enormen Laufzeit von bis zu 7 Minuten. Allerdings nutze der Optimizer in der Zeit von 23:00 bis 07:00 einen anderen Ausführungsplan. In dieser Zeitspanne lag die durchschnittliche Laufzeit unter einer Minute, wenngleich die Kosten des Ausführungsplans ähnlich groß waren.
Bei einem Versuch mit einem HINT wurde dann auch tagsüber ein anderer Ausführungsplan genutzt. Dieses dann in sql*plus ausgeführt zeigte einen ähnlichen Vorteil wie innerhalb der Reporting Engine.
Da das SQL von der Reporting Engine erzeugt wurde, lag der Wunsch auf der Hand, den nächtlichen Ausführungsplan auch tagsüber nutzen zu lassen. Deshalb erstellte ich folgendes kleines PL/SQL Script, um dies zu automatisieren und bei Bedarf auch auf andere SQL anzuwenden:
Im Declarationsteil muss man lediglich die SQL_ID und denjenigen PLAN_HASH_VALUE (aus V$SQL) einsetzen, den man diesem SQL zuordnen möchte:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines; DECLARE j NUMBER := 0; c_sql_id VARCHAR2(30 CHAR) := '2gugugugugu0a'; c_plan NUMBER := 3636363636; c_prefix VARCHAR2(3 CHAR) := 'PL_'; c_plan_name VARCHAR2(30 CHAR) := UPPER( substr(c_prefix||c_sql_id||'_'||c_plan,1,30) ); BEGIN EXECUTE immediate('alter session set optimizer_use_sql_plan_baselines=TRUE'); -- drop all Plan Baselines -- FOR i IN (SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE creator = USER AND plan_name LIKE c_prefix ||'%' AND trunc(created) = trunc(sysdate)) loop j := DBMS_SPM.DROP_SQL_PLAN_BASELINE( SQL_HANDLE => i.sql_handle , PLAN_NAME => i.plan_name ); END loop; -- get one new baselines, SQL_CHILD will be generated automatically -- for this SQL_ID and PLAN_HASH_VALUE -- j := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( SQL_ID => c_sql_id , PLAN_HASH_VALUE => c_plan ); -- set Attributes for the new Baseline -- FOR i IN (SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE creator = USER AND enabled = 'YES' AND trunc(created) = trunc(sysdate)) loop j := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE => i.sql_handle , PLAN_NAME => i.plan_name , ATTRIBUTE_NAME => 'fixed' , ATTRIBUTE_VALUE => 'YES' ); j := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE => i.sql_handle , PLAN_NAME => i.plan_name , ATTRIBUTE_NAME => 'autopurge' , ATTRIBUTE_VALUE => 'NO' ); j := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( SQL_HANDLE => i.sql_handle , PLAN_NAME => i.plan_name , ATTRIBUTE_NAME => 'plan_name' , ATTRIBUTE_VALUE => c_plan_name ); END loop; END; / SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines; |