Jak (najlżej) stroić SQL w Oracle?

Robota idzie najlżej gdy ktoś zrobi ją za Ciebie. W przypadku strojenia SQL będzie to Tuning Advisor dostępy w motorze baz danych firmy Oracle. Pokażę Ci najprostsze przykłady jego użycia.

Koniecznie skomentuj jak znajdujesz użyteczność Tuning Advisora.

Materiał na YouTube

Spis treści

Wstęp

Administrator baz danych jest pierwszą osobą z którą kontaktuje się wsparcie aplikacji gdy użytkownicy skarżą się, że ich apka nie hula. Słyszy wtedy 'Wczoraj działało. Dziś nie działa. Nie było żadnych zmian. Do the needful.’

Oczywiście najlepiej byłoby przyjrzeć sie planom wykonania z wczoraj i dziś. A także zerknąć czy nie było jakiejś dramatycznej zmiany w danych.

Będę szczery: nie zawsze jest wola i ochota aby robić to ręcznie.

W takich momentach świetnym pomocnikiem może okazać się Tuning Advisor, który w drobne kilka minut wygeneruje mniej lub bardziej sensowne rady jak dogodzić użytkownikom.

Rady przychodzą w formie komend SQL i dotyczą zmian w schemacie bazy danych. Na przykład modyfikacji indeksów czy policzenia brakujących statystyk. Generują także 'lepszy’ plan wykonania, który można wymusić profilem. Nawet jeśli nie są trafione centralnie to mogą stanowić punkt wyjścia do dalszego ręcznego strojenia. Natomiast gdy są trafione to zaoszczędzisz sporo swojego czasu.

Najprostsze użycie

W tym materiale pokażę najprostsze sposoby ręcznego użycia Tuning Advisora. Na przykład z sqlplus, SQLcl lub SQL Developer.

Jeśli masz sql_id problematycznego zapytania i jest ono jeszcze w pamięci podręcznej to można przejść do kroku pierwszego, którym jest utworzenie zadania strojącego.

DECLARE
    stmt_task VARCHAR2(40);
BEGIN
    stmt_task := dbms_sqltune.create_tuning_task(sql_id => 'f82bjuz7qsnd9');
    dbms_output.put_line('task_id: ' || stmt_task);
END;
/

Jedynym obowiązkowym parametrem jest sql_id. Zadziała o ile robisz to na tyle szybko, że zapytanie nie zostanie usunięte z cache. Funkcja zwraca nazwę zadania, ale możesz także nadać swoją parametrem task_name. Warto mieć ją gdzieś pod palcem. Będzie potrzebna do kolejnych poleceń.

Na przykład do uruchomienia zadania.

Exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_55397');

Wykonanie tej procedury może potrwać kilka minut.

Trzeba pamiętać, że normalnie optymalizator ma bardzo mało czasu na opracowanie planu wykonania Twojego zapytania. Po tym czasie użyje najlepszego jaki znalazł.

Dzięki Tuning Advisorowi dajesz optymalizatorowi więcej czasu. Czyli zwiększasz szanse na znalezienie najlepszego planu wykonania oraz analizę struktury. Limit czasu podajesz w sekundach parametrem time_limit.

DECLARE
    stmt_task VARCHAR2(40);
BEGIN
    stmt_task := dbms_sqltune.create_tuning_task(sql_id => 'f82bjuz7qsnd9', time_limit => 2222);
    dbms_output.put_line('task_id: ' || stmt_task);
END;
/

Wyszukiwanie najlepszego planu wykonania jest żmudną czynnością. Spróbuj kiedyś samodzielnie to się przekonasz.

Optymalizator ma decydujący głos w sprawie kolejności i algorytmów łączenia zbiorów danych. Próbuje wielu możliwych kombinacji aż znajdzie taką, która wydaje mu się najtańsza. Czyli taka, która będzie kosztować motor bazy danych najmniej zasobów.

Jeśli wystawia Twoją cierpliwość na próbę to status zadania możesz sprawdzić tak:

SELECT
    task_name,
    status
FROM
    dba_advisor_log
WHERE
    task_name = 'TASK_55397';

Jeśli zobaczysz COMPLETED jako status to znaczy, że zlecone przez Ciebie zadanie zostało wykonane.

Teraz możesz generować raport.

Generowanie raportu

Po zakończeniu zadania możesz wygenerować raport funkcją REPORT_TUNING_TASK.

