More

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

    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. Status check of Backups, Top 10 SQLs by number of executions,Top 10 SQLs by Disk reads etc. plays an important role to find out everyday database performance. 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 and Part2 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

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

    TTITLE CENTER ========’ Daily Backup Report ‘ =======

    set linesize 200 pagesize 300
    col input_type for a15
    col status for a25
    set numwidth 20
    col START_TIME for a30
    col END_TIME for a30
    col time_taken_display for a9
    col output_bytes_display for a9
    select session_key,
    input_type,
    status,
    to_char(start_time,’yyyy-mm-dd hh24:mi’) start_time,
    to_char(end_time,’yyyy-mm-dd hh24:mi’) end_time,
    output_bytes_display,
    time_taken_display
    from v$rman_backup_job_details where start_time>sysdate-7 and input_type like’%DB%’ order by session_key Asc;
    /

    TTITLE CENTER ===== ‘TOP SQL by Disk Reads ‘ =======

    set serveroutput on
    col PARSING_SCHEMA_NAME for a15
    col LAST_LOAD_TIME for a25

    select * from (select inst_id,LAST_LOAD_TIME,parsing_schema_name,sql_id,sql_text,disk_reads,executions from gv$sqlarea
    where LAST_LOAD_TIME > sysdate-8/24
    order by 6 desc, 7 desc)
    where rownum <=10;
    /

    TTITLE CENTER =====’ Most No. of Executed SQLs ‘======

    set serveroutput on

    col PARSING_SCHEMA_NAME for a15
    col LAST_LOAD_TIME for a25
    select * from (select inst_id,LAST_LOAD_TIME,parsing_schema_name,sql_id,sql_text,disk_reads,executions from gv$sqlarea
    where LAST_LOAD_TIME > sysdate-8/24
    order by 7 desc, 6 desc)
    where rownum <=10;

    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