Database Report

-- +----------------------------------------------------------------------------+ -- | Jeffrey M. Hunter | -- | jhunter@idevelopment.info | -- | www.idevelopment.info | -- |----------------------------------------------------------------------------| -- | Copyright (c) 1998-2008 Jeffrey M. Hunter. All rights reserved. | -- |----------------------------------------------------------------------------| -- | DATABASE : Oracle | -- | FILE : dba_snapshot_database_9i.sql | -- | CLASS : Database Administration | -- | PURPOSE : This SQL script provides a detailed report (in HTML format) on | -- | all database metrics including installed options, storage, | -- | performance data, and security. | -- | VERSION : This script was designed for Oracle9i. | -- | USAGE : | -- | | -- | sqlplus -s /@ @dba_snapshot_database_9i.sql | -- | | -- | TESTING : This script has been successfully tested on the following | -- | platforms: | -- | | -- | Solaris : Oracle Database 9.2.0.8.0 | -- | | -- | NOTE : As with any code, ensure to test this script in a development | -- | environment before attempting to run it in production. | -- +----------------------------------------------------------------------------+ define reportHeader="Snapshot Database 9i

" -- +----------------------------------------------------------------------------+ -- | SCRIPT SETTINGS | -- +----------------------------------------------------------------------------+ set termout off set echo off set feedback off set heading off set verify off set wrap on set trimspool on set serveroutput on set escape on set pagesize 50000 set linesize 175 set long 2000000000 clear buffer computes columns breaks define fileName=dba_snapshot_database_9i define versionNumber=3.6 define statsPackUser=PERFSTAT -- +----------------------------------------------------------------------------+ -- | GATHER DATABASE REPORT INFORMATION | -- +----------------------------------------------------------------------------+ COLUMN tdate NEW_VALUE _date NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual; COLUMN time NEW_VALUE _time NOPRINT SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual; COLUMN date_time NEW_VALUE _date_time NOPRINT SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual; COLUMN spool_time NEW_VALUE _spool_time NOPRINT SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual; COLUMN dbname NEW_VALUE _dbname NOPRINT SELECT name dbname FROM v$database; COLUMN global_name NEW_VALUE _global_name NOPRINT SELECT global_name global_name FROM global_name; COLUMN blocksize NEW_VALUE _blocksize NOPRINT SELECT value blocksize FROM v$parameter WHERE name='db_block_size'; COLUMN startup_time NEW_VALUE _startup_time NOPRINT SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance; COLUMN host_name NEW_VALUE _host_name NOPRINT SELECT host_name host_name FROM v$instance; COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT SELECT value cluster_database FROM v$parameter WHERE name='cluster_database'; COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances'; COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT SELECT user reportRunUser FROM dual; -- +----------------------------------------------------------------------------+ -- | GATHER DATABASE REPORT INFORMATION | -- +----------------------------------------------------------------------------+ set heading on set markup html on spool on preformat off entmap on - head ' - Database Report - ' - body 'BGCOLOR="#C0C0C0"' - table 'WIDTH="90%" BORDER="1"' spool &FileName._&_dbname._&_spool_time..html set markup html on entmap off -- +----------------------------------------------------------------------------+ -- | - REPORT HEADER - | -- +----------------------------------------------------------------------------+ prompt &reportHeader -- +----------------------------------------------------------------------------+ -- | - REPORT INDEX - | -- +----------------------------------------------------------------------------+ prompt prompt

Report Index

- - - - - - - - - - - - - - - - - - - - prompt - - - - - - - - - - - - - - - - - - - - prompt - - - - - - - - - - - - - - prompt - - - - - - - - - - - - - prompt - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - prompt - - - - - - - - - - - - - - prompt - - - - - - - - - - - - - prompt - - - - - - - - - - - - - - - - - - - prompt - - - - - - - - - - - - prompt - - - - - - - - - - - - - prompt - - - - - - - prompt - - - - - - - - - - - - - - - - - - - - - - - - - -

Database and Instance Information
Report Header	Version	Options	Database Registry
Instance Overview	Database Overview	Initialization Parameters	Control Files
Control File Records	Online Redo Logs	Redo Log Switches
Jobs
Jobs
Storage
Tablespaces	Data Files	Database Growth	Tablespace Extents
Tablespace to Owner	Owner to Tablespace
UNDO Segments
UNDO Segments	UNDO Segment Contention
Archiving
Archiving Mode	Archiving Parameters	Archiving History
Recovery Manager - (RMAN)
RMAN Configuration	Backup Sets	Backup Pieces	Backup Control Files
Backup SPFILE
Performance
SGA Information	DB Buffer Cache Hit Ratio	Dictionary Cache Hit Ratio	Library Cache Hit Ratio
Latch Contention	System Wait Statistics	System Statistics	System Event Statistics
File I/O Statistics	File I/O Timings	Average Overall I/O per Second	Redo Log Contention
Full Table Scans	Sorts	Outlines	Outline Hints
SQL Statements With Most Buffer Gets	SQL Statements With Most Disk Reads	Top 10 Tables	Top 10 Procedures
Statspack
List	Parameters
Sessions
Current Sessions	User Session Matrix
Security
User Accounts	Users With DBA Privileges	Roles	Default Passwords
DB Links
Objects
Object Summary	Segment Summary	Top 200 Segments (by size)	Top 200 Segments (by number of extents)
Directories	Libraries	Types	Type Attributes
Type Methods	Collections	LOB Segments	Objects Unable to Extend
Objects Which Are Nearing MAXEXTENTS	Invalid Objects	Procedural Object Errors	Objects Without Statistics
Tables Suffering From Row Chaining/Migration	Users With Default Tablespace - (SYSTEM)	Users With Default Temp Tablespace - (SYSTEM)	Objects in the SYSTEM Tablespace
Online Analytical Processing - (OLAP)
Dimensions	Dimension Levels	Dimension Attributes	Dimension Hierarchies
Cubes	Materialized Views	Materialized View Logs
Networking
MTS Dispatcher Statistics	MTS Dispatcher Response Queue Wait Stats	MTS Shared Server Wait Statistics
Replication
Replication Summary	Deferred Transactions	Administrative Request Jobs	Initialization Parameters
(Schedule) - Purge Jobs	(Schedule) - Push Jobs	(Schedule) - Refresh Jobs	(Multi-Master) - Master Groups
(Multi-Master) - Master Groups and Sites	(Materialized View) - Master Site Summary	(Materialized View) - Master Site Logs	(Materialized View) - Master Site Templates
(Materialized View) - Summary	(Materialized View) - Groups	(Materialized View) - Materialized Views	(Materialized View) - Refresh Groups

prompt

-- +============================================================================+ -- | | -- | <<<<< Database and Instance Information >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - REPORT HEADER - | -- +----------------------------------------------------------------------------+ prompt prompt prompt Report Header

prompt - - - - - - - - - - - - -

Report Name	`&FileName._&_dbname._&_spool_time..html`
Snapshot Database Version	`&versionNumber`
Run Date / Time	`&_date_time`
Host Name	`&_host_name`
Database Name	`&_dbname`
Global Database Name	`&_global_name`
Clustered Database?	`&_cluster_database`
Clustered Database Instances	`&_cluster_database_instances`
Database Startup Time	`&_startup_time`
Database Block Size	`&_blocksize`
Report Run User	`&_reportRunUser`
Statspack User	`&statsPackUser`

prompt [Top]

-- SET TIMING ON -- +----------------------------------------------------------------------------+ -- | - VERSION - | -- +----------------------------------------------------------------------------+ prompt prompt Version

CLEAR COLUMNS BREAKS COMPUTES COLUMN banner FORMAT a120 HEADING 'Banner' SELECT * FROM v$version; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OPTIONS - | -- +----------------------------------------------------------------------------+ prompt prompt Options

