Мой блог: 01/01/2015

понедельник, 19 января 2015 г.

SQL Plan Management

По умолчанию параметры такие:

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, '', 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) || '')
                                  else xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '', 1, 2) - 1))
                                  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)

четверг, 15 января 2015 г.

Загружаем SQL statements в SQL Tuning Set из another SQL Tuning Set

-- Параметры функции:

DBMS_SQLTUNE.SELECT_SQLSET (
sqlset_name         IN   VARCHAR2,
basic_filter        IN   VARCHAR2 := NULL,
object_filter       IN   VARCHAR2 := NULL,
ranking_measure1    IN   VARCHAR2 := NULL,
ranking_measure2    IN   VARCHAR2 := NULL,
ranking_measure3    IN   VARCHAR2 := NULL,
result_percentage   IN   NUMBER   := 1,
result_limit        IN   NUMBER   := NULL)
attribute_list      IN   VARCHAR2 := NULL,
plan_filter         IN   VARCHAR2 := NULL,
sqlset_owner        IN   VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;

sqlset_name The SQL tuning set name

basic_filter The SQL predicate to filter the SQL from the SQL Tuning Set defined on attributes of the SQLSET_ROW

object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS (similar to SQL_PLAN + row source statistics)

plan_filter The plan filter

sqlset_owner The owner of the SQL tuning set, or NULL for the current schema owner

Return Values
This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

-- Имеется некий STS my_sts:

SELECT *
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQLSET(
         sqlset_name       => 'my_sts',
         basic_filter      => 'disk_reads < 100',
         object_filter     => NULL,
         ranking_measure1 => NULL,
         ranking_measure2 => NULL,
         ranking_measure3 => NULL,
         result_percentage => 1,
         result_limit      => NULL,
         attribute_list    => NULL,
         plan_filter       => NULL,
         sqlset_owner      => NULL ));


-- Загрузим результат этого запроса в другой STS new_sts:


-- Создаем новый STS:
BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'new_sts'
,description => 'Prod workload sample');
END;
/

-- Загружаем SQL statements в SQL Tuning Set new_sts из SQL Tuning Set my_sts:
DECLARE
ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQLSET(
         sqlset_name       => 'my_sts',
         basic_filter      => 'disk_reads < 100',
         object_filter     => NULL,
         ranking_measure1 => NULL,
         ranking_measure2 => NULL,
         ranking_measure3 => NULL,
         result_percentage => 1,
         result_limit      => NULL,
         attribute_list    => NULL,
         plan_filter       => NULL,
         sqlset_owner      => NULL )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'new_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
CLOSE ref_cursor;
END;
/

-- Проверяем что загрузилось:
SELECT sqlset_name, count(*) FROM DBA_SQLSET_STATEMENTS
group by sqlset_name;

new_sts    66
my_sts    69

-- Планы из STS можно посмотреть так:

dbms_xplan.display_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL',
sqlset_owner    IN VARCHAR2 DEFAULT NULL)
RETURN dbms_xplan_type_table PIPELINED;

select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'BASIC'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALL -projection'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED'));
select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

select * from table(dbms_xplan.display_sqlset('my_sts', '5k5207588w9ry', 1388734953, 'ADVANCED', 'SCOTT'));

Захват продуктивной SQL нагрузки

-- Создаем STS:

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
END;
/

--Захватываем SQL из CURSOR_CACHE в SQLSET:

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/

-- Просмотр STS:

SELECT name, created, statement_count
FROM dba_sqlset;

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;

SELECT sql_id, elapsed_time
,cpu_time, buffer_gets
,disk_reads, sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('PROD_WORKLOAD'));

-- Выборочно удалаем ненужные SQL statements из STS:

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,basic_filter => 'disk_reads < 100000');
END;
/
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

-- Создаем таблицу для загрузки SQL statements из STS:

BEGIN
dbms_sqltune.create_stgtab_sqlset(
table_name => 'STS_TABLE'
,schema_name => 'SCOTT');
END;
/
select * from STS_TABLE;

-- Загружаем в неё SQL statements из STS:

BEGIN
dbms_sqltune.pack_stgtab_sqlset(
sqlset_name => 'PROD_WORKLOAD'
,sqlset_owner => 'SCOTT'
,staging_table_name => 'STS_TABLE'
,staging_schema_owner => 'SCOTT');
END;
/
select * from STS_TABLE;

SELECT name, owner, created, statement_count
FROM dba_sqlset;

-- Переносим таблицу STS_TABLE в другую СУБД:

drop database link source_db;
create database link source_db
connect to scott
identified by tiger
using 'testdb';

