Das Ende der Oracle SQL Statement Laufzeit-Degeneration?!

Wer kennt das nicht? Ein SQL Statement ist jahrelang schnell gelaufen und plötzlich läuft es deutlich langsamer. Gerade die Einführung der Adaptiven Optimizer Funktionalitäten hat hier oft dazu geführt, dass andere Ausführungspläne genutzt werden. Diese führen leider oft zu (deutlich) längeren Laufzeiten.

Beginnend mit Oracle 11g gibt es seitens Oracle das SQL Plan Baseline Management. Allerdings hat das so seine Tücken:

In der Oracle Standard Edition (egal welcher) darf es pro SQL Statement nur einen Execution Plan geben und man darf keine Plan Evolution nutzen. Ohne ins Detail zu gehen: damit macht diese Funktionalität keinen Sinn.
In der Oracle Enterprise Edition muss sich der DBA um die Pflege der SQL Plan Baseline kümmern:
- initial befüllen
- bei Applikationsänderungen aktualisieren/erweitern
- gegebenfalls von Entwicklungsdatenbanken auf Produktionsdatenbanken kopieren
- SQL Plan Baseline Evolution: verifizieren und akzeptieren von neuen (besseren) Execution Plänen
  - DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (ab Oracle 11g)
  - DBMS_SPM.CREATE_EVOLVE_TASK (ab Oracle 12c)
Abhängig davon, ob die Applikation mit oder ohne Bind Variablen arbeitet und wie viele verschiedene SQL Statements genutzt werden, kann der Platzverbrauch im SYSAUX Tablespace „explodieren“.

Gerade mit Oracle 11g und teilweise in Oracle 12c waren noch diverse Kinderkrankheiten vorhanden, die Einem das Leben schwer gemacht haben. Aus diesem Grund wird diese Funktionalität bei vielen Datenbanken nicht eingesetzt.

Einschub: Was ist ASTS – automatic SQL Tuning Set – und wie konfiguriert man es?

Das automatic SQL Tuning Set (wurde mit 19.7, 21c eingeführt) ist ein von Oracle permanent gepflegtes SQL Tuning Set, in dem alle SQL Statements und deren Ausführungspläne hinterlegt werden. Vorsicht: Platzverbrauch im SYSAUX Tablespace!

Da dies zusätzliche Ressourcen benötigt, muss man es explizit einschalten (ausgenommen Oracle Cloud, dort ist es automatisch aktiv). SQL Statements, die mehr als 53 Wochen nicht genutzt wurden, werden aus dem ASTS wieder entfernt.

Wie überprüft man die aktuelle Einstellung für ASTS?

select Task_Name, Status, enabled,
       Interval Task_Interval_in_Seconds
  from DBA_AutoTask_Schedule_Control
 where Task_Name = 'Auto STS Capture Task';

TASK_NAME             STATUS    ENABLED TASK_INTERVAL_IN_SECONDS
--------------------- --------- ------- ------------------------
Auto STS Capture Task SUCCEEDED  FALSE                       900

Wenn ENABLED = FALSE, muss man ASTS aktivieren:

exec dbms_auto_task_admin.enable('Auto STS Capture Task',NULL,NULL);

Wie lange benötigt der Auto STS Job, um die neuen SQL Statements zu sammeln?

With dur As
(Select (To_Date('1','J')+Run_Duration-to_Date('1','J'))* 86400 Duration_Sec,
        (To_Date('1','J')+CPU_Used-to_Date('1','J'))* 86400 CPU_Used_Sec
   From DBA_Scheduler_Job_Run_Details
  Where job_name = 'ORA$_ATSK_AUTOSTS'
)
Select Min(Duration_Sec) Min_Time_Sec,
       Max(Duration_Sec) Max_Time_Sec,
       Avg(Duration_Sec) Average_Time_Sec,
       Avg(CPU_Used_Sec) Average_CPU_Sec
From dur; 

MIN_TIME_SEC MAX_TIME_SEC AVERAGE_TIME_SEC AVERAGE_CPU_SEC
------------ ------------ ---------------- ---------------
           1           18       8,14285714               1

Wie viel Platz belegen alle STS – SQL Tuning Sets?

WITH myobj as
(select segment_name as table_name, bytes
   from dba_segments
  where owner='SYS'
    and Segment_Name not like '%WORKSPA%'
    and (Segment_Name like 'WRI%SQLSET%'
         or Segment_Name like 'WRH$_SQLTEXT')
 union all
 select l.table_name, s.bytes
   from dba_segments s, dba_lobs l
  where l.owner='SYS'
    and l.table_name In ('WRI$_SQLSET_PLAN_LINES','WRH$_SQLTEXT')
    and s.segment_name = l.segment_name
    and s.owner=l.owner
)
select table_name, trunc(sum(bytes)/1024/1024,3) as MB 
  from myobj
  group by table_name
union all
select 'Summe: ', trunc(sum(bytes)/1024/1024,3) as MB 
  from myobj;

