• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

awr datafile latency histogram

December 28, 2013 by Natik Ameen   /  Oracle Scripts

---------------------------------------------------------------------
-- File Name : awr-datafile-latency-histogram.sql
-- Replace
-- Description : Datafile latency histogram from AWR tables
-- Last Modified: 08-June-2012
---------------------------------------------------------------------
SET LINES 80;
SET PAGES 80;
WITH HIST as (
SELECT
sn.snap_id,
sn.dbid,
to_char(trunc(cast(begin_interval_time as date ))+
(ROUND ((cast(begin_interval_time as date) -
TRUNC (cast(begin_interval_time as date))) * 24) / 24),
'YYYY/MM/DD HH24:MI') btime,
h.event_name,
h.wait_time_milli,
h.wait_count
FROM dba_hist_event_histogram h,
dba_hist_snapshot sn
WHERE
h.instance_number = 2
AND sn.instance_number = 2
AND h.event_name like 'db file seq%'
AND sn.snap_id=h.snap_id
AND sn.dbid=h.dbid
)
SELECT btime,
sum(MILLI_1) MILLI_1, sum(MILLI_2) MILLI_2,
sum(MILLI_4) MILLI_4, sum(MILLI_8) MILLI_8,
sum(MILLI_16) MILLI_16, sum(MILLI_32) MILLI_32,
sum(MILLI_64) MILLI_64, sum(MILLI_128) MILLI_128,
sum(MILLI_256) MILLI_256, sum(MILLI_512) MILLI_512
FROM
(SELECT btime,
case wait_time_milli WHEN 1 THEN WAIT_COUNT ELSE 0 END MILLI_1,
case wait_time_milli WHEN 2 THEN WAIT_COUNT ELSE 0 END MILLI_2,
case wait_time_milli WHEN 4 THEN WAIT_COUNT ELSE 0 END MILLI_4,
case wait_time_milli WHEN 8 THEN WAIT_COUNT ELSE 0 END MILLI_8,
case wait_time_milli WHEN 16 THEN WAIT_COUNT ELSE 0 END MILLI_16,
case wait_time_milli WHEN 32 THEN WAIT_COUNT ELSE 0 END MILLI_32,
case wait_time_milli WHEN 64 THEN WAIT_COUNT ELSE 0 END MILLI_64,
case wait_time_milli WHEN 128 THEN WAIT_COUNT ELSE 0 END MILLI_128,
case wait_time_milli WHEN 256 THEN WAIT_COUNT ELSE 0 END MILLI_256,
case wait_time_milli WHEN 512 THEN WAIT_COUNT ELSE 0 END MILLI_512
FROM
(SELECT a.btime,
a.wait_time_milli,
sum(b.wait_count - a.wait_count) wait_count
FROM hist a, hist b
WHERE a.dbid=b.dbid
AND a.snap_id=b.snap_id-1
AND a.wait_time_milli = b.wait_time_milli
GROUP BY a.btime, a.wait_time_milli
HAVING sum(b.wait_count - a.wait_count) > 0))
GROUP BY btime
ORDER BY 1, 2;
SQL> desc dba_hist_event_histogram
Name Null? Type
----------------------------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
EVENT_ID NOT NULL NUMBER
EVENT_NAME NOT NULL VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME_MILLI NOT NULL NUMBER
WAIT_COUNT NUMBER

Filed Under: Oracle Scripts Tagged With: Oracle Scripts

Leave a Reply Cancel reply

Your email address will not be published.

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials