По умолчанию параметры такие:
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
-- Сбросим пул:
alter system flush shared_pool;
-- В SQL Management Base планов пока нет:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Если есть то удаляем:
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Создаём таблицу
drop table t;
create table t as select * from all_objects;
-- Выполняем запрос
select distinct object_name from t;
-- Находим sql_id запроса:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
-- Получаем план запроса
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1793979440
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 857 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 857 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 88914 | 2170K| | 388 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]
-- И загружаем план в baseline:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440,
FIXED =>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/
-- План запроса появился в SQL Management Base и baseline:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
-- Создаем индекс:
create index t_idx on t(object_name);
-- Выполняем запрос:
select distinct object_name from t;
-- Новый план запроса появился в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
Оптимизатор построил новый план для данного запроса, занес этот план в историю, но запрос выполнил по старому плану из baseline
-- Посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 1 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));
SQL_ID: 748bpm0yk2x76, child number: 1 cannot be found
-- Повторно выполняем запрос:
select distinct object_name from t;
-- И смотрим план:
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 1
-------------------------------------
select distinct object_name from t
Plan hash value: 1793979440
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 857 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 857 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 88914 | 2170K| | 388 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]
Note
-----
- SQL plan baseline SQL_PLAN_ckf7andhkppn3bba443c6 used for this statement
Теперь в shared pool этот план уже был, но запрос по прежнему был выполнен по старому плану из baseline.
Т.е. при включенном управлении планами, планы выполнения берутся для всех запросов из baseline, если они там есть.
-- Отключим управление планами:
alter session set optimizer_use_sql_plan_baselines = false;
-- Выполним запрос:
select distinct object_name from t;
-- И посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1741570181 1025602790 000000009FEE5A80 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1741570181
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 592 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 592 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_IDX | 88914 | 2170K| | 123 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "OBJECT_NAME"[VARCHAR2,128]
Теперь запрос отработал уже по новому плану с учетом индекса.
-- Вернем управление планами:
alter session set optimizer_use_sql_plan_baselines = true;
-- Посмотрим что у нас в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
запрос вернул две строки, одна с accepted = no и вторая с accepted = yes
Все планы с признаками accepted = no, могут быть проанализированы оптимизатором и в зависимости от результата проверки помещены в baseline
(если они окажутся по эффективности не хуже ранее там имеющихся):
-- Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : ЗАДАЧА_751
Task Owner : SCOTT
Execution Name : EXEC_801
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 01/19/2015 10:35:10
Finished : 01/19/2015 10:35:11
Last Updated : 01/19/2015 10:35:11
Global Time Limit : 60
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_ckf7andhkppn37883a793
Base Plan Name : SQL_PLAN_ckf7andhkppn3bba443c6
SQL Handle : SQL_c938eaa3612ad683
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select distinct object_name from t
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .002383 .002215
CPU Time (s): .001911 .001767
Buffer Gets: 139 45
Optimizer Cost: 857 592
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 5138 5138
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. Проверка плана длилась 0.56000 секунд. План прошел проверку на критерий
преимущества, поскольку проверенный уровень производительности был в
3.06716 раз лучше основного плана.
2. План был автоматически принят.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 3901
Plan Hash Value : 3148104646
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51024 | 1275600 | 857 | 00:00:01 |
| 1 | HASH UNIQUE | | 51024 | 1275600 | 857 | 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 88914 | 2222850 | 388 | 00:00:01 |
------------------------------------------------------------------------
Test Plan
-----------------------------
Plan Id : 3902
Plan Hash Value : 2021894035
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51024 | 1275600 | 592 | 00:00:01 |
| 1 | HASH UNIQUE | | 51024 | 1275600 | 592 | 00:00:01 |
| 2 | INDEX FAST FULL SCAN | T_IDX | 88914 | 2222850 | 123 | 00:00:01 |
----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
-- Смотрим планы в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES YES NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
Новый план после верификации был включен в baseline.
-- Выполняем запрос:
select distinct object_name from t;
-- Посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1741570181 1025602790 000000009BB9F680 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1741570181
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 592 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 592 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_IDX | 88914 | 2170K| | 123 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "OBJECT_NAME"[VARCHAR2,128]
Note
-----
- SQL plan baseline SQL_PLAN_ckf7andhkppn37883a793 used for this statement
-- Получать планы из baseline теперь можно так:
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'BASIC'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL -projection'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL +peeked_binds'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS '));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>'SQL_PLAN_ckf7andhkppn3bba443c6',format=>'ADVANCED'));
-- Пополнение baseline новыми планами:
Мы уже добавляли новые планы в baseline используя функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
Еще планы можно загружать в baseline без предварительной проверки их эффективности:
-- Например можно загрузить планы из STS:
SELECT sqlset_name, count(*) FROM DBA_SQLSET_STATEMENTS
group by sqlset_name;
new_sts 66
my_sts 212
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'my_sts'
);
dbms_output.put_line('Value is '||my_plans);
END;
/
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
228404646742146688 SQL_032b74d0ea1d3e80 SQL_PLAN_06avnu3p1ugn07f2c8b72 YES YES NO SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
228404646742146688 SQL_032b74d0ea1d3e80 SQL_PLAN_06avnu3p1ugn0fa6e684e YES YES NO SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
271831168975187047 SQL_03c5bd0209a52c67 SQL_PLAN_07jdx084uab37df05bb92 YES YES NO CREATE TABLE map_tab
616121024526838780 SQL_088ce6ceb5926bfc SQL_PLAN_0j376tuut4uzw5c154e55 YES YES NO update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,evaledition#=decode(:33,1,null,:33),unusablebefore#=decode(:34,0,null,:34),unusablebeginning#=decode(:35,0,null,:35),spare4=:29,spare2=:30,spare6=:32 where obj#=:1
734444955242386636 SQL_0a3145cba84cd4cc SQL_PLAN_0nca5tfn4tp6caf4c254a YES YES NO select order#,columns,types from access$ where d_obj#=:1
764063482407431782 SQL_0a9a7fb066be2e66 SQL_PLAN_0p6mzq1mbwbm68dd47f8f YES YES NO SELECT CONTROLFILE_TYPE FROM V$DATABASE
908110696082932625 SQL_0c9a41dde0c18391 SQL_PLAN_0t6k1vrhc30wj02acaae9 YES YES NO SELECT AO.ATTR1 OBJD, SUM(AR.BENEFIT) AS BENEFIT FROM DBA_ADVISOR_ACTIONS AA, DBA_ADVISOR_OBJECTS AO, DBA_ADVISOR_RECOMMENDATIONS AR WHERE AA.TASK_ID IN ( SELECT TASK_ID FROM DBA_ADVISOR_TASKS WHERE ADVISOR_ID=1 AND EXECUTION_START > :B1 AND STATUS='COMPLETED' AND HOW_CREATED='AUTO') AND AA.COMMAND = 'RUN SEGMENT ADVISOR' AND AA.TASK_ID = AO.TASK_ID AND AA.OBJECT_ID = AO.OBJECT_ID AND AA.TASK_ID = AR.TASK_ID AND AA.REC_ID = AR.REC_ID GROUP BY AO.ATTR1 ORDER BY 2 DESC
........
-- В baseline можно загружать планы из stage таблицы:
-- Create staging table
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
tablespace_name => 'USERS' );
END;
/
--Pack Baseline in staging table
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
-- Проверим:
select * from stg_tab_baseline;
-- Удалим все планы из baseline:
DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/
-- Проверим:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Unpack staging table into SPM
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
creator => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
-- Проверим:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Можно включить режим автоматического захвата новых планов в baseline:
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
При этом все новые планы в CURSOR_CACHE станут потенциальными кандидатами на пополнение baseline.
Новый планы будут добавлены в Plan History но не будут использованы (не попадут в baseline) до тех пор, пока не будут проверены на производительность.
SQL Tuning Advisor будет их автоматически проверять и добавлять в baseline.
-- Можно включать автоматический захват на уровне сессии:
-- Включили
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
-- Выполнили запрос:
select distinct object_name from t;
-- Отключили
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
-- Чтобы узнать каким путем планы попали в baseline нужно сделать запрос:
select signature, sql_handle, plan_name, enabled, accepted, fixed, origin from dba_sql_plan_baselines;
8469865103426906748 SQL_758b016fb13cea7c SQL_PLAN_7b2s1dysmtumwb29d3e60 YES YES NO MANUAL-LOAD
8469865103426906748 SQL_758b016fb13cea7c SQL_PLAN_7b2s1dysmtumw7b7fa22d YES NO NO AUTO-CAPTURE
Из поля origin мы узнаем как был добавлен план.
Также в baseline попадают планы для которых DBA, согласно рекомендациям SQL Tuning Advisor принимает профили.
Информация baseline хранится в компоненте SMB (SQL Management Base), который расположен в табличном пространстве SYSAUX.
-- Размером SMB можно управлять с помощью параметров:
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SPACE_BUDGET_PERCENT - сколько места в процентах в ТП SYSAUX может занимать SMB
PLAN_RETENTION_WEEKS - сколько недель хранить планы
-- Изменять эти параметры можно так:
BEGIN
DBMS_SPM.configure('space_budget_percent', 11);
DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/
Можно попытаться загрузить в baseline текст некоторого запроса, а план для него загрузить от другого запроса.
dbms_spm.load_plans_from_cursor_cache(
sql_id => sql statement a,
plan_hash_value => sql statement a,
sql_text => sql statement b
)
Иногда это может сработать.
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SPM - SQL Plan Management
SMB - SQL Management Base
Какие планы попали в SQL Management Base и baseline ?
select signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed,
sql_text
from dba_sql_plan_baselines;
signature 14499596987750078083
sql_handle SQL_c938eaa3612ad683
plan_name SQL_PLAN_ckf7andhkppn3bba443c6
enabled YES (План попал в SMB)
accepted YES (План попал в baseline)
fixed NO (Новые планы будут добавляться в baseline)
sql_text select distinct object_name from t
Ещё пример:
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
-------------------- ------------------------------ ------- -------- -----
SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO
SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO
План первого запроса попал только в SMB
План второго запроса попал в SMB и в baseline
Второй план также позволит добавлять новые планы в baseline
Посмотрим что у нас в shared pool.
SELECT sql_id,
child_number,
plan_hash_value,
hash_value,
address,
sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE HASH_VALUE ADDRESS SQL_TEXT
------------- ------------ --------------- ---------- ---------------- ----------------------------------
748bpm0yk2x76 0 1741570181 1025602790 000000009BB9F680 select distinct object_name from t
748bpm0yk2x76 1 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
Как видим, SQL_ID запроса в кэше курсоров соответствует SQL_HANDLE в SMB
a PLAN_HASH_VALUE запроса в кэше курсоров соответствует PLAN_NAME в SMB
Загрузить первый план нового запроса в SMB можно так:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440);
dbms_output.put_line('Value is '||my_plans);
END;
/
Первый план нового запроса попадет в SMB и в baseline.
После этого все новые планы данного запроса будут автоматически добавляться в SMB (но не в baseline).
Включить план, находящийся в SMB в baseline можно двумя способами:
- с верификацией
- без верификации
С верификацией новый план запроса добавится в baseline только если он окажется по производительности
не хуже уже имеющегося плана.
Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE:
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
Верификацией занимается Automatic SQL Tuning Advisor
Также будет выдан отчет о производительности планов запроса.
Включить план в baseline без верификации:
DECLARE rpt clob;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
verify=>'NO',
commit=>'YES'
);
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/
Можно наоборот, провести верификацию плана чтобы посмотреть отчет,
но план в baseline не включать.
DECLARE
rpt clob;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
verify=>'YES',
commit=>'NO'
);
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/
commit=>'NO' означает, что ненужно изменять ACCEPTED с NO на YES
ACCEPTED - Признак того, что план включен в baseline как приемлемый.
По умолчанию:
verify=>'YES'
commit=>'YES'
Модификацию атрибутов планов можно производить так:
DECLARE
ret number;
BEGIN
ret := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
attribute_name=>'ENABLED',
attribute_value=>'NO'
);
DBMS_OUTPUT.PUT_LINE(ret);
END;
/
Отключить устаревшие планы в baseline
(оптимизатор будет игнорировать такой план
т.к. ENABLED - признак нахождения плана в рабочем состоянии)
attribute_name=>'ENABLED'
attribute_value=>'NO'
Автоматическое удаление плана из SMB автоматикой AWR
по прошествии установленного времени.
attribute_name=>'AUTOPURGE'
attribute_value=>'YES'
Описание плана.
attribute_name=>'DESCRIPTION'
attribute_value=>'Описание'
Для таких запросов новые планы больше не будут добваляться в baseline.
attribute_name=>'FIXED'
attribute_value=>'YES'
При добавлении новых планов их можно сразу фиксировать:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440,
FIXED =>'YES');
dbms_output.put_line('Value is '||my_plans);
END;
/
По умолчанию:
FIXED =>'NO'
ENABLED =>'YES'
Удалить план из SMB можно так:
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SQL_c938eaa3612ad683',
plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/
!!!Удалить все планы из baseline можно так:
DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/
Выполним исходный запрос и посмотрим его план:
SQL> var A1 number
SQL> exec :A1 := 338746;
PL/SQL procedure successfully completed.
SQL> SELECT COL1 FROM T WHERE COL2 = :A1;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
Загрузим план исходного запроса в SMB:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID=>'SQL_ID',
PLAN_HASH_VALUE=>'PLAN_HASH_VALUE');
END;
/
или так:
SQL> var res number ;
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => '&sql_id', -
plan_hash_value => '&plan_hash_value' );
Enter value for sql_id: dljmr5z188i35
Enter value for plan_hash_value: 5422946733
PL/SQL procedure successfully completed.
Проверим, что он попал в SMB и baseline:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
и находим SQL_HANDLE
Оптимизируем исходный запрос, выполняем его и смотрим план:
SQL> var A1 number
SQL> exec :A1 := 338746;
PL/SQL procedure successfully completed.
SQL> SELECT /*+ INDEX() */ COL1 FROM T WHERE COL2 = :A1;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
Находим:
OPT_SQL_ID и OPT_PLAN_HASH_VALUE для оптимизированного SQL запроса.
Ассоциируем план от оптимизированного SQL запроса с исходным SQL запросом:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID=>'OPT_SQL_ID',
PLAN_HASH_VALUE=>'OPT_PLAN_HASH_VALUE',
SQL_HANDLE=>'SQL_HANDLE');
dbms_output.put_line('Number of plans loaded: ' || my_plans);
END;
/
или так:
SQL> var res number
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => '&OPT_SQL_ID', -
plan_hash_value => &opt_plan_hash_value, -
sql_handle => '&sql_handle');
Enter value for opt_sql_id: a13xgtdf6hjuy
Enter value for opt_plan_hash_value: 5678340957
Enter value for sql_handle: SQL_f45ty80gh34ft358
PL/SQL procedure successfully completed.
Проверить что оптимизированный план ассоциированный с исходным запросом
появился в SMB:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
SQL> select sql_handle,sql_text, plan_name
from dba_sql_plan_baselines
where sql_text like '%COL1 FROM T WHERE%';
SQL_HANDLE SQL_TEXT PLAN_NAME
--------------------- ------------------------------------ ------------------------------
SQL_f45ty80gh34ft358 SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tityruyr2k4k10167mc53
SQL_f45ty80gh34ft358 SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tigfgg8w2k4k45h6e3169
План SQL_PLAN_tityruyr2k4k10167mc53 оптимизированный.
Его можно переименовать:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
attribute_name=>'PLAN_NAME',
attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/
Зафиксируем новый план в baseline:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_optimyzed_20180301',
attribute_name=>'FIXED',
attribute_value=>'YES');
end;
/
Проверить использование нового закреплённого плана на базе SPM Baseline
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
SQL_ID,
CHILD_NUMBER,
PLAN_HASH_VALUE,
SQL_PROFILE,
SQL_PATCH,
SQL_PLAN_BASELINE,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');
Удалить исходный план из baseline.
var res number
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_f45ty80gh34ft358','SQL_PLAN_tigfgg8w2k4k45h6e3169');
exec dbms_output.put_line('Number of plans dropped: ' || :res);
Если исходный запрос находится в памяти (shared pool) или в AWR репозитории
и оптимизированный запрос находится в памяти (shared pool)
то ассоциировать план от оптимизированного SQL запроса с исходным SQL запросом можно так:
Исползуем инструмент SQLT
Находим SQL_ID и PLAN_HASH_VALUE для обоих запросов (исходного и оптимизированного)
SQL_ID и PLAN_HASH_VALUE
OPT_SQL_ID и OPT_PLAN_HASH_VALUE
Переходим в директорию sqlt/utl где установлен инструмент SQLT
Запускаем скрипт coe_load_sql_baseline.sql с такими параметрами:
SQL_ID OPT_SQL_ID OPT_PLAN_HASH_VALUE
SQL> connect system/*****
SQL> @coe_load_sql_baseline.sql SQL_ID OPT_SQL_ID OPT_PLAN_HASH_VALUE
Необходимо подключиться как пользователь с привилегией администратора баз данных,
например SYSTEM.
Не подключайтесь как SYS,
так как промежуточная таблица не может быть создана в схеме SYS,
и вы получите сообщение об ошибке:
ORA-19381: невозможно создать промежуточную таблицу в схеме SYS.
Проверить закрепление нового плана для исходного SQL_ID:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
Его можно переименовать:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
attribute_name=>'PLAN_NAME',
attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/
Зафиксируем новый план в baseline:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_optimyzed_20180301',
attribute_name=>'FIXED',
attribute_value=>'YES');
end;
/
Проверить использование нового закреплённого плана для исходного SQL_ID
на базе SPM Baseline:
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
SQL_ID,
CHILD_NUMBER,
PLAN_HASH_VALUE,
SQL_PROFILE,
SQL_PATCH,
SQL_PLAN_BASELINE,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');
План действий такой:
Находим sql_id запроса основного потребителя ресурсов
SELECT sql_id, buffer_gets, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;
SELECT sql_id, disk_reads, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
ORDER BY sql_id;
SELECT sql_id, cpu_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY sql_id;
SELECT sql_id, elapsed_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY sql_id;
SELECT sql_id, executions, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY sql_id;
SELECT sql_id, direct_writes, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 10000'))
ORDER BY sql_id;
SELECT sql_id, rows_processed, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 10000'))
ORDER BY sql_id;
Смотрим из каких SQL он вызван:
select top_level_sql_id, count(*)
from v$active_session_history
where sql_id = 'b6usrg82hwsa3'
group by top_level_sql_id
order by count(*) desc
Появились ли дочерние курсоры из-за различий в оптимизаторе:
select sql_id,
child_number,
optimizer_mismatch
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';
В представлении v$sql_shared_cursor можно найти все причины
почему курсор не использовался повторно и был содан дочерний
select *
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';
Или так
select s.inst_id as INST,
s.EXECUTIONS as EXECS,
to_char(to_date(s.last_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi') as last_load_time,
s.users_opening,
to_char(s.last_active_time, 'dd.mm hh24:mi') as last_active_time,
round(s.elapsed_time/decode(s.EXECUTIONS,0,1,s.EXECUTIONS)) as ELA_PER_EXEC,
s.PLAN_HASH_VALUE,
s.optimizer_cost,
s.child_number as CHILD,
s.IS_BIND_SENSITIVE as "BIND_SENSE",
s.IS_BIND_AWARE as "BIND_AWARE",
s.IS_SHAREABLE as "SHAREABLE",
use_feedback_stats as USE_FEEDBACK_STATS,
load_optimizer_stats as OPTIMIZER_STATS,
bind_equiv_failure as BIND_EQ_FAILURE,
ROLL_INVALID_MISMATCH,
bind_uacs_diff,
(select reasons || ' | ' || details
from xmltable('/ChildNode' passing
(select case when dbms_lob.instr(reason, '', 1, 2) = 0
then xmltype(reason)
when dbms_lob.instr(reason, '', 1, 2) > 4000
then xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '
', 1, 1) + 8) || '')ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
-- Сбросим пул:
alter system flush shared_pool;
-- В SQL Management Base планов пока нет:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Если есть то удаляем:
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Создаём таблицу
drop table t;
create table t as select * from all_objects;
-- Выполняем запрос
select distinct object_name from t;
-- Находим sql_id запроса:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
-- Получаем план запроса
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1793979440
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 857 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 857 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 88914 | 2170K| | 388 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]
-- И загружаем план в baseline:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440,
FIXED =>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/
-- План запроса появился в SQL Management Base и baseline:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
-- Создаем индекс:
create index t_idx on t(object_name);
-- Выполняем запрос:
select distinct object_name from t;
-- Новый план запроса появился в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
Оптимизатор построил новый план для данного запроса, занес этот план в историю, но запрос выполнил по старому плану из baseline
-- Посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 1 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));
SQL_ID: 748bpm0yk2x76, child number: 1 cannot be found
-- Повторно выполняем запрос:
select distinct object_name from t;
-- И смотрим план:
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 1
-------------------------------------
select distinct object_name from t
Plan hash value: 1793979440
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 857 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 857 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 88914 | 2170K| | 388 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T"@"SEL$1")
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]
Note
-----
- SQL plan baseline SQL_PLAN_ckf7andhkppn3bba443c6 used for this statement
Теперь в shared pool этот план уже был, но запрос по прежнему был выполнен по старому плану из baseline.
Т.е. при включенном управлении планами, планы выполнения берутся для всех запросов из baseline, если они там есть.
-- Отключим управление планами:
alter session set optimizer_use_sql_plan_baselines = false;
-- Выполним запрос:
select distinct object_name from t;
-- И посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1741570181 1025602790 000000009FEE5A80 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1741570181
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 592 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 592 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_IDX | 88914 | 2170K| | 123 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "OBJECT_NAME"[VARCHAR2,128]
Теперь запрос отработал уже по новому плану с учетом индекса.
-- Вернем управление планами:
alter session set optimizer_use_sql_plan_baselines = true;
-- Посмотрим что у нас в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
запрос вернул две строки, одна с accepted = no и вторая с accepted = yes
Все планы с признаками accepted = no, могут быть проанализированы оптимизатором и в зависимости от результата проверки помещены в baseline
(если они окажутся по эффективности не хуже ранее там имеющихся):
-- Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : ЗАДАЧА_751
Task Owner : SCOTT
Execution Name : EXEC_801
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 01/19/2015 10:35:10
Finished : 01/19/2015 10:35:11
Last Updated : 01/19/2015 10:35:11
Global Time Limit : 60
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_ckf7andhkppn37883a793
Base Plan Name : SQL_PLAN_ckf7andhkppn3bba443c6
SQL Handle : SQL_c938eaa3612ad683
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select distinct object_name from t
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .002383 .002215
CPU Time (s): .001911 .001767
Buffer Gets: 139 45
Optimizer Cost: 857 592
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 5138 5138
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. Проверка плана длилась 0.56000 секунд. План прошел проверку на критерий
преимущества, поскольку проверенный уровень производительности был в
3.06716 раз лучше основного плана.
2. План был автоматически принят.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 3901
Plan Hash Value : 3148104646
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51024 | 1275600 | 857 | 00:00:01 |
| 1 | HASH UNIQUE | | 51024 | 1275600 | 857 | 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 88914 | 2222850 | 388 | 00:00:01 |
------------------------------------------------------------------------
Test Plan
-----------------------------
Plan Id : 3902
Plan Hash Value : 2021894035
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51024 | 1275600 | 592 | 00:00:01 |
| 1 | HASH UNIQUE | | 51024 | 1275600 | 592 | 00:00:01 |
| 2 | INDEX FAST FULL SCAN | T_IDX | 88914 | 2222850 | 123 | 00:00:01 |
----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
-- Смотрим планы в SQL Management Base:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES YES NO select distinct object_name from t
14499596987750078083 SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO select distinct object_name from t
Новый план после верификации был включен в baseline.
-- Выполняем запрос:
select distinct object_name from t;
-- Посмотрим на план:
SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
748bpm0yk2x76 0 1741570181 1025602790 000000009BB9F680 select distinct object_name from t
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));
SQL_ID 748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t
Plan hash value: 1741570181
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 592 (100)| |
| 1 | HASH UNIQUE | | 51024 | 1245K| 2800K| 592 (2)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T_IDX | 88914 | 2170K| | 123 (1)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
USE_HASH_AGGREGATION(@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_NAME"[VARCHAR2,128]
2 - "OBJECT_NAME"[VARCHAR2,128]
Note
-----
- SQL plan baseline SQL_PLAN_ckf7andhkppn37883a793 used for this statement
-- Получать планы из baseline теперь можно так:
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'BASIC'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL -projection'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL +peeked_binds'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS '));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>'SQL_PLAN_ckf7andhkppn3bba443c6',format=>'ADVANCED'));
-- Пополнение baseline новыми планами:
Мы уже добавляли новые планы в baseline используя функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
Еще планы можно загружать в baseline без предварительной проверки их эффективности:
-- Например можно загрузить планы из STS:
SELECT sqlset_name, count(*) FROM DBA_SQLSET_STATEMENTS
group by sqlset_name;
new_sts 66
my_sts 212
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'my_sts'
);
dbms_output.put_line('Value is '||my_plans);
END;
/
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
228404646742146688 SQL_032b74d0ea1d3e80 SQL_PLAN_06avnu3p1ugn07f2c8b72 YES YES NO SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
228404646742146688 SQL_032b74d0ea1d3e80 SQL_PLAN_06avnu3p1ugn0fa6e684e YES YES NO SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
271831168975187047 SQL_03c5bd0209a52c67 SQL_PLAN_07jdx084uab37df05bb92 YES YES NO CREATE TABLE map_tab
616121024526838780 SQL_088ce6ceb5926bfc SQL_PLAN_0j376tuut4uzw5c154e55 YES YES NO update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,evaledition#=decode(:33,1,null,:33),unusablebefore#=decode(:34,0,null,:34),unusablebeginning#=decode(:35,0,null,:35),spare4=:29,spare2=:30,spare6=:32 where obj#=:1
734444955242386636 SQL_0a3145cba84cd4cc SQL_PLAN_0nca5tfn4tp6caf4c254a YES YES NO select order#,columns,types from access$ where d_obj#=:1
764063482407431782 SQL_0a9a7fb066be2e66 SQL_PLAN_0p6mzq1mbwbm68dd47f8f YES YES NO SELECT CONTROLFILE_TYPE FROM V$DATABASE
908110696082932625 SQL_0c9a41dde0c18391 SQL_PLAN_0t6k1vrhc30wj02acaae9 YES YES NO SELECT AO.ATTR1 OBJD, SUM(AR.BENEFIT) AS BENEFIT FROM DBA_ADVISOR_ACTIONS AA, DBA_ADVISOR_OBJECTS AO, DBA_ADVISOR_RECOMMENDATIONS AR WHERE AA.TASK_ID IN ( SELECT TASK_ID FROM DBA_ADVISOR_TASKS WHERE ADVISOR_ID=1 AND EXECUTION_START > :B1 AND STATUS='COMPLETED' AND HOW_CREATED='AUTO') AND AA.COMMAND = 'RUN SEGMENT ADVISOR' AND AA.TASK_ID = AO.TASK_ID AND AA.OBJECT_ID = AO.OBJECT_ID AND AA.TASK_ID = AR.TASK_ID AND AA.REC_ID = AR.REC_ID GROUP BY AO.ATTR1 ORDER BY 2 DESC
........
-- В baseline можно загружать планы из stage таблицы:
-- Create staging table
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
tablespace_name => 'USERS' );
END;
/
--Pack Baseline in staging table
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
-- Проверим:
select * from stg_tab_baseline;
-- Удалим все планы из baseline:
DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/
-- Проверим:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Unpack staging table into SPM
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
creator => 'SCOTT');
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/
-- Проверим:
select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;
-- Можно включить режим автоматического захвата новых планов в baseline:
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
При этом все новые планы в CURSOR_CACHE станут потенциальными кандидатами на пополнение baseline.
Новый планы будут добавлены в Plan History но не будут использованы (не попадут в baseline) до тех пор, пока не будут проверены на производительность.
SQL Tuning Advisor будет их автоматически проверять и добавлять в baseline.
-- Можно включать автоматический захват на уровне сессии:
-- Включили
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
-- Выполнили запрос:
select distinct object_name from t;
-- Отключили
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
-- Чтобы узнать каким путем планы попали в baseline нужно сделать запрос:
select signature, sql_handle, plan_name, enabled, accepted, fixed, origin from dba_sql_plan_baselines;
8469865103426906748 SQL_758b016fb13cea7c SQL_PLAN_7b2s1dysmtumwb29d3e60 YES YES NO MANUAL-LOAD
8469865103426906748 SQL_758b016fb13cea7c SQL_PLAN_7b2s1dysmtumw7b7fa22d YES NO NO AUTO-CAPTURE
Из поля origin мы узнаем как был добавлен план.
Также в baseline попадают планы для которых DBA, согласно рекомендациям SQL Tuning Advisor принимает профили.
Информация baseline хранится в компоненте SMB (SQL Management Base), который расположен в табличном пространстве SYSAUX.
-- Размером SMB можно управлять с помощью параметров:
SELECT parameter_name, parameter_value
FROM dba_sql_management_config;
PARAMETER_NAME PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT 10
PLAN_RETENTION_WEEKS 53
SPACE_BUDGET_PERCENT - сколько места в процентах в ТП SYSAUX может занимать SMB
PLAN_RETENTION_WEEKS - сколько недель хранить планы
-- Изменять эти параметры можно так:
BEGIN
DBMS_SPM.configure('space_budget_percent', 11);
DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/
Можно попытаться загрузить в baseline текст некоторого запроса, а план для него загрузить от другого запроса.
dbms_spm.load_plans_from_cursor_cache(
sql_id => sql statement a,
plan_hash_value => sql statement a,
sql_text => sql statement b
)
Иногда это может сработать.
Рассмотрим примеры подмены планов.
По умолчанию параметры такие:ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;
SPM - SQL Plan Management
SMB - SQL Management Base
Какие планы попали в SQL Management Base и baseline ?
select signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed,
sql_text
from dba_sql_plan_baselines;
signature 14499596987750078083
sql_handle SQL_c938eaa3612ad683
plan_name SQL_PLAN_ckf7andhkppn3bba443c6
enabled YES (План попал в SMB)
accepted YES (План попал в baseline)
fixed NO (Новые планы будут добавляться в baseline)
sql_text select distinct object_name from t
Ещё пример:
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
-------------------- ------------------------------ ------- -------- -----
SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn37883a793 YES NO NO
SQL_c938eaa3612ad683 SQL_PLAN_ckf7andhkppn3bba443c6 YES YES NO
План первого запроса попал только в SMB
План второго запроса попал в SMB и в baseline
Второй план также позволит добавлять новые планы в baseline
Посмотрим что у нас в shared pool.
SELECT sql_id,
child_number,
plan_hash_value,
hash_value,
address,
sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE HASH_VALUE ADDRESS SQL_TEXT
------------- ------------ --------------- ---------- ---------------- ----------------------------------
748bpm0yk2x76 0 1741570181 1025602790 000000009BB9F680 select distinct object_name from t
748bpm0yk2x76 1 1793979440 1025602790 000000009D9141C0 select distinct object_name from t
Как видим, SQL_ID запроса в кэше курсоров соответствует SQL_HANDLE в SMB
a PLAN_HASH_VALUE запроса в кэше курсоров соответствует PLAN_NAME в SMB
Загрузить первый план нового запроса в SMB можно так:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440);
dbms_output.put_line('Value is '||my_plans);
END;
/
Первый план нового запроса попадет в SMB и в baseline.
После этого все новые планы данного запроса будут автоматически добавляться в SMB (но не в baseline).
Включить план, находящийся в SMB в baseline можно двумя способами:
- с верификацией
- без верификации
С верификацией новый план запроса добавится в baseline только если он окажется по производительности
не хуже уже имеющегося плана.
Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE:
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/
Верификацией занимается Automatic SQL Tuning Advisor
Также будет выдан отчет о производительности планов запроса.
Включить план в baseline без верификации:
DECLARE rpt clob;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
verify=>'NO',
commit=>'YES'
);
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/
Можно наоборот, провести верификацию плана чтобы посмотреть отчет,
но план в baseline не включать.
DECLARE
rpt clob;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
verify=>'YES',
commit=>'NO'
);
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/
commit=>'NO' означает, что ненужно изменять ACCEPTED с NO на YES
ACCEPTED - Признак того, что план включен в baseline как приемлемый.
По умолчанию:
verify=>'YES'
commit=>'YES'
Модификацию атрибутов планов можно производить так:
DECLARE
ret number;
BEGIN
ret := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>'SQL_758b016fb13cea7c',
plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
attribute_name=>'ENABLED',
attribute_value=>'NO'
);
DBMS_OUTPUT.PUT_LINE(ret);
END;
/
Отключить устаревшие планы в baseline
(оптимизатор будет игнорировать такой план
т.к. ENABLED - признак нахождения плана в рабочем состоянии)
attribute_name=>'ENABLED'
attribute_value=>'NO'
Автоматическое удаление плана из SMB автоматикой AWR
по прошествии установленного времени.
attribute_name=>'AUTOPURGE'
attribute_value=>'YES'
Описание плана.
attribute_name=>'DESCRIPTION'
attribute_value=>'Описание'
Для таких запросов новые планы больше не будут добваляться в baseline.
attribute_name=>'FIXED'
attribute_value=>'YES'
При добавлении новых планов их можно сразу фиксировать:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440,
FIXED =>'YES');
dbms_output.put_line('Value is '||my_plans);
END;
/
По умолчанию:
FIXED =>'NO'
ENABLED =>'YES'
Удалить план из SMB можно так:
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SQL_c938eaa3612ad683',
plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/
!!!Удалить все планы из baseline можно так:
DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/
Рассмотрим примеры:
Выполним исходный запрос и посмотрим его план:
SQL> var A1 number
SQL> exec :A1 := 338746;
PL/SQL procedure successfully completed.
SQL> SELECT COL1 FROM T WHERE COL2 = :A1;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
Загрузим план исходного запроса в SMB:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID=>'SQL_ID',
PLAN_HASH_VALUE=>'PLAN_HASH_VALUE');
END;
/
или так:
SQL> var res number ;
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => '&sql_id', -
plan_hash_value => '&plan_hash_value' );
Enter value for sql_id: dljmr5z188i35
Enter value for plan_hash_value: 5422946733
PL/SQL procedure successfully completed.
Проверим, что он попал в SMB и baseline:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
и находим SQL_HANDLE
Оптимизируем исходный запрос, выполняем его и смотрим план:
SQL> var A1 number
SQL> exec :A1 := 338746;
PL/SQL procedure successfully completed.
SQL> SELECT /*+ INDEX() */ COL1 FROM T WHERE COL2 = :A1;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
Находим:
OPT_SQL_ID и OPT_PLAN_HASH_VALUE для оптимизированного SQL запроса.
Ассоциируем план от оптимизированного SQL запроса с исходным SQL запросом:
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
SQL_ID=>'OPT_SQL_ID',
PLAN_HASH_VALUE=>'OPT_PLAN_HASH_VALUE',
SQL_HANDLE=>'SQL_HANDLE');
dbms_output.put_line('Number of plans loaded: ' || my_plans);
END;
/
или так:
SQL> var res number
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => '&OPT_SQL_ID', -
plan_hash_value => &opt_plan_hash_value, -
sql_handle => '&sql_handle');
Enter value for opt_sql_id: a13xgtdf6hjuy
Enter value for opt_plan_hash_value: 5678340957
Enter value for sql_handle: SQL_f45ty80gh34ft358
PL/SQL procedure successfully completed.
Проверить что оптимизированный план ассоциированный с исходным запросом
появился в SMB:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
SQL> select sql_handle,sql_text, plan_name
from dba_sql_plan_baselines
where sql_text like '%COL1 FROM T WHERE%';
SQL_HANDLE SQL_TEXT PLAN_NAME
--------------------- ------------------------------------ ------------------------------
SQL_f45ty80gh34ft358 SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tityruyr2k4k10167mc53
SQL_f45ty80gh34ft358 SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tigfgg8w2k4k45h6e3169
План SQL_PLAN_tityruyr2k4k10167mc53 оптимизированный.
Его можно переименовать:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
attribute_name=>'PLAN_NAME',
attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/
Зафиксируем новый план в baseline:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_optimyzed_20180301',
attribute_name=>'FIXED',
attribute_value=>'YES');
end;
/
Проверить использование нового закреплённого плана на базе SPM Baseline
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
SQL_ID,
CHILD_NUMBER,
PLAN_HASH_VALUE,
SQL_PROFILE,
SQL_PATCH,
SQL_PLAN_BASELINE,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');
Удалить исходный план из baseline.
var res number
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_f45ty80gh34ft358','SQL_PLAN_tigfgg8w2k4k45h6e3169');
exec dbms_output.put_line('Number of plans dropped: ' || :res);
Использование инструмента SQLT.
Если исходный запрос находится в памяти (shared pool) или в AWR репозитории
и оптимизированный запрос находится в памяти (shared pool)
то ассоциировать план от оптимизированного SQL запроса с исходным SQL запросом можно так:
Исползуем инструмент SQLT
Находим SQL_ID и PLAN_HASH_VALUE для обоих запросов (исходного и оптимизированного)
SQL_ID и PLAN_HASH_VALUE
OPT_SQL_ID и OPT_PLAN_HASH_VALUE
Переходим в директорию sqlt/utl где установлен инструмент SQLT
Запускаем скрипт coe_load_sql_baseline.sql с такими параметрами:
SQL_ID OPT_SQL_ID OPT_PLAN_HASH_VALUE
SQL> connect system/*****
SQL> @coe_load_sql_baseline.sql SQL_ID OPT_SQL_ID OPT_PLAN_HASH_VALUE
Необходимо подключиться как пользователь с привилегией администратора баз данных,
например SYSTEM.
Не подключайтесь как SYS,
так как промежуточная таблица не может быть создана в схеме SYS,
и вы получите сообщение об ошибке:
ORA-19381: невозможно создать промежуточную таблицу в схеме SYS.
Проверить закрепление нового плана для исходного SQL_ID:
col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
b.parsing_schema_name, b.optimizer_cost, b.origin,
b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';
Его можно переименовать:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
attribute_name=>'PLAN_NAME',
attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/
Зафиксируем новый план в baseline:
declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_f45ty80gh34ft358',
plan_name=>'SQL_PLAN_optimyzed_20180301',
attribute_name=>'FIXED',
attribute_value=>'YES');
end;
/
Проверить использование нового закреплённого плана для исходного SQL_ID
на базе SPM Baseline:
col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
SQL_ID,
CHILD_NUMBER,
PLAN_HASH_VALUE,
SQL_PROFILE,
SQL_PATCH,
SQL_PLAN_BASELINE,
LAST_LOAD_TIME,
LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');
Другие примеры:
План действий такой:
Находим sql_id запроса основного потребителя ресурсов
SELECT sql_id, buffer_gets, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;
SELECT sql_id, disk_reads, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
ORDER BY sql_id;
SELECT sql_id, cpu_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY sql_id;
SELECT sql_id, elapsed_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY sql_id;
SELECT sql_id, executions, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY sql_id;
SELECT sql_id, direct_writes, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 10000'))
ORDER BY sql_id;
SELECT sql_id, rows_processed, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 10000'))
ORDER BY sql_id;
Смотрим из каких SQL он вызван:
select top_level_sql_id, count(*)
from v$active_session_history
where sql_id = 'b6usrg82hwsa3'
group by top_level_sql_id
order by count(*) desc
Появились ли дочерние курсоры из-за различий в оптимизаторе:
select sql_id,
child_number,
optimizer_mismatch
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';
В представлении v$sql_shared_cursor можно найти все причины
почему курсор не использовался повторно и был содан дочерний
select *
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';
Или так
select s.inst_id as INST,
s.EXECUTIONS as EXECS,
to_char(to_date(s.last_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi') as last_load_time,
s.users_opening,
to_char(s.last_active_time, 'dd.mm hh24:mi') as last_active_time,
round(s.elapsed_time/decode(s.EXECUTIONS,0,1,s.EXECUTIONS)) as ELA_PER_EXEC,
s.PLAN_HASH_VALUE,
s.optimizer_cost,
s.child_number as CHILD,
s.IS_BIND_SENSITIVE as "BIND_SENSE",
s.IS_BIND_AWARE as "BIND_AWARE",
s.IS_SHAREABLE as "SHAREABLE",
use_feedback_stats as USE_FEEDBACK_STATS,
load_optimizer_stats as OPTIMIZER_STATS,
bind_equiv_failure as BIND_EQ_FAILURE,
ROLL_INVALID_MISMATCH,
bind_uacs_diff,
(select reasons || ' | ' || details
from xmltable('/ChildNode' passing
(select case when dbms_lob.instr(reason, '
then xmltype(reason)
when dbms_lob.instr(reason, '
then xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '
else xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '
end as xmlval
from gv$sql_shared_cursor
where dbms_lob.substr(reason, 256) <> ' '
and sql_id = sc.sql_id
and inst_id = sc.inst_id
and child_address = sc.child_address)
columns Reasons varchar2(4000) path '/ChildNode/reason',
Details varchar2(4000) path '/ChildNode/details')) as Reason1,
SQL_PLAN_BASELINE,
SQL_PATCH,
OUTLINE_CATEGORY,
SQL_PROFILE,
IS_OBSOLETE
from gv$sql_shared_cursor sc, gv$sql s
where sc.sql_id = 'b6usrg82hwsa3'
and sc.inst_id = s.inst_id
and sc.child_address = s.child_address
and sc.sql_id = s.sql_id
and sc.inst_id > 0
and (s.EXECUTIONS>0 or s.users_opening>0)
order by s.inst_id, --s.child_number
s.last_active_time desc;
Если курсоров несколько, то выбираем с лучшим планом (смотрим ela_per_exec)
Подключаемся к соответствующему экземпляру в sared pool-е которого находится курсор.
Зафиксируем данный курсор в базовой линии.
создать base_line (sql_id plan_hash_value "название");
Рассмотреть вопрос с использованием sql Patch или просто хинтов.
Компиляция нового курсора - это полный разбор (parse count (hard)), активность защёлок,
расход времени CPU (это дорогой процесс).
Нужно повторно использовать курсоры.
При повторном использовании будет частичный разбор
soft_parse = parse count (total) -
parse count (hard)
из v$sysstat
ещё смотрите на время
parse time cpu
parse time elapsed
эти значения не должны расти во время работы приложения в идеале.
Чтобы курсоры использовались повторно должны еще, кроме текстов запросов,
совпадать и установки режимов работы оптимизатора
alter session set optimizer_mode = first_rows;
select ...
alter session set optimizer_mode = all_rows;
select ...
планы будут разные
Столбец sql_id в v$sql и v$open_cursor однозначно идентифицирует курсор
Раньше, до 10g использовали комбинацию (hash_value, address)
Примеры парсинга:
create table test1(a number);
--Flush the pool, or else this test won't be repeatable.
alter system flush shared_pool;
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
47 parse count (total)
5 parse count (hard)
begin
for i in 1 .. 10000 loop
execute immediate 'insert into test1 values('||i||')';
end loop;
commit;
end;
/
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
10072 parse count (total)
10007 parse count (hard)
begin
for i in 1 .. 10000 loop
execute immediate
'begin
delete from test1 where a = :i;
end;'
using i;
end loop;
commit;
end;
/
select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
and name in ('parse count (total)', 'parse count (hard)');
10106 parse count (total)
10019 parse count (hard)
