Oracle AWR SNAP Detail

Awr snap

Oracle AWR snap

[code]

define BEGIN = "2022-05-12 01:00:00"
define END = "2022-05-12 03:00:00"

set linesize 300 pagesize 300
col BEGIN_INTERVAL_TIME for a27
col END_INTERVAL_TIME for a27
select 
  snap_id,
  instance_number,
  begin_interval_time,
  end_interval_time
from dba_hist_snapshot
where dbid = (select dbid from v$database)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= (to_timestamp('&END','YYYY-MM-DD HH24:MI:SS') + 5/24/60)
--and begin_interval_time >= to_timestamp('&BEGIN','YYYY-MM-DD HH24:MI:SS') and end_interval_time <= to_timestamp('&END','YYYY-MM-DD HH24:MI:SS')
and begin_interval_time >sysdate -1
order by snap_id, instance_number;



   SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME         END_INTERVAL_TIME
---------- --------------- --------------------------- ---------------------------
     41738               1 12-MAY-22 01.00.03.510 AM   12-MAY-22 02.00.11.856 AM
     41738               2 12-MAY-22 01.00.03.582 AM   12-MAY-22 02.00.11.905 AM
     41739               1 12-MAY-22 02.00.11.856 AM   12-MAY-22 03.00.20.062 AM
     41739               2 12-MAY-22 02.00.11.905 AM   12-MAY-22 03.00.20.100 AM

SQL>






====

SET LINESIZE   145
SET PAGESIZE   9999
SET TRIMSPOOL  ON
SET VERIFY     off

COLUMN instance_name_print  FORMAT a13                   HEADING 'Instance Name'
COLUMN snap_id              FORMAT 9999999               HEADING 'Snap ID'
COLUMN startup_time         FORMAT a21                   HEADING 'Instance Startup Time'
COLUMN begin_interval_time  FORMAT a20                   HEADING 'Begin Interval Time'
COLUMN end_interval_time    FORMAT a20                   HEADING 'End Interval Time'
COLUMN elapsed_time         FORMAT 999,999,999,999.99    HEADING 'Elapsed Time (min)'
COLUMN db_time              FORMAT 999,999,999,999.99    HEADING 'DB Time (min)'
COLUMN pct_db_time          FORMAT 999999999             HEADING '% DB Time'
COLUMN cpu_time             FORMAT 999,999,999.99        HEADING 'CPU Time (min)'

BREAK ON instance_name_print ON startup_time

SPOOL awr_snap.txt

SELECT
    i.instance_name                                                                     instance_name_print
  , s.snap_id                                                                           snap_id
  , TO_CHAR(s.startup_time, 'dd/mm/yyyy HH24:MI:SS')                                    startup_time
  , TO_CHAR(s.begin_interval_time, 'dd/mm/yyyy HH24:MI:SS')                             begin_interval_time
  , TO_CHAR(s.end_interval_time, 'dd/mm/yyyy HH24:MI:SS')                               end_interval_time
  , ROUND(EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
          EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
          EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
          EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60, 2)     elapsed_time
  , ROUND((e.value - b.value)/1000000/60, 2)                                            db_time
  , ROUND(((((e.value - b.value)/1000000/60) / (EXTRACT(DAY FROM  s.end_interval_time - s.begin_interval_time) * 1440 +
                                                EXTRACT(HOUR FROM s.end_interval_time - s.begin_interval_time) * 60 +
                                                EXTRACT(MINUTE FROM s.end_interval_time - s.begin_interval_time) +
                                                EXTRACT(SECOND FROM s.end_interval_time - s.begin_interval_time) / 60) ) * 100), 2)   pct_db_time
FROM
    dba_hist_snapshot       s
  , gv$instance             i
  , dba_hist_sys_time_model e
  , dba_hist_sys_time_model b
WHERE
      i.instance_number = s.instance_number
  AND e.snap_id         = s.snap_id
  AND b.snap_id         = s.snap_id - 1
  AND e.stat_id         = b.stat_id
  AND e.instance_number = b.instance_number
  AND e.instance_number = s.instance_number
  AND e.stat_name       = 'DB time'
ORDER BY
    i.instance_name
  , s.snap_id;

SPOOL OFF



