Friday, June 16, 2017

awr

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 prompt
DB NameDB IdInstanceInst numReleaseRACHost
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 prompt
Snap IdSnap 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
prompt prompt prompt

Cpu Utilization

prompt prompt prompt prompt prompt prompt prompt
SERVER CPUDB CPUBACKUP CPU
prompt Comments: prompt
prompt
prompt 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 TIMEDB CPUSQL EXEC TIMES
prompt Comments: prompt
prompt
prompt 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
CPUConcurrencySystemiouserioAdministrativeConfigurationApplicationNetworkCommitSchedulerClusterQueueingOtherCPUConcurrencySystemiouserioAdministrativeConfigurationApplicationNetworkCommitSchedulerClusterQueueingOtherCPUConcurrencySystemiouserioAdministrativeConfigurationApplicationNetworkCommitSchedulerClusterQueueingOtherCPUConcurrencySystemiouserioAdministrativeConfigurationApplicationNetworkCommitSchedulerClusterQueueingOther
prompt Comments: prompt
prompt
prompt 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 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 prompt
prompt
prompt Back to Top prompt prompt

SESSION LOGIC READS AND CONSISTENT GETS

prompt prompt prompt prompt prompt prompt
SESSION LOGIC READ Per SecondCONSISTENT GETS Per Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

PHYSICAL WRITES AND READS

prompt prompt prompt prompt prompt prompt
PHYSICAL READ (MB) Per SecondPHYSICAL WRITE (MB) Per Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt >USER COMMITS

prompt prompt prompt prompt prompt
USER COMMITS Per Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

CONNECTIONS

prompt prompt prompt prompt prompt prompt
PROCESSESSESSIONS
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

REDO SIZE

prompt prompt prompt prompt prompt
REDO LOG SIZE(MB)
prompt Comments: prompt
prompt
prompt 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 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 prompt
prompt
prompt Back to Top prompt prompt

GCS/GES MESSAGES

prompt prompt prompt prompt prompt
GCS/GES messages sent:
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Global Cache Average CR Time

prompt prompt prompt prompt prompt prompt
AverageMaxval
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Global Cache Average Current Get Time

prompt prompt prompt prompt prompt prompt
AverageMaxval
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Buffer Cache hit point

prompt prompt prompt prompt prompt
hit point
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

PGA Cache hit point

prompt prompt prompt prompt prompt
hit point
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Library Hit Point

prompt prompt prompt prompt prompt
hit point
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Latch hit point

prompt prompt prompt prompt prompt
Latch hit point %
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:shared pool

prompt prompt prompt prompt prompt
Latch:shared pool - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:row cache objects

prompt prompt prompt prompt prompt
Latch:row cache objects - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:cache buffers chains

prompt prompt prompt prompt prompt
Latch:cache buffers chains - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:cache buffers lru chain

prompt prompt prompt prompt prompt
Latch:cache buffers lru chain - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:gc element

prompt prompt prompt prompt prompt
Latch:gc element - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Latch:DML lock allocation

prompt prompt prompt prompt prompt
Latch:DML lock allocation - MISSES RATE N/10000
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Parses

prompt prompt prompt prompt prompt
Parses Per Second (count)
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Hard Parse

prompt prompt prompt prompt prompt
Hard Parses Per Second (count)
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Top3 File Average Read Time

prompt prompt prompt prompt prompt
Top3 File Average Read Time MS
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Table fetch continued row

prompt prompt prompt prompt prompt
Table fetch continued row / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Dirty buffers inspected

prompt prompt prompt prompt prompt
Dirty buffers inspected / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Cell physical IO MB eligible for predicate offload

prompt prompt prompt prompt prompt
Cell physical IO MB eligible for predicate offload / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Cell physical IO MB saved by storage index

prompt prompt prompt prompt prompt
Cell physical IO MB saved by storage index / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Cell physical IO interconnect MB returned by smart scan

prompt prompt prompt prompt prompt
Cell physical IO interconnect MB returned by smart scan / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt prompt

Cell IO uncompressed MB

prompt prompt prompt prompt prompt
Cell IO uncompressed MB / Second
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Top5 Wait Event

prompt prompt prompt prompt prompt
Top5 Wait Event Of Whole Period
prompt Comments: prompt
prompt
prompt prompt
prompt
prompt Back to Top prompt prompt

Top 5 Wait Event trends

prompt
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('
EventWaitsTime(s)Avg wait (ms)% DB timeWait 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
Posted by at
Subscribe to: Posts (Atom)