SELECT
    dbms_sqltune.report_tuning_task('TASK_55397') AS "Rady"
FROM
    dual;

Jeśli używasz sqlplus to wcześniej podbij mu limity aby raport się zmieścił.

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 9999 PAGESIZE 9999

To jest przykładowy raport tuning advisora:

W dokumentacji opisane są poszczególne sekcje raportu. Podobnie jak cały Tuning Advisor. Interpretacja raportu zależy oczywiście od Twojej wiedzy eksperckiej. Warto pamiętać, że są to rady automatu. Tylko tak dobre jak dane na jakich pracuje. Niemniej do każdej rady dołączone jest uzasadnienie razem z oczekiwaną poprawą wyrażoną w procentach. Warto uważnie czytać te uzasadnienia aby wiedzieć czy powody dla których Tuning Advisor zarekomendował zmianę mają sens w Twoim środowisku.

SQL_ID nie ma w cache

Pewną niedogodnością jest gdy masz sql_id, ale zapytania nie ma już w cache.

W takich przypadkach możesz posiłkować się danymi przechowywanymi przez AWR. Znajdź numery snapshotów z zapytaniem do strojenia w repozytorium. Możesz użyć takiego zapytania:

SELECT
    ss.snap_id,
    ss.begin_interval_time
FROM
         dba_hist_sqlstat st
    JOIN dba_hist_snapshot ss ON st.snap_id = ss.snap_id
WHERE
    st.sql_id = 'gxc2y95uf2wmw'
ORDER BY
    ss.begin_interval_time DESC;

Wybrane numery użyj tworząc zadanie dla Tuning Advisora

SELECT
    dbms_sqltune.create_tuning_task(begin_snap => 1234, end_snap => 5678, sql_id => 'f82bjuz7qsnd9')
FROM
    dual;

Ważne aby begin_snap był mniejszy niż end_snap.

Mam tylko tekst zapytania

Oracle przygotował wersję funkcji CREATE_TUNING_TASK na okoliczność gdy dysponujesz jedynie tekstem SQL. Przekazujesz go poprzez parameter sql_text. Na przykład w taki sposób:

Exec DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'select * from all_tables');

Może być użyteczne dla deweloperów podczas tworzenia nowych zapytań. Podpowie optymalny tekst komendy i pomoże dobrać ścieżki dostępu do danych.

Skrypt

Wcześniej proces, od utworzenia zadania do wygenerowania raportu, rozbiłem na niezależne komendy.Dla wygody można zaszyć je w eleganckim skrypcie dla sqlplus. Wykonają się smukle bez Twojego udziału.

VARIABLE zadanie VARCHAR2(64);
EXEC :zadanie := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=>'f82bjuz7qsnd9');
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:zadanie);
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 9999 PAGESIZE 9999
SPOOL strojenie_f82bjuz7qsnd9.txt
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:zadanie) FROM DUAL;
SPOOL OFF

Sprzątanie

Utworzone przez Ciebie zadanie nie znika. Zostaje w bazie danych do ponownego użycia. Jeśli podobnie jak ja cenisz sobie porządek to możesz posprzątać niepotrzebne zadania komendą podobną do:

Exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'TASK_55397');

Podsumowując

Tuning Advisor jako wsparcie przy strojeniu ad-hoc może być przydatnym narzędziem. Szczególnie gdy nie masz czasu albo pomysłu na ręczne strojenie SQL. Rady Tuning Advisora, nawet gdy nie rozwiążą Twojego problemu, mogą być cenną podpowiedzią na co zwrócić uwagę. Na przykład wychwycą nieaktualne statystyki lub brakujący indeks.

Tuning Advisora można także wyklikać w Enterprise Managerze.

Trzeba mieć na uwadze, że jest dostępny w dodatkowo płatnej opcji Oracle Database Tuning Pack.

Oprócz wspomnianego pakietu dostępny jest również, automatyzujący cały proces, pakiet DBMS_AUTO_SQLTUNE. Nie używałem. A Ty Używasz? Jeśli tak to skomentuj jakie są Twoje wrażenia.

Warto pamiętać, że pakiet DBMS_SQLTUNE dostarcza mnóstwa podprogramów, które moga być użyteczne podczas kompleksowego i regularnego strojenia SQL. A także być wsparciem w dewelopmencie czy testach. Potrafi współpracować z repliką Data Guard czy łączyć się przez db link.