Eksperymenty z CURSOR_SHARING

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.

2 thoughts on “Eksperymenty z CURSOR_SHARING

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *