More

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

    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 ASM Disk Group, ASM Disk Group IO distribution, Database total size , Schema Wise size, Tablespace Size details etc. plays an important role to find out everyday database growth. 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 of daily reports for more queries in database health check.

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

    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_Report2.html

    TTITLE CENTER ======’ ASM Disk Group Information ‘ =============

    SET LINESIZE 200
    SET PAGESIZE 9999
    SET VERIFY off
    COLUMN group_name FORMAT a20 HEAD ‘Disk Group|Name’
    COLUMN sector_size FORMAT 99,999 HEAD ‘Sector|Size’
    COLUMN block_size FORMAT 99,999 HEAD ‘Block|Size’
    COLUMN allocation_unit_size FORMAT 999,999,999 HEAD ‘Allocation|Unit Size’
    COLUMN state FORMAT a11 HEAD ‘State’
    COLUMN type FORMAT a6 HEAD ‘Type’
    COLUMN total_mb FORMAT 999,999,999 HEAD ‘Total Size (MB)’
    COLUMN used_mb FORMAT 999,999,999 HEAD ‘Used Size (MB)’
    COLUMN pct_used FORMAT 999.99 HEAD ‘Pct. Used’

    SELECT group_number,
    name group_name
    , sector_size sector_size
    , block_size block_size
    , allocation_unit_size allocation_unit_size
    , state state
    , type type
    , total_mb total_mb
    , (total_mb – free_mb) used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2) pct_used
    FROM
    v$asm_diskgroup
    ORDER BY
    name
    /

    TTITLE CENTER =======’ ASM Disk Group IO Balancing Information ‘ =======

    SELECT dg.group_number “GROUP#”,dg.name,DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1-df.sum_io / total_dg.total_io), -1, 0,
    (1-df.sum_io / total_dg.total_io)))) “IO_BALANCED”
    FROM
    (SELECT d.group_number group_number,SUM (ABS ((d.reads + d.writes) – tot.avg_io)) sum_io
    FROM v$asm_disk_stat d,
    (SELECT group_number,SUM (reads) + SUM (writes),DECODE (COUNT (), 0, 0, (SUM (reads) + SUM (writes)) / COUNT ()) avg_io
    FROM v$asm_disk_stat WHERE header_status = ‘MEMBER’ GROUP BY group_number) tot
    WHERE header_status = ‘MEMBER’ AND tot.group_number = d.group_number
    GROUP BY d.group_number) df,
    (SELECT group_number,
    SUM (reads) + SUM (writes) total_io
    FROM v$asm_disk_stat
    WHERE header_status = ‘MEMBER’
    GROUP BY group_number) total_dg,
    V$ASM_DISKGROUP dg
    WHERE df.group_number = total_dg.group_number
    AND df.group_number = dg.group_number;

    TTITLE CENTER ===’Schema Wise Used Size excluding Oracle Default schemas ‘===

    select owner,sum(bytes/1024/1024/1024) “Used Size in GB” from dba_segments where owner not like ‘%SYS%’ group by owner;

    set feed on

    TTITLE CENTER ==== ‘DB SIZE ‘ =======

    select sum(bytes)/1024/1024/1024 size_in_GB from dba_segments;

    set feed on

    TTITLE CENTER ====’ LIST OF TABLESPACES AND SIZES Including TEMP’ =====

    set echo off
    column timecol new_value timestamp
    column dbname new_value db_nm
    col TABLESPACE_NAME for a25

    set linesize 200 pages 500
    break on report
    compute sum of “Size (M)” on report
    compute sum of “Used (M)” on report
    column name format a30
    column “Size (M)” format 999,999,999.99
    column “Used (M)” format 999,999,999.99
    column “Used %” format a8

    SELECT d.status “Status”, d.tablespace_name “Name”, d.contents “Type”, d.extent_management “Extent Management”,
    NVL(a.bytes / 1024 / 1024, 0) “Size (M)”,
    (NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024) “Used (M)”,
    TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “Used %”
    FROM sys.dba_tablespaces d, (select
    tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
    tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
    d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
    (d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’) UNION ALL SELECT d.status “Status”, d.tablespace_name “Name”,
    d.contents “Type”, d.extent_management “Extent Management”,
    NVL(a.bytes / 1024 / 1024, 0) “Size (M)”, NVL(t.bytes, 0)/1024/1024 “Used (M)”,
    TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Used %”
    FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
    group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
    gv$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
    d.tablespace_name = t.tablespace_name(+) AND d.extent_management like ‘LOCAL’ AND d.contents like
    ‘TEMPORARY’
    order by 7 desc
    /

    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