Monday, April 5, 2021

Oracle top sessions -- Cpu / Memory / IO

 

We have normally seen  Dba struggling at time of issues to find queries to  check top resoruce consuming sessions . Hence sharing  respective sql below for quick pick . 



#############################

Top Cpu using Sessions : 


set pages 1000
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 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,
se.VALUE/100 cpu_usage_sec
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);



SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000 
SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
/



#############################

Top Memory Using Sessions : 



(TOP PGA SESSION ) 

set lines 300 pages 1000
col logon form a18
col osuser for a30
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$session.SERIAL#, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
STATUS, OSUSER, v$session.PROGRAM, MODULE
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
and v$session.status='ACTIVE'
ORDER BY pga_used_mem DESC ;


SET LINESIZE 300
SET PAGESIZE 9999
COLUMN sid                     FORMAT 999            HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a8             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'
SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory')        session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max')    session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory')        session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max')    session_uga_memory_max
FROM
    v$session s  where s.username is not null and s.username not in ('PERFSTAT','SYS') 
ORDER BY session_pga_memory DESC
/


SET LINESIZE 300
SET PAGESIZE 9999
SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory')        session_pga_memory
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max')    session_pga_memory_max
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory')        session_uga_memory
  , (select round(ss.value/1024/1024, 2) from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max')    session_uga_memory_max
FROM
    v$session  s
ORDER BY session_pga_memory DESC
;




PGA :

spool output.txt

set linesize 120
set pagesize 120
column spid heading 'OSpid' format a8
column pid heading 'Orapid' format 999999
column sid heading 'Sess id' format 99999
column serial# heading 'Serial#' format 999999
column status heading 'Status' format a8
column pga_alloc_mem heading 'PGA alloc'
column username heading 'oracleuser' format a12
column osuser heading 'OS user' format a12
column program heading 'Program' format a28

SELECT p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,s.username,s.osuser,s.program
FROM v$process p,v$session s WHERE s.paddr ( + ) = p.addr Order by p.pga_alloc_mem desc;

select substr(name,1,30), value, unit from v$pgastat;

select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');

spool off




To see top consumes at the OS level that are currently:
Oracle Linux: How to Calculate Memory Usage ( Doc ID 1630754.1 )
Check 10 Top processes which are consuming.



In ASH we might see the PGA utilization for the sampled sessions

sqlplus / as sysdba

set lines 2000
set pages 1000
set numw 30
col sample_time form a25
col program form a35 trunc
col event form a45
col instance_number form 9
spool wait_detail_time.lst
select SAMPLE_TIME,INSTANCE_NUMBER, SESSION_ID, SESSION_SERIAL#, SESSION_STATE, USER_ID, PROGRAM, SQL_ID, SQL_PLAN_HASH_VALUE, TIME_WAITED,
EVENT,SEQ#, BLOCKING_INST_ID, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_OBJECT_ID,
PLSQL_SUBPROGRAM_ID, TOP_LEVEL_SQL_ID, QC_INSTANCE_ID, QC_SESSION_ID, QC_SESSION_SERIAL#, MODULE, ACTION, CLIENT_ID, PGA_ALLOCATED/1024/1024 PGAMB, TEMP_SPACE_ALLOCATED/1024/1024 TEMPMB
from DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time between to_date('07/11/22 03:55','DD/MM/YY HH24:MI') and to_date('07/11/22 04:15','DD/MM/YY HH24:MI')
order by SAMPLE_TIME;

spool off






To see PGA history allocation and SGA

spool pga_sga_hist.out
alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
set lines 131 pages 1000
select SN.INSTANCE_NUMBER,trunc(SN.END_INTERVAL_TIME,'mi') time, sga.allo sga_gb, pga.allo pga_gb,(sga.allo+pga.allo) tot_gb
from (select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024,2) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga,
(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024,2) allo
from DBA_HIST_PGASTAT
where name= 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga,
dba_hist_snapshot sn
where sn.snap_id=sga.snap_id and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER and sn.snap_id=pga.snap_id and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
order by sn.instance_number,sn.snap_id;
spool off





To see current PGA utilization

spool current_pga.out
set lines 300 pages 1000
SELECT INST_ID,pid, spid, PROGRAM, ceil(PGA_USED_MEM/1024/1024) "PGA_USED_MEM(MB)", ceil(PGA_ALLOC_MEM/1024/1024) "PGA_ALLOC_MEM(MB)",ceil(PGA_FREEABLE_MEM/1024/1024) "PGA_FREEABLE_MEM(MB)", ceil(PGA_MAX_MEM/1024/1024) "PGA_MAX_MEM(MB)"
FROM V$PROCESS order by 4 desc   first 20 rows only ;
spool off


set line  999 
col name format a30
select s.sid,
                                 s2.serial#,
                                 n.name,
                                 s.value, s2.program , s2.status ,  s2.last_call_et, 
                                 decode(s2.username,null,s2.program,s2.username) "USERNAME",
                                 s2.server,
                                 s2.logon_time
                          from   v$statname n,
                                 v$sesstat s,
                                 v$session s2
                          where  n.statistic# = s.statistic# and
                                 (s.sid = s2.sid) and
                                 name like 'session%memory max%'  order by 4 desc fetch first 20 rows only ;

SELECT name, round(((value/1000)/1024),2) as value_mb, round((((value/1000)/1024)/1024),2) as value_gb FROM V$PGASTAT;


#run in cdb 
WITH
   MAX_PGA as
     (select round(value/1024/1024,1) max_pga from v$pgastat where name='maximum PGA allocated'),
   MGA_CURR as
     (select round(value/1024/1024,1) mga_curr from v$pgastat where name='MGA allocated (under PGA)'),
   MAX_UTIL as
     (select max_utilization as max_util from v$resource_limit where resource_name='processes')
SELECT
   a.max_pga "Max PGA (MB)",
   b.mga_curr "Current MGA (MB)",
   c.max_util "Max # of processes",
   round(((a.max_pga - b.mga_curr) + (c.max_util * 5)) * 1.1, 1) "New PGA_AGGREGATE_LIMIT (MB)"
FROM MAX_PGA a, MGA_CURR b, MAX_UTIL c
WHERE 1 = 1;




How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)



 Find which process is continue to consume more and more memory. This can be found by using the following query:
 

