Hinty bez ingerencji w treść zapytania? SQL Patch!

Od czasu do czasu pojawia się konieczność skorygowania decyzji optymalizatora zapytań, który pomimo poprawnych i aktualnych statystyk uparcie nie chce wybrać najlepszego planu wykonania (query execution plan). Większość deweloperów sięga wtedy po wskazówki sterujące (hinty), umieszczane w treści problematycznego zapytania. Jest to metoda skuteczna, o ile mamy możliwość edycji treści zapytania – co bywa trudne w przypadku, gdy „leczymy” cudzą aplikację o zamkniętym kodzie źródłowym. Istnieje jednak „półlegalne” rozwiązanie, dzięki któremu możemy poprosić serwer bazy danych Oracle Database o dopisywanie w naszym imieniu hintów do zapytań, których treść jest zgodna z zadeklarowanym przez nas wzorcem. Mechanizm ten nazywa się „SQL Patch” i jest wykorzystywany wewnętrznie przez SQL Repair Advisor będący częścią Support Workbench. Jego „półlegalność” polega na niedostępności oficjalnej dokumentacji dla pakietu DBMS_SQLDIAG_INTERNAL, wykorzystywanego do manipulowania wstrzykiwanymi hintami.

Aby skorzystać z mechanizmu automatycznego dopisywania hintów, należy za pomocą DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH() zdefiniować wzorzec treści zapytania, któremu zamierzamy wstrzykiwać hinty oraz zdefiniować hint/hinty, które będą wstrzykiwane. Oto przykład zastosowania – naszym celem będzie automatyczne wstrzyknięcie hintu PARALLEL(2) do podanego zapytania (poniżej najpierw wykonanie bez hintu, potem ze wstrzykniętym hintem). Na zakończenie usuniemy SQL Patch – tym razem całkowicie legalnie posługując się pakietem DBMS_SQLDIAG.


SQL> variable b1 number;
SQL> begin :b1:=50; end;
/
SQL> select max(prod_name) from prods_by_id where prod_price = :b1;
...
Execution Plan
-----------------------------------------------------------
| Id  | Operation                        | Name           |
-----------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |
|   1 |  SORT AGGREGATE                  |                |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| PRODS_BY_ID|
|*  3 |    INDEX RANGE SCAN              | IND_PRICE      |
-----------------------------------------------------------
...
SQL> exec sys.dbms_sqldiag_internal.i_create_patch(
  sql_text=>'select max(prod_name) from prods_by_id where prod_price = :b1',
  hint_text=>'PARALLEL(2)', name=>'mypatch001');
 
SQL> select max(prod_name) from prods_by_id where prod_price = :b1;
...
Execution Plan
--------------------------------------------------------------
| Id  | Operation                          | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |
|   1 |  SORT AGGREGATE                    |                 |
|   2 |   PX COORDINATOR                   |                 |
|   3 |    PX SEND QC (RANDOM)             | :TQ10001        |
|   4 |     SORT AGGREGATE                 |                 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| PRODS_BY_ID|
|   6 |       PX RECEIVE                   |                 |
|   7 |        PX SEND HASH (BLOCK ADDRESS) | :TQ10000       |
|   8 |       PX SELECTOR                  |                 |
|*  9 |        INDEX RANGE SCAN            | IND_PRICE       |
--------------------------------------------------------------
...
Note
-----
- Degree of Parallelism is 2 because of hint
- SQL patch "mypatch001" used for this statement
...
 
SQL> select name, sql_text, status from dba_sql_patches
 
NAME       SQL_TEXT                                                      STATUS
---------- ------------------------------------------------------------- -------
mypatch001 select max(prod_name) from prods_by_id where prod_price = :b1 ENABLED
 
SQL> exec dbms_sqldiag.drop_sql_patch('mypatch001');

Uwagi końcowe: (1) nie z każdym rodzajem hintu współpracują SQL Patche, (2) zapytanie jest dopasowywane wyłącznie na podstawie tekstu, stąd należy zadbać o prefiksowanie nazw obiektów nazwami schematów. Więcej przykładów tutaj lub tutaj.