DEVOPS - How to find out processes in 'D' state of uninterruptible sleep (High I/O Wait)

DEVOPS - How to find out processes in 'D' state of uninterruptible sleep (High I/O Wait)

Performance Tuning Vineet Sachdeva   May 06, 2020

IN this blog I am going to write on how to find out processes state and specially when there is high I/O wait on the system. 

The key is to list down the process which are in 'D' or 'S' state (primarily 'D').

Run below command to list processes in 'D' state. Replace '^D' with '^S' to get list of processes in S State.

Once you get the process you can do action on it 

 for x in `seq 1 1 10`; do ps -eo state,pid,cmd | grep "^D"; echo "----"; sleep 5; done

Linux: OSWatcher

Linux: OSWatcher

Reporting Vineet Sachdeva   January 28, 2019

OSWatcher is one of the best utilities provided by Oracle (Free of cost) and runs on almost all Operating Systems (Unix based only)


To Install OSW

Step 1. Download OSWatcher (Doc ID 301137.1)

Step 2. Untar 
# Change version as per latest available. 

$ tar -xf /tmp/oswbb812.tar

Step 3.  Start OSW

$ cd /root/osw/oswbb
$ ./startOSWbb.sh 60 10 gzip


Step 4. Stop OSW
$ cd /root/osw/oswbb
$ ./stopOSWbb.sh 


Other Details
1. CMD OPTIONS

• First variable is the interval in seconds between 2 samples
• Second Variable is total hours for which data is kept
• Third Variable is the utility to zip to compress the files generated (acceps zip,gzip, None)
• Fourth Variable is the path (optional) where to put the filess, when left blank it is the archive directory where the files are generated and kept. 
• Fourth variable cannot be provided without 3rd 

I personally recommend 60 seconds interval with 30 days of data kept on the system for you to analyze.

2. Scheduling
To Schedule you can just schedule (cron) the OSWatcher to run every 10 minutes or so.
The key thing is OSWatcher does not starts if an instance of it is running. This makes it by default highly available, or if you want to be advanced you can create it as a service with help of sysadmin team or yourself.


Latest Downloads and Other OSWatcher related information available at - 

OSWatcher (Includes: [Video]) (Doc ID 301137.1)

Performance - Sar - How to Plot Excel Chart (Complete Month)

Performance - Sar - How to Plot Excel Chart (Complete Month)

scripts Vineet Sachdeva   January 15, 2019

In this blog I am going to talk about how to excel plot a chart using sar data in an easy 2 step process.

Step 1 - Extract Required Sar data in semicolon separated format

Below is a script which will help you extract data in the format you wish to.
The script is quite clear for the pros, however here is what I am doing

1. Creating a temporary file name variable for data.
2. Creating a CMD variable which is basically sadf comand by sysstat package, this command outputs sar data into semicolon seperated format.
3. Creating OPTION variable - this is basically what data you want to extract from the files. All the options are valid which are used in the "sar command" (so in place of -q you can give, -R, -r, -S, -H, -p etc etc). Do not remove the double '--'
4. Print the header into the file
5. Run the command for all files 


tempfile=sadf_`date "+%Y_%m_%d_%H_%M_%S"`.out
CMD="sadf -dt "
OPTION="-- -q" #This Option is sar option for Stat
#Print header into file
$CMD /var/log/sa/sa21 $OPTION | head -1 | awk '{print $2}' > ${tempfile}

for f in `ls -rt /var/log/sa/sa[0-9][0-9]` ; 
do 
$CMD $f $OPTION | sed -e '/LINUX-RESTART/d' -e '/hostname/d' >> ${tempfile} ;
done

(Note - the sed command deletes the linux restart entries and the headers which follow in the sar file after the restart.)
Now once you run this, you will have 1 month data into a single file with a semicolon format. 

Step 2 - Download the file and copy paste to excel.
The Second step is your excel skills, you copy the data to excel file separate the data using delimiter and plot your charts. 

1. Paste Data into excel and separate using semicolon

2. The data is all yours now and you should use your excel skills (hint - pivot chart or normal chart) to do this.

Here is an example 

Select The data And Create Pivot Chart

Fill in the Chart Details (Extreme Right)

Good luck working.

Oracle Database: Oracle Enterprise Linux : Page Allocation Failure : Performance/Kernel Tuning / - System Unresponsive

Oracle Database: Oracle Enterprise Linux : Page Allocation Failure : Performance/Kernel Tuning / - System Unresponsive

