Хеш секционирование

При хеш - секционировании таблицы Oracle применит хеш - функцию к ключу секционирования
для определения того, в какую из n секций должны быть помощены данные.

Oracle рекомендует в качестве n значения использовать степень двойки (2, 4, 6, 8, 16 ...)
чтобы данные распределялись между секциями более равномерно.

Выбранный для таблицы хеш - ключ должен быть столбцом или набором столбцов, уникальных или по крайней мере,
использующих столько различных значений, сколько необходимо для достижения равномерного распределения строк между секциями.


Создадим табличные пространства:

drop tablespace tsp1;
drop tablespace tsp2;
drop tablespace tsp3;
drop tablespace tsp4;
drop tablespace tsp5;
drop tablespace tsp6;
drop tablespace tsp7;
drop tablespace tsp8;
create tablespace tsp1 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp2 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp3 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp4 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp5 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp6 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;
create tablespace tsp7 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local uniform size 100m;
create tablespace tsp8 datafile '+DATA' size 500m reuse
autoextend on next 100m extent management local autoallocate;


Создадим таблицу t с ключом секционирования по столбцу object_id:

drop table t;
create table t (object_id, owner, object_type, object_name, created)
partition by hash(object_id)
(
partition part_1 tablespace tsp1,
partition part_2 tablespace tsp2,
partition part_3 tablespace tsp3,
partition part_4 tablespace tsp4,
partition part_5 tablespace tsp5,
partition part_6 tablespace tsp6,
partition part_7 tablespace tsp7,
partition part_8 tablespace tsp8
)
as
select object_id, owner, object_type, object_name, created
from all_objects,
         (select rownum as r from dual connect by rownum <=100);



-- секции таблицы t
select partition_name from user_tab_partitions where table_name = 'T';

PART_1
PART_2
PART_3
PART_4
PART_5
PART_6
PART_7
PART_8


-- табличные пространства в которых расположены секции таблицы t
select tablespace_name from user_segments where segment_type = 'TABLE PARTITION' and segment_name = 'T' ;

TSP1
TSP2
TSP3
TSP4
TSP5
TSP6
TSP7
TSP8

-- сколько выделено и сколько свободного пространства в мегабайтах в этих табличных пространствах
select b.tablespace_name,  mbytes_alloc,  mbytes_free
     from ( select round(sum(bytes)/1024/1024) mbytes_free,  tablespace_name
                  from dba_free_space
                group by tablespace_name ) a,
              ( select round(sum(bytes)/1024/1024) mbytes_alloc, tablespace_name
                  from dba_data_files
                group by tablespace_name ) b
 where a.tablespace_name (+) = b.tablespace_name
 and b.tablespace_name in (select tablespace_name from user_segments
                                        where segment_type = 'TABLE PARTITION' and segment_name = 'T');



TSP4    500    427
TSP7    500    300
TSP1    500    300
TSP2    500    427
TSP3    500    300
TSP6    500    427
TSP5    500    300
TSP8    500    427


Секционированные объекты более удобны в обслуживании.
Например вместо того, чтобы перестроить и реорганизовать всю таблицу:

alter table t move;

Можно перестроить каждую секцию по отдельности:

alter table t move partition part_1;
alter table t move partition part_2;
alter table t move partition part_3;
alter table t move partition part_4;
alter table t move partition part_5;
alter table t move partition part_6;
alter table t move partition part_7;
alter table t move partition part_8;


-- или так:

begin
    for x in ( select partition_name  from user_tab_partitions  where table_name = 'T' )
    loop
         execute immediate
         'alter table t move partition ' ||  x.partition_name;
    end loop;
end;
/



Запросы по отдельным секциям можно делать так:

select * from t partition(part_1);

select count(*) from t partition(part_1);


Используя хеш - секционирование, мы тем самым просим Oracle случайно распределять данные,
надеясь при этом, что распределение между секциями будет равномерным:

Посмотрим какое количество строк попало в каждую секцию:

DECLARE
    CURSOR cur IS
    SELECT table_name, partition_name FROM all_tab_partitions WHERE table_name = 'T';
    v_count number:=0;
BEGIN
    FOR rec IN cur
    LOOP
        execute immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' into v_count;
        dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||v_count||' rows');
    END LOOP;
END;


The Partition PART_1 of table T has 1119500 rows
The Partition PART_2 of table T has 1118400 rows
The Partition PART_3 of table T has 1120800 rows
The Partition PART_4 of table T has 1109900 rows
The Partition PART_5 of table T has 1098900 rows
The Partition PART_6 of table T has 1097300 rows
The Partition PART_7 of table T has 1122100 rows
The Partition PART_8 of table T has 1102400 rows



