Oracle Logons , Transaction , Redo Per Second

 
###########################
Logon Per Second 
###########################

REM
REM     Script:        acquire_lps.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Sep 28, 2021
REM
REM     Last tested:
REM             11.2.0.4
REM             19.3.0.0
REM             21.3.0.0
REM
REM     Purpose:
REM       We can get "LPS" from the metric_name "Logons Per Sec" of the view "DBA_HIST_SYSMETRIC_HISTORY"
REM       or "DBA_HIST_SYSMETRIC_SUMMARY".
REM
REM       Next we use the analytic function "LAG () OVER()" to get the prior snap_id from current snap_id for more
REM       clearly showing "Logons Per Sec" between these two snap_id.
REM
REM       SET LINESIZE 80
REM       DESC acquire_awr_lps
REM        Name                                      Null?    Type
REM        ----------------------------------------- -------- ----------------------------
REM        INSTANCE_NUMBER                           NOT NULL NUMBER
REM        FIRST_SNAP_ID                             NOT NULL NUMBER
REM        SECOND_SNAP_ID                            NOT NULL NUMBER
REM        BEGIN_TIME                                NOT NULL DATE
REM        END_TIME                                  NOT NULL DATE
REM        METRIC_NAME                               NOT NULL VARCHAR2(18)
REM        METRIC_UNIT                               NOT NULL VARCHAR2(18)
REM        AWR_LPS                                            NUMBER
REM
REM     References:
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_HISTORY.html#GUID-4A9988AE-B1B5-4E71-9C38-C95448B3F758
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_SUMMARY.html#GUID-E6377E5F-1FFF-4563-850F-C361B9D85048
REM

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a18
COLUMN metric_unit FORMAT a18
COLUMN awr_lps     FORMAT 999,999,999.99

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH dhsh
AS (
     SELECT *
     FROM (
            SELECT instance_number
                 , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
                 , snap_id second_snap_id
                 , MIN(begin_time) begin_time
                 , MAX(end_time) end_time
                 , metric_name
                 , metric_unit
                 , SUM(value*(intsize/1e2)) logon_numbers
                 , (MAX(end_time)-MIN(begin_time))*24*36e2 interval_secs
            FROM dba_hist_sysmetric_history
            WHERE metric_name = 'Logons Per Sec'
            GROUP BY dbid
                   , instance_number
                   , snap_id
                   , metric_name
                   , metric_unit
            ORDER BY instance_number
                   , first_snap_id
          )
     WHERE first_snap_id <> 0
   )
SELECT instance_number
     , first_snap_id
     , second_snap_id
     , begin_time
     , end_time
     , metric_name
     , metric_unit
     , ROUND(logon_numbers/interval_secs, 2) awr_lps
FROM dhsh
;

or

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average, 2) awr_lps
       FROM dba_hist_sysmetric_summary
       WHERE metric_name = 'Logons Per Sec'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;



Or 

select
   a.begin_interval_time,
   a.end_interval_time,
   b.resource_name,
   b.current_utilization,
   b.max_utilization
from
   dba_hist_resource_limit b,
   dba_hist_snapshot a
where
   a.snap_id = b.snap_id
and
   b.resource_name = 'sessions'
order by
   a.begin_interval_time;


 
###########################
Transaction Per Second 
###########################

REM
REM     Script:        acquire_tps_2.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Sep 23, 2021
REM
REM     Updated:       Sep 26, 2021
REM                    Replacing the old WHERE clause "metric_unit = 'Transactions Per Second'" with
REM                    the new one, such as, "metric_name = 'User Transaction Per Sec'".
REM
REM     Last tested:
REM             11.2.0.4
REM             19.3.0.0
REM             21.3.0.0
REM
REM     Purpose:
REM       It's the 2nd version (which is more simple and easy to understand than the 1st) of acquire_tps.sql,
REM       you can see "https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_tps.sql".
REM
REM       Typically there saves the average value of "Transactions Per Second" in each of snap_id of the SDDV
REM       (Static Data Dictionary View), "DBA_HIST_SYSMETRIC_SUMMARY", (in which the value of its column
REM       "metric_unit" is "Transactions Per Second"), here we use the analytic function "LAG () OVER()" to
REM       get the prior snap_id from current snap_id for more clearly showing "TPS" between these two snap_id.
REM
REM       SET LINESIZE 80
REM       DESC acquire_tps_2
REM        Name                                      Null?    Type
REM        ----------------------------------------- -------- ----------------------------
REM        INSTANCE_NUMBER                           NOT NULL NUMBER
REM        FIRST_SNAP_ID                             NOT NULL NUMBER
REM        SECOND_SNAP_ID                            NOT NULL NUMBER
REM        BEGIN_TIME                                NOT NULL DATE
REM        END_TIME                                  NOT NULL DATE
REM        METRIC_NAME                               NOT NULL VARCHAR2(25)
REM        METRIC_UNIT                               NOT NULL VARCHAR2(25)
REM        TPS                                                NUMBER
REM
REM     References:
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_SUMMARY.html#GUID-E6377E5F-1FFF-4563-850F-C361B9D85048
REM

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_unit FORMAT a25
COLUMN metric_name FORMAT a25
COLUMN tps         FORMAT 999,999,999.99

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average, 2) tps
       FROM dba_hist_sysmetric_summary
       WHERE metric_name = 'User Transaction Per Sec'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;



 