PT Vineet Sachdeva   July 26, 2018

We recently had issue with one of our physical servers. 
It will become unresponsive and hang in a lot of situations. 

Oracle Linux Server release 7.3
NAME="Oracle Linux Server"

VERSION="7.3"

Linux pwbdd-fe-1 4.1.12-61.1.18.el7uek.x86_64 #
256GB of RAM and 16 CPU (2socket * 8 cores)

Looked into sar logs and found out the memory usage was around 99.7%.

We found out Page allocations error  in the /var/log/messages

kworker/u66:0: page allocation failure: order:1, mode:0x204020

CPU: 8 PID: 1579 Comm: kworker/u66:0 Tainted: G           OE   4.1.12-61.1.18.el7uek.x86_64 #2

There was no entry in Oracle Database log or any trace file, so database had to do minimal with it. 

We finally fixed it with below kernel settings.

•  vm.min_free_kbytes = 1048576 
•  vm.swappiness = 60
•  vm.dirty_ratio = 20

I have to admit that I took help of an ODA with Linux 7 and similar amount of RAM as well


Referred Links and 
https://utcc.utoronto.ca/~cks/space/blog/linux/DecodingPageAllocFailures
http://tech.donghao.org/2016/03/25/avoid-page-allocation-failure-for-linux-kernel/
https://unix.stackexchange.com/questions/128539/what-would-happen-if-the-amount-of-free-memory-vm-min-free-kbytes-was-too-low
https://stackoverflow.com/questions/21374491/vm-min-free-kbytes-why-keep-minimum-reserved-memory

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/performance_tuning_guide/s-memory-tunables
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/performance_tuning_guide/sect-red_hat_enterprise_linux-performance_tuning_guide-configuration_tools-configuring_system_memory_capacity

Oracle Support Docs

ODA: Node Inaccessible Due to OS "page allocation failure: order:1" (Doc ID 1642258.1)

[Linux OS] System Hung with Large Numbers of Page Allocation Failures with "order:5" on Exadata Environments (Doc ID 1546861.1)
Linux: Out-of-Memory (OOM) Killer (Doc ID 452000.1)
Top 5 Issues That Cause Node Reboots or Evictions or Unexpected Recycle of CRS (Doc ID 1367153.1)
RAC and Oracle Clusterware Best Practices and Starter Kit (Linux) (Doc ID 811306.1)
Linux Kernel: The SLAB Allocator (Doc ID 434351.1)
[Linux OS] System Hung with 'page allocation failure' (Doc ID 2059297.1)

Oracle Database - Log Switches Per hour

Oracle Database - Log Switches Per hour

scripts Anonymous   July 28, 2017

This script is used to find out log switches per hour in Oracle Database

ROWNUM < 31  tells to display data for last 30 days


SET PAGESIZE 90
SET LINESIZE 150
set heading on
column "00:00" format 9999
column "01:00" format 9999
column "02:00" format 9999
column "03:00" format 9999
column "04:00" format 9999
column "05:00" format 9999
column "06:00" format 9999
column "07:00" format 9999
column "08:00" format 9999
column "09:00" format 9999
column "10:00" format 9999
column "11:00" format 9999
column "12:00" format 9999
column "13:00" format 9999
column "14:00" format 9999
column "15:00" format 9999
column "16:00" format 9999
column "17:00" format 9999
column "18:00" format 9999
column "19:00" format 9999
column "20:00" format 9999
column "21:00" format 9999
column "22:00" format 9999
column "23:00" format 9999
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
, SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
       WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
          GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
  ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
  ) WHERE ROWNUM <31;

Oracle Database - Mining Active Session History (ASH) for Plan/SQL Execution Wait Events

Oracle Database - Mining Active Session History (ASH) for Plan/SQL Execution Wait Events

scripts Anonymous   May 29, 2017

In this blog I am going to discuss about mining ASH for wait events for a specific sql session or specific sql or specific plan hash value execution wait events.

This can be really powerful in cases like - 

1. when sql_monitoring report is no longer available,

2. or you want to tune a particular plan and you want to know at what plan_line it is taking more time

3. you want to tune a plan and want to know at what line it is taking time with what kind of wait event.

There are many other use cases but the above are summary of what can be done. 

Now I will quickly jump onto the sql part.

First SQL is to find out the Time taken by SQL on each plan line per the options

set lines 500 pages 500
col object_name for a25

SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(10)
FROM dba_hist_active_sess_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 446
AND a.session_serial# = 26701
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;

 

There is a sum 10 option because dba_hist_active_sess_history has a sample size of 10 seconds

 Now Lets see to get this output from V$active_session_history, remember the sample size of V$ASH is 1 second.

SELECT plan_hash_value,
id line,
operation,
options,
object_name,
sum(1)
FROM V$active_session_history a,
dba_hist_sql_plan b
WHERE a.sql_plan_hash_value = b.plan_hash_value
AND a.sql_id = b.sql_id
AND a.sql_plan_line_id = b.id
AND a.sql_id = 'ah9xc371174p7'
AND a.session_id = 6769
AND a.session_serial# = 38229
GROUP BY plan_hash_value, id, operation, options, object_name
ORDER by 1,2;

 

 I have truncated the object names for obvious reasons in both. 

The key thing to notice here is that you have the time for each plan_line and the type of operation. 

Now the next thing is to find out within the operation / plan line what was the actual wait event i.e was it really I/O or CPU or direct path read etc. 

 

select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(10)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from dba_hist_active_sess_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 446
AND ss.session_serial# = 26701
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;

SQL_ID        SQL_PLAN_LINE_ID TIME_CATEGORY                SECONDS   PCT_WAIT

------------- ---------------- ------------------------- ---------- ----------

ah9xc371174p7                5 db file sequential read           20        .22

ah9xc371174p7                5 db file parallel read             30        .33

ah9xc371174p7                1 ON CPU                            50        .55

ah9xc371174p7                2 ON CPU                            60        .65

ah9xc371174p7                5 ON CPU                            80        .87

ah9xc371174p7                6 ON CPU                          1130      12.32

ah9xc371174p7                6 db file parallel read           2760       30.1

ah9xc371174p7                6 db file sequential read         5040      54.96

 (In case output above is not formatted properly  due to font / browser limitations - see below output) 

Now lets see this output from V$Active Session History. 

select ss.sql_id,
ss.sql_plan_line_id,
case SESSION_STATE when 'WAITING' then event
else SESSION_STATE end TIME_CATEGORY,
(SUM(1)) seconds,
round(RATIO_TO_REPORT(count(*)) OVER () * 100 ,2) pct_wait
from  V$active_session_history ss
WHERE ss.sql_id = 'ah9xc371174p7'
AND ss.session_id = 6769
AND ss.session_serial# = 38229
group by ss.sql_id,ss.sql_plan_line_id,session_state,event
order by seconds;  

 The key thing here is to find on what step the sql took more time and take an informed decision if we want to tune that step / access path or wait_event. 

You can apply filters in V$ASH / DB_HIST_ASH to find details for different plans as a summary without a specific session or set of sessions, totally up to you. 

Oracle Database - List of Indexes / Tables used in SQL Query / PL-SQL Procedure

Oracle Database - List of Indexes / Tables used in SQL Query / PL-SQL Procedure

scripts Anonymous   May 19, 2017

Recently I was tasked with taking out list of all the indexes which are used by long running queries inside PL/SQL code. 

I had designed query in such a way to select distinct sql_ids from active session history by providing top_level_sql_id of the PL/SQL code. 

Finally I joined DBA_OBJECTS AND DBA_HIST_SQL_PLAN with clause of object type as Index. 

This listed me all the indexes used.



select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'INDEX' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,2
;

This is list for all available plans in the system. If you want to narrow down to a particular plan then 
it is a good idea to add a where statement with plan hash value 
' AND DP.PLAN_HASH_VALUE = ########## '


List of Tables -

select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'TABLE' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,2
;



List of Tables and Indexes both 

select DISTINCT DO.OWNER , DO.OBJECT_NAME, DO.OBJECT_TYPE
FROM
DBA_OBJECTS DO, DBA_HIST_SQL_PLAN DP
WHERE DO.OBJECT_ID = DP.OBJECT# AND
DO.OBJECT_TYPE = 'TABLE' AND
DP.SQL_ID IN (select distinct  sql_id from V$active_session_history where top_level_sql_id in ('6bjgua013q2jj','8fd1c17n8vpxh','cn469rdpw22rz') )
ORDER BY 1,3,2
;


 

Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction

Oracle Database - Historical Events / Wait Times / Statistics - AWR Data Mining & Extraction

scripts Anonymous   April 24, 2017

