Rem awrcrt.sql Rem Rem Copyright (c) 2017 Oracle ACS. All rights reserved. Rem Rem NAME Rem awrcrt.sql Rem Rem DESCRIPTION Rem This script defaults the dbid and instance number to that of the Rem current instance connected-to, then produce Rem the Workload Repository CHART report. Rem VERSION Rem 2.0 Rem MODIFIED (MM/DD/YY) Rem Wang Wenjie 2015-06-12 - Created 0.1 version Rem Wang Wenjie 2016-03-26 - release 1.0 version Rem Wang Wenjie 2016-12-06 - Created 2.0 version Rem Ma Xuefeng 2016-01-10 - Modified, replaced sql to plsql Rem Wang Wenjie 2016-01-16 - Modified, replaced sql to plsql Rem Wang Wenjie 2016-01-18 - Modified major bug set feedback off prompt prompt Current Instance prompt ~~~~~~~~~~~~~~~~ select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; prompt prompt Specify the number of days of snapshots to choose from prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ define days=&0 set linesize 999 select a.snap_id, to_char(a.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') snap_time from dba_hist_snapshot a where a.instance_number=(select b.instance_number from v$instance b) and a.begin_interval_time > sysdate-&days order by 1; /***************************************** --parameter1 begin snap id --parameter2 end snap id --parameter3 split X ( input 80 for thinkpad X230,X250) --parameter4 instance number Author: Date : 2017-01 *****************************************/ set termout on set echo off set heading on prompt Specify the Begin and End Snapshot Ids prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ COLUMN spool_time NEW_VALUE _spool_time NOPRINT SELECT TO_CHAR(SYSDATE,'YYYYMMDDhh24miss') spool_time FROM dual; COLUMN iv NEW_VALUE _iv NOPRINT select trunc(3600*24*(sysdate+snaP_interval-sysdate)) iv from dba_hist_wr_control; COLUMN dbname NEW_VALUE _dbname NOPRINT SELECT name dbname FROM v$database; prompt begin snap id is define bid=&1 prompt end snap id is define eid=&2 prompt time point limit to split X-axis is : (for instance ,using thinkpad x230, better to be 80) define maxx=&3 prompt instance number is define inid=&4 set termout on set echo off set heading on set long 2000000 set pages 0 set linesize 999 set termout off set echo off set feedback off set heading off set verify off set wrap on set trimspool on set serveroutput on size unlimited set escape on COLUMN vp NEW_VALUE _vp NOPRINT SELECT case when &eid-&bid>500 then 6 when &eid-&bid>350 then 4 else 2 end vp FROM dual; --spool awrcrt_&_dbname._&_spool_time..html spool awrcrt_&_dbname._&inid._&bid._&eid..html prompt prompt prompt prompt prompt AWR Chart Report 1.2 prompt prompt prompt prompt prompt

prompt WORKLOAD REPOSITORY CHART report for prompt

prompt

prompt prompt prompt

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
SELECT A.NAME\|\|'	' \|\|A.DBID\|\|'	'\|\|(SELECT B.INSTANCE_NAME\|\|'	'\|\|&inid\|\|'	'\|\|B.VERSION \|\| '	'\|\|(SELECT value FROM V$PARAMETER C WHERE C.NAME ='cluster_database')\|\|'	'\|\|b.HOST_NAME FROM V$INSTANCE B WHERE B.INSTANCE_NUMBER=1) FROM V$DATABASE A; prompt

prompt

prompt prompt prompt prompt

	Snap Id	Snap Time
Begin Snap:	&bid	select nvl((select to_char(a.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot a where a.instance_number=&inid and a.snap_id=&bid), 'minimum snap time') from dual; prompt
End Snap:	&eid	select nvl((select to_char(a.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') from dba_hist_snapshot a where a.instance_number=&inid and a.snap_id=&eid), 'maximum snap time') from dual; prompt

prompt

prompt prompt

prompt Main Report prompt

prompt

CPU Utilization
Time Model : DB TIME DB CPU SQL EXEC TIME
Active session history
SQL execution count
Average SQL execution time
Session logic reads
Physical writes and reads
User commits
Connections
Redo Size
Global cache transformation
Global cache lost
GCS/GES messages
Global Cache Average CR Time
Global Cache Average Current Get Time
Buffer Cache Hit POINT
PGA Cache Hit POINT
Library Hit POINT
Latch Hit POINT
Latch:shared pool
Latch:row cache objects
Latch:cache buffers chains
Latch:cache buffers lru chain
Latch:gc element
Latch:DML lock allocation
Parse count
Hard parse count
Slowest File Average Read Wait time
Table fetch continued row
Dirty buffers inspected
Cell physical IO bytes eligible for predicate offload
Cell physical IO bytes saved by storage index
Cell physical IO interconnect bytes returned by smart scan
Cell IO uncompressed bytes
Top 5 Wait Event
Top 5 Wait Event trends

prompt prompt

Cpu Utilization

prompt prompt prompt prompt prompt prompt prompt

SERVER CPU

DB CPU

BACKUP CPU

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

Time model: DB TIME , DB CPU TIME, SQL EXECUTION TIME

prompt prompt prompt prompt prompt prompt prompt

DB TIME

DB CPU

SQL EXEC TIMES

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

Active Session History

prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt prompt

CPU

Concurrency

Systemio

userio

Administrative

Configuration

Application

Network

Commit

Scheduler

Cluster

Queueing

Other

CPU

Concurrency

Systemio

userio

Administrative

Configuration

Application

Network

Commit

Scheduler

Cluster

Queueing

Other

CPU

Concurrency

Systemio

userio

Administrative

Configuration

Application

Network

Commit

Scheduler

Cluster

Queueing

Other

CPU

Concurrency

Systemio

userio

Administrative

Configuration

Application

Network

Commit

Scheduler

Cluster

Queueing

Other

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt prompt

Workload: SQL EXECUTION COUNT

prompt prompt prompt prompt prompt

SQL EXECUTION COUNT Per Second

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

AVERAGE SQL EXECUTION TIME

prompt prompt prompt prompt prompt

AVG SQL EXECUTION TIME

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

SESSION LOGIC READS AND CONSISTENT GETS

prompt prompt prompt prompt prompt prompt

SESSION LOGIC READ Per Second

CONSISTENT GETS Per Second

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

PHYSICAL WRITES AND READS

prompt prompt prompt prompt prompt prompt

PHYSICAL READ (MB) Per Second

PHYSICAL WRITE (MB) Per Second

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt >USER COMMITS

prompt prompt prompt prompt prompt

USER COMMITS Per Second

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

CONNECTIONS

prompt prompt prompt prompt prompt prompt

PROCESSES

SESSIONS

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

REDO SIZE

prompt prompt prompt prompt prompt

REDO LOG SIZE(MB)

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

GLOBAL CACHE TRANSFORMATION

prompt prompt prompt prompt prompt

Global Cache blocks received KB / second

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

GLOBAL CACHE LOST

prompt prompt prompt prompt prompt

Global Cache blocks lost

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

prompt prompt prompt prompt prompt

Top5 Wait Event Of Whole Period

prompt Comments: prompt

prompt

prompt Back to Top prompt prompt

Top 5 Wait Event trends

prompt

declare vevent varchar2(100); vtime number; vavgtime number; vpctwt number; vwaits number; vwaitclass varchar2(100); vbid number ; veid number ; vinid number:=&inid; startid number:=&bid; endid number:=&eid; vstarttime varchar2(200); vendtime varchar2(200); cursor c1 is SELECT EVENT, WAITS, trunc(TIME,2), trunc(DECODE(WAITS, NULL, TO_NUMBER(NULL), 0, TO_NUMBER(NULL), TIME / WAITS * 1000),2) AVGWT, trunc(PCTWTT,2) , WAIT_CLASS FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS FROM (SELECT E.EVENT_NAME EVENT, E.TOTAL_WAITS_FG - NVL(B.TOTAL_WAITS_FG, 0) WAITS, (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) / 1000000 TIME, 100 * (E.TIME_WAITED_MICRO_FG - NVL(B.TIME_WAITED_MICRO_FG, 0)) / ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = veid AND e.INSTANCE_NUMBER = vinid AND e.STAT_NAME = 'DB time') - (SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = vbid AND b.INSTANCE_NUMBER = vinid AND b.STAT_NAME = 'DB time')) PCTWTT, E.WAIT_CLASS WAIT_CLASS FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E WHERE B.SNAP_ID(+) = vbid AND E.SNAP_ID = veid AND B.INSTANCE_NUMBER(+) = vinid AND E.INSTANCE_NUMBER = vinid AND B.EVENT_ID(+) = E.EVENT_ID AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0) AND E.WAIT_CLASS != 'Idle' UNION ALL SELECT 'CPU time' EVENT, TO_NUMBER(NULL) WAITS, ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = veid AND e.INSTANCE_NUMBER = vinid AND e.STAT_NAME = 'DB CPU') - (SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = vbid AND b.INSTANCE_NUMBER = vinid AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME, 100 * ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = veid AND e.INSTANCE_NUMBER = vinid AND e.STAT_NAME = 'DB CPU') - (SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = vbid AND b.INSTANCE_NUMBER = vinid AND b.STAT_NAME = 'DB CPU')) / ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = veid AND e.INSTANCE_NUMBER = vinid AND e.STAT_NAME = 'DB time') - (SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = vbid AND b.INSTANCE_NUMBER = vinid AND b.STAT_NAME = 'DB time')) PCTWTT, NULL WAIT_CLASS from dual WHERE ((SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL e WHERE e.SNAP_ID = veid AND e.INSTANCE_NUMBER = vinid AND e.STAT_NAME = 'DB CPU') - (SELECT sum(value) FROM DBA_HIST_SYS_TIME_MODEL b WHERE b.SNAP_ID = vbid AND b.INSTANCE_NUMBER = vinid AND b.STAT_NAME = 'DB CPU')) > 0) ORDER BY TIME DESC, WAITS DESC) WHERE ROWNUM <= 5; begin for i in startid..endid-1 loop vbid:=i; veid:=i+1; select to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi') into vstarttime from dba_hist_snapshot a where snap_id=vbid and instance_number=&inid; select to_char(a.end_interval_time,'yyyy-mm-dd hh24:mi') into vendtime from dba_hist_snapshot a where snap_id=veid and instance_number=&inid; dbms_output.put_line(''); dbms_output.put_line(''); open c1; loop fetch c1 into vevent,vwaits,vtime,vavgtime,vpctwt,vwaitclass; exit when c1%notfound; dbms_output.put_line( '' ); dbms_output.put_line('' ); dbms_output.put_line('' ); dbms_output.put_line('' ); dbms_output.put_line('' ); dbms_output.put_line('' ); dbms_output.put_line('' ); dbms_output.put_line('' ); end loop; close c1; dbms_output.put_line('

