Abdul hafeez kalsekar -- OCS/OCE/OCP: 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 * 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;

Abdul hafeez kalsekar -- OCS/OCE/OCP

Monday, April 5, 2021

Oracle top sessions -- Cpu / Memory / IO

No comments:

Post a Comment