create table STS_TABLE as select * from STS_TABLE@source_db;

-- Создать все STS из таблицы (с опцией replace):

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%'
,replace => TRUE
,staging_table_name => 'STS_TABLE'
,staging_schema_owner=> 'SCOTT');
END;
/

-- Проверяем, что STS создан:

SELECT name, owner, created, statement_count
FROM dba_sqlset;

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Параметры функции:

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
basic_filter        IN   VARCHAR2 := NULL,
object_filter       IN   VARCHAR2 := NULL,
ranking_measure1    IN   VARCHAR2 := NULL,
ranking_measure2    IN   VARCHAR2 := NULL,
ranking_measure3    IN   VARCHAR2 := NULL,
result_percentage   IN   NUMBER   := 1,
result_limit        IN   NUMBER   := NULL,
attribute_list      IN   VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;

sqlset_name   The SQL tuning set name

basic_filter The SQL predicate to filter the SQL from the cursor cache defined on attributes of the
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED

object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics

Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Примеры запросов:

SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE);


SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                             'parsing_schema_name <> ''SYS''' ));


SELECT sql_id, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;

SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''bc688uy91wzka'''));

SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''bc688uy91wzka'''))
ORDER BY sql_id, plan_hash_value;

SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('module = ''MMON_SLAVE'''));

-- all statements that ran for at least five seconds
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000'));


-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an SCOTT user
SELECT *
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''SCOTT'''));


-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)
SELECT *
FROM table(dbms_sqltune.select_cursor_cache(
'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN'));

-- Select the top 100 statements in the cursor cache ordering by elapsed_time.
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100));


SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;

SELECT sql_id, substr(sql_text,1,20), disk_reads
,cpu_time, elapsed_time
,buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'parsing_schema_name <> "SYS"'
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));

SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> "SYS"
AND elapsed_time > 1000000'))
ORDER BY sql_id;

-- Запрос к CURSOR_CACHE
SELECT *
FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1 => NULL,
          ranking_measure2 => NULL,
          ranking_measure3 => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' ));

-- Удаляем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/

-- Создаем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/

-- Загружаем SQL statements в SQL Tuning Set из CURSOR_CACHE
DECLARE
ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1 => NULL,
          ranking_measure2 => NULL,
          ranking_measure3 => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
CLOSE ref_cursor;
END;
/

-- Просмотр STS:

SELECT * FROM DBA_SQLSET_STATEMENTS;

-- Просмотр плана из STS:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => 'btd7m8k25qr9h',
       plan_hash_value => 1081635106
       ));

-- Планы из CURSOR_CACHE можно посмотреть так:

-- находим sql_id:

select * from table (dbms_sqltune.select_cursor_cache('sql_text like ''select /*MY_CRITICAL_SQL*/%'''));
select * from table (dbms_sqltune.select_cursor_cache('sql_id = ''4n3pdustvb0yk'''));

-- смотрим план:

select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'BASIC'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL -projection'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL +peeked_binds'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +alias -predicate'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +outline'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;

-- или через SQL monitor:

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;
/

Загрузка SQL statements в SQL Tuning Set из AWR

-- Запрос к AWR
SELECT *
FROM TABLE(
     DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
      begin_snap   => 415,
      end_snap     => 420,
      basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p
WHERE parsing_schema_name = 'SCOTT';

-- Удаляем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/

-- Создаем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/

-- Загружаем SQL statements в SQL Tuning Set из AWR
DECLARE
ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
         begin_snap   => 415,
         end_snap     => 420,
         basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
CLOSE ref_cursor;
END;
/

-- Просмотр STS:

SELECT * FROM DBA_SQLSET_STATEMENTS;

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => '0r1gq4aapgnxd',
       plan_hash_value => 426858176
       ));

Список параметров функции select_workload_repository:

dbms_sqltune.select_workload_repository(
            BEGIN_SNAP        => 415,
            END_SNAP          => 420,
            BASIC_FILTER      => 'SQL_ID = ''64qdks642mqt2'' AND PLAN_HASH_VALUE = 3406458838',
            OBJECT_FILTER     => NULL,
            RANKING_MEASURE1 => 'disk_reads',
            RANKING_MEASURE2 => NULL,
            RANKING_MEASURE3 => NULL,
            RESULT_PERCENTAGE => 1,
            RESULT_LIMIT      => 250,
            ATTRIBUTE_LIST    => 'ALL',
            RECURSIVE_SQL     => 'Y'
)

Вместо BEGIN_SNAP и END_SNAP, можно указать BASELINE_NAME.

