Home Oracle DB Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part4

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part4

TOP 10 SQL STATEMENT WITH MOST NO. of ELAPSED TIME,TOP 10 SQL STATEMENT WITH MOST NO. of ELAPSED TIME,TOP 10 SESSIONS BY CPU UTILIZATION,SQL queries generate heavy archive logs,Top Wait Events

0
111

Database monitoring is an integral part of Database Administrator’s everyday work. We all execute many queries to check health status of the database instances. Checking TOP 10 SQL STATEMENTS WITH MOST NO. of ELAPSED TIME,TOP 10 SQL STATEMENTS WITH MOST NO. of ELAPSED TIME,TOP 10 SESSIONS BY CPU UTILIZATION,SQL QUERIES WHICH GENERATES HEAVY LOGS, TOP 10 WAIT EVENTS etc. plays a very important role to analyze the performance of the database. All queries are written for RAC environment. You may change “gv$” to just “v$” for executing these queries in standalone databases.

Please refer to Part1, Part2, Part3 of daily reports for more queries in database health check.

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part1

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part2

Oracle Database 11g,12c,18c,19c Daily Monitoring Queries with html report format -Part3

Following format and queries can be used to get reports in HTML format for the above mentioned topics.

SET HEAD OFF
SET ECHO OFF
SET MARKUP HTML ON SPOOL ON
Spool Daily_Report4.html

TTITLE CENTER ====’ TOP 10 SQL STATEMENT WITH MOST NO. of ELAPSED TIME ‘=====
set serveroutput on

column SQL_TEXT format a100
column EXECUTIONS format 9999999
column PARSE_CALLS format 99999999
column USER FORMAT a10

select t.inst_id,t.LAST_LOAD_TIME,t.PARSING_SCHEMA_NAME as “USER”,substr(t.SQL_TEXT,0,100) as “SQL_TEXT”,t.EXECUTIONS,t.PARSE_CALLS,t.ELAPSED_TIME
from gv$sqlarea t
where t.LAST_LOAD_TIME > sysdate-8/24
and ROWNUM < 11
order by ELAPSED_TIME DESC,EXECUTIONS DESC
/

TTITLE CENTER =====’ TOP 10 SESSIONS BY MEMORY UTILIZATION ‘=====
set serveroutput on
column SID format 9999999
column USERNAME format a16
column STATUS format a12
column “LOGIN_TIME” format a12
column MACHINE format a30
column PROGRAM format a45
column “TOTAL_MEMORY (KB)” format 99999999

select s.SID,s.USERNAME,s.STATUS,s.last_call_et,to_char(s.logon_time,’dd-mm hh24:mi’) as “LOGIN_TIME”, s.MACHINE,round(se.value/1024,2) as “TOTAL_MEMORY (KB)”,
s.PROGRAM ,s.sql_id
from gv$session s, gv$sesstat se, v$statname sy
where se.STATISTIC#=sy.STATISTIC#
and se.SID=s.SID
and s.logon_time > sysdate-8/24
and s.username is not null
and sy.NAME=’session pga memory’
and ROWNUM < 10
order by se.value desc
/

TTITLE CENTER =====’ TOP 10 SESSIONS BY CPU UTILIZATION 1 ‘====
set serveroutput on

column SID format 9999999
column USERNAME format a15
column STATUS format a12
column “LOGIN_TIME” format a12
column MACHINE format a20
column PROGRAM format a30
column module format a30
column “TOTAL_CPU” format 99999999

select s.inst_id, s.SID,s.serial#,s.USERNAME,to_char(s.logon_time,’dd-mm hh24:mi’) as “LOGIN_TIME”, s.MACHINE,se.value as “TOTAL_CPU”,s.PROGRAM,s.module,
s.STATUS,s.last_call_et,s.SQL_HASH_VALUE
from gv$process p,gv$session s, gv$sesstat se, gv$statname sy
where se.STATISTIC#=sy.STATISTIC#
and se.SID=s.SID
AND s.paddr = p.addr
and s.logon_time>sysdate-8/24
and sy.NAME=’CPU used by this session’
and s.username is not null
and ROWNUM < 10
order by se.value desc
/

TTITLE CENTER ====’ SQL queries generate heavy archive logs ‘=====

set serveroutput on

set lines 2000
set pages 1000
col sid for 99999
col name for a09
col username for a14
col PROGRAM for a21
col MODULE for a25
select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) “program”, sn.type, sn.module,sn.sql_id
from gv$sesstat s join v$statname n on n.statistic# = s.statistic#
join gv$session sn on sn.sid = s.sid where n.name like ‘redo size’ and s.value!=0 and round(value/1024/1024,2)>=500 order by
redo_mb desc;
/

TTITLE CENTER ====’ Top Wait Events ‘====

set serveroutput on

select * from (
select
WAIT_CLASS ,
EVENT,
count(sample_time) as EST_SECS_IN_WAIT
from v$active_session_history
where sample_time between sysdate – interval ‘8’ hour and sysdate
group by WAIT_CLASS,EVENT
order by count(sample_time) desc
)
where rownum <6;

SET MARKUP HTML OFF
SET ECHO ON
SPOOL OFF