Anuj_1022_ORCL.lst <<<<<--- output will be in this file
===
prompt
prompt Report Health Check.
prompt
set trimspool on
set timing off
set linesize 150
set pause off
set verify off
set echo off
set pagesize 40
clear breaks
clear computes
ttitle off
whenever sqlerror continue;
whenever oserror continue;
rem create dated and instance named spool file
column file_name new_value spool_name
set heading off
select 'Anuj_'||to_char(sysdate,'mmdd')||'_'||name as file_name from v$database;
set heading on
spool &spool_name
prompt *****************************
prompt * H E A L T H C H E C K *
prompt *****************************
prompt
column today format a30 heading "Todays Date"
select to_char(sysdate,'HH24:MI:SS ddth Monthfm YYYY') today
from sys.dual
/
@heading.sql
col host_name for a20
col uptime for a20
col startup_time for a17
col version for a10
col instance_number for 9,990 heading 'Inst.'
col days for a5
col HRS for a4
col mins for a4
select instance_number
, instance_name
, host_name
, version
, to_char(startup_time,'Dy dd-Mon hh24:mi') as startup_time
, to_char (floor (sysdate - startup_time), '000') as DAYS
, to_char (mod (floor ((sysdate - startup_time) * 24 ), 24), '00') as HRS
, to_char (mod (floor ((sysdate - startup_time) * (24*60)), 60), '00') as MINS
from v$instance
/
prompt
prompt Files needing recovery.
select * from v$recover_file
/
prompt
prompt Files in backup mode.
Select * from v$backup where status!='NOT ACTIVE'
/
prompt
prompt Unresolved distributed transactions
Select * from dba_2pc_pending
/
prompt
prompt Top 10 wait events from v$system_event
prompt (average_wait is in 100ths of a second)
col event for A30
col average_wait for 999,990.00 heading 'Avg Wait|(s)'
col time_waited for 999,999,990 heading 'Total Wait|(mins)'
col total_waits for 999,999,990 heading 'Number|Waits'
col total_timeouts for 999,999,990 heading 'Number|Timeouts'
col pos for 990
select rownum as Pos
, event
, total_timeouts
, total_waits
, time_waited/(100*60) as time_waited
, average_wait/100 as average_wait
from
( select event
, total_timeouts
, total_waits
, time_waited
, average_wait
from v$system_event e
where e.event not in
('smon timer'
,'pmon timer'
,'rdbms ipc message'
,'Null event'
,'parallel query dequeue'
,'pipe get'
,'client message'
,'SQL*Net message to client'
,'SQL*Net message from client'
,'SQL*Net more data from client'
,'dispatcher timer'
,'virtual circuit status'
,'lock manager wait for remote message'
,'PX Idle Wait'
,'jobq slave wait'
,'PX Deq: Execution Msg'
,'wakeup time manager')
order by time_waited desc, total_waits, event
)
where rownum < 11
/
prompt
prompt CPU usage (Minutes)
prompt THIS NEEDS A BIT OF TESTING
prompt db uptime = how long database has been up
prompt total waits = sum of non-idle non-CPU wait event times
prompt total cpu = amount of CPU used so far
prompt If CPU exceeds other waits then box has CPU contention
col cpu_mins for 999,999,990 heading 'Total CPU'
col uptime_mins for 999,999,990 heading 'DB UpTime'
col wait_mins for 999,999,990 heading 'Total Waits'
select (sysdate - i.startup_time)*(24*60) as uptime_mins
, (to_number (b.value)/100) as cpu_mins
, wait_mins
from v$statname a
, v$sysstat b
, v$instance i
,( select sum (e.time_waited)/100 as wait_mins
from v$system_event e
where e.event not in
('smon timer'
,'pmon timer'
,'rdbms ipc message'
,'Null event'
,'parallel query dequeue'
,'pipe get'
,'client message'
,'SQL*Net message to client'
,'SQL*Net message from client'
,'SQL*Net more data from client'
,'dispatcher timer'
,'virtual circuit status'
,'lock manager wait for remote message'
,'PX Idle Wait'
,'jobq slave wait'
,'PX Deq: Execution Msg'
,'wakeup time manager')
)
where a.statistic# = b.statistic#
and a.name = 'CPU used when call started'
/
prompt
prompt v$waitstat
rem needs timed statistics enabled
set numwidth 15
col count for 999,999,990
col time_secs for 999,990 heading 'Time (s)'
col suggestion for a50
select class, count , time/100 as time_secs
, decode (upper (class)
,'DATA BLOCK', 'eliminate hot blocks (reduce rows per block)'
,'SEGMENT HEADER', 'more freelists/groups'
,'FREELIST BLOCKS', 'more freelists'
,'SYSTEM UNDO HEADER', 'more system rollback extents'
,'SYSTEM UNDO BLOCK', 'bigger system rollback extents'
,'UNDO HEADER', 'more rollback extents'
,'UNDO BLOCK', 'bigger rollback extents'
,to_char(null)) as suggestion
from v$waitstat
where count > 0
order by 1
/
set numwidth 9
prompt
prompt parallel query system statistics
prompt The following figures are only a snapshot but.....
prompt if 'Servers in use' is normally > min_servers then increase min_servers.
prompt if 'Servers in use' is normally < max_servers then decrease max_servers.
column pq_value for 999,999,990 heading 'Value'
select value as pq_value, statistic
from v$px_process_sysstat
where upper(statistic) like '%SERVER%'
union
select to_number(value), name
from v$parameter
where name like ('parallel_%_servers')
order by 2
/
select decode(totalq, 0, 'No Requests'
,round (WAIT/TOTALQ,6) || ' HUNDREDTHS OF SECONDS')
as "AVERAGE WAIT TIME PER REQUESTS"
from v$queue
where type = 'COMMON'
/
prompt 8i
select conf_indx as "INDEX"
,decode (sum (totalq), 0 , 'No Responses'
, round(sum(wait)/sum(totalq),6)||' 100ths of seconds')
as "AVERAGE WAIT TIME PER RESPONSE"
from V$QUEUE Q, V$DISPATCHER d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr
group by conf_indx
/
prompt 7.3.4
select name as dispatcher
,decode (sum (totalq), 0 , 'No Responses'
, round(sum(wait)/sum(totalq),6)||' 100ths of seconds')
as "AVERAGE WAIT TIME PER RESPONSE"
from V$QUEUE Q, V$DISPATCHER d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr
group by name
/
select count (*) as "Shared Server Processes" from v$shared_server
where status != 'QUIT'
/
prompt
prompt LOG BUFFER
col p_name for a30 heading Parameter
col p_value for a20 heading Value
select name as p_name, value as p_value
from v$parameter
where name in ('log_buffer','archive_lag_target')
/
prompt Increase the log_buffer parameter if the ratio is over 5,000
prompt This value should be 512K at least anyway.
col requests for 999,999,990
col entries for 999,999,990
col log_ratio for 999,990
select requests, entries, entries/decode(requests,0,entries,requests) as log_ratio
from
(select nvl(value,1) as requests
from v$sysstat
where name = 'redo log space requests'
),
(select nvl(value,1) as entries
from v$sysstat
where name = 'redo entries'
)
/
prompt archived redo log generation
col aredo_age for 9,990 heading 'No. Days'
col aredo_files for 999,990 heading 'No. Logs'
col aredo_avg for 999,990 heading 'Avg. per day'
col aredo_peak for 999,990 heading 'Peak Daily'
select age as aredo_age
, logfiles as aredo_files
, avg_per_day as aredo_avg
, max_per_day as aredo_peak
from
( select max (per_day) as max_per_day from
( select count (*) as per_day
from v$loghist
group by trunc (first_time)
)
),
( select ceil (sysdate - min(first_time)) as age
, count (*) as logfiles
, count (*) / ceil (sysdate - min(first_time)) as avg_per_day
from v$loghist
)
/
col log_bytes for 999,999,990 heading 'Bytes'
col members for 990 heading 'Members'
col group# for 990
col thread# for 990
col sequence for 999,990
select GROUP#, THREAD#, SEQUENCE#, BYTES as log_bytes
, MEMBERS, ARCHIVED, STATUS, FIRST_CHANGE#
, to_char (FIRST_TIME, 'dd-Mon-yy hh24:mi') as first_time
from v$log
order by group#
/
col memory for a7 heading 'Memory'
col name for a35 heading 'Memory Area/Usage'
col ratio for 990.000 heading 'Efficiency%'
col mb for a15 heading 'Size (Mb)'
select 'SGA' as memory
, name||' '||block_size as name
, 100 * (1 - (physical_reads/(db_block_gets+consistent_gets))) as ratio
from v$buffer_pool_statistics
union
select 'SGA', 'Library Cache'
, 100 - (sum(reloads)/sum(pins) *100)
from v$librarycache
union
select 'SGA', 'Dictionary Cache'
, 100 - (sum(getmisses)/sum(gets) * 100)
from v$rowcache
order by 1,2
/
prompt
prompt latch contention shown by ratio(s) greater then 1%
column lname format a35 heading 'Latch Type'
column gets format 999,999,999,999 heading 'Gets'
column miss format 999,999,999 heading 'Misses'
column igets format 999,999,999,999 heading 'Im. Gets'
column imiss format 999,999,999 heading 'Im. Misses'
column ratio format 90.00
column iratio format 90.00
column sleeps format 999,990
column hwaits format 999,999,990 heading 'waits|when|holding'
select n.name as lname
, gets as gets
, misses as miss
, (misses/decode(gets,0,1,gets))*100 as ratio
, immediate_gets as igets
, immediate_misses as imiss
, (immediate_misses/decode (immediate_gets,0,1,immediate_gets))*100 as iratio
, sleeps
, waits_holding_latch as waits
from v$latchname n , v$latch l
where n.latch# = l.latch#
and ( ((misses/decode(gets,0,1,gets))*100 > 1)
or ((immediate_misses/decode (immediate_gets,0,1,immediate_gets))*100 >1))
order by upper(n.name)
/
prompt
prompt Checking sort_area_size values
prompt To make best use of sort memory the initial extent of your
prompt Users sort-work Tablespace should be sufficient to hold at
prompt least one sort run from memory to reduce dynamic space allocation.
prompt See also dba_mem8i.sql and dba_mem9i.sql
column init_value format A40 heading 'Initialization Parameters'
select rpad (name,27)||value as init_value
from v$parameter
where name in
('sort_area_size','workarea_size_policy','sort_area_retained_size'
,'pga_aggregate_target')
order by 1
/
column sort_name format a20
column sort_value format 999,999,999,999 heading 'Value'
select a.name as sort_name, value as sort_value
from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)','sorts (memory)','sorts (rows)')
/
column max_space format a8 heading 'Tablespace|Name'
column max_tot_mb format 999,990 heading 'Max Total|Mbytes'
column max_used_mb format 999,990 heading 'Max Single|Use Mbytes'
column max_sort_mb format 999,990 heading 'Max Single|Sort Mbytes'
column max_file_mb format 999,990 heading 'Tablespace|Size (Mb)'
column ftype heading 'File|Type'
column extent_size format 99,999,999 heading 'Ext. Size|(Bytes)'
-- get the database blk size
column blk_size new_value _blk_size
set termout off
select to_number (value) as blk_size
from v$parameter
where upper(name) = 'DB_BLOCK_SIZE';
set termout on
prompt sort segments:
select s.tablespace_Name as max_space
, ftype as ftype
, (s.extent_size*&_blk_size) as extent_size
, (file_bytes)/(1024*1024) as max_file_mb
, (s.max_blocks*&_blk_size)/(1024*1024) as max_tot_mb
, (s.max_used_blocks*&_blk_size)/(1024*1024) as max_used_mb
, (s.max_sort_blocks*&_blk_size)/(1024*1024) as max_sort_mb
from v$sort_segment s
,(select tablespace_name
, sum (bytes) as file_bytes
, 'Data' as ftype
from dba_data_files
group by tablespace_name
union
select tablespace_name
, sum (bytes) as file_bytes
, 'Temp' as ftype
from dba_temp_files
group by tablespace_name
) f
where f.tablespace_name = s.tablespace_name
order by 1
/
prompt
prompt Rollback Statistics
prompt if (waits/gets)>.01 then contention for rollback segs
prompt if high wraps then use bigger extents
prompt if high shrinks then increase optimal size
column tablespace_name format A10
column extents format 9990 heading 'Ext.'
column instance_num format a5 heading 'Inst.'
column status format a10
column waits format 99,990
column xacts format 99,990
column shrinks format 9,999,990
column mb format 9,990.00 heading 'Size Mb'
column hwm format 9,990.00 heading 'HWM Mb'
column osize format 9,990.00 heading 'Optimum'
column wraps format 9,990
column gets format 999,999,990
column rb_seg format a11
select rs.tablespace_name,
rs.instance_num,
substr(rs.segment_name,1,10) rb_seg,
rs.status,
v.extents,
(rs.initial_extent*v.extents)/(1024*1024) mb,
v.optsize/(1024*1024) osize,
v.hwmsize/(1024*1024) hwm,
v.xacts,
v.shrinks,
v.wraps,
v.waits,
v.gets
from v$rollstat v,
sys.dba_rollback_segs rs
where v.usn (+) = rs.segment_id
order by 1,2,3
/
prompt
prompt segments that cannot extend into biggest free extent
col owner for a10 heading 'Owner'
col space1 for a20 heading 'Tablespace'
col type for A10 heading 'Type'
col name for a30 heading 'Name'
col next_kb for 999,999,990 heading 'Next Kb'
col free_kb for 999,999,990 heading 'Max Free Kb'
select owner, space1 as tablespace, type, name, next_kb, nvl(free_kb,0) as free_kb
from
(select owner, tablespace_name as space1
,'TABLE' as type
,table_name as name
,round (next_extent/(1024),2) as next_kb
from sys.dba_tables
union
select table_owner, tablespace_name
,'TABPART' as type
,table_name||'.'||partition_name
,round (next_extent/(1024),2)
from sys.dba_tab_partitions
union
select owner, tablespace_name, 'INDEX', index_name, round (next_extent/(1024),2)
from sys.dba_indexes
union
select index_owner, tablespace_name, 'INDPART'
, index_name||'.'||partition_name
, round (next_extent/(1024),2)
from sys.dba_ind_partitions
union
select owner, tablespace_name, 'CLUSTER', cluster_name, round (next_extent/(1024),2)
from sys.dba_clusters
union
select owner, tablespace_name, 'ROLLBACK', segment_name, round (next_extent/(1024),2)
from sys.dba_rollback_segs
),
(select tablespace_name as space2, round (max(bytes)/(1024),2) as free_kb
from sys.dba_free_space
group by tablespace_name
)
where space1 = space2(+)
and next_kb >= nvl(free_kb,0)
order by 1,2,3,4
/
prompt
prompt segments that have reached the maximum number of extents
select owner
, segment_type
, segment_name
, extents as cur_ext
, max_extents as max_ext
from dba_segments
where extents >= max_extents
and max_extents > 0
order by 1,2,3
/
prompt
prompt Tablespaces with less than 10% free space
prompt
col space1 format A30 heading 'Tablespace'
col free_mb format 999,999,990 heading 'Free Mb'
col tot_mb format 999,999,990 heading 'Total Mb'
col pct_free format 990.00 heading '% Free'
col max_ext format 999,999,990 heading 'Max|(Next Ext.)'
col sum_ext format 999,999,990 heading 'Sum|(Next Ext.)'
col cnt_ext format 999,999,990 heading 'Count|(Next Ext.)'
select /*+ RULE */ space1
, tot_mb
, (free_mb/tot_mb)*100 as pct_free
, free_mb
, sum (sum_ext) as sum_ext
, max (max_ext) as max_ext
, sum (cnt_ext) as cnt_ext
from
(select tablespace_name as space1, sum (Bytes)/(1024*1024) as tot_mb
from dba_data_files
group by tablespace_name
),
(select tablespace_name as space2, sum(bytes)/(1024*1024) as free_mb
from dba_free_space
group by tablespace_name
),
(select tablespace_name as space3
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_tables
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_tab_partitions
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_indexes
where next_extent is not null
group by tablespace_name
union
select tablespace_name
,sum (next_extent)/(1024*1024) as sum_ext
,max (next_extent)/(1024*1024) as max_ext
,count (*) as cnt_ext
from dba_ind_partitions
where next_extent is not null
group by tablespace_name
)
where space1 = space2
and space1 = space3
and (free_mb/tot_mb) < 0.1
group by space1, tot_mb, free_mb
order by 1,2
/
prompt
prompt free space that could be coalesced:
prompt
set serveroutput on size 100000
declare
cursor c_ts is
select tablespace_name as name
from dba_tablespaces
where status = 'ONLINE'
and contents = 'PERMANENT'
and extent_management = 'DICTIONARY'
order by tablespace_name;
cursor c_segs (tsname in varchar2) is
select tablespace_name
,file_id
,block_Id
,blocks
from dba_free_space
where tablespace_name = tsname
order by file_id, block_id;
prev_end number default 0;
ts_count number default 0;
segs_count number;
segs_bytes number;
block_size number;
begin
select to_number (value)
into block_size
from v$parameter
where upper(name) = 'DB_BLOCK_SIZE';
<
for l_ts in c_ts
loop
prev_end := 0;
segs_count := 0;
segs_bytes := 0;
ts_count := ts_count + 1;
<
for r_seg in c_segs (l_ts.name)
loop
if r_seg.block_id=prev_end then
segs_count := segs_count + 1;
segs_bytes := segs_bytes + r_seg.blocks*block_size;
end if;
prev_end := r_seg.block_id + r_seg.blocks;
end loop segment;
if segs_count > 0 then
dbms_output.put_line ('TABLESPACE '||l_ts.name||': '||segs_count||' segments, totalling '||round (segs_bytes/(1024*1024))||' Mb');
end if;
end loop tablespace;
dbms_output.put_line (ts_count||' tablespaces analysed');
end;
/
set serveroutput off
set feedback 6
prompt
prompt indexes with blevel > 3
col owner_table_index for a50
select owner||'.'||table_name||'.'||index_name as owner_table_index
, blevel, round (num_rows) as num_rows, last_analyzed
from dba_indexes
where blevel > 2
order by owner, table_name, index_name
/
prompt
prompt The following indexes are unusable
select index_owner, index_name, partition_name, status
from dba_ind_partitions
where status = 'UNUSABLE'
union
select owner, index_name, to_char(null), status
from dba_indexes
where status = 'UNUSABLE'
order by 1,2,3
/
prompt
prompt The following tables may benefit from rebuilding due to the high percentage
prompt of blocks that are in the freelist(s).
col percentage for 90.00
col owner for a20
col table_name for a30
col blocks for 999,990
col num_rows for 999,999,990
select owner
,table_name
,blocks
,NUM_FREELIST_BLOCKS
,round(nvl(num_freelist_blocks,1)/nvl(blocks+1,1)*100,2) as percentage
,num_rows
,chain_cnt
from all_tables
where ( ((nvl(num_freelist_blocks,1)/nvl(blocks+1,1)) > 0.1)
or (num_freelist_blocks > 20 ) )
and nvl(blocks,1) > 128
order by num_freelist_blocks desc
/
-- chained and migrated rows
prompt Chained rows are too large for one block to contain.
prompt Migrated rows have been moved to another block because an update made them too
prompt large to stay in the current block. Too many migrated rows may suggest a table
prompt rebuild is required but this statistic is mixed in with chained rows.
prompt Migrated rows leave a forwarding address thus increasing access IO.
col num_rows format 999,999,990
col chain_cnt format 999,999,990
select owner, table_name, num_rows, chain_cnt, avg_row_len
from dba_tables
where nvl(chain_cnt,0) > 0
order by chain_cnt desc
/
prompt
prompt <=8i SYS/SYSTEM schemas should have 0 objects with stats
select a.owner
, a.tables
, b.indexes
from
( select owner
, sum (decode (last_analyzed, null, 0, 1)) as tables
from dba_tables
where owner in ('SYS', 'SYSTEM')
group by owner
) a,
( select owner
, sum (decode (last_analyzed, null, 0, 1)) as indexes
from dba_indexes
where owner in ('SYS', 'SYSTEM')
group by owner
) b
where a.owner = b.owner
order by 1,2
/
prompt
prompt users using system tablespace (be careful!)
select username, default_tablespace, temporary_tablespace
from dba_users
where default_tablespace = 'SYSTEM'
or temporary_tablespace = 'SYSTEM'
order by 1
/
prompt
prompt Invalid objects
select owner, count (*)
from dba_objects
where status = 'INVALID'
group by owner
order by 1
/
prompt
prompt Anuj HEALTH CHECK report
spool off
clear breaks
clear computes
clear columns
No comments:
Post a Comment