Instance Name  Snap ID Instance Startup Time Begin Interval Time  End Interval Time     Elapsed Time (min)       DB Time (min)  % DB Time
------------- -------- --------------------- -------------------- -------------------- ------------------- ------------------- ----------
orcl              5697 04/11/2011 10:27:02   20/11/2011 01:00:49  20/11/2011 02:00:51                60.03                 .03          0
                  5698                       20/11/2011 02:00:51  20/11/2011 03:00:52                60.03                 .03          0
                  5699                       20/11/2011 03:00:52  20/11/2011 04:00:54                60.03                 .02          0
                  5700                       20/11/2011 04:00:54  20/11/2011 05:00:56                60.03                 .02          0
                  5701                       20/11/2011 05:00:56  20/11/2011 06:00:59                60.06                2.47          4
                  5702                       20/11/2011 06:00:59  20/11/2011 07:00:02                59.05                4.15          7
                  5703                       20/11/2011 07:00:02  20/11/2011 08:00:04                60.04                 .58          1
                  5704                       20/11/2011 08:00:04  20/11/2011 09:00:06                60.03                 .03          0
                  5718                       20/11/2011 22:00:30  20/11/2011 23:00:32                60.03                1.09          2
                  5719                       20/11/2011 23:00:32  21/11/2011 00:00:34                60.03                 .03          0
                  5720                       21/11/2011 00:00:34  21/11/2011 01:00:36                60.04                 .05          0
                  5721                       21/11/2011 01:00:36  21/11/2011 02:00:38                60.03                 .04          0
                  5722                       21/11/2011 02:00:38  21/11/2011 03:00:40                60.03                 .04          0
                  5723                       21/11/2011 03:00:40  21/11/2011 04:00:41                60.03                 .03          0
                  5724                       21/11/2011 04:00:41  21/11/2011 05:00:43                60.03                 .03          0
                  5725                       21/11/2011 05:00:43  21/11/2011 06:00:45                60.03                 .03          0
                  5726                       21/11/2011 06:00:45  21/11/2011 07:00:46                60.03                 .04          0
                  5727                       21/11/2011 07:00:46  21/11/2011 08:00:50                60.06                 .06          0
                  5728                       21/11/2011 08:00:50  21/11/2011 09:00:52                60.03                 .04          0
                  5729                       21/11/2011 09:00:52  21/11/2011 10:00:55                60.04                 .05          0
                  5730                       21/11/2011 10:00:55  21/11/2011 11:00:57                60.04                 .38          1
                  5731                       21/11/2011 11:00:57  21/11/2011 12:00:05                59.13                 .34          1
                  5732                       21/11/2011 12:00:05  21/11/2011 13:00:10                60.09                 .35          1
                  5733                       21/11/2011 13:00:10  21/11/2011 14:00:15                60.08                 .10          0
                  5734                       21/11/2011 14:00:15  21/11/2011 15:00:18                60.05                 .11          0
                  5735                       21/11/2011 15:00:18  21/11/2011 16:00:21                60.05                 .07          0
                  5736                       21/11/2011 16:00:21  21/11/2011 17:00:23                60.04                 .07          0
                  5737                       21/11/2011 17:00:23  21/11/2011 18:00:25                60.03                 .03          0
                  5738                       21/11/2011 18:00:25  21/11/2011 19:00:27                60.03                 .03          0
                  5739                       21/11/2011 19:00:27  21/11/2011 20:00:28                60.03                 .13          0
                  5740                       21/11/2011 20:00:28  21/11/2011 21:00:30                60.03                 .03          0
                  5741                       21/11/2011 21:00:30  21/11/2011 22:00:32                60.03                1.75          3
                  5742                       21/11/2011 22:00:32  21/11/2011 23:00:41                60.15               40.19         67
                  5743                       21/11/2011 23:00:41  22/11/2011 00:00:43                60.03                 .04          0
                  5744                       22/11/2011 00:00:43  22/11/2011 01:00:45                60.04                 .05          0
                  5745                       22/11/2011 01:00:45  22/11/2011 02:00:47                60.03                 .05          0
                  5746                       22/11/2011 02:00:47  22/11/2011 03:00:49                60.03                 .03          0
                  5747                       22/11/2011 03:00:49  22/11/2011 04:00:50                60.03                 .03          0
                  5748                       22/11/2011 04:00:50  22/11/2011 05:00:52                60.03                 .03          0
                  5749                       22/11/2011 05:00:52  22/11/2011 06:00:54                60.03                 .03          0
                  5750                       22/11/2011 06:00:54  22/11/2011 07:00:55                60.03                 .03          0
                  5751                       22/11/2011 07:00:55  22/11/2011 08:00:58                60.05                 .07          0
                  5752                       22/11/2011 08:00:58  22/11/2011 09:00:05                59.12                 .57          1
                  5753                       22/11/2011 09:00:05  22/11/2011 10:00:10                60.08                 .37          1
                  5754                       22/11/2011 10:00:10  22/11/2011 11:00:16                60.09                 .86          1
                  5755                       22/11/2011 11:00:16  22/11/2011 12:00:20                60.06                 .07          0
                  5756                       22/11/2011 12:00:20  22/11/2011 13:00:24                60.08                 .18          0
                  5757                       22/11/2011 13:00:24  22/11/2011 14:00:31                60.11                 .16          0
                  5758                       22/11/2011 14:00:31  22/11/2011 15:00:37                60.11                1.00          2
                  5759                       22/11/2011 15:00:37  22/11/2011 16:00:42                60.08                 .58          1
                  5760                       22/11/2011 16:00:42  22/11/2011 17:00:47                60.09                 .20          0
                  5761                       22/11/2011 17:00:47  22/11/2011 18:00:50                60.06                 .04          0
                  5762                       22/11/2011 18:00:50  22/11/2011 19:00:54                60.06                 .03          0
                  5763                       22/11/2011 19:00:54  22/11/2011 20:00:57                60.06                 .03          0
                  5781                       23/11/2011 13:00:05  23/11/2011 14:00:09                60.07                 .04          0
                  5782                       23/11/2011 14:00:09  23/11/2011 15:00:14                60.08                 .40          1
                  5783                       23/11/2011 15:00:14  23/11/2011 16:00:20                60.09                 .16          0
                  5784                       23/11/2011 16:00:20  23/11/2011 17:00:28                60.14                 .33          1
                  5785                       23/11/2011 17:00:28  23/11/2011 18:00:32                60.06                 .04          0
                  5786                       23/11/2011 18:00:32  23/11/2011 19:00:36                60.07                 .04          0
                  5787                       23/11/2011 19:00:36  23/11/2011 20:00:39                60.06                 .03          0
                  5788                       23/11/2011 20:00:39  23/11/2011 21:00:42                60.06                 .03          0
                  5789                       23/11/2011 21:00:42  23/11/2011 22:00:53                60.17                2.91          5
                  5790                       23/11/2011 22:00:53  23/11/2011 23:00:58                60.10               56.04         93
                  5791                       23/11/2011 23:00:58  24/11/2011 00:00:02                59.07                 .04          0
                  5792                       24/11/2011 00:00:02  24/11/2011 01:00:07                60.08                 .05          0
                  5793                       24/11/2011 01:00:07  24/11/2011 02:00:10                60.06                 .05          0
                  5794                       24/11/2011 02:00:10  24/11/2011 03:00:14                60.06                 .04          0
                  5795                       24/11/2011 03:00:14  24/11/2011 04:00:17                60.05                 .03          0
                  5796                       24/11/2011 04:00:17  24/11/2011 05:00:21                60.06                 .03          0
                  5797                       24/11/2011 05:00:21  24/11/2011 06:00:24                60.06                 .03          0
                  5798                       24/11/2011 06:00:24  24/11/2011 07:00:28                60.06                 .03          0

                  5900                       28/11/2011 12:00:45  28/11/2011 13:00:48                60.06                 .10          0
                  5901                       28/11/2011 13:00:48  28/11/2011 14:00:52                60.06                 .09          0
                  5902                       28/11/2011 14:00:52  28/11/2011 15:00:55                60.06                 .08          0
                  5903                       28/11/2011 15:00:55  28/11/2011 16:00:59                60.07                 .06          0