CLEAR COLUMNS BREAKS COMPUTES COLUMN parameter HEADING 'Option Name' ENTMAP off COLUMN value HEADING 'Installed?' ENTMAP off SELECT DECODE( value , 'FALSE' , '' || parameter || '' , '' || parameter || '') parameter , DECODE( value , 'FALSE' , '

' || value || '

' , '

' || value || '

' ) value FROM v$option ORDER BY parameter; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE REGISTRY - | -- +----------------------------------------------------------------------------+ prompt prompt Database Registry

CLEAR COLUMNS BREAKS COMPUTES COLUMN comp_id FORMAT a75 HEADING 'Component ID' ENTMAP off COLUMN comp_name FORMAT a75 HEADING 'Component Name' ENTMAP off COLUMN version HEADING 'Version' ENTMAP off COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off COLUMN modified FORMAT a75 HEADING 'Modified' ENTMAP off COLUMN control HEADING 'Control' ENTMAP off COLUMN schema HEADING 'Schema' ENTMAP off COLUMN procedure HEADING 'Procedure' ENTMAP off SELECT '' || comp_id || '' comp_id , '

' || comp_name || '

' comp_name , version version , DECODE( status , 'VALID', '

' || status || '

' , 'INVALID', '

' || status || '

' , '

' || status || '

' ) status , '

' || modified || '

' modified , control control , schema schema , procedure procedure FROM dba_registry ORDER BY comp_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - INSTANCE OVERVIEW - | -- +----------------------------------------------------------------------------+ prompt prompt Instance Overview

CLEAR COLUMNS BREAKS COMPUTES COLUMN instance_name FORMAT a75 HEADING 'Instance|Name' ENTMAP off COLUMN instance_number FORMAT a75 HEADING 'Instance|Number' ENTMAP off COLUMN host_name_print FORMAT a75 HEADING 'Host|Name' ENTMAP off COLUMN oracle_version HEADING 'Oracle|Version' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start|Time' ENTMAP off COLUMN uptime HEADING 'Uptime|(in days)' ENTMAP off COLUMN parallel FORMAT a75 HEADING 'Parallel - (RAC)' ENTMAP off COLUMN status FORMAT a75 HEADING 'Instance|Status' ENTMAP off COLUMN logins FORMAT a75 HEADING 'Logins' ENTMAP off COLUMN archiver FORMAT a75 HEADING 'Archiver' ENTMAP off SELECT '

' || instance_name || '

' instance_name , '

' || instance_number || '

' instance_number , '

' || host_name || '

' host_name_print , '

' || version || '

' oracle_version , '

' || TO_CHAR(startup_time,'mm/dd/yyyy HH24:MI:SS') || '

' start_time , ROUND(TO_CHAR(SYSDATE-startup_time), 2) uptime , '

' || parallel || '

' parallel , '

' || status || '

' status , '

' || logins || '

' logins , DECODE( archiver , 'FAILED' , '

' || archiver || '

' , '

' || archiver || '

') archiver FROM v$instance ORDER BY instance_number; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE OVERVIEW - | -- +----------------------------------------------------------------------------+ prompt prompt Database Overview

CLEAR COLUMNS BREAKS COMPUTES COLUMN database_name FORMAT a75 HEADING 'Database Name' ENTMAP off COLUMN dbid HEADING 'Database ID' ENTMAP off COLUMN creation_date FORMAT a75 HEADING 'Creation Date' ENTMAP off COLUMN log_mode HEADING 'Log Mode' ENTMAP off COLUMN open_mode HEADING 'Open Mode' ENTMAP off COLUMN force_logging HEADING 'Force Logging' ENTMAP off COLUMN controlfile_type HEADING 'Controlfile Type' ENTMAP off SELECT '

' || name || '

' database_name , '

' || dbid || '

' dbid , '

' || TO_CHAR(created, 'mm/dd/yyyy HH24:MI:SS') || '

' creation_date , '

' || log_mode || '

' log_mode , '

' || open_mode || '

' open_mode , '

' || force_logging || '

' force_logging , '

' || controlfile_type || '

' controlfile_type FROM v$database; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - INITIALIZATION PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Initialization Parameters

CLEAR COLUMNS BREAKS COMPUTES COLUMN spfile HEADING "SPFILE Usage" SELECT 'This database '|| DECODE( (1-SIGN(1-SIGN(count(*) - 0))) , 1 , 'IS' , 'IS NOT') || ' using an SPFILE.'spfile FROM v$spparameter WHERE value IS NOT null; COLUMN pname FORMAT a75 HEADING 'Parameter Name' ENTMAP off COLUMN value FORMAT a75 HEADING 'Value' ENTMAP off COLUMN isdefault FORMAT a75 HEADING 'Is Default?' ENTMAP off COLUMN issys_modifiable FORMAT a75 HEADING 'Is Dynamic?' ENTMAP off SELECT DECODE( isdefault , 'FALSE' , '' || SUBSTR(name,0,512) || '' , '' || SUBSTR(name,0,512) || '' ) pname , DECODE( isdefault , 'FALSE' , '' || SUBSTR(value,0,512) || '' , SUBSTR(value,0,512) ) value , DECODE( isdefault , 'FALSE' , '

' || isdefault || '

' , '

' || isdefault || '

') isdefault , DECODE( isdefault , 'FALSE' , '

' || issys_modifiable || '

' , '

' || issys_modifiable || '

') issys_modifiable FROM v$parameter ORDER BY name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - CONTROL FILES - | -- +----------------------------------------------------------------------------+ prompt prompt Control Files

CLEAR COLUMNS BREAKS COMPUTES COLUMN name HEADING 'Controlfile Name' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off SELECT '' || c.name || '' name , DECODE( c.status , NULL , '

VALID

' , '

' || c.status || '

') status FROM v$controlfile c ORDER BY c.name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - CONTROL FILE RECORDS - | -- +----------------------------------------------------------------------------+ prompt prompt Control File Records

CLEAR COLUMNS BREAKS COMPUTES COLUMN type FORMAT a95 HEADING 'Record Section Type' ENTMAP off COLUMN record_size FORMAT 999,999 HEADING 'Record Size|(in bytes)' ENTMAP off COLUMN records_total FORMAT 999,999 HEADING 'Records Allocated' ENTMAP off COLUMN bytes_alloc FORMAT 999,999,999 HEADING 'Bytes Allocated' ENTMAP off COLUMN records_used FORMAT 999,999 HEADING 'Records Used' ENTMAP off COLUMN bytes_used FORMAT 999,999,999 HEADING 'Bytes Used' ENTMAP off COLUMN pct_used FORMAT B999 HEADING '% Used' ENTMAP off COLUMN first_index HEADING 'First Index' ENTMAP off COLUMN last_index HEADING 'Last Index' ENTMAP off COLUMN last_recid HEADING 'Last RecID' ENTMAP off BREAK ON REPORT COMPUTE sum LABEL 'Total: ' OF record_size records_total bytes_alloc records_used bytes_used ON report COMPUTE avg LABEL 'Average: ' OF pct_used ON report SELECT '

' || type || '

' type , record_size , records_total , (records_total * record_size) bytes_alloc , records_used , (records_used * record_size) bytes_used , NVL(records_used/records_total * 100, 0) pct_used , first_index , last_index , last_recid FROM v$controlfile_record_section ORDER BY type; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ONLINE REDO LOGS - | -- +----------------------------------------------------------------------------+ prompt prompt Online Redo Logs

CLEAR COLUMNS BREAKS COMPUTES COLUMN groupno FORMAT a75 HEADING 'Group Number' ENTMAP off COLUMN member HEADING 'Member' ENTMAP off COLUMN redo_file_type FORMAT a75 HEADING 'Redo Type' ENTMAP off COLUMN log_status FORMAT a75 HEADING 'Log Status' ENTMAP off COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off COLUMN archived FORMAT a75 HEADING 'Archived?' ENTMAP off BREAK ON groupno SELECT '

' || f.group# || '

' groupno , '' || f.member || '' member , f.type redo_file_type , DECODE( l.status , 'CURRENT' , '

' || l.status || '

' , '

' || l.status || '

') log_status , l.bytes bytes , '

' || l.archived || '

' archived FROM v$logfile f , v$log l WHERE f.group# = l.group# ORDER BY f.group# , f.member; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - REDO LOG SWITCHES - | -- +----------------------------------------------------------------------------+ prompt prompt Redo Log Switches

CLEAR COLUMNS BREAKS COMPUTES COLUMN DAY FORMAT a75 HEADING 'Day / Time' ENTMAP off COLUMN H00 FORMAT 999,999B HEADING '00' ENTMAP off COLUMN H01 FORMAT 999,999B HEADING '01' ENTMAP off COLUMN H02 FORMAT 999,999B HEADING '02' ENTMAP off COLUMN H03 FORMAT 999,999B HEADING '03' ENTMAP off COLUMN H04 FORMAT 999,999B HEADING '04' ENTMAP off COLUMN H05 FORMAT 999,999B HEADING '05' ENTMAP off COLUMN H06 FORMAT 999,999B HEADING '06' ENTMAP off COLUMN H07 FORMAT 999,999B HEADING '07' ENTMAP off COLUMN H08 FORMAT 999,999B HEADING '08' ENTMAP off COLUMN H09 FORMAT 999,999B HEADING '09' ENTMAP off COLUMN H10 FORMAT 999,999B HEADING '10' ENTMAP off COLUMN H11 FORMAT 999,999B HEADING '11' ENTMAP off COLUMN H12 FORMAT 999,999B HEADING '12' ENTMAP off COLUMN H13 FORMAT 999,999B HEADING '13' ENTMAP off COLUMN H14 FORMAT 999,999B HEADING '14' ENTMAP off COLUMN H15 FORMAT 999,999B HEADING '15' ENTMAP off COLUMN H16 FORMAT 999,999B HEADING '16' ENTMAP off COLUMN H17 FORMAT 999,999B HEADING '17' ENTMAP off COLUMN H18 FORMAT 999,999B HEADING '18' ENTMAP off COLUMN H19 FORMAT 999,999B HEADING '19' ENTMAP off COLUMN H20 FORMAT 999,999B HEADING '20' ENTMAP off COLUMN H21 FORMAT 999,999B HEADING '21' ENTMAP off COLUMN H22 FORMAT 999,999B HEADING '22' ENTMAP off COLUMN H23 FORMAT 999,999B HEADING '23' ENTMAP off COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' avg label 'Average:' OF total ON report SELECT '

' || SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) || '

' DAY , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23 , COUNT(*) TOTAL FROM v$log_history a GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) / prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< JOBS >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - JOBS - | -- +----------------------------------------------------------------------------+ prompt prompt Jobs

CLEAR COLUMNS BREAKS COMPUTES COLUMN job_id FORMAT a75 HEADING 'Job ID' ENTMAP off COLUMN username FORMAT a75 HEADING 'User' ENTMAP off COLUMN what FORMAT a175 HEADING 'What' ENTMAP off COLUMN next_date FORMAT a110 HEADING 'Next Run Date' ENTMAP off COLUMN interval FORMAT a75 HEADING 'Interval' ENTMAP off COLUMN last_date FORMAT a110 HEADING 'Last Run Date' ENTMAP off COLUMN failures FORMAT a75 HEADING 'Failures' ENTMAP off COLUMN broken FORMAT a75 HEADING 'Broken?' ENTMAP off SELECT DECODE( broken , 'Y' , '' || job || '
' , '' || job || '
') job_id , DECODE( broken , 'Y' , '' || log_user || '' , log_user ) username , DECODE( broken , 'Y' , '' || what || '' , what ) what , DECODE( broken , 'Y' , '

' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(next_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') next_date , DECODE( broken , 'Y' , '' || interval || '' , interval ) interval , DECODE( broken , 'Y' , '

' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' , '

' || NVL(TO_CHAR(last_date, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

') last_date , DECODE( broken , 'Y' , '' || failures || '
' , '

' || failures || '

') failures , DECODE( broken , 'Y' , '' || broken || '
' , '

' || broken || '

') broken FROM dba_jobs ORDER BY job; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< STORAGE >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - TABLESPACES - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespaces

CLEAR COLUMNS BREAKS COMPUTES COLUMN status HEADING 'Status' ENTMAP off COLUMN name HEADING 'Tablespace Name' ENTMAP off COLUMN type FORMAT a12 HEADING 'TS Type' ENTMAP off COLUMN extent_mgt FORMAT a10 HEADING 'Ext. Mgt.' ENTMAP off COLUMN segment_mgt FORMAT a9 HEADING 'Seg. Mgt.' ENTMAP off COLUMN ts_size FORMAT 999,999,999,999,999 HEADING 'Tablespace Size' ENTMAP off COLUMN free FORMAT 999,999,999,999,999 HEADING 'Free (in bytes)' ENTMAP off COLUMN used FORMAT 999,999,999,999,999 HEADING 'Used (in bytes)' ENTMAP off COLUMN pct_used HEADING 'Pct. Used' ENTMAP off BREAK ON report COMPUTE SUM label 'Total:' OF ts_size used free ON report SELECT DECODE( d.status , 'OFFLINE' , '

' || d.status || '

' , '

' || d.status || '

') status , '' || d.tablespace_name || '' name , d.contents type , d.extent_management extent_mgt , d.segment_space_management segment_mgt , NVL(a.bytes, 0) ts_size , NVL(f.bytes, 0) free , NVL(a.bytes - NVL(f.bytes, 0), 0) used , '

' || DECODE ( (1-SIGN(1-SIGN(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) - 90))) , 1 , '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '' , '' || TO_CHAR(TRUNC(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0))) || '' ) || ' %

' pct_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 DECODE( d.status , 'OFFLINE' , '

' || d.status || '

' , '

' || d.status || '

') status , '' || d.tablespace_name || '' name , d.contents type , d.extent_management extent_mgt , d.segment_space_management segment_mgt , NVL(a.bytes, 0) ts_size , NVL(a.bytes - NVL(t.bytes,0), 0) free , NVL(t.bytes, 0) used , '

' || DECODE ( (1-SIGN(1-SIGN(TRUNC(NVL(t.bytes / a.bytes * 100, 0)) - 90))) , 1 , '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '' , '' || TO_CHAR(TRUNC(NVL(t.bytes / a.bytes * 100, 0))) || '' ) || ' %

' pct_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 v$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 2; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATA FILES - | -- +----------------------------------------------------------------------------+ prompt prompt Data Files

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace HEADING 'Tablespace Name / File Class' ENTMAP off COLUMN filename HEADING 'Filename' ENTMAP off COLUMN filesize FORMAT 999,999,999,999,999 HEADING 'File Size' ENTMAP off COLUMN autoextensible HEADING 'Autoextensible' ENTMAP off COLUMN increment_by FORMAT 999,999,999 HEADING 'Next' ENTMAP off COLUMN maxbytes FORMAT 999,999,999,999,999 HEADING 'Max' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF filesize ON report SELECT /*+ ordered */ '' || d.tablespace_name || '' tablespace , '' || d.file_name || '' filename , d.bytes filesize , '

' || NVL(d.autoextensible, '
') || '

' autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_data_files d , v$datafile v , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e WHERE (d.file_name = v.name) UNION SELECT '' || d.tablespace_name || '' tablespace , '' || d.file_name || '' filename , d.bytes filesize , '

' || NVL(d.autoextensible, '
') || '

' autoextensible , d.increment_by * e.value increment_by , d.maxbytes maxbytes FROM sys.dba_temp_files d , (SELECT value FROM v$parameter WHERE name = 'db_block_size') e UNION SELECT '[ ONLINE REDO LOG ]' , '' || a.member || '' , b.bytes , null , null , null FROM v$logfile a , v$log b WHERE a.group# = b.group# UNION SELECT '[ CONTROL FILE ]' , '' || a.name || '' , null , null , null , null FROM v$controlfile a ORDER BY 1 , 2; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DATABASE GROWTH - | -- +----------------------------------------------------------------------------+ prompt prompt Database Growth

CLEAR COLUMNS BREAKS COMPUTES COLUMN month FORMAT a75 HEADING 'Month' COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (bytes)' BREAK ON report COMPUTE SUM label 'Total:' OF growth ON report SELECT '

' || TO_CHAR(creation_time, 'RRRR-MM') || '

' month , SUM(bytes) growth FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'RRRR-MM') ORDER BY TO_CHAR(creation_time, 'RRRR-MM'); prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TABLESPACE EXTENTS - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespace Extents

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace_name HEADING 'Tablespace Name' ENTMAP off COLUMN largest_ext FORMAT 999,999,999,999,999 HEADING 'Largest Extent' ENTMAP off COLUMN smallest_ext FORMAT 999,999,999,999,999 HEADING 'Smallest Extent' ENTMAP off COLUMN total_free FORMAT 999,999,999,999,999 HEADING 'Total Free' ENTMAP off COLUMN pieces FORMAT 999,999,999,999,999 HEADING 'Number of Free Extents' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' OF largest_ext smallest_ext total_free pieces ON report SELECT '' || tablespace_name || '' tablespace_name , max(bytes) largest_ext , min(bytes) smallest_ext , sum(bytes) total_free , count(*) pieces FROM dba_free_space GROUP BY tablespace_name ORDER BY tablespace_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TABLESPACE TO OWNER - | -- +----------------------------------------------------------------------------+ prompt prompt Tablespace to Owner

CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off BREAK ON report ON tablespace_name COMPUTE sum LABEL 'Total: ' OF seg_count bytes ON report SELECT '' || tablespace_name || '' tablespace_name , '

' || owner || '

' owner , '

' || segment_type || '

' segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments GROUP BY tablespace_name , owner , segment_type ORDER BY tablespace_name , owner , segment_type; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OWNER TO TABLESPACE - | -- +----------------------------------------------------------------------------+ prompt prompt Owner to Tablespace

CLEAR COLUMNS BREAKS COMPUTES COLUMN owner FORMAT a75 HEADING 'Owner' ENTMAP off COLUMN tablespace_name FORMAT a75 HEADING 'Tablespace Name' ENTMAP off COLUMN segment_type FORMAT a75 HEADING 'Segment Type' ENTMAP off COLUMN bytes FORMAT 999,999,999,999,999 HEADING 'Size (in Bytes)' ENTMAP off COLUMN seg_count FORMAT 999,999,999,999 HEADING 'Segment Count' ENTMAP off BREAK ON report ON owner COMPUTE sum LABEL 'Total: ' OF seg_count bytes ON report SELECT '' || owner || '' owner , '

' || tablespace_name || '

' tablespace_name , '

' || segment_type || '

' segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments GROUP BY owner , tablespace_name , segment_type ORDER BY owner , tablespace_name , segment_type; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< UNDO Segments >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - UNDO SEGMENTS - | -- +----------------------------------------------------------------------------+ prompt prompt UNDO Segments

CLEAR COLUMNS BREAKS COMPUTES COLUMN roll_name HEADING 'UNDO Segment Name' ENTMAP off COLUMN tablespace HEADING 'Tablspace' ENTMAP off COLUMN in_extents HEADING 'Init/Next Extents' ENTMAP off COLUMN m_extents HEADING 'Min/Max Extents' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN wraps FORMAT 999,999,999 HEADING 'Wraps' ENTMAP off COLUMN shrinks FORMAT 999,999,999 HEADING 'Shrinks' ENTMAP off COLUMN opt FORMAT 999,999,999,999 HEADING 'Opt. Size' ENTMAP off COLUMN bytes FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off COLUMN extents FORMAT 999,999,999 HEADING 'Extents' ENTMAP off CLEAR COMPUTES BREAKS BREAK ON report COMPUTE sum LABEL 'Total:' OF bytes extents shrinks wraps ON report SELECT '' || a.owner || '.' || a.segment_name || '' roll_name , a.tablespace_name tablespace , '

' || TO_CHAR(a.initial_extent) || ' / ' || TO_CHAR(a.next_extent) || '

' in_extents , '

' || TO_CHAR(a.min_extents) || ' / ' || TO_CHAR(a.max_extents) || '

' m_extents , DECODE( a.status , 'OFFLINE' , '

' || a.status || '

' , '

' || a.status || '

') status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize opt FROM dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat d WHERE a.segment_name = b.segment_name AND a.segment_name = c.name (+) AND c.usn = d.usn (+) ORDER BY a.segment_name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - UNDO SEGMENT CONTENTION - | -- +----------------------------------------------------------------------------+ prompt prompt UNDO Segment Contention

prompt UNDO statistics from V$ROLLSTAT - (ordered by waits) CLEAR COLUMNS BREAKS COMPUTES COLUMN roll_name HEADING 'UNDO Segment Name' ENTMAP off COLUMN gets FORMAT 999,999,999 HEADING 'Gets' ENTMAP off COLUMN waits FORMAT 999,999,999 HEADING 'Waits' ENTMAP off COLUMN immediate_misses FORMAT 999,999,999 HEADING 'Immediate Misses' ENTMAP off COLUMN hit_ratio HEADING 'Hit Ratio' ENTMAP off BREAK ON report COMPUTE SUM label 'Total:' OF gets waits ON report SELECT '' || b.name || '' roll_name , gets gets , waits waits , '

' || TO_CHAR(ROUND(((gets - waits)*100)/gets, 2)) || '%

' hit_ratio FROM sys.v_$rollstat a , sys.v_$rollname b WHERE a.USN = b.USN ORDER BY waits DESC; prompt prompt Wait Statistics CLEAR COLUMNS BREAKS COMPUTES COLUMN class HEADING 'Class' COLUMN ratio HEADING 'Wait Ratio' SELECT '' || w.class || '' class , '

' || TO_CHAR(ROUND(100*(w.count/SUM(s.value)),8)) || '%

' ratio FROM v$waitstat w , v$sysstat s WHERE w.class IN ( 'system undo header' , 'system undo block' , 'undo header' , 'undo block' ) AND s.name IN ('db block gets', 'consistent gets') GROUP BY w.class , w.count; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< ARCHIVING >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - ARCHIVING MODE - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving Mode

CLEAR COLUMNS BREAKS COMPUTES COLUMN db_log_mode FORMAT a95 HEADING 'Database Log Mode' ENTMAP off COLUMN log_archive_start FORMAT a95 HEADING 'Automatic Archival' ENTMAP off COLUMN oldest_online_log_sequence FORMAT 999999999999999 HEADING 'Oldest Online Log Sequence' ENTMAP off COLUMN current_log_seq FORMAT 999999999999999 HEADING 'Current Log Sequence' ENTMAP off SELECT '

' || d.log_mode || '

' db_log_mode , '

' || p.log_archive_start || '

' log_archive_start , c.current_log_seq current_log_seq , o.oldest_online_log_sequence oldest_online_log_sequence FROM (select DECODE( log_mode , 'ARCHIVELOG', 'Archive Mode' , 'NOARCHIVELOG', 'No Archive Mode' , log_mode ) log_mode from v$database ) d , (select DECODE( a.value , 'TRUE', 'Enabled' , 'FALSE', 'Disabled') log_archive_start from v$parameter a where a.name = 'log_archive_start' ) p , (select a.sequence# current_log_seq from v$log a where a.status = 'CURRENT' ) c , (select min(a.sequence#) oldest_online_log_sequence from v$log a ) o / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVING PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving Parameters

CLEAR COLUMNS BREAKS COMPUTES COLUMN name HEADING 'Parameter Name' ENTMAP off COLUMN value HEADING 'Parameter Value' ENTMAP off SELECT '' || a.name || '' name , a.value value FROM v$parameter a WHERE a.name like 'log_%' ORDER BY a.name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - ARCHIVING HISTORY - | -- +----------------------------------------------------------------------------+ prompt prompt Archiving History

CLEAR COLUMNS BREAKS COMPUTES COLUMN sequence# FORMAT a79 HEADING 'Sequence#' ENTMAP off COLUMN name HEADING 'Name' ENTMAP off COLUMN first_change# HEADING 'First|Change #' ENTMAP off COLUMN first_time FORMAT a75 HEADING 'First|Time' ENTMAP off COLUMN next_change# HEADING 'Next|Change #' ENTMAP off COLUMN next_time FORMAT a75 HEADING 'Next|Time' ENTMAP off COLUMN log_size FORMAT 999,999,999,999,999 HEADING 'Size (in bytes)' ENTMAP off COLUMN archived FORMAT a31 HEADING 'Archived?' ENTMAP off COLUMN applied FORMAT a31 HEADING 'Applied?' ENTMAP off COLUMN deleted FORMAT a31 HEADING 'Deleted?' ENTMAP off COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off SELECT '

' || sequence# || '

' sequence# , name , first_change# , '

' || TO_CHAR(first_time, 'mm/dd/yyyy HH24:MI:SS') || '

' first_time , next_change# , '

' || TO_CHAR(next_time, 'mm/dd/yyyy HH24:MI:SS') || '

' next_time , (blocks * block_size) log_size , '

' || archived || '

' archived , '

' || applied || '

' applied , '

' || deleted || '

' deleted , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'U', '

Unavailable

' , 'X', '

Expired

' ) status FROM v$archived_log WHERE status in ('A') ORDER BY sequence#; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< RMAN >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - RMAN CONFIGURATION - | -- +----------------------------------------------------------------------------+ prompt prompt RMAN Configuration

prompt All non-default RMAN configuration settings CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a130 HEADING 'Name' ENTMAP off COLUMN value HEADING 'Value' ENTMAP off SELECT '

' || name || '

' name , value FROM v$rman_configuration ORDER BY name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - BACKUP SETS - | -- +----------------------------------------------------------------------------+ prompt prompt Backup Sets

prompt Available backup sets contained in the control file including available and expired backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN backup_type FORMAT a70 HEADING 'Backup Type' ENTMAP off COLUMN device_type HEADING 'Device Type' ENTMAP off COLUMN controlfile_included FORMAT a30 HEADING 'Controlfile Included?' ENTMAP off COLUMN spfile_included FORMAT a30 HEADING 'SPFILE Included?' ENTMAP off COLUMN incremental_level HEADING 'Incremental Level' ENTMAP off COLUMN pieces FORMAT 999,999,999,999 HEADING '# of Pieces' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a75 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN tag HEADING 'Tag' ENTMAP off COLUMN block_size FORMAT 999,999,999,999,999 HEADING 'Block Size' ENTMAP off COLUMN keep FORMAT a40 HEADING 'Keep?' ENTMAP off COLUMN keep_until FORMAT a75 HEADING 'Keep Until' ENTMAP off COLUMN keep_options FORMAT a15 HEADING 'Keep Options' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' OF pieces elapsed_seconds ON report SELECT '

' || bs.recid || '

' bs_key , DECODE(backup_type , 'L', '

Archived Redo Logs

' , 'D', '

Datafile Full Backup

' , 'I', '

Incremental Backup

') backup_type , '

' || device_type || '

' device_type , '

' || DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '

' controlfile_included , '

' || NVL(sp.spfile_included, '-') || '

' spfile_included , bs.incremental_level incremental_level , bs.pieces pieces , '

' || TO_CHAR(bs.start_time, 'mm/dd/yyyy HH24:MI:SS') || '

' start_time , '

' || TO_CHAR(bs.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '

' completion_time , bs.elapsed_seconds elapsed_seconds , bp.tag tag , bs.block_size block_size , '

' || bs.keep || '

' keep , '

' || NVL(TO_CHAR(bs.keep_until, 'mm/dd/yyyy HH24:MI:SS'), '
') || '

' keep_until , bs.keep_options keep_options FROM v$backup_set bs , (select distinct set_stamp , set_count , tag , device_type from v$backup_piece where status in ('A', 'X')) bp , (select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bs.set_stamp = sp.set_stamp (+) AND bs.set_count = sp.set_count (+) ORDER BY bs.recid; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - BACKUP PIECES - | -- +----------------------------------------------------------------------------+ prompt prompt Backup Pieces

prompt Available backup pieces contained in the control file including available and expired backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a75 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a75 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle , '

' || TO_CHAR(bp.start_time, 'mm/dd/yyyy HH24:MI:SS') || '

' start_time , '

' || TO_CHAR(bp.completion_time, 'mm/dd/yyyy HH24:MI:SS') || '

' completion_time , bp.elapsed_seconds elapsed_seconds FROM v$backup_set bs , v$backup_piece bp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') ORDER BY bs.recid , piece#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - BACKUP CONTROL FILES - | -- +----------------------------------------------------------------------------+ prompt prompt Backup Control Files

prompt Available automatic control files within all available (and expired) backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN controlfile_included FORMAT a75 HEADING 'Controlfile Included?' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , '

' || DECODE(bs.controlfile_included, 'NO', '-', bs.controlfile_included) || '

' controlfile_included , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle FROM v$backup_set bs , v$backup_piece bp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') AND bs.controlfile_included != 'NO' ORDER BY bs.recid , piece#; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - BACKUP SPFILE - | -- +----------------------------------------------------------------------------+ prompt prompt Backup SPFILE

