set pagesize 1100
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
spool ORACLE_DATABASE_INFO.html
set feedback off
set linesize 200;
set pages 50
set lines 1000
set pages 70
set heading on
PROMPT ===============================================================================================================================================
PROMPT ORACLE DATABASE SERVER REPORT
PROMPT ===============================================================================================================================================
PROMPT REPORT DATE:
PROMPT ============
select to_char(sysdate,'DD-MON-YYYY:HH:MI:SS' ) "REPORT RUN DATE" from dual;
PROMPT DATABASE DETAILS:
PROMPT ================
SELECT
gv.instance_name,
gv.host_name,
gv.version,
gv.status,
v.NAME,
v.CREATED,
v.LOG_MODE,
v.CHECKPOINT_CHANGE#,
v.ARCHIVE_CHANGE#
FROM
gv$instance gv
CROSS JOIN
v$database v;
PROMPT DATABASE SIZE:
PROMPT =============
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;
PROMPT TABLESPACE INFO:
PROMPT ================
set linesize 100
set pagesize 100
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;
PROMPT TEMP TABLESPACE USAGE:
PROMPT =====================
set feedback off echo off
set linesize 200
set pagesize 200
set colsep |
SET TRIM ON
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
PROMPT SESSION INFORMATION:
PROMPT ====================
SELECT
CASE
WHEN status = 'ACTIVE' THEN 'Active'
WHEN status = 'INACTIVE' THEN 'Inactive'
ELSE 'Total'
END AS status,
COUNT(status) AS Count
FROM
v$session
GROUP BY
CASE
WHEN status = 'ACTIVE' THEN 'Active'
WHEN status = 'INACTIVE' THEN 'Inactive'
ELSE 'Total'
END
ORDER BY
CASE
WHEN status = 'Total' THEN 2
ELSE 1
END;
PROMPT CPU AND MEMORY LIMIT OF DATABASE SERVER:
PROMPT ========================================
set pagesize 299
set lines 299
SELECT STAT_NAME, TO_CHAR(VALUE) AS VALUE
FROM v$osstat
WHERE stat_name IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
UNION
SELECT STAT_NAME, VALUE/1024/1024/1024 || ' GB'
FROM v$osstat
WHERE stat_name = 'PHYSICAL_MEMORY_BYTES';
PROMPT CURRENT SGA USAGE:
PROMPT ==================
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
select round(used.bytes /1024/1024 ,2) used_mb
, round(free.bytes /1024/1024 ,2) free_mb
, round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
, (select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
, (select sum(bytes) bytes
from v$sgastat) tot;
PROMPT PGA TARGET AND HARD LIMIT:
PROMPT ===========================
SELECT name,
TO_CHAR(value / (1024 * 1024), '999,999,999.99') || ' MB' AS value_in_mb
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit');
PROMPT RESOURCE UTILIZATION IN DATABASE:
PROMPT =================================
select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');
PROMPT INVALID OBJECTS:
PROMPT ===============
Select count(*) "INVALID OBJECTS",OWNER,object_type from all_objects where status='INVALID' group by owner,object_type order by 2;
PROMPT REDO LOGS AND ARCHIVE STATUS:
PROMPT =============================
COLUMN member_name HEADING 'MEMBER_NAME';
COL MEMBER FOR A40;
SELECT vlf.member "MEMBER_NAME",
vl.group# "GROUP",
vl.status "STATUS",
vl.archived "ARCHIVED",
vl.bytes / 1024 "Size (K)",
vl.sequence# "SEQUENCE"
FROM v$logfile vlf,
v$log vl
WHERE vlf.group# = vl.group#
ORDER BY 1, vl.group#, vlf.member;
PROMPT SESSION I/O BY USERS:
PROMPT =====================
column USERNAME format a10
column OS_USER format a10
column PID format a10
set lines 200
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;
PROMPT INACTIVE SESSIONS IN DATABASE:
PROMPT ==============================
PROMPT **********************************************************
PROMPT Copy and Past in Cmd prompt as sys user to kill it
PROMPT **********************************************************
set feedback off
select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where status='INACTIVE';
PROMPT DATABSE LOCK INFORMATION:
PROMPT =========================
col OBJECT_NAME for a13
col OSUSER for a10
col owner for a10
col username for a10
select c.sid,c.serial#,c.osuser,c.username,a.owner,a.object_name,a.object_id
from dba_objects a,v$locked_object b, v$session c
where a.object_id=b.object_id and c.sid=b.session_id;
select 'alter system kill session ''' || sid || ',' || serial# || '''immediate;'
from v$session where sid in (select SESSION_ID from v$locked_object);
PROMPT TOP 5 SQL CONSUMING CPU RESOURCE:
PROMPT =================================
set feedback off
SELECT username,
buffer_gets,
disk_reads,
executions,
buffer_get_per_exec,
parse_calls,
sorts,
rows_processed,
hit_ratio,
module,
sql_text
-- elapsed_time, cpu_time, user_io_wait_time, ,
FROM (SELECT sql_text,
b.username,
a.disk_reads,
a.buffer_gets,
trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.rows_processed,
100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
module
-- cpu_time, elapsed_time, user_io_wait_time
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id
AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
AND a.buffer_gets > 10000
ORDER BY buffer_get_per_exec DESC)
WHERE ROWNUM <= 5;
PROMPT SQL PERFORMANCE MONITORING REPORT:
PROMPT ==================================
SELECT
s.STATUS,
ROUND((TO_DATE(s.END_TIME, 'DD-MON-YYYY HH24:MI:SS') - TO_DATE(s.START_TIME, 'DD-MON-YYYY HH24:MI:SS')) * 24 * 60 * 60) AS DURATION_SECONDS,
s.START_TIME,
s.END_TIME, -- Include START_TIME and END_TIME here
s.SQL_ID,
s.USERNAME AS "USER",
s.IN_DBOP_NAME AS TYPE,
s.BUFFER_GETS AS "Buffer Gets",
ROUND((s.ELAPSED_TIME / 1000000), 2) AS "Elapsed (s)",
ROUND((s.CPU_TIME / 1000000), 2) AS "CPU (s)",
ROUND((s.PHYSICAL_READ_BYTES / (1024 * 1024)), 2) AS "Phys reads (MB)",
ROUND((s.PHYSICAL_WRITE_BYTES / (1024 * 1024)), 2) AS "Phys writes (MB)",
REGEXP_REPLACE(LISTAGG(t.SQL_TEXT, ' ') WITHIN GROUP (ORDER BY t.ADDRESS, t.HASH_VALUE, t.PIECE), '\s+', ' ') AS SQL_TEXT
FROM
(SELECT
s.STATUS,
TO_CHAR(s.SQL_EXEC_START, 'DD-MON-YYYY HH24:MI:SS') AS START_TIME,
TO_CHAR(s.SQL_EXEC_START + (s.ELAPSED_TIME / 1000000) / (24 * 60 * 60), 'DD-MON-YYYY HH24:MI:SS') AS END_TIME,
s.SQL_ID,
s.USERNAME,
s.IN_DBOP_NAME,
s.BUFFER_GETS,
s.ELAPSED_TIME,
s.CPU_TIME,
s.PHYSICAL_READ_BYTES,
s.PHYSICAL_WRITE_BYTES
FROM
V$SQL_MONITOR s
WHERE
s.USERNAME <> 'SYS' -- Exclude SQLs executed by SYS user
) s
JOIN
V$SQLTEXT_WITH_NEWLINES t ON s.SQL_ID = t.SQL_ID
GROUP BY
s.STATUS,
s.SQL_ID,
s.USERNAME,
s.IN_DBOP_NAME,
s.BUFFER_GETS,
s.START_TIME,
s.END_TIME,
s.ELAPSED_TIME,
s.CPU_TIME,
s.PHYSICAL_READ_BYTES,
s.PHYSICAL_WRITE_BYTES
ORDER BY
s.START_TIME DESC;
PROMPT RMAN BACKUP DETAILS SYSDATE-180 DAYS:
PROMPT =====================================
set feedback off
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate) - 180
order by j.start_time;
PROMPT NUMBER OF INDEXES ANALYZED IN LAST 5 DAYS:
PROMPT =========================================
PROMPT ***************************************************************************
PROMPT the below count indicates that index which had their stats gathered
PROMPT ***************************************************************************
set feedback off
column min(last_Analyzed) format a18
column max(last_Analyzed) format a18
select owner,min(last_Analyzed), max(last_analyzed) from dba_indexes where last_analyzed > sysdate-5 group by owner order by 1;
PROMPT DB USERS WHO ARE EXPIRED OR LOCKED IN LAST 7 DAYS:
PROMPT ==================================================
set feedback on
select USERNAME,ACCOUNT_STATUS,CREATED,LAST_LOGIN,EXPIRY_DATE,DEFAULT_TABLESPACE from dba_users where ACCOUNT_STATUS NOT LIKE 'OPEN' and EXPIRY_DATE > sysdate -7;
PROMPT SCHEMA SIZES
PROMPT ==================================================
col OWNER for a20;
select owner,round(sum(bytes/1024/1024/1024)) "Size in GB" from dba_segments group by owner;
PROMPT LARGE TABLES
PROMPT ==================================================
Col OWNER for a30;
col SEGMENT_NAME for a30;
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,round(BYTES/1024/1024/1024) "SIZE (GB)"from dba_segments where SEGMENT_TYPE='TABLE' and BYTES/1024/1024/1024 > 10 order by 4 desc;
PROMPT THE COUNT OF STALE STATISTICS TABLE SCHEMAWISE
PROMPT ==================================================
select OWNER,count(1) STALE_CNT from dba_tab_statistics where STALE_STATS='YES' GROUP BY OWNER;
PROMPT CHECK FOR TOP 10 TABLES
PROMPT ==================================================
SELECT * FROM (SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,round(SUM(BYTES/1024/1024)) "SIZE MB" FROM DBA_SEGMENTS WHERE SEGMENT_TYPE like 'TABLE%' AND SEGMENT_NAME NOT LIKE 'BIN%' AND OWNER IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN' AND USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP')) GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE ORDER BY "SIZE MB" DESC) WHERE ROWNUM<=10;
PROMPT CHECK FOR TOP 10 INDEXES
PROMPT ==================================================
Col OWNER for a20;
col SEGMENT_NAME for a30;
SELECT * FROM (SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,round(BYTES/1024/1024) "SIZE MB" FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='INDEX' AND SEGMENT_NAME NOT LIKE 'BIN%' AND OWNER IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS='OPEN' AND USERNAME NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') ) ORDER BY BYTES/1024/1024 DESC) WHERE ROWNUM<=10;
No comments:
Post a Comment