COLUMN alme     HEADING "Allocated MB" FORMAT 99999D9
COLUMN usme     HEADING "Used MB"      FORMAT 99999D9
COLUMN frme     HEADING "Freeable MB"  FORMAT 99999D9
COLUMN mame     HEADING "Max MB"       FORMAT 99999D9
COLUMN username                        FORMAT a15
COLUMN program                         FORMAT a22
COLUMN sid                             FORMAT a5
COLUMN spid                            FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
       SUBSTR(s.program,1,22) program , s.process pid_remote,
       s.status,
       ROUND(pga_used_mem/1024/1024) usme,
       ROUND(pga_alloc_mem/1024/1024) alme,
       ROUND(pga_freeable_mem/1024/1024) frme,
       ROUND(pga_max_mem/1024/1024) mame
FROM  v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;


COLUMN category      HEADING "Category"
COLUMN allocated     HEADING "Allocated bytes"
COLUMN used          HEADING "Used bytes"
COLUMN max_allocated HEADING "Max allocated bytes"
SELECT pid, category, allocated, used, max_allocated
FROM   v$process_memory
WHERE  pid = (SELECT pid
              FROM   v$process
              WHERE  addr= (select paddr
                            FROM   v$session
                            WHERE  sid = '&sid' ));





 - V$PROCESS_MEMORY_DETAIL
    Contain break down of memory allocation for each component.
    - To activate this view can one of following commands executed:
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>;

    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;






Reference :

Script To Monitor RDBMS Session PGA and UGA Current And Maximum Usage Over Time (Doc ID 835254.1)

https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/05/script-to-monitor-rdbms-session-pga-and.html

Sizing the PGA in Oracle 19c - How to Account for the MGA Size (Doc ID 2808761.1)








Dumping Memory usage of session using oradump 


We need to find spid  of  session whoes  memory information needs to be dumped . Once  memory information is  found we can    dump  memory  usage of this session 




column spid format a5
select spid,pname,round(allocated/1024/1024) alloc_MB 
  from v$process_memory m, v$process p 
  where m.pid = p.pid order by allocated;

SPID  PNAME   ALLOC_MB
----- ----- ----------
...
2546  W01A         155
5316  W00K         155
2401  W00N         155
2432  W01Q         155
2464  W02Q         155
 

oradebug setospid 5316
oradebug dump heapdump 1



Level Description

1 PGA summary
2 SGA summary
4 UGA summary
8 Callheap (Current)
16 Callheap (User)
32 Large pool
64 Streams pool
128 Java pool
1025 PGA with contents
2050 SGA with contents
4100 UGA with contents
8200 Callheap with contents (Current)
16400 Callheap with contents (User)
32800 Large pool with contents
65600 Streams pool with contents
131200 Java pool with contents






#############################

Top  IO using sessions : 


set echo off
-- sessIo.sql
-- Purpose: Display session I\O.
-- -----------------------------------------------------------------------------
set pagesize 9999
set linesize 140
column username  format a10
column sid       format 999
column serial#   format 99999
column actv      format a2
column sess_mi   format 9,999.9
column last      format 9,999.99
column TotGets   format 99,999.999
column phyRds    format 999,999
column "KBSnt"   format 9,999,999
column "Trips/s" format 999
column "KbSnt/s" format 99,999
column Trips     format 99,999,999
column "B/trip"  format 9,999,999
column hit_rat   format 9,999
SELECT a.username, a.sid, a.serial#,
       decode(a.status, 'ACTIVE', 'Y','INACTIVE', 'N') actv,
       (sysdate-logon_time)*24*60 sess_mi,
       a.last_call_et/60 "last",
       c.value/1024 "KBSnt",
       e.value "Trips",
       e.value / ((sysdate - logon_time)*24*60*60) "Trips/s",
       ((8*c.value/1024)/((sysdate - logon_time)*24*60*60)) "KbSnt/s",
       c.value/e.value "B/trip",
       b.block_gets + b.consistent_gets TotGets,
       b.physical_reads phyRds,
       1-(physical_reads/(b.block_gets + b.consistent_gets)) hit_rat
