Benutzer-Werkzeuge

Webseiten-Werkzeuge


dba:awr_manual_reporting

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste ÜberarbeitungBeide Seiten der Revision
dba:awr_manual_reporting [2015/10/17 15:27] – [ASH Report aufrufen] gpipperrdba:awr_manual_reporting [2015/10/20 10:59] – [ASH Report aufrufen] gpipperr
Zeile 122: Zeile 122:
  
  
 +<code sql awr_call_ash_report.sql>
 +
 +prompt
 +prompt !!!!You need the Tuning Pack for this feature!!!!
 +prompt
 +
 +
 +-- ===== 
 +-- get the spoolfile name and instance_number + DB ID
 +-- =====
 +set feedback off
 +set heading off
 +set termout off
 +
 +column spool_name_col new_val spool_name
 +column instance_number new_val inst_nr
 +column aktdbid new_val databaseid
 + 
 +SELECT replace(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_ash_report.html','\','_'
 +--' resolve syntax highlight bug FROM my editer .-(
 +   AS SPOOL_NAME_COL
 +  ,SYS_CONTEXT('USERENV','INSTANCE') as instance_number
 + FROM dual
 +/
 +
 +select dbid as aktdbid 
 +  from v$database
 +/
 +
 +set feedback on
 +set heading on
 +set termout on
 +
 +-- ===== 
 +-- ask for the Start and endtime of the report
 +-- =====
 +set verify off
 +
 +SET linesize 120 pagesize 300 recsep off
 +
 +define TIME_FORMAT='dd.mm.yyyy hh24:mi'  
 +
 +column min_start_time format a18 heading "Early Start|Date"
 +column max_start_time format a18 heading "Latest Start|Date"
 +
 +ttitle left  "Overview over the possible timeframe to get an ash report " skip 2
 +
 +select to_char(min(s.sample_time),'&&TIME_FORMAT') as min_start_time
 +    ,  to_char(max(s.sample_time),'&&TIME_FORMAT') as max_start_time
 +  from dba_hist_active_sess_history s
 + where  dbid = &&databaseid
 +   and  instance_number = &&inst_nr
 +/   
 +
 +--
 +-- fix  and  snap_id in (... ) like seelect  min(snap_id), max(snap_id) from dba_hist_snapshot where  dbid = s.dbid  and  instance_number = s.inst_num )
 +--
 +ttitle off
 + 
 +accept l_btime date prompt 'Enter start time (format &&TIME_FORMAT): '
 +accept l_etime date prompt 'Enter end time   (format &&TIME_FORMAT): '
 +
 +
 +-- ===== 
 +-- create the ASH Report
 +-- =====
 +
 +
 +SET linesize 500 pagesize 9000 recsep off
 +set long 64000
 +set feedback off
 +set heading off
 +
 +spool &&SPOOL_NAME
 +
 +select * 
 +  from table(sys.dbms_workload_repository.ash_report_html( &&databaseid
 +                                                         , &&inst_nr
 + , to_date('&&l_btime','&&TIME_FORMAT')
 + , to_date('&&l_etime','&&TIME_FORMAT')
 + )
 + )
 +/
 +
 +spool off
 +set heading on
 +set feedback on
 +
 +prompt ... check the created report  &&SPOOL_NAME
 +
 +host &&SPOOL_NAME
 +
 +set linesize 130 pagesize 300 recsep off
 +
 +</code>
  
 ==== Quellen ==== ==== Quellen ====
"Autor: Gunther Pipperr"
dba/awr_manual_reporting.txt · Zuletzt geändert: 2021/02/18 11:33 von gpipperr