BEGIN_SNAP          Non-inclusive beginning snapshot ID

END_SNAP            Inclusive ending snapshot ID

BASELINE_NAME       Name of AWR baseline

BASIC_FILTER        SQL predicate to filter SQL statements from workload; if not set, then only SELECT, INSERT, UPDATE, DELETE, MERGE, and CREATE TABLE statements are captured.

OBJECT_FILTER       Not currently used

RANKING_MEASURE(n) Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads, and so on;
N can be 1, 2, or 3. The elapsed_time and cpu_time are measured in seconds.

RESULT_PERCENTAGE   Filter for choosing top N% for ranking measure

RESULT_LIMIT        Limit of the number of SQL statements returned in the result set

ATTRIBUTE_LIST      List of SQL statement attributes (TYPICAL, BASIC, ALL, and so on)

RECURSIVE_SQL       Include/exclude recursive SQL (HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL)

cpu_time : Number of seconds
elapsed_time : Number of seconds
disk_reads : Number of reads from disk
buffer_gets : Number of reads from memory
rows_processed : Average number of rows
optimizer_cost : Calculated optimizer cost
executions : Total execution count of SQL statement

Примеры использования параметров:

BASIC_FILTER   => 'parsing_schema_name <> "SYS"'
( 'parsing_schema_name not in (''DBSNMP'',''SYS'',''ORACLE_OCM'')',)

RANKING_MEASURE1 => 'cpu_time'
( 'elapsed_time', buffer_gets, disk_reads, )

Еще несколько примеров :

SELECT snap_id, instance_number, end_interval_time

FROM dba_hist_snapshot

ORDER BY snap_id;

SELECT sql_id

,substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

null, null, 'disk_reads',null, null, null, 10))

ORDER BY disk_reads DESC;

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

'parsing_schema_name <> ''SYS''',

NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'buffer_gets'

,result_limit => 10

));

==========================================================

SELECT MAX(snap_id) bsnap

FROM dba_hist_snapshot

WHERE begin_interval_time < sysdate-7;

SELECT MAX(snap_id) esnap

FROM dba_hist_snapshot;

COL sql_text FORMAT A40

COL sql_id FORMAT A15

COL parsing_schema_name FORMAT A15

COL cpu_seconds FORMAT 999,999,999,999,999

SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON

SELECT sql_id, sql_text

,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'cpu_time'

,result_limit => 10

));

-- Посмотреть менялся ли план у запроса:

select   snap_id, plan_hash_value
    from dba_hist_sqlstat
   where snap_id in (415,420) and sql_id='0r1gq4aapgnxd'
order by snap_id desc;

-- Посмотреть из AWR какие планы изменялись:

select   a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
    from dba_hist_sqlstat a, dba_hist_sqlstat b
   where (a.snap_id = 415 and b.snap_id = 420)
     and (a.sql_id = b.sql_id)
     and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;

-- по всему репозиторию:

select distinct sql_id, plan_hash_value, f snapshot,
                (select begin_interval_time
                   from dba_hist_snapshot
                  where snap_id = f) snapdate
           from (select sql_id, plan_hash_value,
                        first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
                   from (select   sql_id, plan_hash_value, snap_id,
                                  count (distinct plan_hash_value) over (partition by sql_id) a
                             from dba_hist_sqlstat
                            where plan_hash_value > 0
                         order by sql_id)
                  where a > 1)
       order by sql_id, f;

-- Планы из AWR можно посмотреть так:

select * from table(dbms_xplan.display_awr('5k5207588w9ry'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953 ));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'BASIC'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL -projection'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

dbms_xplan.display_awr(
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
db_id           IN INTEGER DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;

Мой блог

понедельник, 19 января 2015 г.

SQL Plan Management

Рассмотрим примеры подмены планов.

Рассмотрим примеры:

Использование инструмента SQLT.

Другие примеры:

четверг, 15 января 2015 г.

Загружаем SQL statements в SQL Tuning Set из another SQL Tuning Set

Захват продуктивной SQL нагрузки

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Загрузка SQL statements в SQL Tuning Set из AWR

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

Мой блог

понедельник, 19 января 2015 г.

SQL Plan Management

Рассмотрим примеры подмены планов.

Рассмотрим примеры:

Использование инструмента SQLT.

Другие примеры:

четверг, 15 января 2015 г.

Загружаем SQL statements в SQL Tuning Set из another SQL Tuning Set

Захват продуктивной SQL нагрузки

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Загрузка SQL statements в SQL Tuning Set из AWR

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

понедельник, 19 января 2015 г.

четверг, 15 января 2015 г.

среда, 14 января 2015 г.