Oracle AWR

verifica parametri AWR

Verificare livello di statistiche (se TYPICAL o ALL allora AWR raccoglie, altrimenti non raccoglie nulla):

SELECT name, value FROM v$parameter WHERE name = 'statistics_level';

Verifica retention e frequenza snapshot:

SELECT dbid,snap_interval,retention FROM dba_hist_wr_control;

potrebbero esserci dati relativi a più DBID, nel qual caso si può usare:

select * from dba_hist_wr_control where dbid=(select dbid from v$database);

Per verificare quando sono stati eseguiti gli ultimi 5 snapshot:

SELECT * FROM (
   SELECT snap_id, 
          TO_CHAR(begin_interval_time, 'DD/MM/YYYY HH24:MI') AS inizio_snap,
          TO_CHAR(end_interval_time, 'DD/MM/YYYY HH24:MI') AS fine_snap,
          error_count
   FROM dba_hist_snapshot
   ORDER BY snap_id DESC
) WHERE rownum <= 5;

Vedi ultimo snapshot per dbid corrente:

 select a.snap_id,a.begin_interval_time from dba_hist_snapshot a where a.dbid=(select dbid from v$database) and snap_id=(select max(snap_id) from dba_hist_snapshot m where a.dbid=m.dbid);

Cambiare parametri

BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
   retention => 11520,  -- Conservazione in minuti (es. 8 giorni = 8 * 24 * 60)
   interval  => 60      -- Frequenza dello snapshot in minuti (es. 1 ora)
 );
END;
/

exec  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>(15*24*60),interval=>60);

Disattivare AWR

Per disattivare l'AWR è necessario impostare il parametro:

alter system set control_management_pack_access=none scope=both sid='*';

Si possono rimuovere tutte le tabelle dell'AWR:

@?/rdbms/admin/catnoawr
alter system flush shared_pool;

Per verificare se ci sono o meno gli oggetti dell'AWR:

select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

Eventualmente, gli oggetti rimasti possono essere rimossi manualmente.

Attivare AWR

cercare sql_id

--
set pagesize 200
set linesize 200
select sql_id,dbms_lob.substr(sql_fulltext, 4000) from v$sql where sql_fulltext like '%&1%';
select sql_id,dbms_lob.substr(sql_text, 4000) from dba_hist_sqltext where sql_text like '%&1%';

cerca oscillazioni elapsed

with sqlid_vv as 
  (select sql_id, min(ELAPSED_TIME_TOTAL) elamin, max(ELAPSED_TIME_TOTAL) elamax from dba_hist_sqlstat group by sql_id)
 select sql_id,elamin,elamax,elamax-elamin diff from sqlid_vv where elamax-elamin > 2000000 order by 4 asc;

Cercare piani di esecuzione storici

--
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&1'));

Cercare sqlid in snapshot

set pagesize 200
set linesize 200
col BEGIN_INTERVAL_TIME for a25
SELECT SS.SNAP_ID,
--     SS.INSTANCE_NUMBER,
    BEGIN_INTERVAL_TIME,
--     SQL_ID,
    PLAN_HASH_VALUE,OPTIMIZER_COST,
    DISK_READS_TOTAL,
    BUFFER_GETS_TOTAL,
--     ROWS_PROCESSED_TOTAL,
    CPU_TIME_TOTAL,
    ELAPSED_TIME_TOTAL,
    IOWAIT_TOTAL,
    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 = '&1'
    AND SS.SNAP_ID = S.SNAP_ID
    AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
    AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;

forzare piano di esecuzione

Questa procedura serve per forzare il piano di esecuzione migliore trovato nello storico AWR. Vanno passati 4 argomenti:

• sql_id
• hash_plan_value
• snap_id iniziale
• snap_id finale

forza_piano.sql

--
-- https://rajiboracle.blogspot.com/2017/05/how-to-load-sql-plan-baseline-from-awr.html
-- sql_id hash_plan_value snap_begin snap_end

exec DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_&1');
exec DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'STS_&1', description => 'sts from awr emilio &1 &2 &3 &4');

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>&3, end_snap=>&4,basic_filter=>'sql_id = ''&1''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_&1', populate_cursor=>cur);
  CLOSE cur;
END;
/

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_&1', 
    basic_filter=>'plan_hash_value = ''&2''',
    sqlset_owner => 'SYS',
    fixed => 'YES',
    enabled => 'YES'
    );
END;
/