Секционирование также может повысить доступность данных.
Например если произойдет повреждения диска, который содержал данные одной из секций,
(симитировать можно так alter tablespace  p1 offline; )
то вся база будет доступна и в рабочем состоянии не будет доступна только одна секция.

Причем одну секцию можно восстановить намного быстрее, чем всю таблицу.

В некоторых случаях, при использовании параллельного DML можно повысить производительность при секционировании.
Также при операциях select можно исключать ненужные секции, чтобы избежать полного сканирования всей таблицы.

Русский перевод книги: Oracle Core: Essential Internals for DBAs and Developers

Издательство ДМК Пресс:



http://dmkpress.com/catalog/computer/databases/978-5-97060-169-3/

dba_hist_system_event

Несколько последних снапшотов:
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
where end_interval_time between sysdate - interval '5' hour and sysdate
order by end_interval_time desc


Добавим  DBTIME:

select * from (
select begin_snap,
end_snap,
timestamp begin_timestamp,
inst,
to_char(a/1000000/60, '99990.9999') dbtime_mins
from
(
select
 e.snap_id end_snap,
 s.end_interval_time end_time,
 lag(e.snap_id) over (order by e.instance_number,e.snap_id) begin_snap,
 lag(s.end_interval_time) over (order by e.instance_number,e.snap_id) timestamp,
 s.instance_number inst,
 e.value,
 nvl(value-lag(value) over (order by e.instance_number,e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
 and e.instance_number = s.instance_number
 and to_char(e.instance_number) like nvl('&instance_number',to_char(e.instance_number))
 and stat_name = 'DB time'
)
where  end_time between sysdate - interval '10' hour and sysdate
-- order by begin_snap desc
)


Статистика по событию ожидания:

select event, round(sum(tm_delta_time)/1000000/60,1) delta_time_mint
,round(sum(tm_delta_cpu_time)/1000000/60,1) delta_cpu_mint
,round(sum(tm_delta_db_time)/1000000/60,1) delta_db_mint
from dba_hist_active_sess_history
where snap_id between 2716 and 2720 and
instance_number = 1 and event = 'db file sequential read'
group by event;


select * from (
    select
         WAIT_CLASS ,
         EVENT,
         count(sample_time) as EST_SECS_IN_WAIT
    from v$active_session_history
    where sample_time between sysdate - interval '10' hour and sysdate
    group by WAIT_CLASS,EVENT
    order by count(sample_time) desc
    )
where rownum < 11;


Статистика по sql_id из awr:


select sql_id,
       plan_hash_value,
       sum(execs) execs,
    -- sum(etime) etime,
       sum(etime)/sum(execs) avg_etime,
       sum(cpu_time)/sum(execs) avg_cpu_time,
       sum(lio)/sum(execs) avg_lio,
       sum(pio)/sum(execs) avg_pio
from (
      select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
      nvl(executions_delta,0) execs,
      elapsed_time_delta/1000000 etime,
      (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
       buffer_gets_delta lio,
       disk_reads_delta pio,
       cpu_time_delta/1000000 cpu_time,
       (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
       (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
-- and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5;


История плана запроса по awr:

select ss.snap_id,
       ss.instance_number node,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       nvl(executions_delta,0) execs,
      (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
      (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from  DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '4dqs2k5tynk61'
      and ss.snap_id = S.snap_id
      and ss.instance_number = S.instance_number
      and executions_delta > 0
order by 1, 2, 3;







Статистики по SQL:

SELECT
    ss.snap_id, ss.instance_number node, ss.begin_interval_time, s.sql_id, s.plan_hash_value,
NVL(s.executions_delta,0) execs,
    (s.elapsed_time_delta/DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime,
    (s.buffer_gets_delta/DECODE(NVL(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio
FROM
    dba_hist_sqlstat s, dba_hist_snapshot ss
WHERE
    ss.snap_id = s.snap_id AND
    ss.instance_number = s.instance_number AND
    s.executions_delta > 0 AND
    s.sql_id = '271w1sktujfqk'
ORDER BY 1, 2, 3;


TOP по ASH:

select * from (
    select
         SQL_ID ,
         sum(decode(session_state,'ON CPU',1,0)) as CPU,
         sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
         sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
         sum(decode(session_state,'ON CPU',1,1)) as TOTAL
    from v$active_session_history
    where SQL_ID is not NULL
    group by sql_id
    order by sum(decode(session_state,'ON CPU',1,1))   desc
    )where rownum < 11;


Активность сессий за последний час:

SELECT trunc(sample_time,'MI'),
       sql_id,
       count(sql_id) as TOTAL
FROM v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
AND sql_id in (select sql_id from (
 select
     SQL_ID ,
     sum(decode(session_state,'WAITING',1,1))  as TOTAL_ACTIVITY
from v$active_session_history
WHERE sample_time between sysdate - interval '1' hour and sysdate
group by sql_id
order by sum(decode(session_state,'WAITING',1,1)) desc)
where rownum < 11)
group by trunc(sample_time,'MI'),sql_id
order by trunc(sample_time,'MI') desc;



select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
    (select min(sample_time) sample_time
    from  v$active_session_history ash
    ) start_time,
    (select max(sample_time) sample_time
    from  v$active_session_history
    ) end_time,
    v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;


select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS
from
    (select min(sample_time) sample_time
    from  v$active_session_history ash
    where sample_time between sysdate-1/24 and sysdate) start_time,
    (select max(sample_time) sample_time
    from  v$active_session_history
    where sample_time between sysdate-1/24 and sysdate) end_time,
    v$active_session_history ash
where ash.sample_time between start_time.sample_time and end_time.sample_time
group by end_time.sample_time,start_time.sample_time;


Кумулятивная статистика (с момента старта экземпляра):

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
             
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
             
                he.event_name,
                he.total_waits,
                he.time_waited_micro
FROM 
                dba_hist_system_event he,
                dba_hist_snapshot sn
WHERE           sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                he.snap_id=sn.snap_id;



Средняя статистика за период (snap duration):

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
           
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
           
                bse.event_name,
                (ase.total_waits - bse.total_waits)  total_waits,
                to_char((ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits),'99990.9999') avg_wait
           
FROM
                dba_hist_system_event bse,
                dba_hist_system_event ase,
                dba_hist_snapshot sn
         
WHERE
                sn.end_interval_time between sysdate - interval '3' hour and sysdate and              
                bse.event_name = 'db file sequential read' and
                ase.event_name = bse.event_name and
                ase.snap_id = bse.snap_id +1 and
                ase.instance_number = 1 and
                bse.instance_number = ase.instance_number and
                ase.snap_id = sn.snap_id and
                ase.instance_number = sn.instance_number and
                nvl(ase.total_waits - bse.total_waits,1) > 0
ORDER BY    ase.snap_id;



Или так (с подзапросом):

SELECT
       snap_id,
       snap_time,
       snap_duration,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
FROM
       (

         SELECT
                sn.dbid,
                sn.instance_number inst_id,
                sn.snap_id snap_id,
                sn.begin_interval_time begin_snap,
                sn.end_interval_time end_snap,            
           
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
            
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
            
                bse.event_name event_name,
                bse.wait_class,
                (ase.total_waits_fg - bse.total_waits_fg)  total_waits,
                (round(ase.time_waited_micro_fg/1000000, 2) - round(bse.time_waited_micro_fg/1000000, 2))  time_waited
         FROM
                dba_hist_system_event bse,
                dba_hist_system_event ase,
                dba_hist_snapshot sn
          
         WHERE   sn.end_interval_time between sysdate - interval '1' hour and sysdate and
                 ase.snap_id = sn.snap_id and
                 ase.dbid=sn.dbid and
                 ase.snap_id = bse.snap_id +1 and
                 bse.instance_number = ase.instance_number and
                 ase.instance_number = 1 and
                 ase.event_name = bse.event_name
         )

WHERE
                 nvl(total_waits,1) > 0
       --          event_name = 'db file sequential read' and
       --          snap_id between 748 and 849
             
ORDER BY   inst_id,  snap_id;




Связываем с TIME MODEL:

SELECT
                sn.snap_id,
                sn.dbid,
                sn.instance_number,
                (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                (atm.value - btm.value)  / 1000000 db_time,
            
                extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
            
                -- average active session = db time / snap duration
                (((atm.value - btm.value) / 1000000) /
                (extract (second from sn.end_interval_time - sn.begin_interval_time)
                + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60))  aas,
            
                bse.event_name,
                (ase.total_waits - bse.total_waits) total_waits,
                (ase.time_waited_micro - bse.time_waited_micro) / (ase.total_waits - bse.total_waits) avg_wait
            
FROM
               dba_hist_system_event bse,
               dba_hist_system_event ase,

               --- add time model
               dba_hist_sys_time_model btm,
               dba_hist_sys_time_model atm,
           
               dba_hist_snapshot sn
          
WHERE
               sn.end_interval_time between sysdate - interval '3' hour and sysdate and
               bse.event_name = 'db file sequential read' and
               ase.event_name = bse.event_name and
               ase.snap_id = bse.snap_id + 1 and
               ase.instance_number = 1 and
               bse.instance_number = ase.instance_number and
               ase.snap_id = sn.snap_id and
               ase.instance_number = sn.instance_number and
               nvl(ase.total_waits - bse.total_waits,1) > 0 and
           
               --- add time model
               atm.snap_id = btm.snap_id + 1  and
               atm.dbid = btm.dbid  and
               atm.instance_number = btm.instance_number  and
               atm.stat_id = btm.stat_id  and
               atm.snap_id = sn.snap_id  and
               btm.stat_name = 'DB time'

ORDER BY     ase.snap_id;




Но, существуют и аналитические функции:


SELECT    
                     snap_id,
                     end_interval_time,
                     lead(snap_id, 1) over (order by snap_id desc) previous_snap_id,
                     lag(snap_id, 1) over (order by snap_id desc) next_snap_id
FROM       
                     dba_hist_snapshot
WHERE     
                     end_interval_time > sysdate-1
ORDER BY             end_interval_time;


Используем partition by, если хотим разделить группы на подгруппы:

SELECT    
                     snap_id,
                     end_interval_time,
                     lead(snap_id, 1) over (partition by startup_time order by snap_id desc)  previous_snap_id,
                     lag(snap_id, 1) over (partition by startup_time order by snap_id desc)  next_snap_id,
                     startup_time
FROM       
                     dba_hist_snapshot
WHERE     
                     end_interval_time > sysdate-1
ORDER BY             end_interval_time;


Несколько примеров использования аналитических функций:

select   snaptime  "BeginTime",
            event_name  "Event",
            wtdelta  "Waits",
            todelta   "Timeouts",
            twdelta  "SecsWaited"
from (
 select snap_id,snaptime,event_name,therank,
  (waits-lag(waits,1,0)
        over (partition by event_name order by snap_id)) wtdelta,
  (timeouts-lag(timeouts,1,0)
        over (partition by event_name order by snap_id)) todelta,
  (time_waited-lag(time_waited,1,0)
        over (partition by event_name order by snap_id)) twdelta
 from (
   select s.snap_id,
      to_char(s.begin_interval_time,'DD-MON-RR HH24:MI') snaptime,
          event_name, sum(e.total_waits) waits,
          sum(e.total_timeouts) timeouts,
      sum(e.time_waited_micro)/1000000 time_waited,
          (rank() over (order by s.snap_id)) therank
   from dba_hist_system_event e,
        dba_hist_snapshot  s
   where s.snap_id = e.snap_id
   and s.end_interval_time between sysdate - interval '3' hour and sysdate
   and s.dbid = e.dbid
   and s.instance_number=e.instance_number
   and e.event_name like 'logl%'
   group by s.snap_id,
    to_char(s.begin_interval_time,'DD-MON-RR HH24:MI'),event_name
)
order by snap_id, twdelta desc)
where therank > 1;


Пример использования функции LAG() :
SELECT
       dbid,
       btime,
       round((time_ms_end - time_ms_beg) / nullif(count_end - count_beg,0),1) avg_ms
FROM (
SELECT
       sn.dbid,
       to_char(sn.begin_interval_time,'dd-mm-yyyy hh24:mi:ss')  btime,
       total_waits count_end,
       time_waited_micro / 1000 time_ms_end,
       LAG(se.time_waited_micro / 1000) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) time_ms_beg,
       LAG(se.total_waits) OVER( PARTITION BY se.event_name ORDER BY sn.snap_id) count_beg
FROM
       dba_hist_system_event se,
       dba_hist_snapshot sn
WHERE
       sn.end_interval_time between sysdate - interval '3' hour and sysdate and      
       sn.snap_id=se.snap_id
       and se.event_name in ('log file sync' )
       and  sn.dbid=se.dbid
)
ORDER BY btime;


Связываем с dba_hist_snapshot:

select
       snap_id,
       snap_time,
       snap_duration,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
from
              (select
                          sn.dbid,
                          sn.instance_number inst_id,
                          sn.snap_id,
                          sn.begin_interval_time begin_snap,
                          sn.end_interval_time end_snap,
                          (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                          to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
         
                          extract (second from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
          
                          se.event_name,
                          se.wait_class,
                          se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                          round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                          min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id

             from         dba_hist_system_event se,
                          dba_hist_snapshot sn

             where   sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                          se.instance_number=sn.instance_number
                          and se.snap_id=sn.snap_id
                          and se.dbid=sn.dbid
                 
                  )

 where
             inst_id = 1  and
             snap_id > min_snap_id and
             nvl(total_waits,1) > 0  and
             event_name = 'log file sync'
         
order by inst_id, snap_id;



Связываем с TIME MODEL:

select
       snap_id,
       snap_time,
       snap_duration,
       db_time,
       aas,
       event_name "Event",
       total_waits "Waits",
       time_waited "Time(s)",
       round((time_waited/total_waits)*1000) "Avg wait(ms)",
       substr(wait_class, 1, 15) "Wait Class"
from
              (select
                          sn.dbid,
                          sn.instance_number inst_id,
                          sn.snap_id,
                          sn.begin_interval_time begin_snap,
                          sn.end_interval_time end_snap,
                          (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                          to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                      
                           (tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 db_time,
       
                          extract (second from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                          + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60 snap_duration,
                      
                          -- average active session = db time / snap duration
                           (((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 ) /
                           (extract (second from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                           + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60))  aas,
                                          
                                                                        
                          se.event_name,
                          se.wait_class,
                          se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                          round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                          min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id

             from         dba_hist_system_event se,
                          dba_hist_snapshot sn,
                          dba_hist_sys_time_model tm

             where         sn.end_interval_time between sysdate - interval '3' hour and sysdate and 
                           se.instance_number=sn.instance_number and
                           se.snap_id=sn.snap_id and
                           se.dbid=sn.dbid  and
                       
                            --- add time model
                            tm.snap_id = sn.snap_id  and
                            tm.dbid = sn.dbid  and
                            tm.instance_number = sn.instance_number  and
                            tm.stat_name = 'DB time'
               )

 where
             inst_id = 1  and
             snap_id > min_snap_id and
             nvl(total_waits,1) > 0
             and event_name = 'log file sync'
       
order by inst_id, snap_id;


Вывести не более 5-ти топовых ожиданий, на каждый снапшот:

select *
from (
         select
                inst_id,
                snap_id,
                snap_time,
                snap_duration,
                db_time,
                aas,
                event_name "Event",
                total_waits "Waits",
                time_waited "Time(s)",
                round((time_waited/total_waits)*1000) "Avg wait(ms)",
                substr(wait_class, 1, 15) "Wait Class",
                dense_rank() over (partition by inst_id, snap_id order by time_waited desc) - 1 wait_rank
         from
                       (select
                                   sn.dbid,
                                   sn.instance_number inst_id,
                                   sn.snap_id,
                                   sn.begin_interval_time begin_snap,
                                   sn.end_interval_time end_snap,
                                   (sn.end_interval_time - sn.begin_interval_time)  snap_interval,
                                   to_char (sn.end_interval_time, 'dd/mm/yyyy hh24:mi:ss') snap_time,
                            
                                   to_char(
                                   (tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000, '99990.9999') db_time,
                               
                                   to_char(
                                   extract (second from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                                   + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60, '99990.9999') snap_duration,
                            
                                   -- average active session = db time / snap duration
                                    to_char(
                                    (((tm.value - lag(tm.value, 1, tm.value) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id))  / 1000000 ) /
                                    (extract (second from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (minute from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (hour from sn.end_interval_time - sn.begin_interval_time)
                                    + (extract (day from sn.end_interval_time - sn.begin_interval_time) * 24) ) * 60 ) * 60)), '99990.9999')  aas,
                                                
                                                                              
                                   se.event_name,
                                   se.wait_class,
                                   se.total_waits_fg - lag(se.total_waits_fg, 1, se.total_waits_fg) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) total_waits,
                                   round(se.time_waited_micro_fg/1000000, 2) - lag(round(se.time_waited_micro_fg/1000000, 2), 1, round(se.time_waited_micro_fg/1000000, 2)) over (partition by sn.startup_time, sn.instance_number, se.event_name order by sn.snap_id) time_waited,
                                   min(sn.snap_id) over (partition by sn.startup_time, sn.instance_number, se.event_name) min_snap_id
                                                       
                      from         dba_hist_system_event se,
                                   dba_hist_snapshot sn,
                                   dba_hist_sys_time_model tm
   
                      where         sn.end_interval_time between sysdate - interval '3' hour and sysdate and
                                    se.instance_number=sn.instance_number and
                                    se.snap_id=sn.snap_id and
                                    se.dbid=sn.dbid  and
                             
                                     --- add time model
                                     tm.snap_id = sn.snap_id  and
                                     tm.dbid = sn.dbid  and
                                     tm.instance_number = sn.instance_number  and
                                     tm.stat_name = 'DB time'
                        )
   
          where
                      inst_id = 1  and
                      snap_id > min_snap_id and
                      nvl(total_waits,1) > 0
    )      
    
where  wait_rank <= 5
order by inst_id, snap_id;


Статистика по сессиям:


RDBMS Version: 10g.
Script shows following session statistic values:

    PGA Memory, in MB;
    CPU, used by session;
    Hard Parse, %;
    Physical read bytes, in MB;
    Physical write bytes, in MB;
    Redo size, in MB;
    Received from client, in MB;
    Sent to client, in MB.


 SELECT Logon_time,
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 25 AND v$sesstat.SID = v$session.sid)
            AS "PGA Memory, in MB",
         (SELECT VALUE
            FROM v$sesstat
           WHERE STATISTIC# = 12 AND v$sesstat.SID = v$session.sid)
            AS "CPU, used by session",
         ROUND ( (SELECT VALUE
                    FROM v$sesstat
                   WHERE STATISTIC# = 339 AND v$sesstat.SID = v$session.sid)
                / (SELECT DECODE (VALUE, 0, 1, VALUE)
                     FROM v$sesstat
                    WHERE STATISTIC# = 338 AND v$sesstat.SID = v$session.sid),
                2)
            AS "Hard Parse, %",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 58 AND v$sesstat.SID = v$session.sid)
            AS "Physical read bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 66 AND v$sesstat.SID = v$session.sid)
            AS "Physical write bytes, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 139 AND v$sesstat.SID = v$session.sid)
            AS "Redo size, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 344 AND v$sesstat.SID = v$session.sid)
            AS "Received from client, in MB",
         (SELECT ROUND (VALUE / 1024 / 1024, 2)
            FROM v$sesstat
           WHERE STATISTIC# = 343 AND v$sesstat.SID = v$session.sid)
            AS "Sent to client, in MB",
         SID,
         SERIAL#,
         v$session.STATUS,
         PROGRAM,
         USER#,
         USERNAME,
         COMMAND,
         OWNERID,
         OSUSER,
         PROCESS,
         MACHINE,
         OBJECT_NAME
    FROM    v$session
         LEFT OUTER JOIN
            DBA_OBJECTS
         ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
   WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
         --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;


Oracle 11g:
SELECT Logon_time,
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 35 AND v$sesstat.SID = v$session.sid)
          AS "PGA Memory, in MB",
       (SELECT VALUE
          FROM v$sesstat
         WHERE STATISTIC# = 17 AND v$sesstat.SID = v$session.sid)
          AS "CPU, used by session",
       ROUND ( (SELECT VALUE
                  FROM v$sesstat
                 WHERE STATISTIC# = 584 AND v$sesstat.SID = v$session.sid)
              / (SELECT DECODE (VALUE, 0, 1, VALUE)
                   FROM v$sesstat
                  WHERE STATISTIC# = 583 AND v$sesstat.SID = v$session.sid),
              2)
          AS "Hard Parse, %",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 83 AND v$sesstat.SID = v$session.sid)
          AS "Physical read bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 96 AND v$sesstat.SID = v$session.sid)
          AS "Physical write bytes, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 185 AND v$sesstat.SID = v$session.sid)
          AS "Redo size, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 590 AND v$sesstat.SID = v$session.sid)
          AS "Received from client, in MB",
       (SELECT ROUND (VALUE / 1024 / 1024, 2)
          FROM v$sesstat
         WHERE STATISTIC# = 589 AND v$sesstat.SID = v$session.sid)
          AS "Sent to client, in MB",
       SID,
       SERIAL#,
       v$session.STATUS,
       PROGRAM,
       USER#,
       USERNAME,
       COMMAND,
       OWNERID,
       OSUSER,
       PROCESS,
       MACHINE,
       OBJECT_NAME
  FROM    v$session
       LEFT OUTER JOIN
          DBA_OBJECTS
       ON v$session.ROW_WAIT_OBJ# = dba_objects.object_ID
 WHERE v$session.LOGON_TIME BETWEEN TRUNC (SYSDATE) AND SYSDATE
       --AND v$session.STATUS = 'ACTIVE'
ORDER BY 5 DESC;

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)