prompt Available automatic SPFILE backups within all available (and expired) backup sets CLEAR COLUMNS BREAKS COMPUTES COLUMN bs_key FORMAT a75 HEADING 'BS Key' ENTMAP off COLUMN piece# HEADING 'Piece #' ENTMAP off COLUMN copy# HEADING 'Copy #' ENTMAP off COLUMN bp_key HEADING 'BP Key' ENTMAP off COLUMN spfile_included FORMAT a75 HEADING 'SPFILE Included?' ENTMAP off COLUMN status HEADING 'Status' ENTMAP off COLUMN handle HEADING 'Handle' ENTMAP off COLUMN start_time FORMAT a40 HEADING 'Start Time' ENTMAP off COLUMN completion_time FORMAT a40 HEADING 'End Time' ENTMAP off COLUMN elapsed_seconds FORMAT 999,999,999,999,999 HEADING 'Elapsed Seconds' ENTMAP off COLUMN deleted FORMAT a10 HEADING 'Deleted?' ENTMAP off BREAK ON bs_key SELECT '

' || bs.recid || '

' bs_key , bp.piece# piece# , bp.copy# copy# , bp.recid bp_key , '

' || NVL(sp.spfile_included, '-') || '

' spfile_included , DECODE( status , 'A', '

Available

