Freitag, März 09, 2012

SPM-Beispiel

Hier folgt ein minimalistisches Beispiel für die Verwendung von SPM - also SQL Plan Management -, das auf Tim Halls Darstellung des Features in seinem Blog basiert.

Die Dokumentation definiert SPM wie folgt: "SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information." Es geht also darum, einen bekannten Ausführungsplan für zukünftige Ausführungen festzulegen, um das plötzliche Kippen zu Plänen (etwa aufgrund von Statistik-Änderungen) zu vermeiden - was natürlich nur funktioniert, wenn der Plan aktuell verwendbar ist (also z.B. alle darin verwendeten Indizes existieren etc.). Nützlich ist ein solcher Mechanismus z.B. für große ETL-Operationen, für die mehrere unterschiedlich performante Pläne im AWR existieren, zwischen denen der CBO regelmäßig hin und her springt.

Sehr interessant ist das Feature auch deshalb, weil es offenbar Teil der Enterprise Edition ist und keine zusätzliche Lizenzierung erfordert.

Der Test erfolgt in 11.1.0.7:
-- Berechtigungen des Test-Users
select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST                           CONNECT                        NO  YES NO
TEST                           RESOURCE                       NO  YES NO

-- Anlage einer Test-Tabelle
create table test_spm_mpr
as
select rownum id
      , lpad('*', 100, '*') padding
   from dual
connect by level <= 1000000;

-- Ausführungsplan über autotrace anzeigen
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.45

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4227   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |   786K|  4227   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-- Ermittlung der sql_id für die count-Query
select sql_id, sql_text
  from v$sql
 where sql_text like 'select%test_spm_mpr%'
   and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT
------------- ---------------------------------
1ndqm9sqnzr52 select count(*) from test_spm_mpr

-- Versuch, den Plan als Baseline zu registrieren
declare
   l_plans_loaded  pls_integer;
begin
   l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
     sql_id => '1ndqm9sqnzr52');

   dbms_output.put_line('plans loaded: ' || l_plans_loaded);

end;
/

declare
*
FEHLER in Zeile 1:
ORA-38171: Nicht ausreichende Berechtigungen für Vorgang mit SQL-Management-Objekt
ORA-06512: in "SYS.DBMS_SPM", Zeile 2184
ORA-06512: in Zeile 4

Die fehlende Berechtigung ist das Systemprivileg ADMINISTER SQL MANAGEMENT OBJECT, wie man im Blog der CBO-Entwickler erfährt.

-- Zuweisung des Privilegs durch jemanden, der es vergeben kann:
grant ADMINISTER SQL MANAGEMENT OBJECT to test;

Benutzerzugriff (Grant) wurde erteilt.

-- wieder in der Session von TEST:
set serveroutput on
declare
   l_plans_loaded  pls_integer;
begin
   l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
     sql_id => '1ndqm9sqnzr52');

   dbms_output.put_line('plans loaded: ' || l_plans_loaded);

end;
/
plans loaded: 1

-- Prüfung der Verwendung
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.56

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4227   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |   786K|  4227   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb163db08b5e3" used for this statement

Die Baseline wird also verwendet. Mehr zur Baseline erfährt man aus dba_sql_plan_baselines und aus einer dbms_xplan-Routine:

-- das muss natürlich wieder ein privilegierter Nutzer abfragen:
select sql_handle
     , sql_text
     , plan_name
     , enabled
     , accepted
  from dba_sql_plan_baselines
 where sql_text like '%test_spm_mpr%';

SQL_HANDLE                     SQL_TEXT                            PLAN_NAME                      ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb163db08b5e3  YES YES

select *
  from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_a3ebb163db08b5e3'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

---------------------------------------------------------------------------
SQL handle: SYS_SQL_6f076b44a3ebb163
SQL text: select count(*) from test_spm_mpr
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a3ebb163db08b5e3
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
---------------------------------------------------------------------------

Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4231   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |  1270K|  4231   (1)| 00:00:51 |
---------------------------------------------------------------------------

Jetzt zum interessanten Punkt: was passiert, wenn der CBO einen günstigeren Plan wählen könnte? Dazu lege ich (wie vor mir der Herr Hall) einen Index an, der den Zugriff deutlich vereinfachen und die Kosten senken würde, wenn die Baseline nichts dagegen hätte:

-- Index-Anlage + Statistik-Erfassung
create index test_spm_mpr_ix on test_spm_mpr(id); 
-- ohne not null constraint wäre der Index für die Zählung allerdings nicht vervendbar
alter table test_spm_mpr modify id not null;

exec dbms_stats.gather_table_stats(user, 'TEST_SPM_MPR', cascade=>true)

-- Hard-Parse erzwingen durch Löschung des Shared Pools 
-- (!!! was man in einer Produktivumgebung lieber bleiben lassen sollte !!!)
-- auch das kann natürlich nur ein privilegierte User
alter system flush shared_pool;

-- wieder im TEST-Schema
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.50

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4229   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |  1000K|  4229   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb163db08b5e3" used for this statement

Funktioniert also erwartungsgemäß: der neue (in diesem Fall: bessere) Plan wird ignoriert, weil die Baseline Vorrang hat. In dba_sql_plan_baselines findet man aber bereits eine zweite Baseline, die allerdings noch nicht akzeptiert wurde:

SQL_HANDLE                     SQL_TEXT                            PLAN_NAME                      ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb16343842f93  YES NO
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb163db08b5e3  YES YES

Um den neuen, potentiell - und im gegebenen Fall auch tatsächlich - besseren Plan zu übernehmen, kann man nun eine weitere dbms_spm-Routine verwenden, die auch noch eine erläuternde Ausgabe liefert:

select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f076b44a3ebb163')
  from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F076B44A3EBB163')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6f076b44a3ebb163
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_a3ebb16343842f93
-----------------------------------
  Plan was verified: Time used 1,485 seconds.
  Passed performance criterion: Compound improvement ratio >= 6,87
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            1349            102             13,23
  CPU Time(ms):                 156             15              10,4
  Buffer Gets:                15396           2241              6,87
  Disk Reads:                 15385            134            114,81
  Direct Writes:                  0              0
  Fetches:                      137             21              6,52
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

Eine erneute Ausführung der Test-Query zeigt, dass jetzt die neue Baseline verwendet wird:

select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:00.04

Ausführungsplan
----------------------------------------------------------
Plan hash value: 569399299

---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |   613   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE       |                 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_SPM_MPR_IX |  1000K|   613   (2)| 00:00:08 |
---------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb16343842f93" used for this statement

Man kann noch sehr viel mehr mit SPM anstellen, aber ich spare mir eine Verlängerung dieses Eintrags und verweise außer auf Tim Hall auch noch auf mehrere Artikel von Maria Colgan:

Keine Kommentare:

Kommentar veröffentlichen