FROM v$session a, v$sess_io b, v$sesstat c, v$statname d, v$sesstat e, v$statname f
WHERE a.sid = b.sid
AND a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = 'bytes sent via SQL*Net to client'
AND a.sid = e.sid
AND e.statistic# = f.statistic#
AND f.name = 'SQL*Net roundtrips to/from client'
AND a.username is not null
AND (a.last_call_et < 3600 or a.status = 'ACTIVE')
AND sysdate - logon_time > 0
AND a.username != 'SYS'
AND (b.block_gets + b.consistent_gets) > 0
ORDER BY 13
/
set echo on




select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;



#############################

Checking Top Cpu session on OS side 


Linux :

ps -eo pid,comm,%cpu,%mem --sort=-%cpu | head -n 5

 ps -eo pcpu,pid -o args= | sort -k1 -r | head -10

 ps -eo pcpu,pid -o comm= | sort -k1 -r | head -10



Solaris : 

prstat -avm



#############################

Checking Top Memory session on OS side 


Linux :

ps -eo pmem,pcpu,vsize,pid,cmd | sort -k 1 -nr | head -5

ps -eo pid,ppid,cmd,%mem,%cpu --sort=-%mem | head


Solaris : 

prstat -a -s size



#############################

Database health check script 1 




prompt**=====================================================================================================**
prompt**  **Database  Status**
prompt**=====================================================================================================**


set linesize 999
set pagesize 50000
set feedback 1
set trimspool on
set trimout on
set doc off
clear breaks
clear computes
set echo off

-- alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';

select * from v$version
/

select inst_id
      ,instance_name
      ,startup_time
      ,round((sysdate-startup_time),1) up_days
      ,round(round((sysdate-startup_time),1)*24) maximum_cache_hours
      ,to_char(sysdate,'dd-mon-rrrr hh24:mi:ss') right_now
from gv$instance
order by inst_id
/





set serveroutput on
declare 
cursor c1 is select version
from v$instance;
cursor c2 is
    select
          host_name
       ,  instance_name
       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
     from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;
cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
       username, total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('physical reads', 'physical writes',
                     'physical reads direct',
                     'physical reads direct (lob)',
                     'physical writes direct',
                     'physical writes direct (lob)')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('physical reads', 'physical writes',
                       'physical reads direct',
                       'physical reads direct (lob)',
                       'physical writes direct',
                       'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
       total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
 and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
       username, total_user_mem,
       round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_mem
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('session pga memory', 'session uga memory')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_mem
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum < 2;
cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
     dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
     dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
     dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
 dbms_output.put_line( rec.starttime );
  end loop;
dbms_output.put_line( chr(13) );
  for b in
    (select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)


     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
  end loop;
  dbms_output.put_line( chr(13) );
 dbms_output.put_line( 'Sessions Waiting' );
  dbms_output.put_line( chr(13) );
dbms_output.put_line('Count      Event Name');
dbms_output.put_line('-----      -----------------------------------------------------');
for rec in c4 
loop
dbms_output.put_line(rec.cnt||'          '||rec.event);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('-----      -----------------------------------------------------');
dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;
dbms_output.put_line('------------------------------------------------------------------');
dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop
dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;
end;
/

set pages 50
set lines 1000
set pages 70
set heading on
set trims on
set feedback off
set serveroutput off 


prompt**=====================================================================================================**
prompt**  **Database Current Status**
prompt**=====================================================================================================**
set lines 300 pages 3000
select name,open_mode,database_role from v$database;

prompt**=====================================================================================================**
prompt**  **DB Link Details**
prompt**=====================================================================================================**
set lines 300 pages 3000
COL OWNER FORMAT a10
COL USERNAME FORMAT A20
COL DB_LINK FORMAT A30
COL HOST FORMAT A30
SELECT * FROM DBA_DB_LINKS;


prompt**=====================================================================================================**
prompt**  **Failed jobs details**
prompt**=====================================================================================================**
set lines 300
col job_name for a33
col owner for a13
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select JOB_NAME,OWNER,STATUS,additional_info,to_char(ACTUAL_START_DATE,'dd-mm-yyyy hh24:mi:ss') ACTUAL_START_DATE from dba_scheduler_job_run_details where STATUS='FAILED';

 

prompt**=====================================================================================================**
prompt**  **Check Multiplexing control files on different mount points/File systems.**
prompt**=====================================================================================================**
set lines 300 pages 3000
col NAME for a50
select name from v$controlfile;




prompt**=====================================================================================================**
prompt**  **Identify users having SYSTEM as default tablespace or temporary tablespace.**
prompt**=====================================================================================================**
col profile for a15
col username for a15
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
select USERNAME,to_char(CREATED,'dd-mm-yyyy hh24:mi:ss') CREATED,PROFILE,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username not in(
'ANONYMOUS',
'AURORA$ORB$UNAUTHENTICATED',
'AWR_STAGE',
'CSMIG',
'CTXSYS',
'DBSNMP',
'DEMO',
'DIP',
'DMSYS',
'DSSYS',
'EXFSYS',
'HR',
'OE',
'SH',
'LBACSYS',
'MDSYS',
'ORACLE_OCM',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'PERFSTAT',
'SCOTT',
'ADAMS',
'JONES',
'CLARK',
'BLAKE',
'SYS',
'SYSTEM',
'TRACESVR',
'TSMSYS',
'XDB') and (temporary_tablespace='SYSTEM' or DEFAULT_TABLESPACE='SYSTEM');

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where DEFAULT_TABLESPACE='SYSTEM';
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where TEMPORARY_TABLESPACE='SYSTEM';




prompt**=====================================================================================================**
prompt**  **Identify top utilizaed tablespace s**
prompt**=====================================================================================================**
select * From DBA_TABLESPACE_USAGE_METRICS;


prompt**=====================================================================================================**
prompt**  **Check multiplexing of Redo log at different location **
prompt**=====================================================================================================**
col member for a45
select * from v$logfile;


prompt**=====================================================================================================**
prompt**  **Check for snapshot too old error.**
prompt**=====================================================================================================**
set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,PROCESS_ID,HOST_ID,HOST_ADDRESS,to_char(ORIGINATING_TIMESTAMP,'dd-mm-yyyy hh24:mi:ss') ORIGINATING_TIMESTAMP,MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like '%ORA-01555%';

show parameter undo;



prompt**=====================================================================================================**
prompt**  **Check that all tablespaces are locally managed**
prompt**=====================================================================================================**

select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT,EXTENT_MANAGEMENT from dba_tablespaces;




prompt**=====================================================================================================**
prompt**  **Check for  tablespace extent size.**
prompt**=====================================================================================================**


Set echo off lines 130 pages 100 feedback off
compute sum LABEL 'TOTAL' of B1 on report
compute sum LABEL 'TOTAL' of B2 on report
compute sum LABEL 'TOTAL' of b3 on report
BREAK ON REPORT
Column TS1   Format A35 Heading 'TS Name|                                '
Column B1    Format 99,999,999,999     Heading 'Total Space|Allocated (MB)'
Column B2    Format 99,999,999,990     Heading 'Total Free|Space (MB)'
Column b3    Format 99,999,999,999     Heading 'Total Space|Used (MB)'
Column b4x   Format 990.9     Heading '%|Used'
Column b5    Format 990.9     Heading '%|Free'
Column C_B1  Format 99,9999,990        Heading 'Cnt TS|       Files'
Column b6    Format 99,999,999,990     Heading 'Max Single|Free Space'
Column b7    Format 999,990     Heading 'Count|Free|Space'
select d.name, a.tablespace_name ts1,
round(((alloc_space-nvl(free_space, 0))/max_space)*100, 2) B4X,
round(alloc_space/(1024*1000), 2) B1,
round((alloc_space-nvl(free_space, 0))/(1024*1000), 2) B3,
round((nvl(free_space, 0)+(max_space-alloc_space))/(1024*1000), 2) b2
from
(select tablespace_name, sum(bytes) alloc_space, 
sum(greatest(MAXBYTES, BYTES)) max_space
from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes) free_space
from dba_free_space 
group by tablespace_name) b, v$database d
where a.tablespace_name=b.tablespace_name(+)
order by 3;
set feedback on;
set echo on;