207 rows selected.

	   
select * from (
  select /*b.snap_id,*/
         to_char(a.end_interval_time, 'HH24:MI') tm,
         to_char(a.end_interval_time, 'YYYY/MM/DD') dt,
--                   b.instance_number,
                   b.current_utilization tot
              from dba_hist_resource_limit b,
                   dba_hist_snapshot a
             where a.snap_id = b.snap_id
               and a.instance_number=b.instance_number
--               and a.instance_number=1
               and b.resource_name = 'sessions'
      --        and b.resource_name = 'processes'
             order by a.begin_interval_time desc
 )
 pivot (sum(tot) for (dt) in ('2025/07/20','2025/07/21', '2025/07/22','2025/07/23','2025/07/24','2025/04/03', '2025/04/04'))
 order by tm
 ;

Oracle Date manipulation

 

Oracle add hr  to date
Oracle add Min to date
Oracle add sec to date

1/24=one hour , 1/1440=one minute , 1/86400=one second


1/24/60/60   One second
7/24/60/60   Seven seconds

7/24    7 hours
14/24   14 hours


TRUNC(SYSDATE+1/24,'HH') = 1hr




In 1 day 24 hr



select 24*60 "In 24hr ->>Min " from dual;

In 24hr ->>Min
---------------
           1440



