Oracle 11g feature van de maand: SQL Plan Management

Oktober 2008 - Het is de boze droom van menig DBA: één of meerdere wijzigingen op database niveau zorgen ervoor dan een eens prima performende query opeens niet meer vooruit te branden is, omdat de Oracle-optimizer in al zijn wijsheid heeft besloten dat het executieplan gewijzigd diende te worden.

Voorbeelden van genoemde wijzigingen zijn bv. een plotseling verschil in data-omvang van één of meerdere tabellen, een aanpassing in systeemparameters, of, last but not least, een upgrade van de Oracle-software naar een hogere release.

Oracle 11g biedt in dergelijke situaties soelaas in de vorm van de nieuwe optie SQL Plan Management. In dit Whitebook wordt ingegaan op de betekenis van deze optie, de architectuur, alsmede de verschillen tussen SQL Plan Management en Stored Outlines. Deze laatste optie wordt sinds Oracle Database Release 8 aangeboden om het zoekpad dat de Oracle optimizer gebruikt, te beïnvloeden.

Definitie

Oracle omschrijft (het nut van) SQL Plan Management als volgt:

"SQL Plan Management (SPM) ensures that runtime performance will never degrade due to the change of an execution plan.
To guarantee this, only accepted (trusted) execution plans will be used; any plan evolution will be tracked and evaluated at a later point in time and only be accepted as verified if the new plan causes no runtime change or an improvement of the runtime".

Belangrijke opmerking hierbij is dat bovenstaand proces van evaluatie en mogelijke acceptatie van een gewijzigd executieplan zowel handmatig als automatisch (dus door Oracle gereguleerd) kan worden uitgevoerd.

Architectuur

De basis binnen SQL Plan Management wordt gevormd door de zogenoemde SQL Plan Baseline. Een baseline is een verzameling van één of meer executieplannen behorend bij een specifiek sql-statement, zoals door de Oracle optimizer is bepaald. De baselines worden opgeslagen in de zogenoemde SQL Management Base (SMB). Deze bevindt zich in de SYSAUX-tablespace.

Oracle biedt de mogelijkheid om handmatig een bulkload van executieplannen uit te voeren voor het aanleggen van deze baselines, met als bron SQL Tuning Sets en AWR Snapshots (eventueel uit een andere database, derhalve nuttig bij software-upgrades!), alsmede de 'lopende' cursor cache. Wanneer voor deze aanpak wordt gekozen worden alle geladen plannen met de status 'geaccepteerd' toegevoegd aan bestaande of nieuw te creeren SQL Plan Baselines.

Er kan ook voor worden gekozen om Oracle automatisch SQL Plan Baselines te laten aanleggen voor statements die worden aangemerkt als 'repeatable'. Met repeatable wordt hier bedoeld dat het statement is aangemerkt als één dat reeds meerdere keren is uitgevoerd. Oracle bepaalt dit met behulp van de SQL Log, een onderdeel van de SMB. De 'identiteit' (signature) van elk nieuw gecompileerd statement wordt opgeslagen in de SQL Log. Wanneer hetzelfde statement op een later tijdstip opnieuw wordt gecompileerd en/of uitgevoerd, herkent Oracle de signature van het statement, waarna deze als 'repeatable' wordt aangemerkt.

Om het automatisch aanleggen van SQL Plan Baselines te bewerkstelligen dient de dynamische parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES de waarde TRUE te krijgen (default is dit FALSE). De beslisboom die Oracle gebruikt wanneer gekozen is voor het automatisch aanleggen van SQL Plan Baselines is weergegeven in afbeelding 1.

 
Afbeelding 1: Beslisboom ten aanzien van automatische aanleg van SQL Plan Baselines

Nadat voor een statement eenmaal een SQL Plan baseline is aangelegd, zal Oracle te allen tijde bij het uitvoeren van deze query het meest optimale executieplan gebruiken zoals vastgelegd in de baseline, ongeacht eventuele wijzigingen in data, systeemparameters etc.

