Privilegios necesarios para generar reportes AWR

Resulta bastante común que bajo ciertas situaciones especiales, o en determinados proyectos, haya usuarios no administradores requieran la posibilidad de ejecutar reportes AWR de una base de datos para analizar distintas cuestiones.

Para permitir esto, aplicando el principio de “mínimos privilegios”, podemos construir un role de base de datos con los "grants" necesarios para poder generar los reportes, y asignarlo luego a los usuarios que lo necesiten.

Los privilegios necesarios para generar un reporte son los siguientes:

grant select on sys.v_$database to rol_exec_awr;

grant select on sys.v_$instance to rol_exec_awr;

grant execute on sys.dbms_workload_repository to rol_exec_awr;

grant select on sys.dba_hist_database_instance to rol_exec_awr;

grant select on sys.dba_hist_snapshot to rol_exec_awr;

Veamos un ejemplo de como hacerlo:

Vamos a crear primero un usuario (awrusr) con privilegios de connect y resource para simular el usuario que necesita correr los reportes AWR. 

[oracle@server01 ~]$ sqlplus / as sysdba

SQL> create user awrusr identified by oracle

     default tablespace users

     temporary tablespace temp;

User created.

Oracle Database 12.2: Reportes AWR a nivel PDB

Artículo publicado en Oracle Technology Network (OTN) en español -  febrero de 2018 

Introducción

Desde su aparición en Oracle Database 10g, Automatic Workload Repository (AWR) ha ido evolucionando constantemente con el correr de las versiones.

Oracle 12c introdujo un gran cambio en la arquitectura de la base de datos incorporando el concepto de “multitenant”.  En el primer release de 12c, los reportes de AWR solamente pueden ser generados a nivel “Container Database” (CDB). Esto nos impide, en cierta manera, poder analizar en profundidad el comportamiento de determinadas PDBs puntuales.
La versión 12.2 introduce una notable mejora en AWR,  la capacidad de poder correr snapshots  tanto a nivel CDB como de PDB cuando trabajamos en ambientes “multitenant”.

Esta nueva característica nos permite un diagnóstico más granular de problemas de performance focalizados en una PDB en particular, situación que resulta sumamente interesante y útil en soluciones DBaaS donde se espera que el rol de DBA tienda a ser el de un “pDBA” ( Pluggable Database Administrator), es decir un DBA responsable de la administración de una o varias PDBs en particular, pero que a nivel físico comparten recursos con otras en el mismo CDB.

Gestionando AWR Baselines con DBMS_WORKLOAD_REPOSITORY

Creando una Baseline:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 10,
    end_snap_id   => 100,
    baseline_name => 'AWR First baseline');
END;
/

NOTE: In 11g, there is a newly introduced procedure DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE that specifies a template for how baselines should be created for future time periods:

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => to_date('&start_date_time','&start_date_time_format'),
end_time => to_date('&end_date_time','&end_date_time_format'),
baseline_name => 'MORNING',
template_name => 'MORNING',
expiration => NULL ) ;
END;
/

"expiration => NULL" means that this baseline will be kept forever.

Eliminando AWR baseline:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'AWR First baseline');
END;
/

Gestionando AWR SNAPSHOTS con DBMS_WORKLOAD_REPOSITORY

Como modificar la configuración de AWR SNAPSHOT

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (43200 = 30 Days).
                               -- Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

Creando un SNAPSHOT de forma manual:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/

Eliminando AWR snaps de una rango determinado:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
low_snap_id=>40,
High_snap_id=>80);
END;
/

Ref: How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)

Generación de reportes AWR utilizando scripts SQL

Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user. 
It resides in SYSAUX tablespace. 
By default snapshot are generated once every 60min and maintained for 8 days to ensure the capture of an entire week of performance data (7 days in Oracle 10g). 

An AWR report outputs a series of statistics based on the differences between snapshots that may be used to investigate performance and other issues.

Running a Basic Report

With appropriate licenses for AWR, you may generate an AWR report by executing
the following script and pick the two snapshots you want to use for the sample :

$ORACLE_HOME/rdbms/admin/awrrpt.sql

Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.

You will also be asked for the format of the report (text or html) along with the report name.

Generating Various Types of AWR Reports

Workload Repository Views

Tenemos disponibles las siguientes workload repository views:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

Ref: How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)

Verificando el uso de AWR

Podemos verificar el uso de varias funcionalidades relacionadas con AWR en la vista dba_feature_usage_statistics por ejemplo, si  queremos ver si fueron creados Workload Repository Reports podemos utilizar la siguiente consulta:

column name format a30
SQL> SELECT name,
  detected_usages,
  currently_used,
  TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample
FROM dba_feature_usage_statistics
WHERE name = 'AWR Report' ;

NAME                           DETECTED_USAGES CURRE LAST_SAMPLE
------------------------------ --------------- ----- -----------------
AWR Report                                  23 TRUE  21-SEP-2014:02:22

Ref:  AWR Reporting - Licensing Requirements Clarification (Doc ID 1490798.1)

Recreando el repositorio AWR

A continuación el procedimiento para re-crear el repo de AWR:

1. Disable AWR statistics gathering by setting the statistics level to basic as follows:

Check settings for parameters as follows:
sqlplus /nolog
connect / as sysdba
show parameter cluster_database
show parameter statistics_level
show parameter sga_target

Or save the spfile before modifying:
create pfile='/home/oracle/admin/dbs/init@.ora.20140122' from spfile;
In 10g and 11g , if sga_target is not 0, then in pfile or spfile set the following parameters:
The example below refers to spfile:
alter system set shared_pool_size = 200m scope = spfile;
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100 scope = spfile;
alter system set large_pool_size = 50 scope = spfile;
alter system reset sga_target scope= spfile;
alter system reset memory_target scope= spfile;
alter system reset memory_max_target scope=spfile;
alter system set statistics_level=basic scope=spfile;