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

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

Backup report, Top 10 SQLs by number of executions,Top 10 SQLs by Disk reads

0
147

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