' , 'D', '

Deleted

' , 'X', '

Expired

') status , handle handle FROM v$backup_set bs , v$backup_piece bp , (select distinct set_stamp, set_count, 'YES' spfile_included from v$backup_spfile) sp WHERE bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bp.status IN ('A', 'X') AND bs.set_stamp = sp.set_stamp AND bs.set_count = sp.set_count ORDER BY bs.recid , piece#; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< PERFORMANCE >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - SGA INFORMATION - | -- +----------------------------------------------------------------------------+ prompt prompt SGA Information

CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a79 HEADING 'Pool Name' ENTMAP off COLUMN value FORMAT 999,999,999,999 HEADING 'Bytes' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' OF value ON report SELECT '

' || name || '

' name , value FROM v$sga; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DB BUFFER CACHE HIT RATIO - | -- +----------------------------------------------------------------------------+ prompt prompt DB Buffer Cache Hit Ratio

CLEAR COLUMNS BREAKS COMPUTES COLUMN consistent_gets FORMAT 999,999,999,999,999,999 HEADING 'Consistent Gets' ENTMAP off COLUMN db_block_gets FORMAT 999,999,999,999,999,999 HEADING 'DB Block Gets' ENTMAP off COLUMN phys_reads FORMAT 999,999,999,999,999,999 HEADING 'Physical Reads' ENTMAP off COLUMN db_hit_ratio HEADING 'Hit Ratio' ENTMAP off SELECT SUM(DECODE(name, 'consistent gets', value, 0)) consistent_gets , SUM(DECODE(name, 'db block gets', value, 0)) db_block_gets , SUM(DECODE(name, 'physical reads', value, 0)) phys_reads , '

