Wednesday, February 9, 2011

DB review script


SET TRIMSPOOL ON;
SET HEADING OFF;
SET FEEDBACK OFF;
SET SERVEROUT ON;
SET PAGESIZE 9999;
SET VERIFY   off;
SET LINES 2500;
COLUMN cpulogfile NEW_VALUE cpulogfile NOPRINT;
SELECT 'D:\db_check\'||Name || '_DB_REPORT_' || TO_CHAR(sysdate,'DDMonYYYY_hh24_mi') || '.log' AS cpulogfile FROM V$DATABASE;
COLUMN OWNER FORMAT A20
COLUMN SEGMENT_NAME FORMAT A35
COLUMN SEGMENT_TYPE FORMAT A20
COLUMN COMP_NAME FORMAT A35
alter session set nls_date_format='DD.MM.YYYY HH24:MI:SS';
spool &cpulogfile
prompt ************************************************************************
prompt                            Database information
prompt ************************************************************************
prompt
set head on
select name,to_char(created,'DD.MM.YYYY HH24:MI:SS') Created_Date, OPEN_MODE, LOG_MODE, DATABASE_ROLE, DBID from v$database;
prompt
prompt
select instance_name,to_char(startup_time,'DD.MM.YYYY HH24:MI:SS'),status,logins,instance_role from v$instance;

prompt
prompt

select comp_name, version,status,modified from dba_registry order by 1;
prompt
prompt
select ' Size in GB :- ' || round(sum(totalspace)/1024) from (
select sum(trunc(bytes/1024/1024)) as TotalSpace from dba_data_files
UNION
select round(BYTES/1024/1024) as TotalSpace from v$TEMPFILE
UNION
select round(sum(bytes/1024/1024)) as TotalSpace from v$log);
select sessions_highwater from v$license;

