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 .
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)
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
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;
