More

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

    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

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox