Oracle AWR
Jump to navigation
Jump to search
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; /