Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
===== Das Oracle AWR Repository - Abfrage und Pflege =====
**11g/12c**


Das Oracle AWR Repository dient der Datenbank zum langfristigen Speichern der Datenbank Statistiken und ähnlicher Informationen.

Das Repository liegt dazu im Tablespace SYSAUX.

<note important>Die Verwendung, des für die Datenbank Funktion unbedingt notwendigen Repository, zum Nutzen des Kunden setzt die entsprechende EE Lizenz und wenigstens das Tuning Pack voraus! Alles andere ist illegal!</note> 


Begriff:

  * AWR  - Automatic Workload Repository
  * ASH  - Active Session History 
    * current state of all active sessions
  * ADDM - Automatic Database Diagnostics Monitor 
==== Der SYSAUX Tablespace ====


=== Inhalte des SYSAUX Tablespaces abfragen ===

Neben dem AWR liegen auch weitere Repositories in diesem Tablespace, wie viel Platz diese verbrauchen und wie die Daten verlegt werden können lässt sich über die View **v$sysaux_occupants** abfragen.


=== Größe des Repositories ===

Die Größe des Repository wird auf der einen Seite vom Detail Grad und der Anzahl der zu speichernden Snapshots bestimmt und von der Größe des verfügbaren Speicherplatzes im Tablespace.

Der SYSAUX Tablespace sollte auf seine gewünschte maximale Größe limitiert werden, um unnötiges Wachstum zu vermeiden, da später meist der Tablespace nicht mehr verkleinert werden kann.

Ein sinnvoller Wert liegt zwischen 2GB für Datenbank mit niedrigen Transaktionsvolumen und 8GB für Datenbank hohen Durchsatz und erweiterten Log Level.


=== Einstellungen ===

Über die View DBA_HIST_WR_CONTROL können die aktuellen Einstellungen kontrolliert werden.

<code sql>

col SNAP_INTERVAL format a25
col RETENTION format a25

select DBID
      ,SNAP_INTERVAL
      ,RETENTION
      ,TOPNSQL 
 from DBA_HIST_WR_CONTROL
/

</code>


Die Einstellungen lassen sich anpassen:

<code sql>

select dbid from v$database;

      DBID
----------
 278277283

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( 
              retention => (60*24*30) -- 30 Tage
            , interval  => 30
            , topnsql   => 100
            , dbid => 278277283
   );
END;
/
</code>

  * Retention - Aufbewahrung Zeit in Minuten
  * Interval  - Zeitraum zwischen zwei Snapshots
  * Topsql    - Anzahl der x Top SQL Statements für das Capture
  * Dbid      - ID der Datenbank 


=== Daten aus dem Repository wieder löschen ===

Mit folgender Routine können Snapshots wieder gelöscht werden:
<code sql>

BEGIN                                                               
  dbms_workload_repository.drop_snapshot_range( 
        low_snap_id  => <min_snap_id>
      , high_snap_id => <max_snap_id>);                                         
 END;
 / 

</code> 

==== Daten aus dem Repository extrahieren und wieder laden ====


Daten können nur von einer Datenbank in die andere geladen werden. Ein erneutes Laden der Daten in die GLEICHE Datenbank wird NICHT unterstützt!

**Daten aus dem Repository laden**:
\\
Mit den Skript "awrextr.sql" können Daten aus dem Repository extrahiert werden und mit DataPump exportiert.


Daten auswählen:
<code sql>
sqlplus / as sysdba

SQL>@?/rdbms/admin/awrextr.sql

</code>

  * Datenbank ID auswählen oder mit return die Default id wählen
  * Snap ID's der letzten x Tage anzeigen
  * Start Snap ID anzeigen
  * End Snap ID anzeigen
  * Oracle Datenbank Directory auswählen, in das der Export erfolgen soll
  * Namen des Exports angeben
  * per PL/SQL Aufruf wird ein Datapump Export durchgeführt (dauert je nach Volumen etwas!)


**Daten in einer anderen Datenbank  wieder einlesen:**

<code>

SQL>@?/rdbms/admin/awrload.sql

</code>

  * Oracle Datenbank Directory auswählen, in dem der Export liegt
  * Namen des Exports angeben
  * Staging Schema wie "LOAD_AWR" für die Import Tabellen angeben (wird dazu neu angelegt!)
  * Tablespace für die Import Tabellen angeben
  * Temporary Tablespace angeben
  * Repository Daten werden geladen

Der Versuch die DAten in der gleichen DB wieder einzulesen führt zu einem "ORA-20107: not allowed to move AWR data for local dbid" Fehler.



==== AWR Berichte erstellen ====

mit Hilfe der folgenden Skripte können die Berichte aus dem AWR gelesen werden.


Bericht über die ganze Datenbank:
<code sql>
sqlplus / as sysdba

SQL>@?\rdbms\admin\awrrpt.sql

</code>

Bericht über eine Instance:
<code sql>
sqlplus / as sysdba

SQL>@?\rdbms\admin\awrgrpti.sql

</code>