' || TO_CHAR(ROUND((SUM(DECODE(name, 'consistent gets', value, 0)) + SUM(DECODE(name, 'db block gets', value, 0)) - SUM(DECODE(name, 'physical reads', value, 0))) / (SUM(DECODE(name, 'consistent gets', value, 0)) + SUM(DECODE(name, 'db block gets', value, 0)))*100, 2)) || '%

' db_hit_ratio FROM v$sysstat; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - DICTIONARY CACHE HIT RATIO - | -- +----------------------------------------------------------------------------+ prompt prompt Dictionary Cache Hit Ratio

CLEAR COLUMNS BREAKS COMPUTES COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Misses' ENTMAP off COLUMN misses FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off COLUMN dc_hit_ratio HEADING 'Hit Ratio' ENTMAP off SELECT SUM(gets) gets , SUM(getmisses) misses , '

' || TO_CHAR(ROUND((((SUM(gets)-SUM(getmisses))/SUM(gets))*100), 2)) || '%

' dc_hit_ratio FROM v$rowcache; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - LIBRARY CACHE HIT RATIO - | -- +----------------------------------------------------------------------------+ prompt prompt Library Cache Hit Ratio

CLEAR COLUMNS BREAKS COMPUTES COLUMN pins FORMAT 999,999,999,999,999,999 HEADING 'Pins - (Executions)' ENTMAP off COLUMN reloads FORMAT 999,999,999,999,999,999 HEADING 'Reloads - (Cache Miss)' ENTMAP off COLUMN lc_hit_ratio HEADING 'Hit Ratio' ENTMAP off SELECT SUM(pins) pins , SUM(reloads) reloads , '

' || TO_CHAR(ROUND((((SUM(pins)-SUM(reloads))/SUM(pins))*100),2)) || '%

' lc_hit_ratio FROM v$librarycache; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - LATCH CONTENTION - | -- +----------------------------------------------------------------------------+ prompt prompt Latch Contention

