Jednym z bardziej popularnych parametrów konfiguracyjnych jest CURSOR_SHARING, umożliwiający sterowanie współdzieleniem kursorów i planów wykonania zapytań (pojawił się w 8.1.6). Może przyjąć jedną z trzech wartości: EXACT (domyślna), SIMILAR, FORCE, przy czym Oracle zapowiedział wycofanie opcji SIMILAR (już nawet w wersji 11g [ogłoszenie ID 1169017.1], jednak jest nadal dostępna). Parametr CURSOR_SHARING jest reklamowany jako lekarstwo na problemy wydajnościowe powodowane przez niesfornych programistów, którzy w często wykonywanych zapytaniach SQL stosują literały zamiast zmiennych wiązanych (bind variables). Proponuję bliższe przyjrzenie się skutkom różnych ustawień tego parametru. Będziemy korzystać z tabeli ARMIA pochodzącej z mojego wcześniejszego wpisu [Dlaczego 99 ze 100 to 1%?], z dodatkowym indeksem na kolumnie PLEC liczącej 12800 rekordów.
CURSOR_SHARING=EXACT
Takie ustawienie oznacza, że dla poleceń SQL różniących się literałami tworzone będą w Library Cache oddzielne kursory i oddzielne plany wykonania zapytania. To znaczy, że gdy programista wykona następujące dwa polecenia SQL:
select count(*) from armia where plec = ’M’;
select count(*) from armia where plec = ’K’;
to serwer potraktuje je jako zupełnie różne, co spowoduje utworzenie dwóch kursorów w Library Cache i dwukrotną kompilację (optymalizację). Z każdym kursorem związany będzie oddzielny plan wykonania zapytania. Przeanalizujmy poniższy eksperyment:
SQL> alter session set cursor_sharing=exact;
SQL> select count(*) from armia where plec = ’M’;
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| ARMIA | 12672 |
---------------------------------------------
...
SQL> select count(*) from armia where plec = ‘K’;
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| ARMIA_IDX | 128 |
------------------------------------------------
...
SQL> select SQL_TEXT , SQL_ID, VERSION_COUNT,
HASH_VALUE,PLAN_HASH_VALUE from V$SQLAREA
where LOWER(SQL_TEXT) like ‘select count(*) from armia%';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE
------------------------------------------- ------------- ------------- ---------- ---------------
select count(*) from armia where plec = 'M' 4q6wz57b6khpk 1 3597222578 348857963
select count(*) from armia where plec = 'K' bk29hwbdjvy0p 1 3676174357 2060897835
Wynik ostatniego zapytania pokazuje, że każde z dwóch poleceń SQL to oddzielny kursor i każdy kursor posiada oddzielny plan.
CURSOR_SHARING=FORCE
Takie ustawienie oznacza, że polecenia SQL zawierające literały zostaną automatycznie przetransformowane do postaci wykorzystującej zmienne wiązane (zmienne te otrzymają automatycznie generowane nazwy: SYS_B_0, SYS_B_1, itd.). W rezultacie, polecenia SQL różniące się wyłącznie literałami zostaną potraktowane przez serwer jako to samo polecenie – w Library Cache pojawi się jeden kursor. To znaczy, że gdy programista wykona następujące dwa polecenia SQL:
select count(*) from armia where plec = ’M’;
select count(*) from armia where plec = ’K’;
to serwer przetransformuje je do postaci:
select count(*) from armia where plec = :zmienna_wiązana;
Przeanalizujmy poniższy eksperyment:
SQL> alter system flush shared_pool;
SQL> alter session set cursor_sharing=force;
SQL> select count(*) from armia where plec = 'K';
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| ARMIA_IDX | 128 |
------------------------------------------------
...
SQL> select count(*) from armia where plec = 'M';
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| ARMIA_IDX | 128 |
------------------------------------------------
...
SQL> select SQL_TEXT , SQL_ID, VERSION_COUNT,
HASH_VALUE,PLAN_HASH_VALUE from V$SQLAREA where
LOWER(SQL_TEXT) like ‘select count(*) from armia %';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE
-------------------------------------------------- ------------- ------------- ---------- ---------------
select count(*) from armia where plec = :"SYS_B_0" curfd6mhrpd0u 1 3782915098 2060897835
Wynik ostatniego zapytania pokazuje, że oba polecenia SQL korzystają z jednego kursora, który posiada jeden plan wykonania. Niestety jeden plan wykonania! Plan ten powstał w oparciu o pierwszą wartość podstawioną do zmiennej wiązanej – w moim eksperymencie to ‘K’, stąd użycie indeksu. Tak po prostu działa Bind Variable Peeking pochodzący z wersji 10g. Gdybym najpierw szukał rekordów z PLEC=’M’, to oba zapytania wykonałyby się z krokiem Full Table Scan.
Uwaga!
A gdzie podział się słynny w 11g mechanizm Adaptive Cursor Sharing, który powinien doprowadzić do powstania dwóch wersji planu wykonania dla tego kursora? Jak pokazaliśmy wcześniej, optymalny plan wykonania dla PLEC=’M’ jest inny niż optymalny plan wykonania dla PLEC=’K’! Ja nie wiem, gdzie sie podział, ale gdy posłużymy się nieudokumentowanym hintem BIND_AWARE, to efekty będą takie, jak powinny być (jeden kursor, dwa plany):
SQL> alter system flush shared_pool;
SQL> alter session set cursor_sharing=force;
SQL> select /*+ BIND_AWARE */ count(*) from armia where plec = 'M';
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
...
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| ARMIA | 12672 |
---------------------------------------------
...
SQL> select /*+ BIND_AWARE */ count(*) from armia where plec = 'K';
...
SQL> select * from table (dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| ARMIA_IDX | 128 |
------------------------------------------------
...
SQL> SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select /*+ bind_aware */ count(*) from armia %';
SQL_TEXT SQL_ID VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE
-------------------------------------------------------------------- ------------- ------------- ---------- ---------------
select /*+ BIND_AWARE */ count(*) from armia where plec = :"SYS_B_0" 5b9hvzx87nhm5 2 1350189669 348857963
Pułapki CURSOR_SHARING=FORCE
Wydawałoby się, że CURSOR_SHARING=FORCE jest doskonałym sposobem na redukcję zajętości Library Cache, redukcję rywalizacji o dostęp do Library Cache (Latch Contention), redukcję czasu kompilacji (optymalizacji) poleceń SQL i na niepogarszanie ich wydajności (o ile Adaptive Cursor Sharing nas nie zawiedzie). Należy jednak zauważyć, że zamiana literałów na zmienne wiązane dotyczy WSZYSTKICH literałów! Przykładowo, poniższe zapytanie:
select first_name, last_name from hr.employees
where hire_date=to_date('01-01-2013','DD-MM-YYYY')
and substr(first_name,1,1)='J';
zostanie przekształcone do postaci:
select first_name, last_name from hr.employees
where hire_date=to_date(:"SYS_B_0",:"SYS_B_1")
and substr(first_name,:"SYS_B_2",:"SYS_B_3")=:"SYS_B_4"
w której występuje PIĘĆ zmiennych wiązanych zamiast naprawdę potrzebnych dwóch! Może to prowadzić do generowania nieoptymalnych planów, o których nierzadko mówią opisy bugów.
CURSOR_SHARING=SIMILAR
Zapomnijmy o nim. Ma zniknąć w wersji 12c (notka MOS 1169017.1), jest niezalecane w 11g. Straciło sens gdy pojawił się Adaptive Cursor Sharing.
Bardzo treściwy i konkretny artykuł 🙂 Można się wiele nauczyć. Dzięki za niego! Pozdrawiam
Dzięki!