Size of Oracle database

This summary is not available. Please click here to view the post.

Oracle 15 bigest / Largest Objects

Oracle 15 Largest Objects
Oracle 15 bigest Objects
Oracle bigest Objects


col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner, segment_name , segment_type,Mb from ( select owner
, segment_name
, segment_type
, bytes/1024/1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 16



OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ------------------------------ --------------- ------------
SYS IDL_UB1$ TABLE 264
GGATE GGS_DDL_HIST TABLE 232
SYS SOURCE$ TABLE 96
GGATE GGS_MARKER TABLE 96
XDB SYS_LOB0000056506C00025$$ LOBSEGMENT 57
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 46
SYS IDL_UB2$ TABLE 35
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 29
MDSYS SYS_LOB0000062569C00006$$ LOBSEGMENT 26
SYS C_TOID_VERSION# CLUSTER 23
SYS I_WRI$_OPTSTAT_H_ST INDEX 21
SYS SYS_LOB0000064046C00004$$ LOBSEGMENT 20
SYS C_OBJ#_INTCOL# CLUSTER 20
SYS WRH$_SYSMETRIC_HISTORY TABLE 15
SYS ARGUMENT$ TABLE 14

15 rows selected.

Oracle Oracle OS watcher (OSWatcher) tips from metalink 301137.1

Oracle OS watcher (OSWatcher) tips from metalink 301137.1

oswhacher

OS Watcher by Carl Davis Center of Expertise May 31, 2011


tar xvf osw302.tar

-bash-3.2$ pwd
/aptus/oracle/home/osw



./startOSW.sh
./startOSW.sh 60 10 &



nohup ./startOSW.sh &


nohup /aptus/oracle/home/osw/startOSW.sh & > /tmp/oswatcher.log


/aptus/oracle/home/osw
-bash-3.2$ nohup ./startOSW.sh & ---- for default Value


-bash-3.2$ ps -ef|grep OS


oracle 7232 1 0 09:20 pts/1 00:00:00 /usr/bin/ksh ./OSWatcher.sh
oracle 7272 7232 0 09:20 pts/1 00:00:00 /usr/bin/ksh ./OSWatcherFM.sh 48



-bash-3.2$ cat nohup.out


Info...You did not enter a value for snapshotInterval.
Info...Using default value = 30
Info...You did not enter a value for archiveInterval.
Info...Using default value = 48

Testing for discovery of OS Utilities...

VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
NETSTAT found on your system.
TOP found on your system.

Discovery completed.

Starting OSWatcher v3.0.2 on Fri Sep 2 09:20:20 BST 2011
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise, Oracle Corporation

Starting Data Collection...

osw heartbeat:Fri Sep 2 09:20:20 BST 2011


====


to stop


./stopOSW.sh


-bash-3.2$ ls -ld */
drwxr-xr-x 11 oracle dba 4096 Sep 2 09:20 archive/
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:29 locks/

-bash-3.2$ pwd
/apt/oracle/home/osw/archive

-bash-3.2$ ls -ltr
total 72
drwxr-xr-x 2 oracle dba 4096 Sep 2 09:20 oswprvtnet
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswvmstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswtop
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswslabinfo
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswps
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswnetstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswmpstat
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswmeminfo
drwxr-xr-x 2 oracle dba 4096 Sep 3 18:00 oswiostat

Oracle session report (waits CPU used by this session etc .. )

set linesize 300 pagesize 200 
column statname for a35 heading 'statistic' truncate 
column value    for 999,999,999,999 heading 'value' 
col sid_serial  for a20 
col kill        for a15
SELECT 
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,ss.username username, sn.name statname, st.value value ,sql_id FROM gv$sesstat st, v$statname sn, gv$session ss 
WHERE 
--st.sid = user_sid -- AND 
sn.name IN ('CPU used by this session', 'OS Wait-cpu (latency) time','session logical reads', 'physical reads') 
AND sn.statistic# = st.statistic# 
and ss.sid = st.sid and ss.inst_id = st.inst_id
and ss.USERNAME is not null 
and ss.sql_id is not null
ORDER BY value DESC ;

===

set pages 1000 lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a23
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
col EVENT for a27
select * from (
select p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.con_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.MACHINE,ss.status,ss.BLOCKING_SESSION blk_sid,ss.BLOCKING_SESSION_STATUS blk_status,
se.VALUE/100 cpu_usage_sec,ss.event
from
v$session ss,
v$sesstat se,
v$statname sn,
v$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
--and ss.username !='SYS' 
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

SQL Wise CPU Usage

col cpu_usage_sec form 99990 heading "CPU in Seconds"
select * from (
select
(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from v$session ss,v$sesstat se,v$statname sn, v$process p, v$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);



set lines 1000
col OSPID for a06
col SID for 99999
col SERIAL# for 999999
col SQL_ID for a14
col USERNAME for a15
col PROGRAM for a20
col MODULE for a18
col OSUSER for a10
col MACHINE for a25
col kill for a17
select * from (
select 
--ss.inst_id,
''''||ss.sid ||','|| ss.serial#||',@'||ss.inst_id ||'''' kill,
p.spid "ospid",
(se.SID),ss.serial#,ss.SQL_ID,ss.username,ss.program,ss.module,ss.osuser,ss.MACHINE,ss.status,
se.VALUE/100 cpu_usage_seconds
from
gv$session ss,
gv$sesstat se,
gv$statname sn,
gv$process p
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
--and ss.username !='SYS' 
--and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc)
where rownum <16;




set pages 1000 lines 1000
col ospid 		for a06
col sid 		for 99999
col serial# 	for 999999
col sql_id 		for a14
col username 	for a15
col program 	for a23
col module 		for a18
col osuser 		for a10
col machine 	for a25
select * from (
select p.spid "ospid",
(se.sid),ss.serial#,ss.sql_id,ss.username,substr(ss.program,1,22) "program",ss.module,ss.osuser,ss.machine,ss.status,ss.blocking_session blk_sid,ss.blocking_session_status blk_status,
se.value/100 cpu_usage_sec,ss.event
from
v$session ss,v$sesstat se,v$statname sn,v$process p
where 1=1
and se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.SID = ss.SID
-- and ss.username !='SYS' 
--and ss.status='ACTIVE'
--and ss.username is not null
and ss.paddr=p.addr 
and value > 0
order by se.value desc)
;

set pages 500 lines 500
col sid for a05
col serial for a06
col program for a15
col event for a28
col username for a15
col sql_text for a65
select substr(v2.sid,1,5) "SID",substr(v2.serial#,1,5) "SERIAL",substr(v2.username,1,15) "Username"
,substr(program,1,45) "Program",v2.event event,v1.sql_id,sql_text 
from gv$sqlarea v1,gv$session v2 
where v1.address= v2.sql_address 
and v1.hash_value = v2.sql_hash_value 
and v1.inst_id = v2.inst_id
order by 1 asc
;

set pages 100

set lines 1000

col SPID heading 'PID' for a06

col SID for 99999

col SERIAL# heading 'serial' for 999999

col SQL_ID for a14

col USERNAME for a10

col PROGRAM for a20

col MODULE for a18

col OSUSER for a07

col MACHINE for a20

select p.spid,s.sid,s.serial#,s.sql_id,s.username,s.status,s.program,s.module,s.osuser,s.machine,s.event

from gv$session s, gv$process p 

where s.paddr=p.addr 

and p.spid=&spid;

               

====

SET LINESIZE 200
	SET PAGESIZE 200
	COLUMN metric_name FORMAT a25
	COLUMN metric_unit FORMAT a20
	COLUMN awr_cpu_usage FORMAT a13
	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) || '%' awr_cpu_usage
	FROM dba_hist_sysmetric_summary
	WHERE metric_name = 'Host CPU Utilization (%)'
	ORDER BY instance_number
	, first_snap_id
	)
	WHERE first_snap_id <> 0
	; INSTANCE_NUMBER FIRST_SNAP_ID SECOND_SNAP_ID BEGIN_TIME END_TIME METRIC_NAME METRIC_UNIT AWR_CPU_USAGE --------------- ------------- -------------- ------------------- ------------------- ------------------------- -------------------- ------------- 1 2569 2570 2023-04-22 06:59:41 2023-04-22 07:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 5.66% 1 2570 2571 2023-04-22 07:59:40 2023-04-22 08:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.86% 1 2571 2572 2023-04-22 08:59:40 2023-04-22 10:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 3.99% 1 2572 2573 2023-04-22 10:00:41 2023-04-22 11:00:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.24% 1 2573 2574 2023-04-22 11:00:41 2023-04-22 11:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.36% 1 2574 2575 2023-04-22 11:59:41 2023-04-22 12:59:41 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.7% 1 2575 2576 2023-04-22 12:59:41 2023-04-22 13:59:40 Host CPU Utilization (%) % Busy/(Idle+Busy) 4.93% 204 rows selected.

=====
col kill for a15
col CLIENT_INFO for a20
col USERNAME  for a20 
select * from
(
select username,''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,sid,sql_id,prev_sql_id,
       round((cpu_usage/(
                        select sum(value) total_cpu_usage
                          from gv$sesstat t
                         inner join gv$session  s on ( t.sid = s.sid )
                         inner join gv$statname n on ( t.statistic# = n.statistic# )
                         where n.name like '%CPU used by this session%'
                           and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
                        ))*100,2) cpu_usage_per_cent,
       module_info,client_info 
  from
(
select nvl(s.username,'Oracle Internal Proc.') username,s.sid,s.serial#,s.inst_id,t.value cpu_usage, nvl(s.module, s.program) module_info, decode(s.osuser,'oracle', s.client_info, s.osuser) client_info,
sql_id,prev_sql_id
  from gv$sesstat t
       inner join gv$session  s on ( t.sid = s.sid )
       inner join gv$statname n on ( t.statistic# = n.statistic# )
 where n.name like '%CPU used by this session%'
   and nvl(s.sql_exec_start, s.prev_exec_start) >= sysdate-1/24
) s1
)
order by cpu_usage_per_cent desc;

===

set linesize 500
col sql_text for a80
col cpu_usage_sec form 99990 heading "CPU in Seconds"
select * from (
select
(se.SID),q.sql_id,substr(q.sql_text,80) sql_text,ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from v$session ss,v$sesstat se,
v$statname sn, v$process p, v$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
--and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);

=======

set linesize 300
col day for a11
select
to_char(begin_time,'DD-MON-YY') Day,
round(max(decode(to_char(begin_time,'HH24'),'00',maxval,NULL)),2) "00",
round(max(decode(to_char(begin_time,'HH24'),'01',maxval,NULL)),2) "01",
round(max(decode(to_char(begin_time,'HH24'),'02',maxval,NULL)),2) "02",
round(max(decode(to_char(begin_time,'HH24'),'03',maxval,NULL)),2) "03",
round(max(decode(to_char(begin_time,'HH24'),'04',maxval,NULL)),2) "04",
round(max(decode(to_char(begin_time,'HH24'),'05',maxval,NULL)),2) "05",
round(max(decode(to_char(begin_time,'HH24'),'06',maxval,NULL)),2) "06",
round(max(decode(to_char(begin_time,'HH24'),'07',maxval,NULL)),2) "07",
round(max(decode(to_char(begin_time,'HH24'),'08',maxval,NULL)),2) "08",
round(max(decode(to_char(begin_time,'HH24'),'09',maxval,NULL)),2) "09",
round(max(decode(to_char(begin_time,'HH24'),'10',maxval,NULL)),2) "10",
round(max(decode(to_char(begin_time,'HH24'),'11',maxval,NULL)),2) "11",
round(max(decode(to_char(begin_time,'HH24'),'12',maxval,NULL)),2) "12",
round(max(decode(to_char(begin_time,'HH24'),'13',maxval,NULL)),2) "13",
round(max(decode(to_char(begin_time,'HH24'),'14',maxval,NULL)),2) "14",
round(max(decode(to_char(begin_time,'HH24'),'15',maxval,NULL)),2) "15",
round(max(decode(to_char(begin_time,'HH24'),'16',maxval,NULL)),2) "16",
round(max(decode(to_char(begin_time,'HH24'),'17',maxval,NULL)),2) "17",
round(max(decode(to_char(begin_time,'HH24'),'18',maxval,NULL)),2) "18",
round(max(decode(to_char(begin_time,'HH24'),'19',maxval,NULL)),2) "19",
round(max(decode(to_char(begin_time,'HH24'),'20',maxval,NULL)),2) "20",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "21",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "22",
round(max(decode(to_char(begin_time,'HH24'),'21',maxval,NULL)),2) "23"
from     dba_hist_sysmetric_SUMMARY A 
where BEGIN_TIME > sysdate - 7
and metric_name like 'Host CPU Utilization%'
--and  A.METRIC_NAME in('Average Active Sessions') 
group by to_char(begin_time,'DD-MON-YY')
order by 1
;





DAY                 00         01         02         03         04         05         06         07         08         09         10         11      12         13         14         15         16         17         18         19         20         21         22         23
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
07-JUL-23                                                                                                 33.5      40.53      47.96      45.15      43.84   43.65      41.18      39.09      38.59      38.64       31.5      47.73      49.27      54.92      54.92      54.92
08-JUL-23        25.24      16.35      17.24      19.12      18.15        100      20.54      19.42      23.73      25.46      31.01      31.27      29.57   33.55       30.2      30.01      26.34      28.52      29.12      40.59      53.57      43.98      43.98      43.98

https://github.com/khailey-zz/ashmasters/commit/2963230a03e8b8fbc8b288e8829c08066206907d

set linesize 400

with AASIO as (
           select
              class, sum(AAS) AAS, begin_time, end_time
              from (
                 select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))  AAS,
                 -- normalize the begin/end times between instances
                 -- not perfect but I think close enough for general trends
                 -- should never be more than 2 minutes variance assuming 3+ instances
                 -- 1 minute variance for 2 instances
                 min(BEGIN_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) begin_time ,
                 min(END_TIME) over (partition by decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')) end_time
           from  gv$waitclassmetric  m,
                 gv$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
                                 and n.inst_id = m.inst_id
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait'), BEGIN_TIME, END_TIME
           order by begin_time
         )
         group by class, begin_time, end_time
),
CORES as (
   select cpu_core_count from dba_cpu_usage_statistics where timestamp= (select max(timestamp) from dba_cpu_usage_statistics)
),
AASSTAT as (
             select class, aas, begin_time, end_time from aasio
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(sum(value)/100,3)                                     AAS,
                 min(BEGIN_TIME) begin_time,
                 max(END_TIME) end_time
             from gv$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
            group by metric_name
          union
           select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS,
                 BEGIN_TIME ,
                 END_TIME
            from
              ( select sum(value) busy, min(BEGIN_TIME) begin_time,max(END_TIME) end_time from gv$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select sum(value) cpu_count from gv$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS,
               cast(min(SAMPLE_TIME) as date) BEGIN_TIME ,
               cast(max(SAMPLE_TIME) as date) END_TIME
             from gv$active_session_history ash
              where SAMPLE_TIME >= (select min(BEGIN_TIME) begin_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
               and SAMPLE_TIME < (select max(END_TIME) end_time from gv$sysmetric where metric_name='CPU Usage Per Sec' and group_id=2 )
)
select
		 sysdate timestamp,
       to_char(BEGIN_TIME,'YYYY-MM-DD HH24:MI:SS') BEGIN_TIME,
       to_char(END_TIME,'YYYY-MM-DD HH24:MI:SS') END_TIME,
       cpu.cpu_core_count cores,
       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
       CPU_ORA_CONSUMED +
        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
       CPU_ORA_CONSUMED CPU_ORA,
       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
       COMMIT,
       READIO,
       WAIT
from (
select
       min(BEGIN_TIME) BEGIN_TIME,
       max(END_TIME) END_TIME,
       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,
       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,
       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,
       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,
       sum(decode(CLASS,'Wait'            ,AAS,0)) WAIT
from AASSTAT
)
, cores cpu
/

Oracle wait report

wait.sql

Author : Daniel W. Fink

Oracle Response Time report


SET LINESIZE 132 PAGESIZE 100 FEEDBACK OFF
COLUMN time_sum NOPRINT
COLUMN total_time NOPRINT
COLUMN wait_class FORMAT A15 HEADING 'Class'
COLUMN event FORMAT A40 HEADING 'Event'
COLUMN wait_seconds FORMAT 999,999,999,999.99 HEADING 'Wait Seconds'
COLUMN wait_pct FORMAT 99.999999 HEADING '% of T' NOPRINT
COLUMN nonidle_pct FORMAT 99.999999 HEADING '% of R'

BREAK ON wait_class SKIP PAGE
COMPUTE SUM OF wait_seconds nonidle_pct ON wait_class

TTITLE LEFT "*** Response Time Components by Class ***"

SELECT vswc.time_waited time_sum,
ven.wait_class,
vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
instime.time_waited total_time,
(ROUND((vse.time_waited_micro/1000000),6)/(instime.time_waited/100))*100 wait_pct,
TO_NUMBER(DECODE(vswc.wait_class, 'Idle', NULL, 'Network', NULL,
(ROUND((vse.time_waited_micro/1000000),6)/
((instime.time_waited - idletime.time_waited)/100))*100)) nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
UNION
SELECT vss.value time_sum,
'CPU Utilization' wait_class,
NULL,
vss.value/100 wait_seconds,
instime.time_waited total_time,
(ROUND((vss.value/100),6)/(instime.time_waited/100))*100 wait_pct,
((ROUND((vss.value/100),6)/((instime.time_waited - idletime.time_waited)/100))*100) nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) instime,
(SELECT SUM(time_waited) time_waited
FROM v$system_wait_class
WHERE wait_class IN ('Idle', 'Network')) idletime
WHERE vss.name = 'CPU used when call started'
ORDER BY time_sum, wait_class, wait_seconds DESC
/

CLEAR BREAK
CLEAR COMPUTE

TTITLE LEFT "*** Response Time Components by Time ***"

SELECT vse.event,
ROUND((vse.time_waited_micro/1000000),4) wait_seconds,
(ROUND((vse.time_waited_micro/1000000),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$system_event vse,
v$event_name ven,
v$system_wait_class vswc,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE ven.event_id = vse.event_id
AND vswc.wait_class = ven.wait_class
AND vswc.wait_class NOT IN ('Idle', 'Network')
UNION
SELECT 'CPU Utilization' wait_class,
vss.value/100 wait_seconds,
(ROUND((vss.value/100),6)/((nonidletime.time_waited)/100))*100 nonidle_pct
FROM v$sysstat vss,
(SELECT SUM(time_waited) time_waited
FROM (SELECT time_waited
FROM v$system_wait_class
WHERE wait_class NOT IN ('Idle', 'Network')
UNION
SELECT value
FROM v$sysstat
WHERE name = 'CPU used when call started')) nonidletime
WHERE vss.name = 'CPU used when call started'
ORDER BY wait_seconds DESC
/



*** Response Time Components by Time ***
Event Wait Seconds % of R
---------------------------------------- ------------------- ----------
control file parallel write 34,523.56 29.613725
latch: shared pool 18,121.77 15.544544
CPU Utilization 14,916.65 12.795248
db file async I/O submit 10,522.31 9.025855
os thread startup 9,933.96 8.521180
db file sequential read 7,489.39 6.424267
log file parallel write 7,079.45 6.072632
RMAN backup & recovery I/O 4,572.45 3.922174
log file sync 2,991.17 2.565779

Oracle awr matrix report

awr snapshot report 
awr snapshot matrix report


snap info !!!!!



Author      : Daniel W. Fink 


awr_snap_wkly_matrix.sql


COLUMN sort_ord                        NOPRINT
COLUMN snap_week                       NOPRINT

DEFINE awr_dbid = &1
DEFINE awr_instid = &2

SET HEADING off PAGESIZE 0
TTITLE off

PROMPT *** Snapshots in AWR repository for Database &usr_db_name Instance &usr_inst_name
PROMPT *** Weekly by hour (intermediate snapshots not listed)

SELECT     DISTINCT 001 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	         hour_of_day
         , 'Sunday'	 sunday_snapid
         , 'Monday'	 monday_snapid
         , 'Tuesday'	 tuesday_snapid
         , 'Wednesday'	 wednesday_snapid
         , 'Thursday'	 thursday_snapid
         , 'Friday'	 friday_snapid
         , 'Saturday'	 saturday_snapid
FROM       sys.wrm$_snapshot
UNION ALL
SELECT     010 sort_ord
         , TO_CHAR(s.first_sunday, 'YYYYMMDD')	snap_week
         , NULL hour_of_day
         , TO_CHAR(s.first_sunday,   'MM/DD/YY') sunday_snapid
         , TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
         , TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
         , TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
         , TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
         , TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
         , TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM       ( SELECT     NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
             FROM       sys.wrm$_snapshot
             WHERE      dbid = &awr_dbid
               AND      instance_number = &awr_instid
           ) s
UNION ALL
SELECT     011 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL hour_of_day
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
         , MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING     MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT     DISTINCT 020 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , '---------'   hour_of_day
         , '---------'	 sunday_snapid
         , '---------'	 monday_snapid
         , '---------'	 tuesday_snapid
         , '---------'	 wednesday_snapid
         , '---------'	 thursday_snapid
         , '---------'	 friday_snapid
         , '---------'	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
UNION ALL
SELECT     030 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
         , TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
GROUP BY   TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
         , TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT     DISTINCT 999 sort_ord
         , TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
         , NULL	 hour_of_day
         , NULL	 sunday_snapid
         , NULL	 monday_snapid
         , NULL	 tuesday_snapid
         , NULL	 wednesday_snapid
         , NULL	 thursday_snapid
         , NULL	 friday_snapid
         , NULL	 saturday_snapid
FROM       sys.wrm$_snapshot
WHERE      dbid = &awr_dbid
  AND      instance_number = &awr_instid
ORDER BY   snap_week
         , sort_ord
         , hour_of_day
/

SET HEADING ON






apt-rdbms-01.aptus.co.uk:APTDB\sys> @awr_snap_wkly_matrix.sql
*** Snapshots in AWR repository for Database APTDB Instance aptdb
*** Weekly by hour (intermediate snapshots not listed)
          Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
          08/21/11  08/22/11  08/23/11  08/24/11  08/25/11  08/26/11  08/27/11
--------- --------- --------- --------- --------- --------- --------- ---------
00:00                                      1920      1944      1968
01:00                            1897      1921      1945      1969
02:00                            1898      1922      1946      1970
03:00                            1899      1923      1947      1971
04:00                            1900      1924      1948      1972
05:00                            1901      1925      1949      1973
06:00                            1902      1926      1950      1974
07:00                            1903      1927      1951      1975
08:00                            1904      1928      1952      1976
09:00                            1905      1929      1953      1977
10:00                            1906      1930      1954      1978
11:00                            1907      1931      1955      1979
12:00                            1908      1932      1956      1980
13:00                            1909      1933      1957      1981
14:00                            1910      1934      1958      1982
15:00                            1911      1935      1959      1983
16:00                            1912      1936      1960      1984
17:00                            1913      1937      1961      1985
18:00                            1914      1938      1962      1986
19:00                            1915      1939      1963      1987
20:00                            1916      1940      1964      1988
21:00                            1917      1941      1965      1989
22:00                            1918      1942      1966      1990
23:00                            1919      1943      1967      1991

  =========
  
 
SET HEADING off PAGESIZE 0 linesize 200
COLUMN sort_ord NOPRINT
SELECT DISTINCT 001 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, 'Sunday' sunday_snapid
, 'Monday' monday_snapid
, 'Tuesday' tuesday_snapid
, 'Wednesday' wednesday_snapid
, 'Thursday' thursday_snapid
, 'Friday' friday_snapid
, 'Saturday' saturday_snapid
FROM sys.wrm$_snapshot
UNION ALL
SELECT 010 sort_ord
, TO_CHAR(s.first_sunday, 'YYYYMMDD') snap_week
, NULL hour_of_day
, TO_CHAR(s.first_sunday, 'MM/DD/YY') sunday_snapid
, TO_CHAR(s.first_sunday+1, 'MM/DD/YY') monday_snapid
, TO_CHAR(s.first_sunday+2, 'MM/DD/YY') tuesday_snapid
, TO_CHAR(s.first_sunday+3, 'MM/DD/YY') wednesday_snapid
, TO_CHAR(s.first_sunday+4, 'MM/DD/YY') thursday_snapid
, TO_CHAR(s.first_sunday+5, 'MM/DD/YY') friday_snapid
, TO_CHAR(s.first_sunday+6, 'MM/DD/YY') saturday_snapid
FROM ( SELECT NEXT_DAY(MIN(end_interval_time) - 7, 'SUNDAY') first_sunday
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
) s
UNION ALL
SELECT 011 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) sunday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),2,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) monday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),3,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) tuesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),4,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) wednesday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),5,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) thursday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),6,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) friday_of_week
, MAX(DECODE(TO_CHAR(end_interval_time, 'D'),7,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD')
HAVING MAX(DECODE(TO_CHAR(end_interval_time, 'D'),1,TO_CHAR(end_interval_time, 'MM/DD/YY'),NULL)) IS NOT NULL
UNION ALL
SELECT DISTINCT 020 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, '---------' hour_of_day
, '---------' sunday_snapid
, '---------' monday_snapid
, '---------' tuesday_snapid
, '---------' wednesday_snapid
, '---------' thursday_snapid
, '---------' friday_snapid
, '---------' saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
UNION ALL
SELECT 030 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, TO_CHAR(end_interval_time, 'hh24')||':00' hour_of_day
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),1,snap_id,NULL)),'999999') sunday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),2,snap_id,NULL)),'999999') monday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),3,snap_id,NULL)),'999999') tuesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),4,snap_id,NULL)),'999999') wednesday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),5,snap_id,NULL)),'999999') thursday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),6,snap_id,NULL)),'999999') friday_of_week
, TO_CHAR(MIN(DECODE(TO_CHAR(end_interval_time, 'D'),7,snap_id,NULL)),'999999') saturday_of_week
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE')
GROUP BY TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD'), TO_CHAR(end_interval_time, 'hh24')||':00'
UNION ALL
SELECT DISTINCT 999 sort_ord
, TO_CHAR(NEXT_DAY(end_interval_time - 7, 'SUNDAY'), 'YYYYMMDD') snap_week
, NULL hour_of_day
, NULL sunday_snapid
, NULL monday_snapid
, NULL tuesday_snapid
, NULL wednesday_snapid
, NULL thursday_snapid
, NULL friday_snapid
, NULL saturday_snapid
FROM sys.wrm$_snapshot
WHERE dbid = (select DBID from v$database)
AND instance_number = sys_context ('userenv','INSTANCE') 
ORDER BY snap_week, sort_ord , hour_of_day
/



20211003           Sunday    Monday    Tuesday   Wednesday Thursday  Friday    Saturday
20211003           10/03/21  10/04/21  10/05/21  10/06/21  10/07/21  10/08/21  10/09/21
20211003 --------- --------- --------- --------- --------- --------- --------- ---------
20211003 00:00                                     36504     36528     36552     36576
20211003 01:00                                     36505     36529     36553     36577
20211003 02:00                                     36506     36530     36554     36578