prompt Latches with "gets", "misses", "sleeps", "immediate gets", or "immediate misses" greater than 0 - (ordered by misses) CLEAR COLUMNS BREAKS COMPUTES COLUMN latch_name FORMAT a110 HEADING 'Latch Name' ENTMAP off COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off COLUMN misses FORMAT 999,999,999,999,999,999 HEADING 'Misses' ENTMAP off COLUMN sleeps FORMAT 999,999,999,999,999,999 HEADING 'Sleeps' ENTMAP off COLUMN miss_ratio HEADING 'Willing to Wait Ratio' ENTMAP off COLUMN imm_gets FORMAT 999,999,999,999,999,999 HEADING 'Immediate Gets' ENTMAP off COLUMN imm_misses FORMAT 999,999,999,999,999,999 HEADING 'Immediate Misses' ENTMAP off COLUMN imm_miss_ratio HEADING 'Immediate Ratio' ENTMAP off SELECT '' || SUBSTR(a.name,1,40) || '' latch_name , gets gets , misses misses , sleeps sleeps , '

' || ROUND((misses/(gets+.001))*100, 4) || '%

' miss_ratio , immediate_gets imm_gets , immediate_misses imm_misses , '

' || ROUND((immediate_misses/(immediate_gets+.001))*100, 4) || '%

' imm_miss_ratio FROM v$latch a , v$latchname b WHERE a.latch# = b.latch# AND ( gets > 0 OR misses > 0 OR sleeps > 0 OR immediate_gets > 0 OR immediate_misses > 0 ) ORDER BY misses DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SYSTEM WAIT STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt System Wait Statistics

prompt Classes with counts greater than 0 CLEAR COLUMNS BREAKS COMPUTES COLUMN class FORMAT A95 HEADING 'Class' ENTMAP off COLUMN count FORMAT 99999999999990 HEADING 'Count' ENTMAP off SELECT '' || class || '' class , count FROM v$waitstat WHERE count > 0 ORDER BY 2 DESC , 1; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SYSTEM STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt System Statistics

prompt Statistics with values greater than 0 CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT A95 HEADING 'Name' ENTMAP off COLUMN value FORMAT 999,999,999,999,999,999,999,990 HEADING 'Value' ENTMAP off SELECT '' || name || '' name , value FROM v$sysstat WHERE value > 0 ORDER BY 2 DESC , 1; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SYSTEM EVENT STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt System Event Statistics

prompt Non-idle events with total waits greater than 0 - (ordered by "Time Waited") prompt prompt - EVENT: The name of the wait event.
- TOTAL_WAITS: The total number of waits for this event.
- TOTAL_TIMEOUTS: The total number of timeouts for this event.
- TIME_WAITED: The total amount of time waited for this event, in hundredths of a second.
- AVERAGE_WAIT: The average amount of time waited for this event, in hundredths of a second. CLEAR COLUMNS BREAKS COMPUTES COLUMN event FORMAT a95 HEADING 'Event' ENTMAP off COLUMN total_waits FORMAT 999,999,999,999,999,999 HEADING 'Total Waits' ENTMAP off COLUMN total_timeouts FORMAT 999,999,999,999,999,999 HEADING 'Total Timeouts' ENTMAP off COLUMN time_waited FORMAT 999,999,999,999,999,999 HEADING 'Time Waited' ENTMAP off COLUMN average_wait FORMAT 999,999,999,999,999,999 HEADING 'Average Wait' ENTMAP off SELECT '' || event || '' event , total_waits , total_timeouts , time_waited , average_wait FROM v$system_event WHERE total_waits > 0 AND event NOT IN ( 'PX Idle Wait' , 'pmon timer' , 'smon timer' , 'rdbms ipc message' , 'parallel dequeue wait' , 'parallel query dequeue' , 'virtual circuit' , 'SQL*Net message from client' , 'SQL*Net message to client' , 'SQL*Net more data to client' , 'client message','Null event' , 'WMON goes to sleep' , 'virtual circuit status' , 'dispatcher timer' , 'pipe get' , 'slave wait' , 'KXFX: execution message dequeue - Slaves' , 'parallel query idle wait - Slaves' , 'lock manager wait for remote message') ORDER BY time_waited DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FILE I/O STATISTICS - | -- +----------------------------------------------------------------------------+ prompt prompt File I/O Statistics

prompt Ordered by "Physical Reads" since last startup of the Oracle instance CLEAR COLUMNS BREAKS COMPUTES COLUMN tablespace_name FORMAT a50 HEAD 'Tablespace' ENTMAP off COLUMN fname HEAD 'File Name' ENTMAP off COLUMN phyrds FORMAT 999,999,999,999,999 HEAD 'Physical Reads' ENTMAP off COLUMN phywrts FORMAT 999,999,999,999,999 HEAD 'Physical Writes' ENTMAP off COLUMN read_pct HEAD 'Read Pct.' ENTMAP off COLUMN write_pct HEAD 'Write Pct.' ENTMAP off COLUMN total_io FORMAT 999,999,999,999,999 HEAD 'Total I/O' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF phyrds phywrts total_io ON report SELECT '' || df.tablespace_name || '' tablespace_name , df.file_name fname , fs.phyrds phyrds , '

' || ROUND((fs.phyrds * 100) / (fst.pr + tst.pr), 2) || '%

' read_pct , fs.phywrts phywrts , '

' || ROUND((fs.phywrts * 100) / (fst.pw + tst.pw), 2) || '%

' write_pct , (fs.phyrds + fs.phywrts) total_io FROM sys.dba_data_files df , v$filestat fs , (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst , (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst WHERE df.file_id = fs.file# UNION SELECT '' || tf.tablespace_name || '' tablespace_name , tf.file_name fname , ts.phyrds phyrds , '

' || ROUND((ts.phyrds * 100) / (fst.pr + tst.pr), 2) || '%

' read_pct , ts.phywrts phywrts , '

' || ROUND((ts.phywrts * 100) / (fst.pw + tst.pw), 2) || '%

' write_pct , (ts.phyrds + ts.phywrts) total_io FROM sys.dba_temp_files tf , v$tempstat ts , (select sum(f.phyrds) pr, sum(f.phywrts) pw from v$filestat f) fst , (select sum(t.phyrds) pr, sum(t.phywrts) pw from v$tempstat t) tst WHERE tf.file_id = ts.file# ORDER BY phyrds DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FILE I/O TIMINGS - | -- +----------------------------------------------------------------------------+ prompt prompt File I/O Timings

prompt Average time (in milliseconds) for an I/O call per datafile since last startup of the Oracle instance - (ordered by Physical Reads) CLEAR COLUMNS BREAKS COMPUTES COLUMN fname HEAD 'File Name' ENTMAP off COLUMN phyrds FORMAT 999,999,999,999,999 HEAD 'Physical Reads' ENTMAP off COLUMN read_rate FORMAT 999,999,999,999,999.99 HEAD 'Average Read Time
(milliseconds per read)' ENTMAP off COLUMN phywrts FORMAT 999,999,999,999,999 HEAD 'Physical Writes' ENTMAP off COLUMN write_rate FORMAT 999,999,999,999,999.99 HEAD 'Average Write Time
(milliseconds per write)' ENTMAP off BREAK ON REPORT COMPUTE sum LABEL 'Total: ' OF phyrds phywrts ON report COMPUTE avg LABEL 'Average: ' OF read_rate write_rate ON report SELECT '' || d.name || '' fname , s.phyrds phyrds , ROUND((s.readtim/GREATEST(s.phyrds,1)), 2) read_rate , s.phywrts phywrts , ROUND((s.writetim/GREATEST(s.phywrts,1)),2) write_rate FROM v$filestat s , v$datafile d WHERE s.file# = d.file# UNION SELECT '' || t.name || '' fname , s.phyrds phyrds , ROUND((s.readtim/GREATEST(s.phyrds,1)), 2) read_rate , s.phywrts phywrts , ROUND((s.writetim/GREATEST(s.phywrts,1)),2) write_rate FROM v$tempstat s , v$tempfile t WHERE s.file# = t.file# ORDER BY 2 DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - AVERAGE OVERALL I/O PER SECOND - | -- +----------------------------------------------------------------------------+ prompt prompt Average Overall I/O per Second