prompt**=====================================================================================================**
prompt**  **Check for Temp tablespace extent size.**
prompt**=====================================================================================================**
SELECT inst_id,tablespace_name ,sum(BYTES_CACHED)/1024/1024 "ALLOCATED(MB)",sum(BYTES_USED)/1024/1024 "USED(MB)",
sum(BYTES_CACHED-BYTES_USED)/1024/1024 "FREE(MB)",100*sum(BYTES_USED)/sum(BYTES_CACHED) "%USED(Extent Pool)"
FROM gv$TEMP_EXTENT_POOL group by inst_id,tablespace_name
/

col file_name for a45
col tablespace_name for a15
select file_id,file_name,tablespace_name,status,bytes/1024/1024/1024 GB,autoextensible from dba_temp_files;



prompt**=====================================================================================================**
prompt**  **Check archive log mode for production databases.**
prompt**=====================================================================================================**
archive log list


prompt**=====================================================================================================**
prompt**  **archive_generation_for_month.**
prompt**=====================================================================================================**
SELECT SUM_ARCH.DAY,
     SUM_ARCH.GENERATED_MB,
     SUM_ARCH_DEL.DELETED_MB,
     SUM_ARCH.GENERATED_MB - SUM_ARCH_DEL.DELETED_MB "REMAINING_MB"
  FROM ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
          SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
           GENERATED_MB
       FROM V$ARCHIVED_LOG
       WHERE ARCHIVED = 'YES'
     GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH,
     ( SELECT TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY') DAY,
          SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
           DELETED_MB
       FROM V$ARCHIVED_LOG
       WHERE ARCHIVED = 'YES' AND DELETED = 'YES'
     GROUP BY TO_CHAR (COMPLETION_TIME, 'DD/MM/YYYY')) SUM_ARCH_DEL
  WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, 'DD/MM/YYYY');




 

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_Gb
FROM
(
SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM
v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
gv$log
) B;

 SELECT   A.*, ROUND (A.Count# * B.AVG# / 1024 / 1024/1024) Daily_Avg_gb
  FROM   (  SELECT   TO_CHAR (First_Time, 'YYYY-MM-DD') DAY,
                     COUNT (1) Count#,
                     MIN (RECID) Min#,
                     MAX (RECID) Max#
              FROM   v$log_history
          GROUP BY   TO_CHAR (First_Time, 'YYYY-MM-DD')
          ORDER BY   1 DESC) A, (SELECT   AVG (BYTES) AVG#,
                                          COUNT (1) Count#,
                                          MAX (BYTES) Max_Bytes,
                                          MIN (BYTES) Min_Bytes
                                   FROM   v$log) B;



 
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from 
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/







prompt**=====================================================================================================**
PROMPT**  **Check all Datafile status**
prompt**=====================================================================================================**
col NAME for a60
SELECT name,
  FILE#,
  STATUS,
  CHECKPOINT_CHANGE# "CHECKPOINT"  
 FROM  V$DATAFILE;



prompt**=====================================================================================================**
prompt**  **Check Database component status**
prompt**=====================================================================================================**
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;


prompt**=====================================================================================================**
prompt**  **Check dba directories**
prompt**=====================================================================================================**
col owner for a10
col directory_name for a40
col directory_path for a60
select owner,directory_name,directory_path from dba_directories;



prompt**=====================================================================================================**
prompt**  **Check Database recyclebin status**
prompt**=====================================================================================================**
SELECT Value FROM V$parameter WHERE Name = 'recyclebin';


prompt**=====================================================================================================**
prompt**  **Identify DBs for which RMAN backup is not configured**
prompt**=====================================================================================================**
col GB for9,999
col START_TIME for a20
col end_TIME for a20
col LEVEL for 99
col operation for a10
col status for a10
select stamp,ROW_LEVEL "LEVEL",OPERATION,status,(MBYTES_PROCESSED/1024) "GB",
 to_char(START_TIME,'DD-MON-YYYY HH24:MI:SS') "START_TIME", to_char(end_TIME,'DD-MON-YYYY HH24:MI:SS') "END_TIME", object_type from v$RMAN_STATUS where OPERATION='BACKUP' and start_time > (sysdate - 10)
order by stamp ;


prompt**=====================================================================================================**
prompt**  **Identify the DBs with high archive log generation to tune archive log backup frequency **
prompt**=====================================================================================================**
SET PAGESIZE 6000
SET LINESIZE 300
SET VERIFY OFF
break on report
compute sum of TOTAL_ARCHIVES on report
compute sum of TotalArchive_sIZE_in_MB on report
col arch_date for a15
SELECT thread#,TO_CHAR(completion_time,'DD-MM-YYYY') ARCH_DATE,
count(sequence#) TOTAL_ARCHIVES,
round(sum(blocks*block_size)/1024/1024) "TotalArchive_sIZE_in_MB"
from gv$archived_log where dest_id=1 and trunc(completion_time)>=trunc(sysdate-15)
group by thread#,TO_CHAR(completion_TIME,'DD-MM-YYYY') order by 1;



prompt**=====================================================================================================**
prompt**  **Identify non DBA users with DBA role**
prompt**=====================================================================================================**
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from dba_role_privs where GRANTED_ROLE in('DBA') and ADMIN_OPTION='YES' order by GRANTEE;



prompt**=====================================================================================================**
prompt**  **Identify non DBA/System users having elevated privilages, e..g user having access to V$ views, or any privileges granted to it, or privilege with admin option**
prompt**=====================================================================================================**
select * from dba_sys_privs where PRIVILEGE like '%ANY%' and GRANTEE not in(
'ANONYMOUS',
'AURORA$ORB$UNAUTHENTICATED',
'AWR_STAGE',
'CSMIG',
'CTXSYS',
'DBSNMP',
'DEMO',
'DIP',
'DMSYS',
'DSSYS',
'EXFSYS',
'HR',
'OE',
'SH',
'LBACSYS',
'MDSYS',
'ORACLE_OCM',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'PERFSTAT',
'SCOTT',
'ADAMS',
'JONES',
'CLARK',
'BLAKE',
'SYS',
'SYSTEM',
'TRACESVR',
'TSMSYS',
'IMP_FULL_DATABASE',
'EXP_FULL_DATABASE',
'DBA',
'DATAPUMP_IMP_FULL_DATABASE',
'AQ_ADMINISTRATOR_ROLE',
'JAVADEBUGPRIV',
'SCHEDULER_ADMIN',
'SYSMAN',
'XDB') order by 1,2;


PROMPT**=====================================================================================================**
prompt**  **Identify Oracle default users with account status as .OPEN.**
prompt**=====================================================================================================**
select username,account_status from dba_users where username in (
'ANONYMOUS',
'AURORA$ORB$UNAUTHENTICATED',
'AWR_STAGE',
'CSMIG',
'CTXSYS',
'DBSNMP',
'DEMO',
'DIP',
'DMSYS',
'DSSYS',
'EXFSYS',
'HR',
'OE',
'SH',
'LBACSYS',
'MDSYS',
'ORACLE_OCM',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'PERFSTAT',
'SCOTT',
'ADAMS',
'JONES',
'CLARK',
'BLAKE',
'SYS',
'SYSTEM',
'TRACESVR',
'TSMSYS',
'XDB') order by 2,1;

 
prompt**=====================================================================================================**
prompt**  **Identify Users with privilege to access Metadata**
prompt**=====================================================================================================**
select * from dba_role_privs where GRANTED_ROLE='SELECT_CATALOG_ROLE' and GRANTEE not in(
'ANONYMOUS',
'AURORA$ORB$UNAUTHENTICATED',
'AWR_STAGE',
'CSMIG',
'CTXSYS',
'DBSNMP',
'DEMO',
'DIP',
'DMSYS',
'DSSYS',
'EXFSYS',
'HR',
'OE',
'SH',
'LBACSYS',
'MDSYS',
'ORACLE_OCM',
'ORDPLUGINS',
'ORDSYS',
'OUTLN',
'PERFSTAT',
'SCOTT',
'ADAMS',
'JONES',
'CLARK',
'BLAKE',
'SYS',
'SYSTEM',
'TRACESVR',
'TSMSYS',
'IMP_FULL_DATABASE',
'EXP_FULL_DATABASE',
'DBA',
'XDB') order by 1;


prompt**=====================================================================================================**
prompt**  **Identify db audit**
prompt**=====================================================================================================**
show parameter audit

prompt**=====================================================================================================**
prompt**  **Oracle user and profiles **
prompt**=====================================================================================================**
set lines 300
col username for a20
col profile for a20
select username,profile from dba_users;

prompt**=====================================================================================================**
prompt**  **Oracle supplied packages**
prompt**=====================================================================================================**
col object_name for a45
SELECT DISTINCT Owner, Object_Type, Object_Name,STATUS FROM DBA_Objects_AE
   WHERE Owner IN (
    'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP',
    'LOGSTDBY_ADMINISTRATOR', 'ORDSYS',
    'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY',
    'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS',
    'WMSYS', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
    'OLAPSYS', 'EXFSYS', 'SYSMAN', 'MDDATA',
    'SI_INFORMTN_SCHEMA', 'XDB', 'ODM')
   AND Object_Type IN ('PACKAGE')
   ORDER BY Owner, Object_Type, Object_Name;




prompt**=====================================================================================================**
prompt**  **Check if allocated SGA is adequate **
prompt**=====================================================================================================**
show parameter sga
select round((sum(decode(name, 'free memory', bytes, 0)) / sum(bytes))* 100,2) "SGA Free Memory" from v$sgastat;



prompt**=====================================================================================================**
prompt**  **Identify DBs performing heavy sorting, hash joining and configure PGA Separately**
prompt**=====================================================================================================**
show parameter PGA
SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dictionary_Cache_Hit_Ratio FROM v$rowcache;
SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 Library_Cache_Hit_Ratio FROM  v$librarycache;
SELECT (1 - (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio FROM  v$latch;
select (disk.value/mem.value) * 100 Disk_Sort_Ratio FROM v$sysstat disk,v$sysstat mem WHERE disk.name = 'sorts (disk)' AND  mem.name = 'sorts (memory)';



prompt**=====================================================================================================**
prompt**  **Check any unusable status of existing Indexes**
prompt**=====================================================================================================**
select owner,index_name from dba_indexes WHERE STATUS = 'UNUSABLE'
union all
select INDEX_OWNER, index_name from dba_ind_partitions WHERE STATUS = 'UNUSABLE'
union all
select INDEX_OWNER, index_name from dba_ind_subpartitions WHERE STATUS = 'UNUSABLE';




prompt**=====================================================================================================**
prompt**  **Identify Tables with fragmentation**
prompt**=====================================================================================================**
set lines 300 pages 3000
col TABLESPACE_NAME for a25
col TABLE_NAME for a25
break on report
compute sum of FRAGMENTED_SPACE on report;
select 
owner,table_name,tablespace_name,
blocks,
num_rows,
avg_row_len,round(((blocks*8/1024)),2) "TOTAL_SIZE", 
round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE", 
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE"
from dba_tables 
where owner <> 'SYS' 
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 500
order by FRAGMENTED_SPACE desc;
select OWNER,TABLE_NAME,SEGMENT_NAME from dba_lobs where table_name in (select table_name from dba_tables where owner <> 'SYS' and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100);


prompt**=====================================================================================================**
prompt**  **Row chaining and recommend**
prompt**=====================================================================================================**  
set pages 9999;
column c1 heading "Owner"  format a9;
column c2 heading "Table"  format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"  format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct"   format .99; 
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
select 
  owner       c1, 
  table_name     c2, 
  pct_free      c3, 
  pct_used      c4, 
  avg_row_len    c5, 
 num_rows      c6, 
  chain_cnt     c7,
  chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
  where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;

prompt**=====================================================================================================**
prompt**  **Tablespace Level Fragmentation**
prompt**=====================================================================================================**
SELECT
 tablespace_name, 
 count(*) free_chunks,
 decode(round((max(bytes) / 1024000),2),
 null,0,
 round((max(bytes) / 1024000),2)) largest_chunk,
 nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
 FROM
 sys.dba_free_space 
 group by 
 tablespace_name
 order by 2 desc, 1;


prompt**=====================================================================================================**
prompt**  **Identify objects with stale statistics **
prompt**=====================================================================================================**
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_TAB_STATISTICS where STALE_STATS='YES' and OWNER not in('SYS','SYSTEM') and TABLE_NAME not like '%BIN$%';

 

prompt**=====================================================================================================**
prompt**  **Identify INVALID objects**
prompt**=====================================================================================================**
set lines 300
col CREATED for a28
col LAST_DDL_TIME for a28
col object_name for a40
col object_type for a18
col owner for a16
col status for a19
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS,to_char(CREATED,'dd-mm-yyyy hh24:mi:ss') CREATED,to_char(LAST_DDL_TIME,'dd-mm-yyyy hh24:mi:ss') LAST_DDL_TIME from dba_objects where status='INVALID' and OWNER not in ('SYS','SYSTEM');


 
prompt**=====================================================================================================**
prompt**  **Check the alertlog for Frequently Occurring Deadlocks.**
prompt**=====================================================================================================**
set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,PROCESS_ID,HOST_ID,HOST_ADDRESS,to_char(ORIGINATING_TIMESTAMP,'dd-mm-yyyy hh24:mi:ss') ORIGINATING_TIMESTAMP,MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like '%ORA-00060%';
 


select * from  v$diag_problem ; 


 
prompt**=====================================================================================================**
prompt**  **Check Primary and standby sync status .**
prompt**=====================================================================================================**
set lines 300
col name for a10
col status for a10
select a.name,a.status,to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') time,a.thread#,(select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived,max(a.sequence#) applied,(select max(sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max(a.sequence#) gap from v$archived_log a where a.applied='YES' group by a.thread#,a.name,a.status;
 
 


PROMPT ==================
PROMPT TOTAL Connections
PROMPT ==================

set lines 750 pages 9999
break on report
compute SUM of tot on report
compute SUM of active on report
compute SUM of inactive on report
col username for a50
select DECODE(username,NULL,'INTERNAL',USERNAME) Username,
count(*) TOT,
COUNT(DECODE(status,'ACTIVE',STATUS)) ACTIVE,
COUNT(DECODE(status,'INACTIVE',STATUS)) INACTIVE
from gv$session
where status in ('ACTIVE','INACTIVE')
group by username;

PROMPT ================
PROMPT Session Details
PROMPT ================

set linesize 750 pages 9999
column box format a30
col serial# for 999999
column spid format a10
column username format a30
column program format a30
column os_user format a20
col LOGON_TIME for a20

select b.inst_id,b.sid,b.serial#,a.spid, substr(b.machine,1,30) box,to_char (b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
substr(b.username,1,30) username,
substr(b.osuser,1,20) os_user,
substr(b.program,1,30) program,status,b.last_call_et AS last_call_et_secs,b.sql_id
from gv$session b,gv$process a
where b.paddr = a.addr
and a.inst_id = b.inst_id
and type='USER'
order by b.inst_id,b.sid;


PROMPT ================
PROMPT Sql Details
PROMPT ================

column sid format 9999
column username format a15
column PARSING_SCHEMA_NAME format a15
column sql_text format a50
column module format a35
select a.inst_id,a.sid,a.username,b.PARSING_SCHEMA_NAME,a.module,a.sql_id,a.sql_child_number child,b.hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,(sysdate-sql_exec_start)*24*60*60 SECS,b.rows_processed,a.status,substr(b.sql_text,1,50) sql_text
from gv$session a,gv$sqlarea b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.module not like '%emagent%'
and a.module not like '%oraagent.bin%'
and a.username is not null
order by a.status;



PROMPT =========================
PROMPT Sql Monitor - REPORT
PROMPT =========================

column text_line format a1000
set lines 750 pages 9999
set long 20000 longchunksize 20000
select dbms_sqltune.report_sql_monitor_list() text_line from dual;




PROMPT ================
PROMPT Blocking Session
PROMPT ================

set lines 750 pages 9999
col blocking_status for a100
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;




set linesize 110
col BLOCKING_STATUS format a110
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;


select distinct BLOCKING_SESSION , inst_id from gv$session where  BLOCKING_SESSION is not null   ;

 







PROMPT ================
PROMPT Session LongOps
PROMPT ================

SET VERIFY OFF

SELECT a.sid,RPAD(a.opname,30),a.sofar,a.totalwork,a.ELAPSED_SECONDS,ROUND(((a.sofar)*100)/a.totalwork,3) "%_COMPLETED",time_remaining,
RPAD(a.username,10) username,a.SQL_HASH_VALUE,B.STATUS
FROM GV$SESSION_LONGOPS a, gv$session b
WHERE a.sid=&sid
and b.inst_id=&inst_id
AND a.sofar<> a.totalwork
/


SELECT OPNAME,TARGET,SID,SOFAR,TOTALWORK,TIME_REMAINING FROM V$SESSION_LONGOPS where time_remaining>0
/





prompt**=====================================================================================================**
prompt**  **wait details **
prompt**=====================================================================================================**


set line 9999 
column average_wait format 9999990.00
col event format a40
select
  substr(e.event, 1, 40)  event,
  e.time_waited,
  e.time_waited / (
    e.total_waits - decode(e.event, 'latch free', 0, e.total_timeouts)
  )  average_wait
from
  sys.v_$system_event  e,
  sys.v_$instance  i
where
  e.event = 'buffer busy waits' or
  e.event = 'enqueue' or
  e.event = 'free buffer waits' or
  e.event = 'global cache freelist wait' or
  e.event = 'latch free' or
  e.event = 'log buffer space' or
  e.event = 'parallel query qref latch' or
  e.event = 'pipe put' or
  e.event = 'write complete waits' or
  e.event like 'library cache%' or
  e.event like 'log file switch%' or
  ( e.event = 'row cache lock' and
    i.parallel = 'NO'
  )
union all
select
  'non-routine log file syncs',
  round(e.average_wait * greatest(e.total_waits - s.value, 0)),
  e.average_wait
from
  sys.v_$system_event e,
  sys.v_$sysstat s
where
  e.event = 'log file sync' and
  s.name = 'user commits'
order by
  2 desc
/




col type format a5 heading "Svc,|Idle,|Wait"
col name format a35 heading "Name" truncate
col tot_secs_spent format 999,999,999,990.00 heading "Total|Seconds|Spent"
col pct_total format 990.00 heading "%|Total"
col nonidle_total format 990.00 heading "%|NonIdle"

select type,
name,
tot_secs_spent,
(tot_secs_spent / (sum(tot_secs_spent) over ()))*100 pct_total,
(nonidle_secs_spent / (sum(nonidle_secs_spent) over ()))*100 nonidle_total
from (select decode(event,
'rdbms ipc message', 'Idle',
'rdbms ipc reply', 'Idle',
'SQL*Net message from client', 'Idle',
'SQL*Net break/reset to client', 'Idle',
'pipe get', 'Idle',
'pmon timer', 'Idle',
'smon timer', 'Idle',
'dispatcher timer', 'Idle',
'virtual circuit status', 'Idle',
'PX Idle Wait', 'Idle',
'PX Deq: Execute Reply', 'Idle',
'PX Deq: Execution Msg', 'Idle',
'PX Deq: Table Q Normal', 'Idle',
'PX Deq Credit: send blkd', 'Idle',
'PX Deq Credit: need buffer', 'Idle',
'PX Deq: Parse Reply', 'Idle',
'PX Deq: Signal ACK', 'Idle',
'PX Deq: Join ACK', 'Idle',
'PX qref latch', 'Idle',
'PX Deq: Msg Fragment', 'Idle',
'PL/SQL lock timer', 'Idle',
'inactive session', 'Idle',
'Wait') type,
event name,
time_waited/100 tot_secs_spent,
decode(event,
'rdbms ipc message', 0,
'rdbms ipc reply', 0,
'SQL*Net message from client', 0,
'SQL*Net break/reset to client', 0,
'pipe get', 0,
'pmon timer', 0,
'smon timer', 0,
'dispatcher timer', 0,
'virtual circuit status', 0,
'PX Idle Wait', 0,
'PX Deq: Execute Reply', 0,
'PX Deq: Execution Msg', 0,
'PX Deq: Table Q Normal', 0,
'PX Deq Credit: send blkd', 0,
'PX Deq Credit: need buffer', 0,
'PX Deq: Parse Reply', 0,
'PX Deq: Signal ACK', 0,
'PX Deq: Join ACK', 0,
'PX qref latch', 0,
'PX Deq: Msg Fragment', 0,
'PL/SQL lock timer', 0,
'inactive session', 0,
time_waited/100) nonidle_secs_spent
from v$system_event  where time_waited > 0
union all
select 'Svc' type,
'other cpu usage' name,
(t.value - (p.value + r.value))/100 tot_secs_spent,
(t.value - (p.value + r.value))/100 nonidle_secs_spent
from v$sysstat t,
v$sysstat p,
v$sysstat r
where t.name = 'CPU used by this session'
and p.name = 'recursive cpu usage'
and r.name = 'parse time cpu'
union all
select 'Svc' type,
name,
value/100 tot_secs_spent,
value/100 nonidle_secs_spent
from v$sysstat
where name = 'recursive cpu usage'
and value > 0
union all
select 'Svc' type,
name,
value/100 tot_secs_spent,
value/100 nonidle_secs_spent
from v$sysstat
where name = 'parse time cpu'
and value > 0)
order by 5 desc, 4 desc, 3 desc, 2  ;




prompt**=====================================================================================================**
prompt**  **hit ratios  **
prompt**=====================================================================================================**

prompt**#buffer cache**
select (1 - (sum(decode(name, 'physical reads', value, 0)) /
       (sum(decode(name, 'db block gets', value, 0)) +
       sum(decode(name, 'consistent gets', value, 0)))))
       * 100 "Hit Ratio Buffer Cache"
from   v$sysstat;



prompt**=====================================================================================================**
prompt**  ** DISK WRITE DETAILS  **
prompt**=====================================================================================================**


col PHYRDS   format 99,999,999
col PHYWRTS  format 99,999,999
ttitle "Disk Balancing Report"
col READTIM  format 99,999,999
col WRITETIM format 99,999,999
col name format a30
select name, phyrds, phywrts, readtim, writetim
from   v$filestat a, v$dbfile b
where a.file# = b.file#
order by readtim desc;

                                                                     

No comments:

Post a Comment