In this blog I am going to discuss on how to mine and extract data from AWR Tables. 

In my previous blog I have discussed how to extract event / statistics currently (from V$views)

 Oracle Database - Current Wait Events / Metrics Statistics
 I will be discussing 3 particular AWR Views / Tables for extraction of data. 


DBA_HIST_SYSMETRIC_SUMMARY
  This view contains historical information for sysmetric V$ view. It is useful in getting important data such as Buffer Cache Hit Ratio.  This view is not cumulative, it is helpful in finding min/max/average values over period of time as a function of snap_id / time.

DBA_HIST_SYSTEM_EVENT
  This view is historical view to V$System_Event, the event statistics in V$system_event are snapped and kept in this view as per the AWR snapshot period. This event since is a snap of V$system_event is obviously cumulative, so to make sense or get the deltas, values should be subtracted from previous value



DBA_HIST_EVENT_HISTOGRAM
  This view is a snap of V$event_histogram, this view is again cumulative, so to find values for a one snap period period, values should be subtracted from previous snaps value. 


DBA_HIST_FILESTATXS

  This view is historical view to V$filestat, the event statistics in V$filestat are snapped and kept in the view. this view is also cumulative. 


Below are queries to get data from these views. 
I have written the queries in a such a way to get the # of days you want to look data for, the # of days can be like 1/24 signifying 1 hour. 

If the values of any of the historical stats are negative (in case of cumulative stats), that means instance was restarted before that snap.



Find out Buffer Cache Hit Ratio over Period of time 

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name'
ORDER BY SNAP_ID;

Find out Log file Sync Time over Period of time 

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40


WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID
 )
ORDER BY SNAP_ID;

Find out log file sync histogram details

SQL> break on snap_id on BEGIN_TIME skip 1
SQL> col BEGIN_TIME format a30
SQL> var snapid number;
SQL> EXEC SELECT MIN(SNAP_ID)  INTO :SNAPID FROM DBA_HIST_SNAPSHOT WHERE BEGIN_INTERVAL_TIME > sysdate - &days;
Enter value for days: 1/12
 
PL/SQL procedure successfully completed.
SQL> r
    with hist as  (
      select
           sn.snap_id,
           begin_interval_time 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 = &inst_num
        and sn.instance_number = h.instance_number
        and h.event_name =  '&event_name'
        and sn.snap_id=h.snap_id
        and sn.snap_id > :snapid
      )
   select  a.snap_id, a.btime BEGIN_TIME,
           a.wait_time_milli,
           b.wait_count - a.wait_count WAIT_COUNT
   from hist a,
        hist b
   where
      a.snap_id=b.snap_id-1
     and a.wait_time_milli = b.wait_time_milli
   order by a.snap_id, WAIT_TIME_MILLI

Enter value for inst_num: 1
old  11:          h.instance_number = &inst_num
new  11:          h.instance_number = 1
Enter value for event_name: log file sync
old  13:      and h.event_name =  '&event_name'
new  13:      and h.event_name =  'log file sync'

   SNAP_ID BEGIN_TIME                     WAIT_TIME_MILLI WAIT_COUNT
---------- ------------------------------ --------------- ----------
    278182 25-APR-17 06.00.47.887 AM                    1      26272
                                                        2       9054
                                                        4       6352
                                                        8       2734
                                                       16        827
                                                       32        273
                                                       64         27
                                                      128          1
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278183 25-APR-17 06.15.50.075 AM                    1      28470
                                                        2       9173
                                                        4       5343
                                                        8       1506
                                                       16        375
                                                       32        215
                                                       64         34
                                                      128          0
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278184 25-APR-17 06.30.51.845 AM                    1      28746
                                                        2       7457
                                                        4       3772
                                                        8       1121
                                                       16        369
                                                       32        214
                                                       64         26
                                                      128          2
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

    278185 25-APR-17 06.45.53.497 AM                    1      11102
                                                        2      10205
                                                        4      13808
                                                        8      10206
                                                       16       3895
                                                       32       4013
                                                       64       4255
                                                      128       2664
                                                      256        796
                                                      512       1679
                                                     1024       2481
                                                     2048       3255
                                                     4096        994
                                                     8192         36
                                                    16384          0

    278186 25-APR-17 07.00.55.247 AM                    1      17512
                                                        2      11811
                                                        4      10061
                                                        8       4623
                                                       16       1204
                                                       32        455
                                                       64         89
                                                      128         19
                                                      256          0
                                                      512          0
                                                     1024          0
                                                     2048          0
                                                     4096          0
                                                     8192          0
                                                    16384          0