prompt Average overall I/O calls (physical read/write calls) since last startup of the Oracle instance CLEAR COLUMNS BREAKS COMPUTES DECLARE CURSOR get_file_io IS SELECT NVL(SUM(a.phyrds + a.phywrts), 0) sum_datafile_io , TO_NUMBER(null) sum_tempfile_io FROM v$filestat a UNION SELECT TO_NUMBER(null) sum_datafile_io , NVL(SUM(b.phyrds + b.phywrts), 0) sum_tempfile_io FROM v$tempstat b; current_time DATE; elapsed_time_seconds NUMBER; sum_datafile_io NUMBER; sum_datafile_io2 NUMBER; sum_tempfile_io NUMBER; sum_tempfile_io2 NUMBER; total_io NUMBER; datafile_io_per_sec NUMBER; tempfile_io_per_sec NUMBER; total_io_per_sec NUMBER; BEGIN OPEN get_file_io; FOR i IN 1..2 LOOP FETCH get_file_io INTO sum_datafile_io, sum_tempfile_io; IF i = 1 THEN sum_datafile_io2 := sum_datafile_io; ELSE sum_tempfile_io2 := sum_tempfile_io; END IF; END LOOP; total_io := sum_datafile_io2 + sum_tempfile_io2; SELECT sysdate INTO current_time FROM dual; SELECT CEIL ((current_time - startup_time)*(60*60*24)) INTO elapsed_time_seconds FROM v$instance; datafile_io_per_sec := sum_datafile_io2/elapsed_time_seconds; tempfile_io_per_sec := sum_tempfile_io2/elapsed_time_seconds; total_io_per_sec := total_io/elapsed_time_seconds; DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE(''); DBMS_OUTPUT.PUT_LINE('

Elapsed Time (in seconds)	' \|\| TO_CHAR(elapsed_time_seconds, '9,999,999,999,999') \|\| '
Datafile I/O Calls per Second	' \|\| TO_CHAR(datafile_io_per_sec, '9,999,999,999,999') \|\| '
Tempfile I/O Calls per Second	' \|\| TO_CHAR(tempfile_io_per_sec, '9,999,999,999,999') \|\| '
Total I/O Calls per Second	' \|\| TO_CHAR(total_io_per_sec, '9,999,999,999,999') \|\| '

'); END; / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - REDO LOG CONTENTION - | -- +----------------------------------------------------------------------------+ prompt prompt Redo Log Contention

prompt All latches like redo% - (ordered by misses) CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a95 HEADING 'Latch Name' ENTMAP off COLUMN gets FORMAT 999,999,999,999,999,999 HEADING 'Gets' ENTMAP off COLUMN misses FORMAT 999,999,999,999 HEADING 'Misses' ENTMAP off COLUMN sleeps FORMAT 999,999,999,999 HEADING 'Sleeps' ENTMAP off COLUMN immediate_gets FORMAT 999,999,999,999,999,999 HEADING 'Immediate Gets' ENTMAP off COLUMN immediate_misses FORMAT 999,999,999,999 HEADING 'Immediate Misses' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total:' OF gets misses sleeps immediate_gets immediate_misses ON report SELECT '

' || INITCAP(name) || '

' name , gets , misses , sleeps , immediate_gets , immediate_misses FROM sys.v_$latch WHERE name LIKE 'redo%' ORDER BY 1; prompt prompt System statistics like redo% CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a95 HEADING 'Statistics Name' COLUMN value FORMAT 999,999,999,999,999 HEADING 'Value' SELECT '

' || INITCAP(name) || '

' name , value FROM v$sysstat WHERE name LIKE 'redo%' ORDER BY 1; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - FULL TABLE SCANS - | -- +----------------------------------------------------------------------------+ prompt prompt Full Table Scans

CLEAR COLUMNS BREAKS COMPUTES COLUMN large_table_scans FORMAT 999,999,999,999,999 HEADING 'Large Table Scans' ENTMAP off COLUMN small_table_scans FORMAT 999,999,999,999,999 HEADING 'Small Table Scans' ENTMAP off COLUMN pct_large_scans HEADING 'Pct. Large Scans' ENTMAP off SELECT a.value large_table_scans , b.value small_table_scans , '

' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%

' pct_large_scans FROM v$sysstat a , v$sysstat b WHERE a.name = 'table scans (long tables)' AND b.name = 'table scans (short tables)'; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SORTS - | -- +----------------------------------------------------------------------------+ prompt prompt Sorts

CLEAR COLUMNS BREAKS COMPUTES COLUMN disk_sorts FORMAT 999,999,999,999,999 HEADING 'Disk Sorts' ENTMAP off COLUMN memory_sorts FORMAT 999,999,999,999,999 HEADING 'Memory Sorts' ENTMAP off COLUMN pct_disk_sorts HEADING 'Pct. Disk Sorts' ENTMAP off SELECT a.value disk_sorts , b.value memory_sorts , '

' || ROUND(100*a.value/DECODE((a.value+b.value),0,1,(a.value+b.value)),2) || '%

' pct_disk_sorts FROM v$sysstat a , v$sysstat b WHERE a.name = 'sorts (disk)' AND b.name = 'sorts (memory)'; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OUTLINES - | -- +----------------------------------------------------------------------------+ prompt prompt Outlines

CLEAR COLUMNS BREAKS COMPUTES COLUMN category FORMAT a125 HEADING 'Category' ENTMAP off COLUMN owner FORMAT a125 HEADING 'Owner' ENTMAP off COLUMN name FORMAT a125 HEADING 'Name' ENTMAP off COLUMN used HEADING 'Used?' ENTMAP off COLUMN timestamp FORMAT a125 HEADING 'Time Stamp' ENTMAP off COLUMN version HEADING 'Version' ENTMAP off COLUMN sql_text HEADING 'SQL Text' ENTMAP off SELECT '

' || category || '

' category , owner , name , used , '

' || TO_CHAR(timestamp, 'mm/dd/yyyy HH24:MI:SS') || '

' timestamp , version , sql_text FROM dba_outlines ORDER BY category , owner , name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - OUTLINE HINTS - | -- +----------------------------------------------------------------------------+ prompt prompt Outline Hints

CLEAR COLUMNS BREAKS COMPUTES COLUMN category FORMAT a125 HEADING 'Category' ENTMAP off COLUMN owner FORMAT a125 HEADING 'Owner' ENTMAP off COLUMN name FORMAT a125 HEADING 'Name' ENTMAP off COLUMN node HEADING 'Node' ENTMAP off COLUMN join_pos HEADING 'Join Position' ENTMAP off COLUMN hint HEADING 'Hint' ENTMAP off BREAK ON category ON owner ON name SELECT '

' || a.category || '

' category , a.owner owner , a.name name , '

' || b.node || '

' node , '

' || b.join_pos || '

' join_pos , b.hint hint FROM dba_outlines a , dba_outline_hints b WHERE a.owner = b.owner AND b.name = b.name ORDER BY category , owner , name; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SQL STATEMENTS WITH MOST BUFFER GETS - | -- +----------------------------------------------------------------------------+ prompt prompt SQL Statements With Most Buffer Gets

prompt Top 100 SQL statements with buffer gets greater than 1000 CLEAR COLUMNS BREAKS COMPUTES COLUMN username FORMAT a75 HEADING 'Username' ENTMAP off COLUMN buffer_gets FORMAT 999,999,999,999,999 HEADING 'Buffer Gets' ENTMAP off COLUMN executions FORMAT 999,999,999,999,999 HEADING 'Executions' ENTMAP off COLUMN gets_per_exec FORMAT 999,999,999,999,999 HEADING 'Buffer Gets / Execution' ENTMAP off COLUMN sql_text HEADING 'SQL Text' ENTMAP off SELECT '' || UPPER(b.username) || '' username , a.buffer_gets buffer_gets , a.executions executions , (a.buffer_gets / decode(a.executions, 0, 1, a.executions)) gets_per_exec , a.sql_text sql_text FROM (SELECT ai.buffer_gets, ai.executions, ai.sql_text, ai.parsing_user_id FROM sys.v_$sqlarea ai ORDER BY ai.buffer_gets ) a , dba_users b WHERE a.parsing_user_id = b.user_id AND a.buffer_gets > 1000 AND b.username NOT IN ('SYS','SYSTEM') AND rownum < 101 ORDER BY a.buffer_gets DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - SQL STATEMENTS WITH MOST DISK READS - | -- +----------------------------------------------------------------------------+ prompt prompt SQL Statements With Most Disk Reads

prompt Top 100 SQL statements with disk reads greater than 1000 CLEAR COLUMNS BREAKS COMPUTES COLUMN username FORMAT a75 HEADING 'Username' ENTMAP off COLUMN disk_reads FORMAT 999,999,999,999,999 HEADING 'Disk Reads' ENTMAP off COLUMN executions FORMAT 999,999,999,999,999 HEADING 'Executions' ENTMAP off COLUMN reads_per_exec FORMAT 999,999,999,999,999 HEADING 'Reads / Execution' ENTMAP off COLUMN sql_text HEADING 'SQL Text' ENTMAP off SELECT '' || UPPER(b.username) || '' username , a.disk_reads disk_reads , a.executions executions , (a.disk_reads / decode(a.executions, 0, 1, a.executions)) reads_per_exec , a.sql_text sql_text FROM (SELECT ai.disk_reads, ai.executions, ai.sql_text, ai.parsing_user_id FROM sys.v_$sqlarea ai ORDER BY ai.buffer_gets ) a , dba_users b WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 1000 AND b.username NOT IN ('SYS','SYSTEM') AND rownum < 101 ORDER BY a.disk_reads DESC; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TOP 10 TABLES - | -- +----------------------------------------------------------------------------+ prompt prompt Top 10 Tables

CLEAR COLUMNS BREAKS COMPUTES COLUMN ctyp FORMAT a79 HEADING 'Command Type' ENTMAP off COLUMN obj FORMAT a30 HEADING 'Object Name' ENTMAP off COLUMN noe FORMAT 999,999,999,999,999 HEADING 'Number of Executions' ENTMAP off COLUMN gets FORMAT 999,999,999,999,999 HEADING 'Buffer Gets' ENTMAP off COLUMN rowp FORMAT 999,999,999,999,999 HEADING 'Rows Processed' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF noe gets rowp ON report SELECT '

' || ctyp || '

' ctyp , obj , 0 - exem noe , gets , rowp FROM ( select distinct exem, ctyp, obj, gets, rowp from (select DECODE( s.command_type , 2, 'INSERT INTO ' , 3, 'SELECT FROM ' , 6, 'UPDATE OF ' , 7, 'DELETE FROM ' , 26, 'LOCK OF ') ctyp , o.owner || '.' || o.name obj , SUM(0 - s.executions) exem , SUM(s.buffer_gets) gets , SUM(s.rows_processed) rowp from v$sql s , v$object_dependency d , v$db_object_cache o where s.command_type IN (2,3,6,7,26) and d.from_address = s.address and d.to_owner = o.owner and d.to_name = o.name and o.type = 'TABLE' group by s.command_type , o.owner , o.name ) ) WHERE rownum <= 10; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - TOP 10 PROCEDURES - | -- +----------------------------------------------------------------------------+ prompt prompt Top 10 Procedures

CLEAR COLUMNS BREAKS COMPUTES COLUMN ptyp FORMAT a79 HEADING 'Object Type' ENTMAP off COLUMN obj FORMAT a42 HEADING 'Object Name' ENTMAP off COLUMN noe FORMAT 999,999,999,999,999 HEADING 'Number of Executions' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF noe ON report SELECT '

' || ptyp || '

' ptyp , obj , 0 - exem noe FROM ( select distinct exem, ptyp, obj from ( select o.type ptyp , o.owner || '.' || o.name obj , 0 - o.executions exem from v$db_object_cache O where o.type in ( 'FUNCTION' , 'PACKAGE' , 'PACKAGE BODY' , 'PROCEDURE' , 'TRIGGER') ) ) WHERE rownum <= 10; prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< STATSPACK >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - STATSPACK LIST - | -- +----------------------------------------------------------------------------+ prompt prompt Statspack List

CLEAR COLUMNS BREAKS COMPUTES COLUMN snap_id FORMAT a75 HEAD 'Snap ID' ENTMAP off COLUMN snap_time FORMAT a75 HEAD 'Statspack Snap Time' ENTMAP off COLUMN startup_time FORMAT a75 HEAD 'Database Startup Time' ENTMAP off BREAK ON startup_time SKIP 1 SELECT '

' || a.snap_id || '

' snap_id , '

' || TO_CHAR(a.startup_time, 'mm/dd/yyyy HH24:MI:SS') || '

' startup_time , '

' || TO_CHAR(a.snap_time, 'mm/dd/yyyy HH24:MI:SS') || '

' snap_time FROM stats$snapshot a , v$database b ORDER BY a.snap_id / prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - STATSPACK PARAMETERS - | -- +----------------------------------------------------------------------------+ prompt prompt Statspack Parameters

CLEAR COLUMNS BREAKS COMPUTES COLUMN name FORMAT a75 HEAD 'Database|Name' ENTMAP off COLUMN snap_level FORMAT 999999 HEAD 'Snap|Level' ENTMAP off COLUMN num_sql FORMAT 999,999 HEAD 'Number|SQL' ENTMAP off COLUMN executions_th FORMAT 999,999 HEAD 'Executions|(TH)' ENTMAP off COLUMN parse_calls_th FORMAT 999,999 HEAD 'Parse|Calls|(TH)' ENTMAP off COLUMN disk_reads_th FORMAT 999,999 HEAD 'Disk|Reads|(TH)' ENTMAP off COLUMN buffer_gets_th FORMAT 999,999 HEAD 'Buffer|Gets|(TH)' ENTMAP off COLUMN sharable_mem_th FORMAT 999,999,999 HEAD 'Sharable|Mem.|(TH)' ENTMAP off COLUMN version_count_th HEAD 'Version|Count|(TH)' ENTMAP off COLUMN pin_statspack HEAD 'Pin|Statspack' ENTMAP off COLUMN all_init HEAD 'All|Init' ENTMAP off COLUMN last_modified FORMAT a75 HEAD 'Last|Modified' ENTMAP off SELECT '' || b.name || '' name , a.snap_level , a.num_sql , a.executions_th , a.parse_calls_th , a.disk_reads_th , a.buffer_gets_th , a.sharable_mem_th , a.version_count_th , '

' || a.pin_statspack || '

' pin_statspack , '

' || a.all_init || '

' all_init , '

' || TO_CHAR(a.last_modified, 'mm/dd/yyyy HH24:MI:SS') || '

' last_modified FROM stats$statspack_parameter a , v$database b / prompt [Top]

-- +============================================================================+ -- | | -- | <<<<< SESSIONS >>>>> | -- | | -- +============================================================================+ -- +----------------------------------------------------------------------------+ -- | - CURRENT SESSIONS - | -- +----------------------------------------------------------------------------+ prompt prompt Current Sessions

CLEAR COLUMNS BREAKS COMPUTES COLUMN count FORMAT a45 HEADING 'Current No. of Processes' ENTMAP off COLUMN value FORMAT a45 HEADING 'Max No. of Processes' ENTMAP off COLUMN pct_usage FORMAT a45 HEADING '% Usage' ENTMAP off SELECT '

' || TO_char(a.count) || '

' count , '

' || b.value || '

' value , '

' || TO_CHAR(ROUND(100*(a.count / b.value), 2)) || '%

' pct_usage FROM (select count(*) count from v$session) a , (select value from v$parameter where name='processes') b; prompt [Top]

-- +----------------------------------------------------------------------------+ -- | - USER SESSION MATRIX - | -- +----------------------------------------------------------------------------+ prompt prompt User Session Matrix

prompt User sessions (including SYS and background processes) COLUMN username FORMAT a79 HEADING 'Oracle User' ENTMAP off COLUMN num_user_sess FORMAT 999,999,999,999 HEADING 'Total Number of Logins' ENTMAP off COLUMN count_a FORMAT 999,999,999 HEADING 'Active Logins' ENTMAP off COLUMN count_i FORMAT 999,999,999 HEADING 'Inactive Logins' ENTMAP off COLUMN count_k FORMAT 999,999,999 HEADING 'Killed Logins' ENTMAP off BREAK ON report COMPUTE sum LABEL 'Total: ' OF num_user_sess count_a count_i count_k ON report SELECT '

' || NVL(sess.username, '[B.G. Process]') || '

' username , count(*) num_user_sess , NVL(act.count, 0) count_a , NVL(inact.count, 0) count_i , NVL(killed.count, 0) count_k FROM v$session sess , (SELECT count(*) count, nvl(username, '[B.G. Process]') username FROM v$session WHERE status = 'ACTIVE' GROUP BY username) act , (SELECT count(*) count, nvl(username, '[B.G. Process]') username FROM v$session WHERE status = 'INACTIVE' GROUP BY username) inact , (SELECT count(*) count, nvl(username, '[B.G. Process]') username FROM v$session WHERE status = 'KILLED' GROUP BY username) killed WHERE nvl(sess.username, '[B.G. Process]') = act.username (+) and nvl(sess.username, '[B.G.