Oracle Database - Scripts - SID to PID and PID to SID

Oracle Database - Scripts - SID to PID and PID to SID

scripts Vineet Sachdeva   January 17, 2019

In this blog I am going to cover a simple script which converts the SID  you mentioned to OS PID . 

SID to PID
set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr
and a.sid = &SID
order by b.spid;

When Prompted Enter SID

  1  select a.sid,b.spid,a.username,a.osuser
  2  from v$session a,
  3  v$process b
  4  where a.paddr = b.addr
  5  and a.sid = &SID
  6* order by b.spid
Enter value for sid: 31
old   5: and a.sid = &SID
new   5: and a.sid = 31

   SID SPID                     USERNAME                                           OSUSER
------ ------------------------ -------------------------------------------------- ------------------------------

    31 9936                     AS                                                 iisuser

PID to SID

set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr
and b.spid = &PID
order by b.spid;

When Prompted Enter PID

  1  select a.sid,b.spid,a.username,a.osuser
  2  from v$session a,
  3  v$process b
  4  where a.paddr = b.addr
  5  and b.spid = &PID
  6* order by b.spid
Enter value for pid: 9936
old   5: and b.spid = &PID
new   5: and b.spid = 9936

   SID SPID                     USERNAME                                           OSUSER
------ ------------------------ -------------------------------------------------- ------------------------------

    31 9936                     AS                                                 iisuser

To List all SIDs with their PIDs
set lines 500 pages 500
column sid format 99999
column spid format 99999
column program format a30
column osuser format a30

select a.sid,b.spid,a.username,a.osuser
from v$session a,
v$process b
where a.paddr = b.addr

order by b.spid

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 Enterprise Manager - 13c Reporting from Management Views

Oracle Enterprise Manager - 13c Reporting from Management Views

scripts Vineet Sachdeva   January 07, 2019

In this blog I discuss on how to extract data from OEM management views. 

I am discussing a task of extracting SGA / PGA monitoring data from OEM views

There 3 key views to keep eye on 
1. MGMT$METRIC_CURRENT - Keeps record of current values
2. MGMT$METRIC_HOURLY - Hourly records
3. MGMT$METRIC_DAILY - Aggregated Daily Records

You can get  more information on views from below - https://docs.oracle.com/cd/E63000_01/EMVWS/monview.htm#EMVWS12357

So to extract data from OEM view I use the below query to get my current SGA / PGA data

col DB_NAME  for a15
col SGA_Size_MB for a18
col value for a15
col DATA_COLLECTED for a30
break on DB_NAME ON DATA_COLLECTED skip 1

SELECT TARGET_NAME AS DB_NAME, COLUMN_LABEL AS METRIC_NAME, 
VALUE, to_char(COLLECTION_TIMESTAMP, 'dd-mm-yyyy hh24:mi') as DATA_COLLECTED
FROM MGMT$METRIC_CURRENT
WHERE 
TARGET_TYPE = 'oracle_database' AND
METRIC_NAME = 'memory_usage_sga_pga' AND
METRIC_LABEL = 'SGA and PGA usage' AND 
COLUMN_LABEL in  ('SGA Size(MB)' , 'PGA Total(MB)')
ORDER BY 
DB_NAME, METRIC_NAME;

Oracle Database - Backup Status (Check) Query

Oracle Database - Backup Status (Check) Query

scripts Vineet Sachdeva   September 10, 2018

This query is to check on Database backups history from v$ views. 
It provides below

1. BACKUP NAME 
2. STATUS 
3. START TIME                
4. TIME TAKEN TYPE            
5. OUTPUT DEVICE
6. INPUT SIZE   
7. OUTPUT SIZE 

SET ECHO ON
set linesize 222
column "START TIME" format a40
column "END TIME" format a40
col "BACKUP NAME" for a20
col STATUS for a21
col "START TIME" for a25
col "END TIME" for a25
col "TIME TAKEN" for a10
col "TYPE" for a15
col "OUTPUT DEVICES" for a10
col "INPUT SIZE" for a12
col "OUTPUT SIZE" for a12
col "OUTPUT BYTES PER SECOND" for a10

select command_id "BACKUP NAME",
STATUS,
to_char(start_time,'Mon DD,YYYY HH24:MI:SS') "START TIME",
time_taken_display "TIME TAKEN",
input_type "TYPE",
output_device_type "OUTPUT DEVICES",
input_bytes_display "INPUT SIZE",
output_bytes_display "OUTPUT SIZE",
output_bytes_per_sec_display "OUTPUT BYTES PER SECOND"
FROM V$RMAN_BACKUP_JOB_DETAILS where trunc(start_time) between trunc(sysdate-4) and  trunc(sysdate) ORDER BY END_TIME DESC;