**Tip**: Der Html Bericht läßt sich in Windows einfach mit "host <name_des_html_berichts>.html" öffnen, da Windows per Default html Dateien sofort im Browser öffnet.


**SQL Berichte **

Für eine SQL Bericht muss die SQL ID  des Statements bekannt sein.

Bericht über SQL Statement:
<code sql>

SQL>@?\rdbms\admin\awrsqrpt.sql

</code>

Bericht über das SQL Statement auf einer bestimmten Instance:
<code sql>

SQL>@?\rdbms\admin\awrsqrpi.sql

</code>




==== Übergelaufenes AWR neu aufbauen ====


Vor dem Löschen unbedingt ein vollständiges Backup der DB erstellen und entsprechend lange Downtime einplanen.


<code sql>

SQL> connect / as sysdba 

SQl> startup force

SQL> @?\rdbms\admin\catnoawr.sql 
SQL> alter system flush shared_pool;

-- check for orphan objects
select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';


SQL> @?\rdbms\admin\catawrtb.sql 

SQL> @?\rdbms\admin\utlrp.sql

SQL> @?\rdbms\admin\execsvrm.sql

SQL> @?\rdbms\admin\utlrp.sql

SQL> startup force

-- Tablespace reorganiseren / verkleinern wenn möglich


-- testen 

SQL> exec dbms_workload_repository.create_snapshot;
</code>

Im Test ließ sich das ohne Anpassung von DB Parameter durchführen, allerdings wird im Support Portal ein etwas anderes Verfahren vorgeschlagen, siehe dazu "How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)" im Metalink.





==== Skript ====

Skript für die Auswertung aktuellste Version unter [[https://orapowershell.codeplex.com/SourceControl/latest#sql/awr.sql|awr.sql]].

<code sql>

SET pagesize 300
SET linesize 250

---
@space_tablespace.sql SYSAUX
--


ttitle left  "AWR Snapshots count" skip 2
column snapshot_count format 999999 heading "Snapshot Count"

select count(*) as snapshot_count from sys.wrm$_snapshot
/

ttitle left  "AWR Snapshots time frame" skip 2
column snap_id format 999999 heading "Snap|Id"
column start_time format a21 heading "Start|time"
column end_time   format a21 heading "End|time"


select  snap_id
      , to_char(begin_interval_time,'dd.mm.yyyy hh24:mi:ss') as start_time
	  , to_char(end_interval_time ,'dd.mm.yyyy hh24:mi:ss')  as end_time
 from sys.wrm$_snapshot 
where   ( 
        snap_id = ( select min (snap_id) from sys.wrm$_snapshot) 
    or  snap_id = ( select max(snap_id) from sys.wrm$_snapshot) 
    )
order by snap_id asc
/


ttitle left  "AWR Usage Overview" skip 2

column occupant_name  format a25
column schema_name    format a18
column move_procedure format a40
column space_usage    format 9G999 heading "Space | Usage (M)"

select   occupant_name
       , round( space_usage_kbytes/1024) as space_usage  
       , schema_name
       , move_procedure
 from  v$sysaux_occupants  
 where space_usage_kbytes > 1
order by 2 desc    
/


DOC 
-------------------------------------------------------------------------------
 to drop some snapshots from the repostitory you can use this command:
 begin                                                               
  dbms_workload_repository.drop_snapshot_range( low_snap_id  => <min_snap_id>
                                              , high_snap_id => <max_snap_id>);                                         
 end;
 / 
-------------------------------------------------------------------------------
#

ttitle off

</code>

==== Fehlermeldung Emergency Flush Messages im Alert log====

Meldung:
<code>
Sun Apr 27 19:00:16 2014
Active Session History (ASH) performed an emergency flush.
</code>

Größe überprüfen mit:
<code sql>
select total_size,awr_flush_emergency_count from v$ash_info;
</code>

Lösung:

Über den DB Parameter "_ASH_SIZE" den eingestellten Wert verdoppeln.


siehe Note: Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log (Doc ID 1385872.1)

 
==== Quellen ====

==Oracle:==

  * http://docs.oracle.com/cd/E11882_01/server.112/e16638/autostat.htm#i27008


==AWR Abfragen:==

  * http://www.nocoug.org/download/2008-08/a-tour-of-the-awr-tables.nocoug-Aug-21-2008.abercrombie.html
  * http://oracleprof.blogspot.de/2011/06/how-to-color-mark-sql-for-awr-snapshots.html


== ASH ==


  * http://de.slideshare.net/jberesni/ash-architecture-and-advanced-usage-rmoug2014-36611678
  * http://de.slideshare.net/jberesni/aas-oaktable-world-2013-jb?related=1

==Allgemein:==

  * http://psoug.org/reference/dbms_wrkld_repos.html
  * http://remidian.com/2009/08/purging-sysaux-tablespace-purging-awr-reports/
  * http://www.jaggy.com/community/message/1241
  * http://gavinsoorma.com/2009/07/exporting-and-importing-awr-snapshot-data/
  * http://www.dba-oracle.com/t_export_import_awr_snapshots.htm