Adaptives Cursor Sharing und SQL Plan Management


sql_baseline

Ich möchte hier kurz zusammenfassen wie Adaptives Cursor Sharing und SQL Plan Management zusammen passen. Als erstes, kurz zusammengefasst was Adaptives Cursor Sharing (ACS) oder SQL Plan Management macht.

ACS steuert basierend auf den Bind Variablen des SQL Statements ob ein Child Cursor „shared“ verarbeitet wird oder ob der Optimizer die Chance bekommt einen neuen und besseren Ausführungs Plan evalurieren kann.

SQL Plan Management (SPM) ist verantwortlich welchen Ausführungs Plan der Optimizer wählt. Ist ein „child cursor bind-aware“ ist es eigentlich egal ob die Ausfühurng durch SPM beeinflusst wird oder nicht. Wird jedoch eine Abfrage inklusive Bind Variablen an den Optimizer gesendet, erstellt SPM die Plan Auswahl unabhängig davon ob die Abfrage von ACS optimiert worden ist.

Als Beispiel bauen wir uns eine Anfrage welche ACS nutzt:

exec :job := 'AD_PRES';
select /*+ BIND_AWARE */ avg(e.salary),d.department_name
from employees_t1 e,departments d
where e.job_id = :job
and e.department_id = d.department_id
group by d.department_name;

    24000 Executive
SQL_ID    artpr1du1wy2n, child number 0
-------------------------------------
select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
employees_t1 e,departments d where e.job_id = :job and e.department_id
= d.department_id group by d.department_name

Plan hash value: 3973244994

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       | 19134 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |   864 | 19134   (1)| 00:03:50 |
|*  2 |   HASH JOIN         |              | 23464 |   733K| 19132   (1)| 00:03:50 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES_T1 | 23692 |   370K| 19129   (1)| 00:03:50 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   4 - filter("E"."JOB_ID"=:JOB)

Jetzt ändern wir mal die BIND Variable und sehen was passiert.

exec :job := 'SA_REP';
select /*+ BIND_AWARE */ avg(e.salary),d.department_name
from employees_t1 e,departments d
where e.job_id = :job
and e.department_id = d.department_id
group by d.department_name;

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
   8396.55172 Sales
SQL_ID    artpr1du1wy2n, child number 1
-------------------------------------
select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
employees_t1 e,departments d where e.job_id = :job and e.department_id
= d.department_id group by d.department_name

Plan hash value: 4089471367

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows    | Bytes   | Cost (%CPU)|   Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |         |         | 19230 (100)  |          |
|   1 |  HASH GROUP BY                |              |      11 |     495 | 19230   (2)  | 00:03:51 |
|   2 |   MERGE JOIN                  |              |      11 |     495 | 19229   (2)  | 00:03:51 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |      27 |     432 |     2   (0)  | 00:00:01 |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK   |      27 |         |     1   (0)  | 00:00:01 |
|*  5 |    SORT JOIN                  |              |      11 |     319 | 19227   (2)  | 00:03:51 |
|   6 |     VIEW                      | VW_GBC_5     |      11 |     319 | 19226   (2)  | 00:03:51 |
|   7 |      HASH GROUP BY            |              |      11 |     176 | 19226   (2)  | 00:03:51 |
|*  8 |       TABLE ACCESS FULL       | EMPLOYEES_T1 |    1991K|      30M| 19136   (1)  | 00:03:50 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ITEM_1"="D"."DEPARTMENT_ID")
       filter("ITEM_1"="D"."DEPARTMENT_ID")
   8 - filter("E"."JOB_ID"=:JOB)

Wir können erkennen, das durch das Austauschen der BIND Variablen sich der Execution Plan ändern. Das ist genau das was wir uns von Adaptiven Cursor Sharing erwarten.

Wir haben eine SQL ID, aber wenn wir uns die v$sql ansehen, können wir erkennen das wir 2 Plan Hash Values haben.

select child_number,plan_hash_value from v$sql where sql_id='artpr1du1wy2n';
CHILD_NUMBER  PLAN_HASH_VALUE
------------  ---------------
 0            3973244994
 1            4089471367

Gut laden wir diese SQL id einmal in das SQL Plan Management.

variable geladen number;
 exec :geladen := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('artpr1du1wy2n');
 print :geladen
 2