prompt
prompt
set head off
SELECT 'Database Character Set :- '||a.VALUE || '_' || b.VALUE || '.' || c.VALUE
  FROM (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_LANGUAGE') a,
       (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_TERRITORY') b,
       (SELECT *
          FROM nls_database_parameters
         WHERE parameter = 'NLS_CHARACTERSET') c;
prompt
prompt
select * from v$version;
prompt
prompt

set head on
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for DMT and LMT
prompt ************************************************************************
prompt
select extent_management,count(1) from dba_tablespaces group by extent_management;
select TABLESPACE_NAME from dba_tablespaces where EXTENT_MANAGEMENT ='DICTIONARY';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking users with non default TEMP tablespace
prompt ************************************************************************
prompt
select username,TEMPORARY_TABLESPACE from dba_users where TEMPORARY_TABLESPACE not in (select name from v$tablespace  where TS# in (SELECT distinct TS# from v$tempfile)) and TEMPORARY_TABLESPACE not in (select GROUP_NAME from DBA_TABLESPACE_GROUPS);

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for users with weak or default passwords
prompt ************************************************************************
prompt
 SELECT username, password, account_status
   FROM dba_users
  WHERE password IN ('402B659C15EAF6CB', '71E687F036AD56E5', '24ABAB8B06281B4C', 'A13C035631643BA0', '72979A94BAD2AF80', 'D728438E8A5925E0', '2FFDCBB4FD11D9DC', '447B729161192C24', '1B9F1F9A5CB9EB31', 'D4DF7931AB130E37', '2D594E86F93B17A1', '4861C2264FB17936', '970BAA5B81930A40', '135176FFB5BA07C9', 'E4519FCD3A565446', '66A490AEAA61FF72', '10B0C2DA37E11872', 'D5DD57A09A63AA38', '69C27FA786BA774C', '86FDB286770CD4B9', 'B171042374D7E6A2', 'D7C18B3B3F2A4D4B', '4438308EE0CAFB7F', 'FAAD7ADAF48B5F45', '685657E9DC29E185', '49B70B505DF0247F', 'B49C4279EBD8D1A8', '604101D3AACE7E88', '02AB2DB93C952A8F', '203CD8CF183E716C', '1FA22316B703EBDD', '12CFB5AE1D087BA3', '380E3D3AD5CE32D4', '2563EFAAE44E785A', 'E7686462E8CD2F5E', '691C5E7E424B821A', '355CBEC355C10FEF', '80294AE45A46E77B', 'E74B15A3F7A19CA8', 'D4C5016086B2DC6A', '43BE121A2A135FF3', '8A8F025737A9097A', '4DE42795E66117AE', '66BC3FF56063CE97', '57D7CFA12BB5BABF', 'A9A57E819B32A03D', '2905ECA56A830226', '64074AF827F4B74A',
  '41B328CA13F70713', '0B4409DDD5688913', '6CFF570939041278', '3522F32DD32A9706', 'BE29E31B2B0EDA33', '5AC333703DE0DBD4', '639C32A115D2CA57');

prompt
prompt
prompt
prompt ******************************************************************************
prompt Checking for objects not analyzed in 10 days
prompt ******************************************************************************
prompt
select owner,count(1) from dba_tables where LAST_ANALYZED < (sysdate - 10) or last_analyzed is NULL having count(1) > 100 and owner not in ('SYS','SYSTEM') group by owner order by 2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for invalid SYS, SYSTEM objects
prompt ************************************************************************
prompt
col owner format a10
col OBJECT_NAME format a28
col OBJECT_TYPE format a20
SELECT OWNER, object_name, OBJECT_TYPE FROM DBA_OBJECTS WHERE OWNER IN ('SYS','SYSTEM')  and status !='VALID' order by 1;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for MLOG$ larger than 500 MB
prompt ************************************************************************
prompt

col onwer format a15
col segment_name format a30
select owner,segment_name,round(bytes/1024/1024,2) Size_MB from dba_SEGMENTS where segment_name like 'MLOG$%' and round(bytes/1024/1024,0) > 500 ;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for MVIEW not refreshed in last 15 days
prompt ************************************************************************
prompt
select owner,mview_name,to_char(last_refresh_date,'DD.MM.YYYY HH24:MI:SS') Rfrsh_Date from dba_mviews where trunc(last_refresh_date) < trunc(sysdate-15) order by 3;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for non system objects in system tablespace.
prompt ************************************************************************
prompt
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP');
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top wait events in database
prompt ************************************************************************
prompt
col event format a30
Select distinct * from (SELECT a.event,
  a.total_waits,
  a.time_wait_sec,
  ROUND(100 * (a.total_waits / b.sum_waits),2) pct_waits
FROM
  (SELECT event,
    total_waits,
    ROUND((time_waited / 100),2) time_wait_sec,
    total_timeouts,
    ROUND((average_wait / 100),2) average_wait_sec
  FROM v$system_event) a,
  (SELECT SUM(total_waits) sum_waits,
    SUM(total_timeouts) sum_timeouts,
    SUM(ROUND((time_waited / 100),2)) sum_time_waited
  FROM v$system_event
  ) b WHERE a.EVENT NOT IN ('SQL*Net more data from dblink', 'SQL*Net message to dblink', 'SQL*Net message from dblink','SQL*Net more data from client','SQL*Net more data to client','SQL*Net message from client','SQL*Net message to client','rdbms ipc message')) ab where pct_waits > 1 order by 4 desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking archive log generation for last 24 hours
prompt ************************************************************************
prompt
Select max(GBytes) Max_GB, min(GBytes) Min_GB ,round(sum(GBytes)/24,2) AVg_GB, sum(GBytes) total_GB  from (SELECT Start_Date,
       Start_Time,
       Num_Logs,
       Round(Num_Logs * (Vl.Bytes / (1024 * 1024 * 1024)), 2) AS GBytes,
       Vdb.NAME AS Dbname
  FROM (SELECT To_Char(Vlh.First_Time,
                       'YYYY-MM-DD') AS Start_Date,
               To_Char(Vlh.First_Time,
                       'HH24') || ':00' AS Start_Time,
               COUNT(Vlh.Thread#) Num_Logs
          FROM V$log_History Vlh
         GROUP BY To_Char(Vlh.First_Time,
                          'YYYY-MM-DD'),
                  To_Char(Vlh.First_Time,
                          'HH24') || ':00') Log_Hist,
       V$log Vl,
       V$database Vdb
 WHERE Vl.group# = (select min(group#) from v$log)
 ORDER BY Log_Hist.Start_Date desc, Log_Hist.Start_Time desc) where rownum < 25;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking total UNDO usage
prompt ************************************************************************
prompt
select a.TABLESPACE_NAME,
 nvl(round((a.BYTES/1024)/1024,2),0) MB_assigned,
 nvl(round((b.BYTES/1024)/1024,2),0) MB_free,
 nvl(round(((a.BYTES-b.BYTES)/a.BYTES)*100,2),0) percent_used
from
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_data_files
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_free_space
  group  by TABLESPACE_NAME
 )
 b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.tablespace_name in (select tablespace_name from dba_rollback_segs) order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking total TEMP usage
prompt ************************************************************************
prompt
select tablespace_name, total_gb,gb_used, round((gb_used*100)/total_gb,2) Prcnt_Used from (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) Total_GB from dba_temp_files group by tablespace_name) a, (select TABLESPACE,round(sum(blocks)*8192/1024/1024/1024,2) GB_USED from v$sort_usage group by tablespace) b where a.tablespace_name=b.tablespace;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking SGA Statistics
prompt ************************************************************************
prompt
prompt
set heading off
Select 'Total of SGA Size :- '||round(sum(value)/1024/1024) from v$sga;
prompt
prompt
set heading on
select name,round(value/1024/1024) MB_Size from v$sga;
prompt
prompt
set serverout on termout on heading off

prompt ====================== DB  Cache Hit Ratio ===========================
prompt = Consider increasing DB_CACHE_SIZE if hit ratio is less than 70%    =
prompt ======================================================================
prompt
SELECT round(((P1.value + P2.value - P3.value) / (P1.value + P2.value))*100) "DB Cache Hit Ratio %"
     FROM   v$sysstat P1, v$sysstat P2, v$sysstat P3
 WHERE  P1.name = 'db block gets'
   AND    P2.name = 'consistent gets'
   AND    P3.name = 'physical reads';
prompt
prompt
prompt
prompt ==================== Libray Cache Hit Ratio ==========================
prompt = Consider increasing SHARED_POOL_SIZE if hit ratio is less than 99% =
prompt ======================================================================
prompt
SELECT 'Library Cache Hit Ration for :-  ',ROUND(100*(SUM(pins)-SUM(reloads))/SUM(pins)) "Near to 100 is good" FROM v$librarycache;
prompt
prompt
prompt
prompt ================== Dictionary Cache Hit Ratio ========================
prompt = Consider increasing SHARED_POOL_SIZE if hit ratio is less than 99% =
prompt ======================================================================
prompt
SELECT 'Dictionary Cache Hit Ration for :- ', ROUND(100*(SUM(gets)-SUM(getmisses))/SUM(gets)) "Near to 100 is good" FROM v$rowcache;
prompt
prompt
prompt
prompt ======================= Redo Buffer Requests =========================
prompt = Consider increasing LOG_BUFFERS if below value is less then 5000:1 =
prompt ======================================================================
prompt
select 'Redo Log buffer requests for :- ', Round(e.value/s.value) ">5000 is good",'  : 1' " " from v$sysstat s, v$sysstat e Where s.name = 'redo log space requests' and e.name = 'redo entries';
prompt
prompt
prompt
prompt ======================= Memory Vs. Disk Sort =========================
prompt = Consider increasing PGA_AGGREGATE_TARGET / SORT_AREA_SIZE if > 1   =
prompt ======================================================================
prompt
select 'Disk Sort % on :- ', round((b.value*100)/a.value,4) " should be < 1" from v$sysstat a, v$sysstat b where a.name like 'sort%memory%' and b.name like 'sorts%disk%';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Packages Functions Procedures being reloaded more often
prompt ************************************************************************
prompt
set head on
col owner format a15
col type format a15
col name format a30
select * from (SELECT 
      owner,
      type,
      name,
      loads, 
      executions,
      round((loads/executions)*100,2) Percent_Load
FROM
      v$db_object_cache
WHERE
      executions>100 and type NOT IN ('NOT LOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE','INVALID TYPE')
      AND executions>100 AND loads>1 AND kept='NO' and owner not in ('SYS','SYSTEM')
ORDER BY 6 desc) where percent_load > 3 order by percent_load desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt       SQL statements that may cause errors like OutOfMemoryError
prompt ************************************************************************
prompt
Set head on
column module format A40
column program format A30
select oc.sid,oc.hash_value,oc.sql_text,count(*) How_Many from gv$open_cursor oc
group by sid,hash_value,sql_text having count(*) > 5 order by 4;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Total sessions in database
prompt ************************************************************************
prompt

select count(1) "No. of Sessions" from v$session where username is not null;
select count(status) "No. of Sessions",status from v$session  where username is not null group by status order by 1,2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking sessions idle for > 24 hours
prompt ************************************************************************
col username format a13
select username, count(username) from v$session where round(last_call_et/3600) > 24 and username is not null group by username order by 2 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 CPU using sessions as of now
prompt ************************************************************************
prompt
col sid format 999999
col username format a10
col osuser format a12
col program format a30
col CPU_MINS format 99.99
col logon_time format a20
col machine format a30
select distinct * from (select ss.sid, se.username,se.osuser ,nvl(se.program,se.module) program , se.status, round(((ss.VALUE*.01)/60),2) CPU_Mins,to_char(logon_time,'DD.MM.YYYY HH24:MI:SS') LOGON_TIME, NVL(MACHINE,TERMINAL) Machine from v$sesstat ss , v$statname sn, v$session se where sn.name like( '%CPU%by%') and ss.STATISTIC# = sn.STATISTIC# and ss.sid = se.sid and se.username is not null and se.status='ACTIVE' and round(((ss.VALUE*.01)/60),2) > .5 order by ss.value desc) where rownum < 11;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for transactions under recovery
prompt ************************************************************************
prompt
select state, undoblocksdone, undoblockstotal, undoblocksdone / undoblockstotal * 100 from gv$fast_start_transactions where state != 'RECOVERED';

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 memory using sessions
prompt ************************************************************************
prompt
SET LINESIZE       380
SET PAGESIZE       5000
SET FEEDBACK       on
SET VERIFY         on
SET SERVEROUTPUT   ON
SET TRIMSPOOL      ON
COL "SESSION"      FORMAT A50
COL "PID/THREAD"   FORMAT A10
COL "      CURRENT SIZE" FORMAT A18
COL "      MAXIMUM SIZE" FORMAT A18
select * from (SELECT   to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||' - ' ||
                  nvl(lower(ssn.machine), ins.host_name) "SESSION",
             to_char(prc.spid, '999999999') "PID/THREAD",
             to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "      CURRENT SIZE",
             to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "      MAXIMUM SIZE"
    FROM     v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
          v$instance ins,  v$statname stat1, v$statname stat2
 WHERE    se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
 AND      se2.statistic#  = stat2.statistic# and stat2.name = 'session pga memory max'
 AND      se1.sid        = ssn.sid
 AND      se2.sid        = ssn.sid
 AND      ssn.paddr      = bgp.paddr (+)
 AND      ssn.paddr      = prc.addr  (+)
 AND      ssn.username is NOT NULL
ORDER BY 3 DESC) where rownum < 11;
set lines 2300
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 redo generating sessions
prompt ************************************************************************
prompt
col sid form 9999
col username form a10
col value Head "Redo|Generated|in MB" form 9999999999.99
col program form a20
col logtime head "Logon Time" form a15
col sql_text format a45 wrap
select distinct a.sid,a.username,a.logtime,a.program,a.value,sq.sql_text from (SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, nvl(se.program,se.module) program, round((value/1048576),2) VALUE, se.sql_address FROM v$sesstat st, v$statname sn, v$session se WHERE sn.name = 'redo size' AND value > 104857600
AND sn.statistic# = st.statistic# AND st.sid = se.sid ORDER BY 5 desc ) a, v$sql sq where a.sql_address=sq.address and rownum < 11 order by 5 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 UNDO using sessions
prompt ************************************************************************
prompt

col object_name format a30
col username format a13
col name format a14
col EXTENTS format 9999999
col SID_SERIAL format a15
col sql_text format a40 wrap
col undo_MB format 99,999.99
select * from (select  s.sid||','||s.serial# SID_SERIAL, s.username, t.used_ublk * TO_NUMBER(x.value)/1024/1024 undo_MB, sq.sql_text ,do.object_name
        from    v$transaction   t
               ,v$session       s
               ,v$rollname      rn
               ,v$rollstat      rs
               ,v$locked_object lo
               ,dba_objects     do
               ,v$sql           sq
               ,v$parameter   x
        where  t.addr        = s.taddr
        and    t.xidusn      = rn.usn
        and    rn.usn        = rs.usn
        and    t.xidusn      = lo.xidusn(+)
        and    do.object_id  = lo.object_id
        and    s.sql_id      = sq.sql_id(+)
        AND    x.name  = 'db_block_size'
        order by used_ublk desc) where rownum < 11;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 TEMP using sessions
prompt ************************************************************************
prompt
COLUMN sid_serial FORMAT A15
COLUMN osuser FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A28
COLUMN TABLESPACE FORMAT a15
COLUMN sql_text FORMAT A45
select * from (select distinct * from (select TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) AS sid_serial, s.osuser,s.username, s.program, u.tablespace, (u.blocks*8192/1024/1024) TEMP_MB, sq.sql_text
from   v$session s, v$sort_usage u, v$sql sq where  s.saddr = u.session_addr and s.sql_id = sq.sql_id order by u.blocks desc) where rownum < 11) order by temp_mb desc;

prompt
prompt
prompt ************************************************************************
prompt   Checking for resource consuming Sql statements
prompt ************************************************************************
prompt
COLUMN USERNAME         FORMAT a10         HEADING 'User Name'
COLUMN avgdiskreads     FORMAT 9999999     HEADING 'Avg.Disk.Reads'
COLUMN disk_reads       FORMAT 999,999,999 HEADING 'Block Reads'
COLUMN sql_text         FORMAT a60         HEADING 'Statement' WORD_WRAPPED
select * from (SELECT   *
    FROM (SELECT   username, ROUND (disk_reads / executions) avgdiskreads,
                   executions, disk_reads, sql_text
              FROM v$sqlarea, dba_users
             WHERE disk_reads > 25000
               AND EXECUTIONS > 0
               AND command_type != 47
               AND user_id = parsing_user_id
               AND username not in ('SYS','SYSMAN')
          ORDER BY (executions*disk_reads)) a
   WHERE a.avgdiskreads > 10000
ORDER BY (a.executions*a.avgdiskreads) DESC) where rownum < 11;
prompt
prompt
prompt
COLUMN USERNAME         FORMAT a10         HEADING 'User Name'
COLUMN avgbuffgets     FORMAT 99,99,99,999     HEADING 'Avg.Buff.Reads'
COLUMN buffer_gets       FORMAT 99,99,99,99,999 HEADING 'Buffer Gets'
COLUMN sql_text         FORMAT a60         HEADING 'Statement' WORD_WRAPPED
select * from (SELECT   *
    FROM (SELECT   username, ROUND (buffer_gets / executions) avgbuffgets,
                   executions, buffer_gets, sql_text
              FROM v$sqlarea, dba_users
             WHERE buffer_gets > 50000
               and EXECUTIONS > 0
               AND command_type != 47
               AND user_id = parsing_user_id
               AND username not in ('SYS','SYSMAN')
          ORDER BY (executions*buffer_gets)) a
   WHERE a.avgbuffgets > 100000
ORDER BY (a.executions*a.avgbuffgets) DESC) where rownum < 11;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for active sessions as of now
prompt ************************************************************************
prompt

col sid format 999999
col osuser format a15
col username format a12
col sql_text format a45
col event format a28
col program format a28
select distinct b.sid, c.osuser, c.username, d.sql_text, b.event, nvl(nvl(c.program,c.module),c.machine) Program   from v$session_wait b, v$session c, v$sql d where b.sid = c.sid and c.sql_address = d.address and c.last_call_et > 1 and c.status = 'ACTIVE';
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for locks on database
prompt ************************************************************************
prompt

column blocker format 9999999
column blockee format 9999999
column os_user format a15
column username format a12
column table_name format a32
column program format a18
column SQL_STATEMENT_WAITER format a45 wrap
column SQL_STATEMENT_BLOCKER format a45 wrap
column Program_Waiter format a20 wrap
column Program_Blocker format a20 wrap
select s2.sid Blockee, s2.username, s2.osuser Os_User,nvl(s2.program,s2.module) Program_Waiter, sa2.sql_text sql_statement_waiter, s1.sid Blocker, s1.username, s1.osuser Os_User,nvl(s1.program,s1.module) Program_Blocker, sa1.sql_text sql_statement_blocker,tbl.owner || '.' || tbl.object_name Table_Name, DBMS_ROWID.rowid_create (1,s2.row_wait_obj#,s2.row_wait_file#,s2.row_wait_block#,s2.row_wait_row#) Waiting_Row_ID
    from v$lock l1, v$session s1, v$lock l2, v$session s2, dba_objects tbl, v$sqlarea sa2, v$sqlarea sa1
    where s1.sid=l1.sid and s2.sid=l2.sid
    and s2.sql_address= sa2.address and s1.sql_address= sa1.address
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2
    and s2.last_call_et > 0
    and s2.status = 'ACTIVE'
    and tbl.object_id = s2.row_wait_obj#;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for long running transactions
prompt ************************************************************************
prompt

col username format a12
col program format a18
col osuser format a10
col sql_text format a45
select distinct * from (SELECT to_date(start_time,'MM.DD.YY HH24:MI:SS') ST_DT, s.sid,s.serial#,s.username,nvl(s.program,s.module) program, t.status T_Status, s.status S_Status, sq.sql_text FROM v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and to_date(start_time,'MM.DD.YY HH24:MI:SS') < sysdate-4/24 and s.sql_id=sq.sql_id (+)) order by 1;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Jobs running on database
prompt ************************************************************************
prompt

col schema_user format a15
col what format a65
select a.this_date, sid,a.job,schema_user,what,a.LAST_DATE  from dba_jobs_running a, dba_jobs b where a.job=b.job order by this_date;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for long running queries
prompt ************************************************************************
prompt

col sql_text format a45
col target format a38
col opname format a15
col sql_start_Time format a19
col PCT format 999.99
col RUN_TIME format a8
col sofar format 99999999
col totalwork format 99999999
select distinct * from (select l.sid ssid, sq.sql_text, substr(OPNAME,1,15) opname,
target, to_char(sysdate-(last_call_et/60/60/24),'DD.MM.YYYY HH24:MI:SS') sql_start_Time, round(l.sofar *100/ (l.totalwork+.0000000001),1) pct,
  lpad(mod( trunc(last_call_et/60/60),60*24),2,'0') || ':' ||
  lpad(mod( trunc(last_call_et/60),60),2,'0') || ':' ||
  lpad(mod(last_call_et,60),2,'0') Run_Time,
sofar, totalwork
from v$session_longops l, v$session s, v$sql sq
where s.sid = l.sid
and s.sid in (select distinct sid from v$session_longops)
and s.serial# = l.serial#
and s.sql_id = sq.sql_id
and l.sofar < l.totalwork
and l.start_time >= sysdate - ( (s.last_call_et+2)/60/60/24)
order by to_char(sysdate-(last_call_et/60/60/24),'DD.MM.YYYY HH24:MI:SS'), l.sid, decode(l.sofar,l.totalwork,1,2), l.start_time, l.last_update_time) order by 7 desc;

set heading on
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking parallel degree queries
prompt ************************************************************************
prompt
col sql_text format a45 wrap
select qcsid, username, asgnd_degree,Reqstd_Degree, sql_text from (select QCSID, max(sid), sum(degree)/count(QCSID) Asgnd_Degree, sum(req_degree)/count(QCSID) Reqstd_Degree from V$PX_SESSION where sid!=qcsid group by qcsid) a, v$session b, v$sqlarea sa where a.qcsid=b.sid and b.sql_address= sa.address;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking pending distributed transaction
prompt ************************************************************************
prompt
col LOCAL_TRAN_ID format a15
col GLOBAL_TRAN_ID format a15
col HOST format a15
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING;

prompt
prompt
prompt
prompt ************************************************************************
prompt  List of tablespace used upto 90% or more and SYSTEM tablespaces.
prompt ************************************************************************
prompt

select a.TABLESPACE_NAME,
 nvl(round((a.BYTES/1024)/1024,2),0) MB_assigned,
 nvl(round((b.BYTES/1024)/1024,2),0) MB_free,
 nvl(round((b.largest/1024),2),0) KB_Chunk,
 nvl(round(((a.BYTES-b.BYTES)/a.BYTES)*100,2),0) percent_used
from
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES
  from  dba_data_files
  group  by TABLESPACE_NAME
 )
 a,
 (
  select  TABLESPACE_NAME,
   nvl(sum(BYTES),0) BYTES ,
   nvl(max(BYTES),0) largest
  from  dba_free_space
  group  by TABLESPACE_NAME
 )
 b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and (a.tablespace_name in ('SYSTEM','SYSAUX') or round((b.BYTES/1024)/1024,2) < 1000 OR round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) > 90)
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt    Checking for probable next_extent failures
prompt ************************************************************************
prompt

column SEGMENT_NAME format a35
column TABLESPACE_NAME format a30
column owner format a15
select a.owner,a.segment_name, a.tablespace_name, Round(a.next_extent/1024/1024) MB_NEXT_EXT, round(b.largest_chunk/1024/1024) MB_LARGEST_CHUNK from dba_segments a, (select tablespace_name,max(bytes) Largest_Chunk from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name and a.next_extent > b.largest_chunk;

prompt
prompt
prompt ************************************************************************
prompt        Tables with high number of chained rows.
prompt ************************************************************************
prompt
column c1 heading "Owner"   format a9;
column c2 heading "Table"   format a25;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"    format 999,999,999;
column c7 heading "Chains"  format 999,999,999;
column c8 heading "Lst_Analyzed"  format A12
column c9 heading "Pct"     format 999.99;
set heading off;
set heading on;
select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   last_analyzed      c8,
   round(chain_cnt/num_rows*100,2) c9
from dba_tables
where
owner not in ('SYS','SYSTEM') and chain_cnt > 0 and num_rows > 0
and (chain_cnt/num_rows*100) > 9
order by (chain_cnt/num_rows*100) desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt Checking for fragmented tables
prompt ************************************************************************
prompt

COL SEG_NAME FORMAT A40
COL TOTAL_MB FORMAT 999999999
COL ACTUAL_MB FORMAT 999999999
COL FRAGMENTED_MB FORMAT 999999999
SELECT SEG_NAME     ,
  TOTAL_MB      ,
  ACTUAL_MB     ,
  FRAGMENTED_MB,
  ROUND(FRAGMENTED_MB*100/TOTAL_MB,2) PERCENT_FRAG,
  trunc(Last_analyzed)
   FROM
  (SELECT OWNER
    ||'.'
    ||TABLE_NAME SEG_NAME                                   ,
    ROUND(((BLOCKS *8192/1024/1024)),2) TOTAL_MB       ,
    ROUND((NUM_ROWS*AVG_ROW_LEN/1024/1024),2) ACTUAL_MB,
    ROUND(((BLOCKS *8192/1024/1024)-(NUM_ROWS*AVG_ROW_LEN/1024/1024)),2) FRAGMENTED_MB,
    Last_analyzed
     FROM DBA_TABLES
    WHERE OWNER NOT IN ('SYS','SYSTEM')
  AND BLOCKS         > 12800
  )
  WHERE ROUND(FRAGMENTED_MB*100/TOTAL_MB,2) > 75
ORDER BY 5 DESC,2 desc;

prompt
prompt
prompt
prompt ************************************************************************
prompt     Checking for queries doing full table scan and executed > 1000
prompt ************************************************************************
prompt

col operation format a15
col object_name format a30
col object_owner format a12
col options format a15
col executions format 999,999,999
col sql_text format a35
select distinct * from (select a.object_owner,a.object_name, a.operation, a.options, b.executions, b.SQL_TEXT from v$sql_plan a, v$sqlarea b
where a.address=b.address and a.operation IN ('TABLE ACCESS','INDEX') and round(b.ROWS_PROCESSED/b.executions,0) > 100000 and a.bytes > 10485760
and a.options in ('FULL','FULL SCAN') and a.object_owner not in ('SYS','SYSTEM','PERFSTAT') and b.executions > 100
order by b.executions*a.bytes);

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for Tables with concurrent requests > 50
prompt ************************************************************************
prompt
set lines 132 pages 2000
col object format a35
col owner format a25
col object_type format a15
select count(object),owner,object,type from v$access where type in ('TABLE','VIEW') and owner not in ('SYS','SYSTEM') group by owner,object,type having count(object) > 50 order by 1 desc;
prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for Tablespaces with top file IO waits
prompt ************************************************************************
prompt
column tablespace_name format a25
column Read_Time format 99999
column Write_Time format 99999
Select * from (select * from (
Select tablespace_name, round(sum(MAXIORTM)/100) Read_Time , round(sum(MAXIOWTM)/100) Write_Time from v$filestat fs, dba_data_files ddf where fs.file# = ddf.file_id group by tablespace_name
UNION
Select tablespace_name, round(sum(MAXIORTM)/100) Read_Time , round(sum(MAXIOWTM)/100) Write_Time from v$tempstat ts, dba_temp_files dtf where ts.file# = dtf.file_id group by tablespace_name) order by 2 desc, 3 desc) where rownum < 6;

prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for top 10 file IO waits
prompt ************************************************************************
prompt

col file_name format a75
select tablespace_name,file_name,b.maxiortm,b.maxiowtm from dba_data_files a, (select * from (select file#,MAXIORTM,MAXIOWTM from v$filestat order by 2 desc, 3 desc) where rownum < 11) b where a.file_id=b.file# order by 3 desc, 4 desc;


prompt
prompt
prompt
prompt ************************************************************************
prompt           Checking for corruption on database
prompt ************************************************************************
prompt
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
prompt
prompt
prompt
prompt
set head on
show parameters
SPOOL OFF
EXIT

No comments:

Post a Comment