SQL> select 24*60*60 "In 24hr ->>second  " from dual;

In 24hr ->>second
-------------------
              86400



SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';

Session altered.




SQL> SELECT   SYSDATE,SYSDATE+1/24 FROM DUAL;  ---- 1 hr add

SYSDATE             SYSDATE+1/24
------------------- -------------------
28-11-2011 14:09:47 28-11-2011 15:09:47


SQL> SELECT SYSDATE, SYSDATE+1/1440 FROM DUAL;  ---- add one min

SYSDATE             SYSDATE+1/1440
------------------- -------------------
28-11-2011 14:10:47 28-11-2011 14:11:47


SQL> SELECT SYSDATE , SYSDATE+1/86400 FROM DUAL;  ---- add one sec

SYSDATE             SYSDATE+1/86400
------------------- -------------------
28-11-2011 14:47:34 28-11-2011 14:47:35





http://anuj-singh.blogspot.com/2011/02/oracle-date-math-with-anuj.html

Oracle Memory Report

from this site
http://karlarao.wordpress.com/2010/01/31/workload-characterization-using-dba_hist-tables-and-ksar/

define instance_num="1"
define start_date = "2011_1126_0009"
define end_date = "2011_1127_1900"
define start_hour = "9"
define end_hour = "19"
define date_format = "YYYY_MMDD_HH24MI"
set pagesize 1000
col dur format 999990.00 heading "Snap|Dur|(m)"
col id format 99999 heading "Snap|ID"
col tm format a15 heading "Snap|Start|Time"
col inst format 90 heading "i|n|s|t|#"
col cpu format 90 heading "C|P|U"
col cap format 9999990.00 heading "***|Total|CPU|Time|(s)"
col dbt format 999999990.00 heading "DB|Time"
col dbc format 99990.00 heading "DB|CPU"
col bgc format 99990.00 heading "Bg|CPU"
col rman format 9990.00 heading "RMAN|CPU"
col aas format 90.0 heading "A|A|S"
col totora format 9999990.00 heading "***|Total|Oracle|CPU|(s)"
col busy format 9999990.00 heading "Busy|Time"
col load format 990.00 heading "OS|Load"
col totos format 9999990.00 heading "***|Total|OS|CPU|(s)"
col mem format 999990.00 heading "Physical|Memory|(mb)"
col iors format 9990.000 heading "IOPs|r"
col iows format 9990.000 heading "IOPs|w"
col ioredo format 9990.000 heading "IOPs|redo"
col iormbs format 9990.000 heading "IO r|(mb)/s"
col iowmbs format 9990.000 heading "IO w|(mb)/s"
col redosizesec format 999990.000 heading "Redo|(mb)/s"
col logons format 99990 heading "Sess"
col logone format 990 heading "Sess|End"
col exsraw format 99990.000 heading "Exec|raw|delta"
col exs format 999990.000 heading "Exec|/s"
col oracpupct format 990 heading "Oracle|CPU|%"
col rmancpupct format 990 heading "RMAN|CPU|%"
col oscpupct format 990 heading "OS|CPU|%"
col oscpuusr format 990 heading "U|S|R|%"
col oscpusys format 990 heading "S|Y|S|%"
col oscpuio format 990 heading "I|O|%"
WITH sub_snap
AS (SELECT dbid,
instance_number,
snap_id,
fin end_interval_time,
Round(Extract(DAY FROM fin - debut) * 1440 + Extract(HOUR FROM fin - debut) * 60 + Extract(MINUTE FROM fin - debut) + Extract(SECOND FROM fin - debut) / 60, 2) dur
FROM (SELECT dbid,
instance_number,
snap_id,
end_interval_time fin,
( Lag(end_interval_time, 1) over (PARTITION BY dbid, instance_number ORDER BY snap_id) ) debut
FROM dba_hist_snapshot
WHERE instance_number = &instance_num
AND begin_interval_time >= To_date('&start_date', '&date_format')
-- AND end_interval_time = 7
AND To_number(To_char(end_interval_time, 'HH24')) <= 19
AND To_number(To_char(begin_interval_time, 'D', 'NLS_DATE_LANGUAGE=AMERICAN')) < 6)),
sub_sys_time_model
AS (SELECT dbid,
snap_id,
instance_number,
SUM(db_time) db_time,
SUM(db_cpu) db_cpu,
SUM(bg_cpu) bg_cpu,
SUM(rm_cpu) rm_cpu
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'DB time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_time,
CASE
WHEN stat_name = 'DB CPU' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END db_cpu,
CASE
WHEN stat_name = 'background cpu time' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END bg_cpu,
CASE
WHEN stat_name = 'RMAN cpu time (backup/restore)' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END rm_cpu
FROM dba_hist_snapshot
natural join dba_hist_sys_time_model
WHERE stat_name IN ( 'DB time', 'DB CPU', 'background cpu time', 'RMAN cpu time (backup/restore)' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_sys_stat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(logons_current) logons_current,
SUM(execute_count) execute_count,
SUM(physical_reads) physical_reads,
SUM(physical_writes) physical_writes,
SUM(redo_writes) redo_writes,
SUM(redo_size) redo_size,
SUM(physical_read_io_requests) physical_read_io_requests,
SUM(physical_write_io_requests) physical_write_io_requests
FROM (SELECT dbid,
snap_id,
instance_number,
CASE
WHEN stat_name = 'logons current' THEN VALUE
ELSE NULL
END logons_current,
CASE
WHEN stat_name = 'execute count' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END execute_count,
CASE
WHEN stat_name = 'physical reads' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_reads,
CASE
WHEN stat_name = 'physical writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_writes,
CASE
WHEN stat_name = 'redo writes' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_writes,
CASE
WHEN stat_name = 'redo size' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END redo_size,
CASE
WHEN stat_name = 'physical read IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_read_io_requests,
CASE
WHEN stat_name = 'physical write IO requests' THEN CASE
WHEN begin_interval_time = startup_time THEN VALUE
ELSE VALUE - Lag(VALUE, 1) over(PARTITION BY stat_id, dbid, instance_number, startup_time ORDER BY snap_id)
END
ELSE NULL
END physical_write_io_requests
FROM dba_hist_snapshot
natural join dba_hist_sysstat
WHERE stat_name IN ( 'logons current', 'execute count', 'physical reads', 'physical writes', 'redo writes', 'redo size', 'physical read IO requests', 'physical write IO requests' ))
GROUP BY dbid,
snap_id,
instance_number),
sub_osstat
AS (SELECT dbid,
snap_id,
instance_number,
MAX(load) load,
SUM(busy_time) busy_time,
SUM(user_time) user_time,
SUM(sys_time) sys_time,
SUM(io_wait_time) io_wait_time,
MAX(cpu) cpu,
MAX(phy_mem) phy_mem,
SUM(avg_sys_time),
SUM(avg_busy_time),
SUM(avg_iowait_time),
SUM(avg_idle_time),
SUM(avg_user_time)
FROM (SELECT e.snap_id,
e.dbid,
e.instance_number,
CASE
WHEN e.stat_name = 'LOAD' THEN e.VALUE
ELSE 0
END load,
CASE
WHEN e.stat_name = 'NUM_CPUS' THEN e.VALUE
ELSE 0
END cpu,
CASE
WHEN e.stat_name = 'AVG_BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_busy_time,
CASE
WHEN e.stat_name = 'AVG_SYS_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_sys_time,
CASE
WHEN e.stat_name = 'AVG_USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_user_time,
CASE
WHEN e.stat_name = 'AVG_IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_iowait_time,
CASE
WHEN e.stat_name = 'AVG_IDLE_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END avg_idle_time,
CASE
WHEN e.stat_name = 'BUSY_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END busy_time,
CASE
WHEN e.stat_name = 'USER_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END user_time,
CASE
WHEN e.stat_name = 'SYSR_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END sys_time,
CASE
WHEN e.stat_name = 'IOWAIT_TIME' THEN CASE
WHEN s.begin_interval_time = s.startup_time THEN e.VALUE
ELSE e.VALUE - Lag(e.VALUE) over(PARTITION BY e.stat_name, e.dbid, e.instance_number, s.startup_time ORDER BY e.snap_id)
END
ELSE 0
END io_wait_time,
CASE
WHEN e.stat_name = 'PHYSICAL_MEMORY_BYTES' THEN e.VALUE
ELSE 0
END phy_mem
FROM dba_hist_osstat e,
dba_hist_snapshot s
WHERE s.snap_id = e.snap_id
AND e.instance_number = s.instance_number
AND e.dbid = s.dbid
AND e.stat_name IN ( 'LOAD', 'AVG_BUSY_TIME', 'AVG_IOWAIT_TIME', 'AVG_USER_TIME',
'AVG_SYS_TIME', 'AVG_IDLE_TIME', 'NUM_CPUS', 'BUSY_TIME',
'PHYSICAL_MEMORY_BYTES', 'USER_TIME', 'SYS_TIME', 'IOWAIT_TIME' ))
GROUP BY dbid,
snap_id,
instance_number)
SELECT snap_id id,
To_char(end_interval_time, 'YYYYMMDD HH24MI') tm,
instance_number inst,
dur,
cpu,
( dur * cpu * 60 ) cap,
( db_time / 1000000 ) dbt,
( db_cpu / 1000000 ) dbc,
( bg_cpu / 1000000 ) bgc,
( rm_cpu / 1000000 ) rman,
( db_time / 1000000 / 60 ) / dur aas,
Round(bg_cpu / 1000000 + db_cpu / 1000000, 2) totora,
Round(load, 2) AS load,
( busy_time ) / 100 AS totos,
( phy_mem / 1024 / 1024 ) AS mem,
physical_reads / ( dur * 60 ) AS iors,
physical_writes / ( dur * 60 ) AS iows,
redo_writes / ( dur * 60 ) AS ioredo,
physical_read_io_requests / ( dur * 60 ) AS iormbs,
- physical_write_IO_requests/(dur*60) as IOWmbs,
- redo_size/(dur*60) as redosizesec,
- logons_current as logons,
- execute_count as exs,
( ( Round(( ( db_cpu ) / 1000000 ) + ( ( bg_cpu ) / 1000000 ), 2) ) / ( dur * 60 * cpu ) ) * 100 AS oracpupct,
( ( Round(Decode(rm_cpu, NULL, 'null',rm_cpu / 1000000), 2) ) / ( dur * 60 * cpu ) ) * 100 AS rmancpupct,
( ( ( busy_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpupct,
( ( user_time / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpuusr,
( ( ( sys_time ) / 100 ) / ( dur * 60 * cpu ) ) * 100 AS oscpusys,
( ( ( io_wait_time ) / 100 ) / ( dur * cpu * 60 ) ) * 100 AS oscpuio
FROM sub_snap
natural join sub_sys_time_model
natural join sub_sys_stat
natural join sub_osstat
ORDER BY id ASC
/

============

 

 @mem1
old  13: WHERE instance_number = &instance_num
new  13: WHERE instance_number = 1
old  14: AND begin_interval_time >= To_date('&start_date', '&date_format')
new  14: AND begin_interval_time >= To_date('2011_1126_0009', 'YYYY_MMDD_HH24MI')

                         i                        ***                                                          ***                 ***
                         n                      Total                                                        Total               Total
       Snap              s       Snap   C         CPU                                                A      Oracle                  OS   Physical
  Snap Start             t        Dur   P        Time            DB        DB        Bg     RMAN     A         CPU      OS         CPU     Memory      IOPs      IOPs      IOPs      IO r      IO w
    ID Time              #        (m)   U         (s)          Time       CPU       CPU      CPU     S         (s)    Load         (s)       (mb)         r         w      redo    (mb)/s    (mb)/s
------ --------------- --- ---------- --- ----------- ------------- --------- --------- -------- ----- ----------- ------- ----------- ---------- --------- --------- --------- --------- ---------
                                                   U    S
                               Oracle RMAN   OS    S    Y    I
       Redo               Exec    CPU  CPU  CPU    R    S    O
     (mb)/s   Sess          /s      %    %    %    %    %    %
----------- ------ ----------- ------ ---- ---- ---- ---- ----
  5864 20111127 0100     1              1                      2.13      1.99     20.20     0.00             22.19    0.00      276.09    2008.28
               -33  -17736.000

  5865 20111127 0200     1      60.03   1     3601.80          1.81      1.88     32.90     0.00   0.0       34.79    0.00      253.48    2008.28     0.017     0.213     0.042     0.016    -0.156
   -476.717    -31   -3444.000      1    0    7    5    0    1

  5866 20111127 0300     1      60.03   1     3601.80          1.89      2.08      5.75     0.00   0.0        7.82    0.11      251.37    2008.28     0.015     0.205     0.041     0.015    -0.150
   -405.729    -31   -3048.000      0    0    7    5    0    1

  5867 20111127 0400     1      60.03   1     3601.80          1.79      1.93      5.69     0.00   0.0        7.61    0.00      250.89    2008.28     0.002     0.200     0.039     0.002    -0.142
   -373.793    -31   -2878.000      0    0    7    5    0    1

  5868 20111127 0500     1      60.03   1     3601.80          1.65      1.84      5.61     0.00   0.0        7.44    0.00      251.16    2008.28     0.001     0.182     0.040     0.001    -0.134
   -382.797    -31   -2803.000      0    0    7    5    0    1

  5869 20111127 0600     1      60.03   1     3601.80        114.08     31.84      6.16     0.00   0.0       38.01    2.07      281.67    2008.28     1.533     0.575     0.095     1.299    -0.271
  -2229.629    -35  -45405.000      1    0    8    6    0    1

  5870 20111127 0700     1      60.03   1     3601.80       2922.71   2370.90      7.13     0.00   0.8     2378.03    0.32     1435.30    2008.28     6.731     1.596     0.176     2.565    -0.871
  -8896.886    -32  -40023.000     66    0   40   38    0    1

  5871 20111127 0800     1      60.03   1     3601.80          4.31      2.81      8.11     0.00   0.0       10.92    0.31      262.49    2008.28     0.187     0.385     0.044     0.178    -0.272
  -2397.647    -33   -7563.000      0    0    7    5    0    2

  5872 20111127 0900     1      60.04   1     3602.40          2.18      1.78      5.87     0.00   0.0        7.64    0.03      255.82    2008.28     0.023     0.231     0.041     0.023    -0.168
   -592.602    -33   -3721.000      0    0    7    5    0    2

  5873 20111127 1000     1      60.03   1     3601.80         40.03     36.49      6.03     0.00   0.0       42.53    0.05      278.79    2008.28     0.033     0.201     0.041     0.032    -0.152
   -532.957    -33   -4260.000      1    0    8    6    0    1

  5874 20111127 1100     1      60.03   1     3601.80        197.02     37.29      6.80     0.00   0.1       44.09    0.00      262.95    2008.28     2.206     0.676     0.085     1.624    -0.439
  -3947.046    -33  -47552.000      1    0    7    5    0    2

  5875 20111127 1200     1      60.03   1     3601.80          1.61      1.53      5.90     0.00   0.0        7.42    0.06      247.03    2008.28     0.023     0.234     0.041     0.022    -0.174
   -568.728    -33   -3356.000      0    0    7    5    0    1

  5876 20111127 1300     1      60.03   1     3601.80          1.72      1.50      6.07     0.00   0.0        7.56    0.15      246.49    2008.28     0.630     0.203     0.041     0.630    -0.149
   -438.576    -32   -3237.000      0    0    7    5    0    1

  5877 20111127 1400     1      60.03   1     3601.80          1.64      1.54      5.81     0.00   0.0        7.35    0.09      243.98    2008.28     0.020     0.193     0.040     0.020    -0.144
   -411.757    -30   -2893.000      0    0    7    5    0    1

  5878 20111127 1500     1      60.03   1     3601.80        134.56     50.66      6.04     0.00   0.0       56.71    0.35      271.03    2008.28     1.312     0.376     0.063     1.217    -0.265
  -1219.445    -30  -39622.000      2    0    8    6    0    2

  5879 20111127 1600     1      60.03   1     3601.80          1.62      1.52      5.81     0.00   0.0        7.33    0.12      246.28    2008.28     0.009     0.205     0.041     0.009    -0.156
   -404.427    -30   -3032.000      0    0    7    5    0    1

  5880 20111127 1700     1      60.03   1     3601.80          1.57      1.52      5.93     0.00   0.0        7.44    0.21      246.90    2008.28     0.009     0.189     0.040     0.009    -0.139
   -429.958    -30   -2939.000      0    0    7    5    0    1

  5881 20111127 1800     1      60.03   1     3601.80          1.60      1.51      5.71     0.00   0.0        7.22    0.21      249.48    2008.28     0.005     0.194     0.040     0.005    -0.142
   -396.104    -30   -2872.000      0    0    7    5    0    1

  5882 20111127 1900     1      59.03   1     3541.80         40.16     24.87      6.24     0.00   0.0       31.11    0.18      261.76    2008.28     1.195     0.385     0.070     1.190    -0.262
  -1435.691    -33  -40145.000      1    0    7    5    0    2

  5887 20111128 0000     1     300.15   1    18009.00          1.44      1.48      6.01     0.00   0.0        7.50    0.01      246.41    2008.28     0.003     0.039     0.008     0.002    -0.030
    -82.805    -32   -3106.000      0    0    1    1    0    0

  5888 20111128 0100     1      60.04   1     3602.40          3.31      1.63     20.80     0.00   0.0       22.43    0.09      263.24    2008.28     3.520     2.446     0.151     2.175    -1.980
 -15881.552    -33  -17739.000      1    0    7    5    0    4

  5889 20111128 0200     1      60.03   1     3601.80          1.47      1.46     32.32     0.00   0.0       33.78    0.28      247.52    2008.28     0.024     0.222     0.042     0.023    -0.162
   -467.332    -31   -3594.000      1    0    7    5    0    1

  5890 20111128 0300     1      60.03   1     3601.80          1.49      1.56      5.68     0.00   0.0        7.25    0.07      242.56    2008.28     0.007     0.206     0.040     0.007    -0.148
   -403.055    -31   -2946.000      0    0    7    5    0    1

  5891 20111128 0400     1      60.03   1     3601.80          1.35      1.46      5.56     0.00   0.0        7.02    0.00      250.56    2008.28     0.003     0.195     0.039     0.003    -0.139
   -393.477    -31   -2820.000      0    0    7    5    0    1

  5892 20111128 0500     1      60.03   1     3601.80          1.50      1.49      5.64     0.00   0.0        7.14    0.02      251.27    2008.28     0.002     0.189     0.041     0.002    -0.140
   -377.975    -31   -2801.000      0    0    7    5    0    1

  5893 20111128 0600     1      60.03   1     3601.80          1.40      1.46      5.55     0.00   0.0        7.02    0.13      242.30    2008.28     0.002     0.186     0.039     0.002    -0.138
   -410.680    -31   -2802.000      0    0    7    5    0    1

  5894 20111128 0700     1      60.03   1     3601.80          1.50      1.52      5.54     0.00   0.0        7.06    0.27      242.49    2008.28     0.002     0.191     0.041     0.002    -0.148
   -394.106    -31   -2851.000      0    0    7    5    0    1

  5895 20111128 0800     1      60.04   1     3602.40          1.56      1.58      5.70     0.00   0.0        7.28    0.14      242.87    2008.28     0.005     0.185     0.041     0.005    -0.141
   -406.256    -32   -2813.000      0    0    7    5    0    1

  5896 20111128 0900     1      60.03   1     3601.80          1.36      1.39      5.56     0.00   0.0        6.95    0.05      242.45    2008.28     0.002     0.184     0.039     0.002    -0.136
   -409.270    -31   -2783.000      0    0    7    5    0    1

  5897 20111128 1000     1      60.03   1     3601.80          2.34      1.51      5.76     0.00   0.0        7.27    0.30      332.63    2008.28     0.003     0.188     0.039     0.003    -0.146
   -391.966    -31   -2797.000      0    0    9    7    0    3

  5898 20111128 1100     1      60.13   1     3607.80        107.21      9.39      6.31     0.00   0.0       15.70    0.30     1137.09    2008.28     1.731     0.196     0.053     1.730    -0.151
   -556.871    -31   -3629.000      0    0   32   27    0   10


31 rows selected.

 

 

 

 

Oracle Primary Key - Foreign Key Report

---

 

 

Primary Key - Foreign Key Report
pk fk Report On user




UNDEF ENTER_OWNER_NAME
COLUMN A_OWNER            FORMAT A15      HEADING 'Primary|Table|Owner'
COLUMN B_OWNER            FORMAT A15      HEADING 'For|Table|Owner'
COLUMN TABLE_NAME         FORMAT A15      HEADING 'Table Name'
COLUMN COLUMN_NAME        FORMAT A15      HEADING 'Column Name'
REM
SET LINES 132 pages 66 feedback off verify off
TTITLE "Primary Key - Foreign Key Report"
REM
WITH reference_view AS
     (SELECT a.owner, a.table_name, a.constraint_name, a.constraint_type,
             a.r_owner, a.r_constraint_name, b.column_name
        FROM dba_constraints a, dba_cons_columns b
       WHERE a.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
         AND a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND constraint_type = 'R'),
     constraint_view AS
     (SELECT a.owner a_owner, a.table_name, a.column_name, b.owner b_owner,
             b.constraint_name
        FROM dba_cons_columns a, dba_constraints b
       WHERE a.owner = b.owner
         AND a.constraint_name = b.constraint_name
         AND b.constraint_type = 'P'
         AND a.owner LIKE UPPER ('&&ENTER_OWNER_NAME'))
SELECT CV.a_owner a_owner, CV.table_name, rv.column_name, rv.owner b_owner,
       rv.table_name, rv.column_name
  FROM reference_view rv, constraint_view CV
 WHERE rv.r_constraint_name = CV.constraint_name AND rv.r_owner = CV.b_owner;




Primary Key - Foreign Key Report

Primary                                         For
Table                                           Table
Owner           Table Name      Column Name     Owner           Table Name      Column Name
--------------- --------------- --------------- --------------- --------------- ---------------
SCOTT           ORDERS          ORDER_ID        SCOTT           ORDER_LINES     ORDER_ID