Wir haben 2 accepted Pläne für einen SQL Handle geladen.

select sql_handle,plan_name,accepted
 from dba_sql_plan_baselines;
SQL_HANDLE                       PLAN_NAME                       ACC
 ------------------------------  ------------------------------  ---
 SQL_09b4d205847b148d            SQL_PLAN_0md6k0q27q54d3fb79f9d  YES
 SQL_09b4d205847b148d            SQL_PLAN_0md6k0q27q54da820f5bb  YES

Da wir 2 „accepted“ Pläne in der dba_sql_plan_baselines haben, gehen wir davon aus das wir für jede Bind Variable einen eigenen Execution Plan haben. Sehen wir uns das im nächsten Test an, ob dies wirklich so ist.

SQL>     24000 Executive
 SQL_ID    artpr1du1wy2n, child number 0
 -------------------------------------
 select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
 employees_t1 e,departments d where e.job_id = :job and e.department_id
 = d.department_id group by d.department_name
Plan hash value: 3973244994
------------------------------------------------------------------------------------
 | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |              |       |       | 19134 (100)|          |
 |   1 |  HASH GROUP BY      |              |    27 |   864 | 19134   (1)| 00:03:50 |
 |*  2 |   HASH JOIN         |              | 23464 |   733K| 19132   (1)| 00:03:50 |
 |   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
 |*  4 |    TABLE ACCESS FULL| EMPLOYEES_T1 | 23692 |   370K| 19129   (1)| 00:03:50 |
 ------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 4 - filter("E"."JOB_ID"=:JOB)
Note
 -----
 - SQL plan baseline SQL_PLAN_0md6k0q27q54da820f5bb used for this statement
 8396.55172 Sales
 SQL_ID    artpr1du1wy2n, child number 1
 -------------------------------------
 select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
 employees_t1 e,departments d where e.job_id = :job and e.department_id
 = d.department_id group by d.department_name
Plan hash value: 4089471367
-----------------------------------------------------------------------------------------------------
 | Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)      | Time     |
 ----------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |              |         |         | 19230 (100)  |          |
 |   1 |  HASH GROUP BY                |              |      11 |     495 | 19230   (2)  | 00:03:51 |
 |   2 |   MERGE JOIN                  |              |      11 |     495 | 19229   (2)  | 00:03:51 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |      27 |     432 |       2   (0)| 00:00:01 |
 |   4 |     INDEX FULL SCAN           | DEPT_ID_PK   |      27 |         |       1   (0)| 00:00:01 |
 |*  5 |    SORT JOIN                  |              |      11 |     319 | 19227   (2)  | 00:03:51 |
 |   6 |     VIEW                      | VW_GBC_5     |      11 |     319 | 19226   (2)  | 00:03:51 |
 |   7 |      HASH GROUP BY            |              |      11 |     176 | 19226   (2)  | 00:03:51 |
 |*  8 |       TABLE ACCESS FULL       | EMPLOYEES_T1 |    1991K|      30M| 19136   (1)  | 00:03:50 |
 ----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 5 - access("ITEM_1"="D"."DEPARTMENT_ID")
 filter("ITEM_1"="D"."DEPARTMENT_ID")
 8 - filter("E"."JOB_ID"=:JOB)
Note
 -----
 - SQL plan baseline SQL_PLAN_0md6k0q27q54d3fb79f9d used for this statement
Das Stamtment mit einer komplett neuen BIND Variable SH_CLERK.
3215 Shipping
 SQL_ID    artpr1du1wy2n, child number 0
 -------------------------------------
 select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
 employees_t1 e,departments d where e.job_id = :job and e.department_id
 = d.department_id group by d.department_name
