Rem
Rem $Header: db_stats_sql.sql 22-apr-2005.12:47:07 neearora Exp $
Rem
Rem db_stats_sql.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      db_stats_sql.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    neearora    04/22/05 - neearora_database_dump
Rem    neearora    04/18/05 - Created
Rem
rem
rem This script dumps basic database health information
rem

rem
rem This script dumps basic database health information
rem

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

set serveroutput on size 1000000
set pagesize 1000
set linesize 200
set echo on
column event format a30 wrap
column inst_id format 9999 heading inst 
column module format a40
column action format a40 
column client_identifier format a40 
column client_info format a30
column mode_held format a20
column mode_requested format a20
spool &2

column what format a50

rem  Is this RAC database?
prompt "Is this RAC database?";
select parallel from v$instance;

rem get database parameters
prompt "get database parameters";
show parameters;

rem get SGA statistics
prompt "get SGA statistics";
select * from gv$sgastat 
order by inst_id,pool,name ;

rem get system event stats
prompt "get system event stats";
select * from gv$system_event 
order by time_waited desc;

compute sum of count(*) on report
break on report

rem total sessions grouped by user
prompt "total sessions grouped by user";
select schemaname, count(*) from gv$session group by schemaname ;

rem print session information
prompt print session information

select inst_id,sid,module,action,client_identifier
from gv$session
where username = UPPER('&1' )
order by module,inst_id,action ;

rem how are user sessions doing
prompt "what are &1 sessions waiting on";
column sessinfo format a30 wrap
select s.sid,w.event, w.seq#, w.wait_time, w.seconds_in_wait,w.state
  from gv$session_wait w, 
       gv$session s
  where w.event not like 'SQL%'
    and w.sid = s.sid
    and w.inst_id = s.inst_id
    and s.username = UPPER('&1')
  order by w.seconds_in_wait desc;

rem what are the EM sessions doing
prompt "what are the EM sessions doing";
select inst_id,client_info, module, action, count(*)
  from gv$session
 where username = UPPER('&1')
 group by inst_id,client_info, module, action ;

rem how are the loader sessions distributed
prompt "how are the loader sessions distributed";
select inst_id,  sid, machine, action
  from gv$session
 where username = UPPER('&1') and action like 'XMLLoader%';

rem how are the user sessions distributed in general across RAC instances
prompt "how are the &1  sessions distributed in general across RAC instances";
select inst_id, count(*)
  from gv$session
 where username = UPPER('&1')
 group by inst_id;

rem how are user-jobs doing
prompt "how are user-jobs doing";
select what, broken, last_date, this_date, next_date from user_jobs;

rem locking information
prompt locking information

select loc.type,loc.sid,loc.id1,
                    decode(lmode,
                           0, 'None',       
                           1, 'Null',    
                           2, 'Row-S (SS)',
                           3, 'Row-X (SX)',
                           4, 'Share',    
                           5, 'S/Row-X (SSX)', 
                           6, 'Exclusive',    
                           to_char(lmode)) mode_held,
                    decode(request,
                           0, NULL,
                           1, 'Null',       
                           2, 'Row-S (SS)', 
                           3, 'Row-X (SX)',
                           4, 'Share',  
                           5, 'S/Row-X (SSX)',
                           6, 'Exclusive',   
                           to_char(request)) mode_requested
from gv$lock  loc
order by loc.type,loc.id1,loc.request ;


Rem print out locking information, might give warning output
Rem that cannot output trace on remote db, it is ok if not on rac.
prompt "print out hang/system state information .";
Alter session set events 'immediate trace name hanganalyze level 4' ;
Alter session set events 'immediate trace name systemstate level 2' ;

spool off;
quit;