TABLE_NAME                             MB
------------------------------ ----------
WRI$_SQLSET_STATISTICS               ,125
WRH$_SQLTEXT                        7,187
WRI$_SQLSET_MASK_PK                  ,062
WRI$_SQLSET_DEFINITIONS_PK           ,062
WRI$_SQLSET_DEFINITIONS_IDX_01       ,062
WRI$_SQLSET_REFERENCES_PK            ,062
WRI$_SQLSET_PLAN_LINES_PK             ,25
WRI$_SQLSET_MASK                     ,062
WRI$_SQLSET_PLANS_PK                 ,062
WRI$_SQLSET_PLANS                    ,375
WRI$_SQLSET_PLAN_LINES              4,187
WRI$_SQLSET_BINDS                    ,062
WRI$_SQLSET_DEFINITIONS              ,062
WRI$_SQLSET_STATEMENTS_PK            ,062
WRI$_SQLSET_BINDS_PK                 ,062
WRI$_SQLSET_STATEMENTS               ,062
WRI$_SQLSET_STATEMENTS_IDX_01        ,125
WRI$_SQLSET_REFERENCES               ,062
WRI$_SQLSET_STATEMENTS_IDX_02        ,125
WRI$_SQLSET_STATISTICS_PK            ,062
Summe:                             13,187

Ende Einschub ASTA.

Was ist jetzt das Neue am SQL Plan Baseline Management?

Beginnend mit Oracle 19.7 und 21c werden die automatic SQL Tuning Sets genutzt, um das SQL Plan Baseline Management zu verbessern. Allerdings gilt das nur für die Autonomous Datenbank (Oracle Cloud) und Exadata Systemen. Das Neue dabei ist, dass Oracle NACH der Ausführung eines SQL Statements mit einem neuen Execution Plan im ASTS verifiziert, ob die Ausführungszeit (deutlich) größer ist als mit den zuvor genutzten Ausführungsplänen. Ist das der Fall, wird automatisch der ALTE Ausführungsplan in die SQL Plan Baseline gestellt und somit der Execution Plan repariert.

Was sind die Vorteile dieser Vorgangsweise?

Man muss sich um die Pflege der SQL Plan Baseline nicht mehr kümmern – weder initial befüllen, noch für Evolution sorgen.
In der SQL Plan Baseline sind nur noch jene SQL Pläne hinterlegt, die wirklich benötigt werden. Dadurch ist der Platzverbraucht deutlich geringer – sowohl im SYSAUX Tablespace als auch im Buffer Cache und in der SQL Area.

Was kann man machen, wenn die Datenbank weder in der Oracle Cloud noch auf einer Exadata läuft?

Man muss mit Einschränkungen leben und sich selbst um das Evolvement kümmern. Das kann man durch die Konfiguration eines Auto Evolve Task – gibt es seit 19c – erreichen:

BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON');
END;
/

BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'AUTO');
END;
/

Dies kann zu zusätzlicher Last während des Laufs des EVOLVE Task führen. Das sollte in der Praxis aber nur selten vorkommen. In den meisten Fällen werden SQL Statements durch neue Ausführungspläne nicht langsamer.

Die aktuelle Konfiguration für den Auto SPM Task kann man sich in den folgenden Statements ansehen:

select * 
  from dba_autotask_schedule_control 
 where dbid = sys_context('userenv','con_dbid') 
   and task_name = 'Auto SPM Task';

TASK_NAME     STATUS      INTERVAL MAX_RUN_TIME ENABLED  ELAPSED_TIME LAST_SCHEDULE_TIME
------------- --------- ---------- ------------ -------- ------------ ----------------------------
Auto SPM Task SUCCEEDED       3600         1800 TRUE                0 19.10.22 14:24:28,682 +02:00

Wichtig ist dabei, dass der Parameter ACCEPT_PLANS auf TRUE steht:

select PARAMETER_NAME, PARAMETER_VALUE 
  from DBA_ADVISOR_PARAMETERS 
 where TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK'
   and PARAMETER_VALUE <> 'UNUSED';

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ------------------------------
ALTERNATE_PLAN_LIMIT           UNLIMITED
ALTERNATE_PLAN_SOURCE          AUTO
ALTERNATE_PLAN_BASELINE        AUTO
ACCEPT_PLANS                   TRUE
DAYS_TO_EXPIRE                 UNLIMITED
JOURNALING                     INFORMATION
MODE                           COMPREHENSIVE
TARGET_OBJECTS                 1
TIME_LIMIT                     3600
DEFAULT_EXECUTION_TYPE         SPM EVOLVE
EXECUTION_DAYS_TO_EXPIRE       30

Referenzen

Blog: What is Automatic SQL Plan Management?
Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)
Mike Dietrich: Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0.
White Paper: SQL Plan Management mit Oracle 11g
White Paper: SQL Pl an Management mit Oracle 12cR2