Plan hash value: 4089471367
-----------------------------------------------------------------------------------------------------
 | Id  | Operation                     | Name         | Rows    | Bytes   | Cost (%CPU)  | Time     |
 ----------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT              |              |         |         | 19196 (100)  |          |
 |   1 |  HASH GROUP BY                |              |      11 |     495 | 19196   (2)  | 00:03:51 |
 |   2 |   MERGE JOIN                  |              |      11 |     495 | 19195   (2)  | 00:03:51 |
 |   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |      27 |     432 |       2   (0)| 00:00:01 |
 |   4 |     INDEX FULL SCAN           | DEPT_ID_PK   |      27 |         |       1   (0)| 00:00:01 |
 |*  5 |    SORT JOIN                  |              |      11 |     319 | 19193   (2)  | 00:03:51 |
 |   6 |     VIEW                      | VW_GBC_5     |      11 |     319 | 19192   (1)  | 00:03:51 |
 |   7 |      HASH GROUP BY            |              |      11 |     176 | 19192   (1)  | 00:03:51 |
 |*  8 |       TABLE ACCESS FULL       | EMPLOYEES_T1 |    1320K|      20M| 19134   (1)  | 00:03:50 |
 ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 5 - access("ITEM_1"="D"."DEPARTMENT_ID")
 filter("ITEM_1"="D"."DEPARTMENT_ID")
 8 - filter("E"."JOB_ID"=:JOB)
Note
 -----
 - SQL plan baseline SQL_PLAN_0md6k0q27q54d3fb79f9d used for this statement

Wir erwartet, in der SPM liegt für beide Bind Variablen ein accepted Plan vor, deswegen sehen wir auch das in der Ausführung der Abfragen der entsprechende Execution Plan and die Bind Variable angepasst wird.

Setzen wir alle zurück, löschen unsere Baseline und fangen neu an.

Laden wir jetzt nun die erste Abfrage mit der Bind Variable AD_PERS in das SPM. Und führen anschliessend die Abfrage mit der BIND Variable SP_REP aus. Fragen wir jetzt die SPM ab, sehen wir unseren geladenen Plan als accepted bzw. die weitere Ausführung mit geänderter Bind Variable als unaccepted Plan in der SPM.

SQL_HANDLE                      PLAN_NAME                      ACC
 ------------------------------ ------------------------------ ---
 SQL_09b4d205847b148d           SQL_PLAN_0md6k0q27q54d3fb79f9d NO
 SQL_09b4d205847b148d           SQL_PLAN_0md6k0q27q54da820f5bb YES

Der Execution Plan von unserer Abfrage mit SP_REP.

SQL>    8396.55172 Sales
 SQL_ID    artpr1du1wy2n, child number 0
 -------------------------------------
 select /*+ BIND_AWARE */ avg(e.salary),d.department_name from
 employees_t1 e,departments d where e.job_id = :job and e.department_id
 = d.department_id group by d.department_name
Plan hash value: 3973244994
------------------------------------------------------------------------------------
 | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |              |       |       | 19238 (100)|          |
 |   1 |  HASH GROUP BY      |              |    27 |   864 | 19238   (2)| 00:03:51 |
 |*  2 |   HASH JOIN         |              |  1972K|    60M| 19149   (1)| 00:03:50 |
 |   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
 |*  4 |    TABLE ACCESS FULL| EMPLOYEES_T1 |  1991K|    30M| 19136   (1)| 00:03:50 |
 ------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 4 - filter("E"."JOB_ID"=:JOB)
Note
 -----
 - SQL plan baseline SQL_PLAN_0md6k0q27q54da820f5bb used for this statement

Eigentlich sollten wir jetzt einen „INDEX FULL SCAN“ sehen, wie oben im Beispiel mit der Plan Hash Value 4089471367. Jedoch übersteuert SPM hier ACS und wir sehen den abgespeicherten Plan.

Konklusio:

Beide Features SQL Plan Management und Adaptives Cursor Sharing, können einen Performance Improvment für die Datenbank bringen.

  • Jedoch kann die falsche Handhabung von SPM sehr wohl einen negativen Impact auf das System haben, da der Vorteil welcher durch ACS möglich ist durch SPM nicht ohne zutun genutzt wird.Anders gesagt ist SPM ein Feature das einer gewissen Überwachung bedarf.
  • ACS nimmt direkten Einfluss darauf wieviele Child Cursor bzw. Hard Parses wir in einem System sehen. Was mitunter ein Grund ist, warum ich ACS bei Kunden schon deaktiviert habe, um eine stabile Performance zu gewährleisten.
  • Neue Execution Pläne werden vom Optimizer in die SQL Baseline (als unaccepted) eingetragen. Dies bedeutet das der Optimizer nur dann diesen Plan wählen kann, wenn das Cursor Sharing scheitert und ein „hard parse“ angestossen wird.

Wie immer freue ich mich über Kommentare

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s