21-08-2013 door: Patrick Diks
Voor het hard vastzetten van een executieplan zijn er in Oracle verschillende methoden beschikbaar.
De meest toekomstvaste en stabiele voor 11g is nu het custom SQL profile. Dit is een variant van het door de tuning advisor aangemaakte SQL profile. Waar de tuning advisor een set hints met relatieve aanpassingen op statistieken oplevert geeft het custom profile een set directieven die een specifiek plan afdwingen. Dit is vergelijkbaar met een outline.
Oracle support levert hiervoor het script coe_xfr_sql_profile.sql (DOC ID 1487302.1), welke opgenomen is in de SQLT tool (DOC ID 215187.1).
Laatst liep ik hiermee tegen een probleem aan. Het script maakte een profile aan met de juiste hints, maar het profile werd niet gebruikt voor het betreffende statement.
Na onderzoek bleek dat de signature van het profile in DBA_SQL_PROFILES afweek van de signature zoals deze in V$SQL stond.
Hoe kon dit gebeuren? De profile was immers gemaakt vanuit hetzelfde SQL ID.
Het probleem zat hem in een truc die in het coe script gebruikt wordt om lange regels op te knippen:
| :sql_text := REPLACE(:sql_text, ‘)’, ‘)’||CHR(10)); :sql_text := REPLACE(:sql_text, ‘,’, ‘,’||CHR(10)); |
In het betreffende statement stond een conditie in ‘–‘ stijl commentaar:
| — AND to_char(a.kolom,’YYYY-MM-DD’) = b.kolom |
Het coe script maakt hier dit van:
| — AND to_char(a.kolom, ‘YYYY-MM-DD’) = b.kolom |
Hierin worden de 2e en 3e regel niet meer als commentaar gezien en verandert dus de signature van het statement.
Om dit te omzeilen heb ik het door het coe script gegenereerde script handmatig aangepast en de signature was weer gelijk.
Het profile werd opgepakt en het plan veranderde in het gewenste.
Een signature mismatch valt op doordat de aangemaakte profile niet in het ‘plan control’ tabblad verschijn in de enterprise manager en niet te zien is in de notes bij het opvragen van een dbms_xplan.display_cursor.