Dit betekent niet dat een beter executieplan voor het betreffende statement nooit kan 'evolueren' tot het geldende, te gebruiken executieplan. Wanneer de optimizer een gunstige wijziging ontdekt in het plan dat voor de query bedacht wordt (bv. wanneer een index wordt gecreëerd ten gunste van een query die voorheen resulteerde in een full table scan) wordt dit gewijzigde plan toegevoegd aan de SQL Plan Baseline historie, echter met de status 'niet geaccepteerd'. In de praktijk betekent dit, dat het gewijzigde plan (nog) niet door Oracle kan worden gebruikt. Een 'evolutie' van dit nog ongeaccepteerde plan naar een geaccepteerd plan zal daadwerkelijk het meer optimale plan (één waarin de index gebruikt wordt) effectueren.

Bovenstaande illustreert de 'conservatieve' strategie die Oracle heeft gekozen binnen SQL Plan Management. Een executieplan waarvan Oracle weet dat het optimaler is dan een geldend exectieplan voor een query, wordt wel opgenomen als kandidaat voor de baseline, maar (nog) niet als zodanig aangemerkt. Een vervolgstap, hetzij via handmatig ingrijpen door de DBA, hetzij automatisch binnen OEM, kan dit optimale plan promoveren tot actueel, geldend plan. Dit mechanisme zorgt er uiteraard voor dat een suboptimaal executieplan nooit de voorkeur zal krijgen boven een optimaal plan. Enerzijds zal een DBA uiteraard nooit een plan accepteren dat minder optimaal is dan een reeds bekend plan. Anderzijds zal Oracle, in het geval van 'automatische planpromotie' een plan met een hogere cost-waarde niet laten prevaleren boven een plan waarvan de cost lager uitvalt. Afbeelding 2 toont de beslisboom die Oracle hanteert bij plan-selectie binnen SQL Plan Management.

 
Afbeelding 2: Beslisboom ten aanzien van plankeuze binnen SQL Plan Management

Verschillen met Stored Outlines

Sinds Oracle Database Release 8 wordt de mogelijkheid geboden om executieplannen te fixeren en Oracle te dwingen een vastgesteld plan te allen tijde te gebruiken voor een specifiek statement. Deze optie staat bekend onder de naam Stored Outlines. Eigenlijk borduurt SQL Plan Management voort op dit concept. Waar Stored Outlines statisch zijn, en niet kunnen 'evolueren' naar een meer optimaal plan, biedt SQL Plan Management deze flexibiliteit wel. Daarnaast plaatst Oracle alle in de tijd gegenereerde executieplannen voor een specifiek statement (al dan niet met status 'geaccepteerd') in de SQL Plan Baseline. Op deze manier wordt een plan-historie aangelegd welke van nut kan zijn bij verdere analyses van de database.

Een voorbeeld

De werking van SQL Plan Management kan het beste geïllustreerd worden aan de hand van een voorbeeld. Het in deze paragraaf gebruikte voorbeeld is vrij vertaald naar http://www.pythian.com/blogs/584/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app We maken gebruik van een testtabel, PLAN_TEST, met één kolom (WAARDE). De tabel bevat 100000 records.

create table plan_test (waarde number);

 Tabel is aangemaakt.

 begin
   for i in 1..100000 loop
  insert into plan_test values (1);
 end loop; 

 commit;       

 PL/SQL-procedure is geslaagd.

 exec dbms_stats.gather_table_stats(user,'PLAN_TEST');

 PL/SQL-procedure is geslaagd. 

Vervolgens voeren we een query uit op deze tabel die, bij gebrek aan een index, een full table scan oplevert.

set autot trace
 select * from plan_test where waarde = 59374;

Er zijn geen rijen geselecteerd.

 Uitvoeringsplan
 ----------------------------------------------------------
 Plan hash value: 534695957

 -------------------------------------------------------------------------------
 | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |           |     1 |     3 |    44   (3)| 00:00:01 |
 |*  1 |  TABLE ACCESS FULL| PLAN_TEST |     1 |     3 |    44   (3)| 00:00:01 |
 -------------------------------------------------------------------------------

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

    1 - filter("WAARDE"=59374) 