Find out File Statistics over Period of time


set lines 500 pages 500
col BEGIN_TIME for a30
break on FILE# SKIP 1

 WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT FILE#, SNAP_ID, BEGIN_TIME,
round(WRITETM * 10 / PHYWRTS,2) AS AVG_WRT_MS,
round(READTIM * 10 /PHYRDS,2) AS AVG_RD_MS FROM
(
SELECT FILE#,
 DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME,
 PHYWRTS - LAG(PHYWRTS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYWRTS,
 WRITETIM - LAG(WRITETIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) WRITETM,
 PHYRDS - LAG(PHYRDS, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) PHYRDS,
 READTIM - LAG(READTIM, 1, 0) OVER (PARTITION BY FILE# ORDER BY DHS.SNAP_ID) READTIM
 FROM DBA_HIST_FILESTATXS DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
 WHERE FILE# IN (&file_id) AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID
)
ORDER BY FILE#, SNAP_ID;  

  Query Variations for RAC and specific Instance

DBA_HIST_SYSMETRIC Summary

Specific Instance

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
DHSYSM.INSTANCE_NUMBER = &instance_number
ORDER BY SNAP_ID;

RAC All Instances

set lines 500 pages 500
col BEGIN_INTERVAL_TIME for a30
col METRIC_NAME for a30
col METRIC_UNIT for a30

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
DHSYSM.INSTANCE_NUMBER, DHS.SNAP_ID, DHS.BEGIN_INTERVAL_TIME,
DHSYSM.METRIC_NAME, round(DHSYSM.MAXVAL,2) MAX_VAL , round(DHSYSM.MINVAL,2) MIN_VAL, round(DHSYSM.AVERAGE,2) AVG_VAL , METRIC_UNIT
FROM DBA_HIST_SNAPSHOT DHS, DBA_HIST_SYSMETRIC_SUMMARY DHSYSM , MN_SNAP_ID MNS
WHERE
DHS.SNAP_ID = DHSYSM.SNAP_ID AND
DHS.SNAP_ID > MNS.SNAP_ID AND
METRIC_NAME = '&Metric_Name' AND
DHSYSM.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
ORDER BY DHSYSM.INSTANCE_NUMBER, SNAP_ID;

DBA_HIST_SYSTEM_EVENT

Specific Instance

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
SNAP_ID, BEGIN_TIME, EVENT_NAME , TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC, round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID AND
  DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER AND
  DHSE.INSTANCE_NUMBER = &instance_number
   )
ORDER BY SNAP_ID;

RAC All Instances

set lines 500 pages 500
col BEGIN_TIME for a30
col EVENT_NAME for a40

WITH MN_SNAP_ID AS
(
SELECT MIN(SNAP_ID) SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME > sysdate - &days
)
SELECT
INSTANCE_NUMBER, SNAP_ID, BEGIN_TIME, EVENT_NAME ,
TOTAL_WAITS, round(TOTAL_TIME_WAITED_MICRO/1000000,2) TOTAL_TIME_WAITED_SEC,
round(TOTAL_TIME_WAITED_MICRO/TOTAL_WAITS/1000,2) AVG_TIME_WAITED_MS
FROM
(
SELECT
 DHSE.INSTANCE_NUMBER,DHS.snap_id, DHS.BEGIN_INTERVAL_TIME BEGIN_TIME, DHSE.EVENT_NAME,
 TOTAL_WAITS - LAG(TOTAL_WAITS, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_WAITS,
 TIME_WAITED_MICRO - LAG(TIME_WAITED_MICRO, 1, 0) OVER (PARTITION BY DHSE.INSTANCE_NUMBER ORDER BY DHS.SNAP_ID) TOTAL_TIME_WAITED_MICRO
  FROM
  DBA_HIST_SYSTEM_EVENT DHSE, DBA_HIST_SNAPSHOT DHS, MN_SNAP_ID MNS
  WHERE
  EVENT_NAME  = '&event_name' AND
  DHS.SNAP_ID = DHSE.SNAP_ID AND
  DHS.SNAP_ID > MNS.SNAP_ID AND
  DHSE.INSTANCE_NUMBER = DHS.INSTANCE_NUMBER
   )
ORDER BY INSTANCE_NUMBER, SNAP_ID;