Kopiowanie planu zapytania do innej bazy

Ostatnio zgłoszono mi problem, polegający na powolnym działaniu zapytania w produkcyjnej bazie danych. Jak się okazało przyczyną było użycie przez optymalizator niewłaściwego planu, co w efekcie doprowadziło do dużego obciążenia bazy i spowolnionej pracy aplikacji. To samo zapytanie tyle, że uruchomione że w bazie testowej działało znacznie szybciej pomimo, że obie mają porównywalny wolumen danych.

Jako, że struktura obu baz jest identyczna zwłaszcza w kontekście obiektów biorących udział w zapytaniu, zdecydowałem o skopiowaniu planu ze środowiska testowego do produkcyjnego, co potencjalnie miało naprowadzić optymalizator na właściwe tory i w efekcie naprawić wydajnościowy problem.

Skorzystałem z poniższej procedury dzięki której udało się usunąć awarię, co znacząco powiększyło grono zadowolonych użytkowników systemu. 😉

A było to tak:

Procedurę kopiowania rozpocząłem od weryfikacji, czy istnieje plan zapytania o podanym sql_id na bazie źródłowej:

select hash_value,sql_id,plan_hash_value,timestamp,operation,optimizer,cost,parent_id
from v$sql_plan
where parent_id is null
and sql_id = '93c4e9rdek54n';


select plan_table_output from table(dbms_xplan.display_cursor('93c4e9rdek54n',0));

Następnie upewniłem się, że w tej samej bazie istnieje baseline. Poszukiwanie przeprowadziłem w oparciu o unikalny fragmentu zapytania (sql_text).

Dla uproszenia załóżmy, że był to znany ze szkoleń Oracle:

 SELECT EMPLOYEE_ID, FIRST_NAME FROM HR.EMPLOYEES%;

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,
fixed,to_char(last_executed,'dd-mon-yy HH24:MI') 
last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT ID, NAME FROM HR_USERS%';

Tu uwaga: jeżeli powyższe zapytanie wykaże, że baseline nie jest dostępny, należy załadować plan z shared poola do SQL Plan Management.

SQL Plan Management to mechanizm, który umożliwia optymalizatorowi automatyczne zarządzanie planami wykonania zapewniając, że baza danych korzysta tylko ze znanych lub zweryfikowanych planów zapytań. Wykorzystywany jest tu mechanizm SQL plan baseline, który jest zestawem zaakceptowanych planów, których optymalizator może bezpiecznie używać dla konkretnych zapytań SQL.

DECLARE
 my_plans pls_integer;
BEGIN
 my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'93c4e9rdek54n', plan_hash_value=>'714642614', fixed=>'YES');
end;
/

Następnie sprawdziłem, czy utworzył się baseline i zanotowałem jego SQL_HANDLE:

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,
fixed,to_char(last_executed,'dd-mon-yy HH24:MI')
last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT EMPLOYEE_ID, FIRST_NAME FROM HR.EMPLOYEES%';


SQL_HANDLE: SQL_704a4963b4e17aes
PLAN_NAME:  SQL_PLAN_70kd8gasf46ra670644sa

Utworzyłem niewielką tabelkę w prywatnym schemacie, która posłużyła mi do załadowania i eksportu planu do innej bazy:

BEGIN
 DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => 'STGTAB', table_owner => 'KAROLW', tablespace_name => 'USERS');
END;
/

DECLARE
 my_plans number;
BEGIN
 my_plans := DBMS_SPM.PACK_STGTAB_BASELINE(
 table_name => 'STGTAB',
 table_owner => 'KAROLW',
 sql_handle => 'SQL_704a4963b4e17aes',
 plan_name => 'SQL_PLAN_70kd8gasf46ra670644sa');
END;
/

Tabelka zawierała tylko jeden rekord:

select VERSION, SQL_HANDLE, OBJ_NAME, OBJ_TYPE, PLAN_ID, SQL_TEXT from KAROLW.STGTAB;

 VERSION SQL_HANDLE             OBJ_NAME                       OBJ_TYPE              PLAN_ID SQL_TEXT                                                                        
-------- ---------------------- ------------------------------ ------------------ ---------- -----------------------------
       3 SQL_704a4963b4e17aes   SQL_PLAN_70kd8gasf46ra670644sa SQL_PLAN_BASELINE  2543962752 SELECT ID, NAME FROM HR_USERS

Wyeksportowałem ją przy użyciu standardowego narzędzia expdp:

create or replace directory TMP_DIR as '/oracle/tmp';
exit;

$ expdp \"/ as sysdba\" dumpfile=expdp_STGTAB.dmp parallel=1 directory=TMP_DIR logfile=expdp_STGTAB tables=KAROLW.STGTAB

Po to, aby chwilę później zaimportować ją w bazie produkcyjnej:

create or replace directory TMP_DIR as '/oracle/tmp';
exit;

$ impdp \"/ as sysdba\" dumpfile=expdp_STGTAB.dmp parallel=1 directory=TMP_DIR logfile=impdp_STGTAB.log

select VERSION, SQL_HANDLE, OBJ_NAME, OBJ_TYPE, PLAN_ID, SQL_TEXT from KAROLW.STGTAB;

 VERSION SQL_HANDLE             OBJ_NAME                       OBJ_TYPE              PLAN_ID SQL_TEXT                                                                        
-------- ---------------------- ------------------------------ ------------------ ---------- -----------------------------
       3 SQL_704a4963b4e17aes   SQL_PLAN_70kd8gasf46ra670644sa SQL_PLAN_BASELINE  2543962752 SELECT ID, NAME FROM HR_USERS

Pozostając w bazie produkcyjnej (docelowej), wypakowałem baselinea:

declare
 l_plans_unpacked PLS_INTEGER;
begin
 l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
 table_name => 'STGTAB',
 table_owner => 'KAROLW');
 DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
end;
/

select sql_handle, sql_text, plan_name,PARSING_SCHEMA_NAME,enabled,accepted,fixed,to_char(last_executed,'dd-mon-yy HH24:MI') last_executed from dba_sql_plan_baselines where upper(sql_text) like 'SELECT EMPLOYEE_ID,FIRST_NAME FROM HR.EMPLOYEES%';

Po wszystkim wyczyściłem kursor, aby nowe zapytania korzystały już z nowego planu:

BEGIN
 FOR i IN (SELECT address, hash_value FROM gv$sqlarea WHERE sql_id = '93c4e9rdek54n')
 LOOP
  SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
 END LOOP;
END;
/

Po wykonaniu powyższej procedury, zespół aplikacyjny pozostający w kontakcie z użytkownikami potwierdził prawidłowe działanie systemu.