###########################
Redo  Per Second 
###########################

REM
REM     Script:        acquire_redo_gen_mbps.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Sep 28, 2021
REM
REM     Last tested:
REM             11.2.0.4
REM             19.3.0.0
REM             21.3.0.0
REM
REM     Purpose:
REM       We can get "redo generated mbps" from the metric_name "Redo Generated Per Sec" of the view
REM       "DBA_HIST_SYSMETRIC_HISTORY" or "DBA_HIST_SYSMETRIC_SUMMARY".
REM
REM       Next we use the analytic function "LAG () OVER()" to get the prior snap_id from current
REM       snap_id for more clearly showing "Redo Generated Per Sec" between these two snap_id.
REM
REM       SET LINESIZE 80
REM       DESC acquire_redo_gen_mbps
REM        Name                                      Null?    Type
REM        ----------------------------------------- -------- ----------------------------
REM        INSTANCE_NUMBER                           NOT NULL NUMBER
REM        FIRST_SNAP_ID                             NOT NULL NUMBER
REM        SECOND_SNAP_ID                            NOT NULL NUMBER
REM        BEGIN_TIME                                NOT NULL DATE
REM        END_TIME                                  NOT NULL DATE
REM        METRIC_NAME                               NOT NULL VARCHAR2(25)
REM        METRIC_UNIT                               NOT NULL VARCHAR2(18)
REM        REDO_GEN_MBPS                                      NUMBER
REM
REM     References:
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_HISTORY.html#GUID-4A9988AE-B1B5-4E71-9C38-C95448B3F758
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_SUMMARY.html#GUID-E6377E5F-1FFF-4563-850F-C361B9D85048
REM

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name   FORMAT a25
COLUMN metric_unit   FORMAT a18
COLUMN redo_gen_mbps FORMAT 999,999,999.99

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH dhsh
AS (
     SELECT *
     FROM (
            SELECT instance_number
                 , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
                 , snap_id second_snap_id
                 , MIN(begin_time) begin_time
                 , MAX(end_time) end_time
                 , metric_name
                 , metric_unit
                 , SUM(value/POWER(2, 20)*(intsize/1e2)) redo_gen_mb_size
                 , (MAX(end_time)-MIN(begin_time))*24*36e2 interval_secs
            FROM dba_hist_sysmetric_history
            WHERE metric_name = 'Redo Generated Per Sec'
            GROUP BY dbid
                   , instance_number
                   , snap_id
                   , metric_name
                   , metric_unit
            ORDER BY instance_number
                   , first_snap_id
          )
     WHERE first_snap_id <> 0
   )
SELECT instance_number
     , first_snap_id
     , second_snap_id
     , begin_time
     , end_time
     , metric_name
     , metric_unit
     , ROUND(redo_gen_mb_size/interval_secs, 2) redo_gen_mbps
FROM dhsh
;

or

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average/POWER(2, 20), 2) redo_gen_mbps
       FROM dba_hist_sysmetric_summary
       WHERE metric_name = 'Redo Generated Per Sec'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;





 
###########################
Cpu Load Per Awr 
###########################