We kunnen Oracle voor dit statement nu een SQL Plan Baseline laten aanleggen. Hiervoor is het SQL_ID benodigd. Dit halen we op aan de hand van de plan_hash_value.

set autot off
 select distinct sql_id
 from   v$sql_plan
 where  plan_hash_value=534695957;

 SQL_ID
 -------------
 fku8y2j69j2n5

 declare
   plan_out binary_integer;
 begin
   plan_out:=dbms_spm.load_plans_from_cursor_cache(
                  sql_id=>'fku8y2j69j2n5');
 end;


 PL/SQL-procedure is geslaagd.

In de view DBA_SQL_PLAN_BASELINES is het resultaat van bovenstaande actie te zien. Er is een baseline aangemaakt, met de waarde 'YES' in de kolommen ENABLED en ACCEPTED.

select SQL_HANDLE,
        PARSING_SCHEMA_NAME SCHEMA,
        ENABLED,
        ACCEPTED
   from dba_sql_plan_baselines;
 SQL_HANDLE               PLAN_NAME                      SCHEM ENA ACC 
 ------------------------ ------------------------------ ----- --- --- 
 SYS_SQL_52d3fb141055ee95 SYS_SQL_PLAN_1055ee9580dcf3a7  SYSTE YES YES  

Het is duidelijk dat deze query geoptimaliseerd kan worden door de creatie van een index op kolom WAARDE. Oracle zal het executieplan na het aanmaken van de index echter niet wijzigen. Dit is het gevolg van de eerder genoemde 'conservatieve' aanpak.

create index plan_test_idx on plan_test(waarde);

 Index is aangemaakt.

 alter system flush shared_pool;

 Systeem is gewijzigd.

 set autot trace

 select * from plan_test where waarde = 59374;

 Uitvoeringsplan
 ----------------------------------------------------------
 Plan hash value: 534695957

 -------------------------------------------------------------------------------
 | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |           |     1 |     3 |    44   (3)| 00:00:01 |
 |*  1 |  TABLE ACCESS FULL| PLAN_TEST |     1 |     3 |    44   (3)| 00:00:01 |
-------------------------------------------------------------------------------

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

    1 - filter("WAARDE"=59374)

 Note
 -----
    - SQL plan baseline "SYS_SQL_PLAN_1055ee9580dcf3a7" used for this statement 

Hoewel Oracle dus nog kiest voor het plan met de full table scan, is er wel een nieuwe SQL Plan Baseline toegevoegd voor het statement. Het executieplan waarin gebruik wordt gemaakt van de index heeft echter de waarde 'NO' voor kolom ACCEPTED.

set autot off

 select SQL_HANDLE,
        PLAN_NAME,
        PARSING_SCHEMA_NAME SCHEMA,
        ENABLED,
        ACCEPTED
   from dba_sql_plan_baselines;

 SQL_HANDLE               PLAN_NAME                      SCHEM ENA ACC 
 ------------------------ ------------------------------ ----- --- --- 
 SYS_SQL_52d3fb141055ee95 SYS_SQL_PLAN_1055ee9580dcf3a7  SYSTE YES YES 
 SYS_SQL_52d3fb141055ee95 SYS_SQL_PLAN_1055ee95c0f5e63d  SYSTE YES NO 

 select * from table(
    dbms_xplan.display_sql_plan_baseline(
           sql_handle=>'SYS_SQL_52d3fb141055ee95'));
       
 PLAN_TABLE_OUTPUT
 -------------------------------------------------------------------------------

 -------------------------------------------------------------------------------
 SQL handle: SYS_SQL_52d3fb141055ee95
 SQL text: select * from plan_test where waarde = 59374
 -------------------------------------------------------------------------------

 -------------------------------------------------------------------------------
 Plan name: SYS_SQL_PLAN_1055ee95c0f5e63d
 Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
