SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.Man kann also für ein Statement einen offiziell abgesegneten Plan festlegen, der für alle folgenden Ausführungen der Query maßgeblich ist. Wenn ein potentiell besserer Plan berechnet wird, dann wird er von der Datenbank gespeichert, aber erst nach expliziter Bestätigung durch den DBA für weitere Ausführungen verwendet. Bei Tim Hall findet sich eine instruktive Einführung in das Thema.
Trotzdem folgt hier ein kurzer Versuch: Zunächst lege ich eine Testtabelle an:
-- Session 1
create table test_baseline(col1 not null, col2, col3)
as
select rownum col1
, lpad(' ', 200, '*') col2
, lpad(' ', 200, '*') col3
from dual
connect by level <= 1000000
In einer zweiten Session aktiviere ich jetzt das Erfassen von Plan Baselines, zähle die Sätze meiner Testtabelle und deaktiviere die Planerfassung wieder:
-- Session 2 ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE; SELECT /* baseline_test */ COUNT(*) FROM test_baseline; ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
Der Zugriff kann natürlich nur über FTS erfolgen, da noch kein Index für die Tabelle existiert:
SQL> SELECT /* test */ COUNT(*) FROM test_baseline;
Abgelaufen: 00:00:00.90
Ausführungsplan
----------------------------------------------------------
Plan hash value: 2118400422
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10800 (1)| 00:02:32 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_BASELINE | 814K| 10800 (1)| 00:02:32 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
- SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement
Statistiken
----------------------------------------------------------
5 recursive calls
0 db block gets
28644 consistent gets
28572 physical reads
0 redo size
342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Den fehlenden Index lege ich nun in Session 1 an:
SQL> create index test_baseline_idx on test_baseline(col1);
Index wurde erstellt.
Abgelaufen: 00:00:07.45
SQL> SELECT /* test */ COUNT(*) FROM test_baseline;
Abgelaufen: 00:00:00.93
Ausführungsplan
----------------------------------------------------------
Plan hash value: 2118400422
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10830 (1)| 00:02:32 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_BASELINE | 4701K| 10830 (1)| 00:02:32 |
----------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement
Statistiken
----------------------------------------------------------
112 recursive calls
23 db block gets
28684 consistent gets
28726 physical reads
4592 redo size
342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
Der Index wird vom cbo offenbar kalt lächelnd ignoriert, aber immerhin liefert autotrace auch die Begründung dafür: SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement.
Details zur Baseline liefert die View DBA_SQL_PLAN_BASELINES:
select * from DBA_SQL_PLAN_BASELINES where PLAN_NAME = 'SYS_SQL_PLAN_8713193eb8066074'; -- hier nur die Spalte SQL_HANDLE SQL_HANDLE ------------------------ SYS_SQL_0c6637d282f8ddb7
Mit Hilfe der Prozedur evolve_sql_plan_baseline aus dem Package dbms_spm kann ich dann prüfen, ob eine geeigneterer Plan zum Statement existiert und diesen gegebenenfalls akzeptieren:
If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').Der Vergleich der Pläne fällt (natürlich) zugunsten des indizierten Zugriffs aus:
SQL> r
1 SELECT dbms_spm.evolve_sql_plan_baseline(
2 sql_handle => 'SYS_SQL_9f943b5f8713193e',
3 plan_name => '',
4 time_limit => 10,
5 verify => 'yes',
6 commit => 'yes'
7 )
8* FROM dual
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_9F943B5F8713193E',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_9f943b5f8713193e
PLAN_NAME =
TIME_LIMIT = 10
VERIFY = yes
COMMIT = yes
Plan: SYS_SQL_PLAN_8713193efefc82f9
-----------------------------------
Plan was verified: Time used 1,77 seconds.
Passed performance criterion: Compound improvement ratio >= 25,65
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 1469 104 14,13
CPU Time(ms): 1456 103 14,14
Buffer Gets: 28587 1110 25,75
Disk Reads: 27260 1099 24,8
Direct Writes: 0 0
Fetches: 608 32 19
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
Offenbar gibt's da eine Verbesserungsmöglichkeit, weil der neue Plan (mit Index-Zugriff) effizienter ist, so dass der neue Plan akzeptiert wird.
SQL> SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline
('SYS_SQL_0c6637d282f8ddb7', NULL, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_0c6637d282f8ddb7
SQL text: SELECT /* baseline */ COUNT(*) FROM test_baseline
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7b8066074
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2118400422
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS FULL| TEST_BASELINE |
--------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7fefc82f9
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3816238667
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX FAST FULL SCAN| TEST_BASELINE_IDX |
---------------------------------------------------
Wenn ich die Testquery jetzt noch einmal ausführe, verwendete sie nun auch den Index, da der verbesserte Plan über den Prozeduraufruf akzeptiert wurde:
SQL> SELECT /* test */ COUNT(*) FROM test_baseline;
Abgelaufen: 00:00:00.29
Ausführungsplan
----------------------------------------------------------
Plan hash value: 3816238667
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 430 (2)| 00:00:07 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| TEST_BASELINE_IDX | 814K| 430 (2)| 00:00:07 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
- SQL plan baseline "SYS_SQL_PLAN_8713193efefc82f9" used for this statement
Statistiken
----------------------------------------------------------
20 recursive calls
14 db block gets
1187 consistent gets
161 physical reads
5916 redo size
342 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Ich hatte mit diesem Test ein paar Schwierigkeiten, aber nach einem Blick in Tim Halls Erläuterungen wurde der Fall deutlich klarer.
Keine Kommentare:
Kommentar veröffentlichen