REM
REM     Script:        acquire_cpu_load_2.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Sep 27, 2021
REM
REM     Last tested:
REM             11.2.0.4
REM             19.3.0.0
REM             21.3.0.0
REM
REM     Purpose:
REM       The 2nd version of acquire_cpu_load.sql (https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_cpu_load.sql),
REM       which is more simple and easy to understand. The another formula calculating "CPU Load" is "Average Active Sessions (AAS)/CPU_NUMS*100%"
REM       (these metrics are from AWR report). We can get "AAS" from the SQL script (https://github.com/guestart/Oracle-SQL-Scripts/blob/master/awr_trend/acquire_aas_2.sql),
REM       and we can also get the number of cpus from the view "DBA_HIST_OSSTAT" by querying the column "stat_name" who equals to "num_cpus".    
REM
REM       Utimately we can get all of the values with "CPU Load" from the historical AWR reports of oracle database.
REM
REM       SET LINESIZE 80
REM       DESC acquire_awr_cpu_load_2
REM        Name                                      Null?    Type
REM        ----------------------------------------- -------- ----------------------------
REM        INSTANCE_NUMBER                           NOT NULL NUMBER
REM        FIRST_SNAP_ID                             NOT NULL NUMBER
REM        SECOND_SNAP_ID                            NOT NULL NUMBER
REM        BEGIN_TIME                                NOT NULL DATE
REM        END_TIME                                  NOT NULL DATE
REM        AWR_CPU_LOAD                                       VARCHAR2(12)
REM
REM     Reference:
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_SUMMARY.html#GUID-E6377E5F-1FFF-4563-850F-C361B9D85048
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_OSSTAT.html#GUID-C94C3F25-ADE2-4E4C-B942-C0D14D9441D8
REM

SET LINESIZE 200
SET PAGESIZE 200

COLUMN begin_time   FORMAT a19
COLUMN end_time     FORMAT a19
COLUMN awr_cpu_load FORMAT a12

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH aas
AS (
     SELECT snap_id
          , dbid
          , instance_number
          , begin_time
          , end_time
          , ROUND(average/1e2, 2) average_active_sessions -- metric_unit is "CentiSeconds Per Second" so average should divide by 1e2.
     FROM dba_hist_sysmetric_summary
  -- WHERE metric_name = 'DB Time Per Second' -- not "DB Time Per Second", should the following metric_name "Database Time Per Sec".
     WHERE metric_name = 'Database Time Per Sec'
     ORDER BY snap_id
   ),
dhos AS (
          SELECT snap_id
               , dbid
               , instance_number
               , stat_name
               , value
          FROM dba_hist_osstat
          WHERE stat_name = 'NUM_CPUS'
        )
SELECT *
FROM (
       SELECT aas.instance_number
            , LAG(aas.snap_id, 1, 0) OVER(PARTITION BY aas.dbid, aas.instance_number ORDER BY aas.snap_id) first_snap_id
            , aas.snap_id second_snap_id
            , aas.begin_time
            , aas.end_time
            , ROUND(aas.average_active_sessions/dhos.value*100, 2) || '%' awr_cpu_load
       FROM aas
          , dhos
       WHERE aas.snap_id = dhos.snap_id
       AND   aas.instance_number = dhos.instance_number
       AND   aas.dbid = dhos.dbid
       ORDER BY aas.instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;



###########################
COMMITS PER MINUTE
###########################


col STAT_NAME for a20
col VALUE_DIFF for 9999,999,999
col STAT_PER_MIN for 9999,999,999
set lines 200 pages 1500 long 99999999
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
set pagesize 40
set pause on
select hsys.SNAP_ID,
 hsnap.BEGIN_INTERVAL_TIME,
 hsnap.END_INTERVAL_TIME,
 hsys.STAT_NAME,
 hsys.VALUE,
 hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS
"VALUE_DIFF",
 round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY
hsys.SNAP_ID)) /
 round(abs(extract(hour from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME))*60 +
 extract(minute from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME)) +
 extract(second from (hsnap.END_INTERVAL_TIME -
hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"
from dba_hist_sysstat hsys, dba_hist_snapshot hsnap
where hsys.snap_id = hsnap.snap_id
and hsnap.instance_number in (select instance_number from v$instance)
and hsnap.instance_number = hsys.instance_number
and hsys.STAT_NAME='user commits'
order by 1;




###########################
IO MB  Per Second Per AWR 
###########################


REM
REM     Script:        acquire_io_mbps_2.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Sep 26, 2021
REM
REM     Last tested:
REM             11.2.0.4
REM             19.3.0.0
REM             21.3.0.0
REM
REM     Purpose:
REM       We can get "IO MBPS" from the metric_name "I/O Megabytes per Second" of the view "DBA_HIST_SYSMETRIC_HISTORY"
REM       or "DBA_HIST_SYSMETRIC_SUMMARY".
REM
REM       SET LINESIZE 80
REM       DESC acquire_io_mbps_2
REM        Name                                      Null?    Type
REM        ----------------------------------------- -------- ----------------------------
REM        INSTANCE_NUMBER                           NOT NULL NUMBER
REM        FIRST_SNAP_ID                             NOT NULL NUMBER
REM        SECOND_SNAP_ID                            NOT NULL NUMBER
REM        BEGIN_TIME                                NOT NULL DATE
REM        END_TIME                                  NOT NULL DATE
REM        METRIC_NAME                               NOT NULL VARCHAR2(25)
REM        METRIC_UNIT                               NOT NULL VARCHAR2(20)
REM        IO_MBPS                                            NUMBER
REM
REM     References:
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_HISTORY.html#GUID-4A9988AE-B1B5-4E71-9C38-C95448B3F758
REM       https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SYSMETRIC_SUMMARY.html#GUID-E6377E5F-1FFF-4563-850F-C361B9D85048
REM

SET LINESIZE 200
SET PAGESIZE 200

COLUMN metric_name FORMAT a25
COLUMN metric_unit FORMAT a20
COLUMN io_mbps FORMAT 999,999,999.99

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH dhsh
AS (
     SELECT *
     FROM (
            SELECT instance_number
                 , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
                 , snap_id second_snap_id
                 , MIN(begin_time) begin_time
                 , MAX(end_time) end_time
                 , metric_name
                 , metric_unit
                 , SUM(value*(intsize/1e2)) io_mb_size
                 , (MAX(end_time)-MIN(begin_time))*24*36e2 interval_secs
            FROM dba_hist_sysmetric_history
            WHERE metric_name = 'I/O Megabytes per Second'
            GROUP BY dbid
                   , instance_number
                   , snap_id
                   , metric_name
                   , metric_unit
            ORDER BY instance_number
                   , first_snap_id
          )
     WHERE first_snap_id <> 0
   )
SELECT instance_number
     , first_snap_id
     , second_snap_id
     , begin_time
     , end_time
     , metric_name
     , metric_unit
     , ROUND(io_mb_size/interval_secs, 2) io_mbps
FROM dhsh
;

or

SELECT *
FROM (
       SELECT instance_number
            , LAG(snap_id, 1, 0) OVER(PARTITION BY dbid, instance_number ORDER BY snap_id) first_snap_id
            , snap_id second_snap_id
            , begin_time
            , end_time
            , metric_name
            , metric_unit
            , ROUND(average, 2) io_mbps
       FROM dba_hist_sysmetric_summary
       WHERE metric_name = 'I/O Megabytes per Second'
       ORDER BY instance_number
              , first_snap_id
     )
WHERE first_snap_id <> 0
;




 
###########################
Database Parameter history 
###########################


3. You can keep track of this changes by enabling audit on ALTER SYSTEM command

-- Enable the Audit
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
--Enable also for SYS user
ALTER SYSTEM SET audit_sys_operations=true SCOPE=SPFILE;
-- Restart the database for audit parameter in effect.
shutdown immediate
startup
-- Enable the audit on ALTER SYSTEM privileges on user you want.
AUDIT ALTER SYSTEM BY test;




set linesize 155
col time for a15
col parameter_name format a50
col old_value format a30
col new_value format a30
break on instance skip 3
select instance_number instance, snap_id, time, parameter_name, old_value, new_value from (
select a.snap_id,to_char(end_interval_time,'DD-MON-YY HH24:MI') TIME, a.instance_number, parameter_name, value new_value,
lag(parameter_name,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_pname,
lag(value,1) over (partition by parameter_name, a.instance_number order by a.snap_id) old_value ,
decode(substr(parameter_name,1,2),'__',2,1) calc_flag
from dba_hist_parameter a, dba_Hist_snapshot b , v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl('&parameter_name',parameter_name)
and a.instance_number like nvl('&instance_number',v.instance_number)
)
where
new_value != old_value
order by 1,2;

 



###########################
Database Process History
###########################

set lines 200 pages 1000
col begin_interval_time FOR A30
col "%UsedProcesses" for A15
 
SELECT HIST_SNAPSHOT.snap_id,
       --HIST_SNAPSHOT.instance_number, -- for RAC
       HIST_SNAPSHOT.begin_interval_time,
       HIST_RESOURCE_LIMIT.curr_util,  
       HIST_RESOURCE_LIMIT.max_util,
       HIST_RESOURCE_LIMIT.ini_alloc,
  round((HIST_RESOURCE_LIMIT.max_utilization/HIST_RESOURCE_LIMIT.initial_allocation)*100,2) 
      || '%' "%UsedProcesses"
FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT,
     DBA_HIST_SNAPSHOT   HIST_SNAPSHOT
WHERE HIST_RESOURCE_LIMIT.resource_name='processes'
AND   HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id
--AND HIST_RESOURCE_LIMIT.instance_number=HIST_SNAPSHOT.instance_number --for RAC
ORDER BY HIST_SNAPSHOT.snap_id;



###########################
Database Load Per Awr snap
###########################

-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

SET LINES 999 PAGES 5000 TRIMSPOOL ON TRIMOUT ON TAB OFF

COL snap_time FOR A30

SELECT
    sn.begin_interval_time snap_time
  , ROUND(SUM(CASE WHEN metric_name = 'Average Active Sessions'                       THEN value END)) aas
  , ROUND(AVG(CASE WHEN metric_name = 'Average Synchronous Single-Block Read Latency' THEN value END)) iolat
  , ROUND(SUM(CASE WHEN metric_name = 'CPU Usage Per Sec'                             THEN value END)) cpusec
  , ROUND(SUM(CASE WHEN metric_name = 'Background CPU Usage Per Sec'                  THEN value END)) bgcpusec
  , ROUND(AVG(CASE WHEN metric_name = 'DB Block Changes Per Txn'                      THEN value END)) blkchgtxn
  , ROUND(SUM(CASE WHEN metric_name = 'Executions Per Sec'                            THEN value END)) execsec
  , ROUND(SUM(CASE WHEN metric_name = 'Host CPU Usage Per Sec'                        THEN value END)) oscpusec 
  , ROUND(SUM(CASE WHEN metric_name = 'I/O Megabytes per Second'                      THEN value END)) iombsec
  , ROUND(SUM(CASE WHEN metric_name = 'I/O Requests per Second'                       THEN value END)) ioreqsec
  , ROUND(AVG(CASE WHEN metric_name = 'Logical Reads Per Txn'                         THEN value END)) liotxn
  , ROUND(SUM(CASE WHEN metric_name = 'Logons Per Sec'                                THEN value END)) logsec
  , ROUND(SUM(CASE WHEN metric_name = 'Network Traffic Volume Per Sec'                THEN value END)/1048576) netmbsec
  , ROUND(SUM(CASE WHEN metric_name = 'Physical Reads Per Sec'                        THEN value END)) phyrdsec
  , ROUND(AVG(CASE WHEN metric_name = 'Physical Reads Per Txn'                        THEN value END)) phyrdtxn
  , ROUND(SUM(CASE WHEN metric_name = 'Physical Writes Per Sec'                       THEN value END)) phywrsec
  , ROUND(SUM(CASE WHEN metric_name = 'Redo Generated Per Sec'                        THEN value END)/1024) redokbsec
  , ROUND(AVG(CASE WHEN metric_name = 'Redo Generated Per Txn'                        THEN value END)/1024) redokbtxn
  , ROUND(AVG(CASE WHEN metric_name = 'Response Time Per Txn'                         THEN value END)*10) timemsectxn
  , ROUND(AVG(CASE WHEN metric_name = 'SQL Service Response Time'                     THEN value END)*10) timemseccall
  , ROUND(AVG(CASE WHEN metric_name = 'Total Parse Count Per Txn'                     THEN value END)) prstxn
  , ROUND(SUM(CASE WHEN metric_name = 'User Calls Per Sec'                            THEN value END)) ucallsec
  , ROUND(SUM(CASE WHEN metric_name = 'User Transaction Per Sec'                      THEN value END)) utxnsec
FROM
    dba_hist_snapshot sn
  , dba_hist_sysmetric_history m
WHERE
    sn.snap_id = m.snap_id
AND sn.dbid    = m.dbid
AND sn.instance_number = m.instance_number
AND sn.begin_interval_time > SYSDATE - 7
GROUP BY
    sn.begin_interval_time
ORDER BY
    sn.begin_interval_time
/






Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more