'\|\|vstarttime\|\|' to '\|\|vendtime); dbms_output.put_line('
Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
'\|\|vevent\|\|'	'\|\|vwaits\|\|'	'\|\|vtime\|\|'	'\|\|vavgtime\|\|'	'\|\|vpctwt\|\|'	'\|\|vwaitclass\|\|'

'); end loop; end; / prompt

prompt

prompt Back to Top prompt prompt prompt prompt

prompt

prompt The License of Awr Chart prompt
prompt Permission is hereby granted by Author, free of charge, to any person abotaining a copy of this software, prompt
prompt to deal in the software without restriction, including without limitation the rights to use,copy or distribute . prompt
prompt The copy right of Chart javascript belongs to its author Nick Downie (http://www.nickdownie.com/) . This js is an open source project which prompt
prompt is under MIT license. prompt
prompt Author : prompt
Wang,Wenjie prompt
Ma,Xuefeng From prompt

prompt Oracle Advanced Customer Support prompt

prompt
prompt Technical support: Send email to WangWenjie - valen.wang@oracle.com prompt
prompt Version : 1.2 prompt
prompt Date : 2016-10 prompt prompt spool off set termout on !cat awrcrt_&_dbname._&inid._&bid._&eid..html|grep ORA-; prompt report wrote to awrcrt_&_dbname._&inid._&bid._&eid..html

'\|\|vstarttime\|\|' to '\|\|vendtime); dbms_output.put_line('
Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
'\|\|vevent\|\|'	'\|\|vwaits\|\|'	'\|\|vtime\|\|'	'\|\|vavgtime\|\|'	'\|\|vpctwt\|\|'	'\|\|vwaitclass\|\|'

Netopeak

Friday, June 16, 2017

awr

prompt WORKLOAD REPOSITORY CHART report for prompt

prompt Main Report prompt

Cpu Utilization

Time model: DB TIME , DB CPU TIME, SQL EXECUTION TIME

Active Session History

Workload: SQL EXECUTION COUNT

AVERAGE SQL EXECUTION TIME

SESSION LOGIC READS AND CONSISTENT GETS

PHYSICAL WRITES AND READS

CONNECTIONS

REDO SIZE

GLOBAL CACHE TRANSFORMATION

GLOBAL CACHE LOST

GCS/GES MESSAGES

Global Cache Average CR Time

Global Cache Average Current Get Time

Buffer Cache hit point

PGA Cache hit point

Library Hit Point

Latch hit point

Latch:shared pool

Latch:row cache objects

Latch:cache buffers chains

Latch:cache buffers lru chain

Latch:gc element

Latch:DML lock allocation

Parses

Hard Parse

Top3 File Average Read Time

Table fetch continued row

Dirty buffers inspected

Cell physical IO MB eligible for predicate offload

Cell physical IO MB saved by storage index

Cell physical IO interconnect MB returned by smart scan

Cell IO uncompressed MB

Top5 Wait Event

Top 5 Wait Event trends

prompt Oracle Advanced Customer Support prompt

Labels

weithenn

Blog Archive

About Me