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