-------------------------------------------------------------------------------

 Plan hash value: 1081803808

 -------------------------------------------------------------------------------
 | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time  |
 -------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT |               |     1 |     3 |     1   (0)| 00:01 |
 |*  1 |  INDEX RANGE SCAN| PLAN_TEST_IDX |     1 |     3 |     1   (0)| 00:01 |
 -------------------------------------------------------------------------------

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

    1 - access("WAARDE"=59374)

 -------------------------------------------------------------------------------
 Plan name: SYS_SQL_PLAN_1055ee9580dcf3a7
 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
-------------------------------------------------------------------------------

 Plan hash value: 534695957

 -------------------------------------------------------------------------------
 | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |           |     1 |     3 |    44   (3)| 00:00:01 |
 |*  1 |  TABLE ACCESS FULL| PLAN_TEST |     1 |     3 |    44   (3)| 00:00:01 |
 -------------------------------------------------------------------------------

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

    1 - filter("WAARDE"=59374)

 43 rijen zijn geselecteerd.

Omdat we weten dat het executieplan waarbij gebruik wordt gemaakt van de index efficiënter is dan dat waarin een full table scan plaatsvindt (een cost van 1 tegenover een cost van 44), wordt het tijd het plan te laten 'evolueren'.

var v_out clob
 exec :v_out :=
   dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_52d3fb141055ee95')
 print v_out
 
 select SQL_HANDLE,
        PLAN_NAME,
        ENABLED,
        ACCEPTED
 from dba_sql_plan_baselines;

 SQL_HANDLE                     PLAN_NAME                      ENA ACC
 ------------------------------ ------------------------------ --- ---
 SYS_SQL_52d3fb141055ee95       SYS_SQL_PLAN_1055ee9580dcf3a7  YES YES
 SYS_SQL_52d3fb141055ee95       SYS_SQL_PLAN_1055ee95c0f5e63d  YES YES 

Na de evolutie van het executieplan zien we dat beide plannen geregistreerd staan als geaccepteerd. Omdat het executieplan met de index een beduidend lagere cost heeft, zal Oracle dit plan laten prevaleren. Alleen in het geval dat de index gedropt wordt, zal op het oude, oorspronkelijke plan worden teruggevallen. Als er nu een explain plan wordt gemaakt van het betreffende statement, zien we dat er netjes gebruikt wordt gemaakt van de index.

set autot trace

 select * from plan_test where waarde = 59374;

 Er zijn geen rijen geselecteerd.

 Uitvoeringsplan
 ----------------------------------------------------------
 Plan hash value: 1081803808

 -------------------------------------------------------------------------------
 | Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time  |
-------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT |               |     1 |     3 |     1   (0)| 00:01 |
 |*  1 |  INDEX RANGE SCAN| PLAN_TEST_IDX |     1 |     3 |     1   (0)| 00:01 |
-------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
    1 - access("WAARDE"=59374)

 Note
 -----
    - SQL plan baseline "SYS_SQL_PLAN_1055ee95c0f5e63d" used for this statement

Conclusie

SQL Plan Management is zonder twijfel een welkome aanvulling op het optiepakket binnen de Oracle database. Het geeft de DBA een krachtig middel om invloed uit te oefenen op de keuzes die de Oracle optimizer maakt ten aanzien van het executieplan voor een statement. De DBA kan ervoor kiezen een door de optimizer voorgesteld plan al dan niet handmatig te accepteren en derhalve te laten toevoegen aan de baseline als zijnde geldend.

De vraag rijst of de optie om Oracle de vrije hand geven in het aanleggen van de SQL Plan Baselines voor repeatable queries en om deze zelfstandig al dan niet te 'promoveren' tot geaccepteerd, wel zo'n verstandige keuze is. Zeker als het een produktie omgeving betreft. Hoewel het algoritme dat Oracle gebruikt degelijk en weloverwogen is, is de DBA op dat moment wel de grip kwijt op het executieplan dat Oracle gaat gebruiken. En dat is nu juist iets dat met SQL Plan Management tot het verleden zou moeten behoren.

Referenties

• Oracle 11g Release 1 Performance Tuning Guide, hoofdstuk 15
• Oracle Whitepaper - SQL Plan Management in Oracle Database 11g
• Oracle Technoloy Network: Oracle Database 11g: The Top New Features for DBAs and Developers