AWR


Oracle Parameter 
STATISTICS_LEVEL(BASIC, TYPICAL(Default), ALL )

TYPICAL force will collection all statistics for normal tuning,
BASIC level will disable virtually all statistics and all performance tuning advisor and Server-generated Alert system. 
ALL Level will collect extremely detailed statistics on SQL statement execution, If it is necessary for doing advance SQL statement tuning.

  • By default every one hours, they are flushed to disk to AWR + AWR snapshot  for MMON
  • Statistics kept for 8 days by default
  • it posible to mark a couple as baseline to keep indefinitely.
  • Baseline snapshot are not automatic purged.
  • For default AWR needed between 200Mb to 300Mb in SYSAUX
DBMS_WORKLOAD_REPOSITORY for modify retention and frequency.  
  • MODIFY_SNAPHOT_SETTING change de 30 day and half and hour
    CREATE_SNAPHOT forces the snaphot
To check occupants in SYSAUX tablespace 
select sum(SPACE_USAGE_KBYTES)/1024/1024 used_Mb from V$SYSAUX_OCCUPANTS;

 select OCCUPANT_NAME, SCHEMA_NAME, SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;

 To modify retention and frequency
select
   extract( day from snap_interval) *24*60+
   extract( hour from snap_interval) *60+
   extract( minute from snap_interval ) "Snapshot Interval",
   extract( day from retention) *24*60+
   extract( hour from retention) *60+
   extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

 Snapshot Interval Retention Interval
----------------- ------------------
               60              11520
note: (60 seconds * 24 hours)/1 hour = 1140 seconds
      11520 (11520 seconds * 1 day/1440 seconds = 8 days

 execute dbms_workload_repository.modify_snapshot_settings (interval => 60, retention => 43200);

 note:43200 equal 30days

 To take a AWR snapshot
EXEC dbms_workload_repository.create_snapshot;

 To check history AWR sanapshot
select snap_id, snap_level,
to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot
order by 1;

 To run AWR report 
cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba 
awrrpt.sql

To run AWR Report for comparing two points in time

cd /u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin
sqlplus / as sysdba 
awrddrpt.sql

 to create a baseline between two snap  
select snap_id, snap_level,
to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from dba_hist_snapshot 
order by 1;


EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 144,end_snap_id => 145, baseline_name => 'oltp_test_rman');
Posted by