Query Output

SQL> r
1 select command_id "BACKUP NAME",
2 STATUS,
3 to_char(start_time,'Mon DD,YYYY HH24:MI:SS') "START TIME",
4 time_taken_display "TIME TAKEN",
5 input_type "TYPE",
6 output_device_type "OUTPUT DEVICES",
7 input_bytes_display "INPUT SIZE",
8 output_bytes_display "OUTPUT SIZE",
9 output_bytes_per_sec_display "OUTPUT BYTES PER SECOND"
10* FROM V$RMAN_BACKUP_JOB_DETAILS where trunc(start_time) between trunc(sysdate-4) and trunc(sysdate) ORDER BY END_TIME DESC

BACKUP NAME STATUS START TIME TIME TAKEN TYPE OUTPUT DEV INPUT SIZE OUTPUT SIZE OUTPUT BYT
-------------------- --------------------- ------------------------- ---------- --------------- ---------- ------------ ------------ ----------
ARCH_BKP COMPLETED Sept. 10,2018 12:00:03 00:12:40 ARCHIVELOG DISK 27.65G 7.83G 10.55M
ARCH_BKP FAILED Sept. 10,2018 08:00:03 00:01:39 ARCHIVELOG DISK 3.72G 997.12M 10.07M
ARCH_BKP FAILED Sept. 10,2018 04:00:02 00:01:39 ARCHIVELOG DISK 3.72G 997.12M 10.07M
ARCH_BKP FAILED Sept. 10,2018 00:00:02 00:01:49 ARCHIVELOG DISK 3.72G 997.12M 9.15M
DB_INCREMENTAL_L0 FAILED Sept. 09,2018 01:00:02 04:37:20 DB INCR DISK 971.06G 140.76G 8.66M
ARCH_BKP FAILED Sept. 08,2018 20:00:02 00:00:40 ARCHIVELOG DISK 1015.20M 301.32M 7.53M
ARCH_BKP FAILED Sept. 08,2018 16:00:03 00:02:01 ARCHIVELOG DISK 3.82G 1.19G 10.07M
ARCH_BKP FAILED Sept. 08,2018 12:00:02 00:04:33 ARCHIVELOG DISK 10.04G 2.98G 11.19M
ARCH_BKP FAILED Sept. 08,2018 08:00:02 00:03:43 ARCHIVELOG DISK 7.98G 2.36G 10.85M
ARCH_BKP FAILED Sept. 08,2018 04:00:02 00:02:10 ARCHIVELOG DISK 4.71G 1.29G 10.18M
ARCH_BKP FAILED Sept. 08,2018 00:00:03 00:01:01 ARCHIVELOG DISK 1.42G 440.86M 7.23M
ARCH_BKP FAILED Sept. 07,2018 20:00:03 00:01:30 ARCHIVELOG DISK 3.04G 969.58M 10.77M
ARCH_BKP FAILED Sept. 07,2018 16:00:03 00:03:03 ARCHIVELOG DISK 6.25G 1.95G 10.94M
ARCH_BKP FAILED Sept. 07,2018 12:00:02 00:02:21 ARCHIVELOG DISK 5.11G 1.43G 10.38M
ARCH_BKP FAILED Sept. 07,2018 08:00:03 00:04:03 ARCHIVELOG DISK 8.85G 2.59G 10.93M
ARCH_BKP COMPLETED Sept. 07,2018 04:00:02 00:02:08 ARCHIVELOG DISK 4.73G 1.29G 10.36M
ARCH_BKP COMPLETED Sept. 07,2018 00:00:02 00:01:03 ARCHIVELOG DISK 1.82G 618.67M 9.82M
ARCH_BKP COMPLETED Sept. 06,2018 20:00:02 00:01:33 ARCHIVELOG DISK 2.74G 927.82M 9.98M
ARCH_BKP COMPLETED Sept. 06,2018 16:00:02 00:02:17 ARCHIVELOG DISK 4.32G 1.39G 10.38M
ARCH_BKP COMPLETED Sept. 06,2018 12:00:02 00:11:19 ARCHIVELOG DISK 24.07G 7.07G 10.66M

20 rows selected.

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;