/* dbprobe.sql */
/* This script looks at the v$ and dba_ tables to report on the */
/* database system performance. Suggestions as to the 'normal' */
/* ranges is provided as a reference. */
/* NOTE!!!!!! */
/* This script spools to a file - dbprobe.lst */
spool dbprobe.lst
set timing off
set linesize 80
set pagesize 9999
set feedback off
set termout off
prompt
set heading off
ttitle left ''
select 'Reported as DB User '||user||' on '||
to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' sec: '||hsecs
from v$timer;
set heading on
prompt
prompt Section 1 Version and Configuration Information
prompt Section 2 SGA Allocations and Analysis
prompt Section 3 File and Tablespace Information
prompt Section 4 Database Object Analysis
prompt Section 5 Rollback Segment Information
prompt Section 6 MTS
prompt Section 7 I/O Analyis
prompt Section 8 Redo Log
prompt Section 9 Current Processes
prompt
prompt
prompt
prompt +---+
prompt | | Section 1: Version and Configuration Information
prompt +---+
prompt
set heading off
ttitle left '1.01 Database Version Information' skip 2
select *
from v$version;
ttitle left '1.02 Database Instance Information' skip 2
prompt
select 'Database '||name||' created '||created||' log mode '||log_mode
from v$database
union all
select 'Last System Commit Number (SCN) checkpointed '||checkpoint_change#
from v$database
union all
select 'Last System Commit Number (SCN) archived '||archive_change#
from v$database;
set heading on
ttitle left '1.03 Information about license limits' skip 2
prompt
select *
from v$license;
ttitle left '1.04 Initialization Parameters' skip 2
prompt
column "Init.Ora Value" format a70
column "Def." format a7 truncated
select decode(isdefault,'TRUE','','Changed') "Def.",
name||' = '||value "Init.Ora Value"
from v$parameter
order by name;
ttitle left '1.05 National Language Parameters' skip 2
prompt
column parameter format a35 heading 'Parameter'
column value format a35 heading 'Value'
select *
from v$nls_parameters
order by 1;
prompt
prompt
prompt
prompt +---+
prompt | | Section 2: SGA Allocations and Analysis
prompt +---+
prompt
ttitle Left '2.01 SGA Storage Allocation Information' skip 2
compute sum of Value on report
break on report
column value format 999,999,999
select *
from v$sga;
ttitle left '2.02 SGA Statitics' skip 2
prompt
prompt
column name format a40
column bytes format 999,999,999
select *
from v$sgastat;
ttitle left '2.03 Library Cache Information' skip 2
prompt
prompt
column "Miss/Exec" format a10
column "Action To Take" format a30 wrap
select sum(pins) "Executes", sum(reloads) "Misses",
lpad(round(100 * (sum(reloads))/sum(pins), 2) || '%',10) "Miss/Exec",
decode(trunc(100* (sum(reloads))/sum(pins)), 0, 'No Action Necessary',
'Allocate additional memory for library cache (SHARED_POOL_SIZE)')
"Action To Take"
from v$librarycache;
prompt
prompt Exec: Number of times an item in the library cache was executed
prompt Misses: Number of library cache misses on execution steps
prompt Miss/Exec: Ratio of misses to executes
prompt Goal: Misses to executions ratio less than 1%
ttitle left '2.04 SGA Waits' skip 2
prompt
prompt
select a.count "Waits", sum(b.value) "Gets", round(100*(a.count) /
sum(b.value),2) "Waits/Gets", 'No Action Necessary' "Action To Take"
from v$waitstat a, v$sysstat b
where a.class='free list' and
b.name in ('db block gets','consistent gets')
group by a.class, a.count
having (a.count)/sum(b.value) < .01
union
select a.count "Waits", sum(b.value) "Gets", round(100*(a.count) /
sum(b.value),2) "Waits/Gets", 'Increase Free Lists (created per table)'
from v$waitstat a, v$sysstat b
where a.class='free list' and
b.name in ('db block gets','consistent gets')
group by a.class, a.count
having (a.count)/sum(b.value) >= .01;
prompt
prompt Wait: Number of waits for free blocks.
prompt Gets: Total number of requests for data.
prompt Goal: Keep counts per get less than 1%
ttitle left '2.05 Buffer Hit Ratio' skip 2
prompt
prompt
prompt
column phys format 99,999,999,999 heading 'Physical Reads'
column gets format 99,999,999,999 heading ' DB Block Gets'
column con_gets format 99,999,999,999 heading 'Consistent Gets'
column hitratio format 9.999 heading 'Hit Ratio'
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1-(sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) +
sum(decode(name,'consistent gets',value,0))))) hitratio
from v$sysstat;
prompt
prompt Goal: Hit Ratio should be 90 or greater, else increase db_block_buffers
ttitle left '2.06 Data Dictionary Hit Ratio' skip 2
prompt
prompt
prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. Cache Misses" format 999,999,999
select sum(gets) "Data Dict. Gets", sum(getmisses) "Data Dict. Cache Misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "Data Dict Cache Hit Ratio"
from v$rowcache;
prompt
prompt Goal: Hit Ratio should be > 90, else increase shared_pool_size
ttitle left '2.07 Shared Pool Library Cache Analysis' skip 2
prompt
prompt
prompt
column "Gets" format 999,999,999
column "Pins" format 999,999,999
column "Reloads" format 999,999
select namespace, gets "Gets", trunc(gethitratio * 100) "GH Ratio",
pins "Pins", trunc(pinhitratio * 100) "PH Ratio", Reloads "Reloads"
from v$librarycache;
prompt
prompt
prompt Goal: Get Hit Ratio should be > 90, Pin Ratio should be > 90.
prompt - A problem exists if the ratio of Reloads to Pins is > 1%.
ttitle '2.08 Database Buffer Activity Analysis' skip 2
prompt
prompt
prompt
column "Action To Take" format a25
select w.class "Class", w.count "Count", sum(s.value) "Total Gets",
round( 100*(w.count)/sum(s.value), 2 ) "Wait/Gets",
decode( trunc( 100*(w.count)/sum(s.value)), 0, 'No Action Necessary',
'Add More Rollback Segments' ) "Action To Take"
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
prompt Total gets: Sum of db block gets and consistent gets
prompt Sys undo header: Waits for buffers containing header blocks of the
prompt - SYSTEM RBS
prompt Sys undo block: Waits for buffers other than header blocks in SYSTEM RBS
prompt Undo header: Waits for buffers of header blocks of other than SYSTEM
prompt - RBS
prompt Undo block: Waits for buffers other than header blocks of other
prompt - than SYSTEM RBS
prompt Goal: If any of the waits is > 1% of number of requests (gets)
prompt - then a problem exists.
ttitle '2.09 Database Sorts Analysis' skip 2
prompt
prompt
prompt
select sum(decode(name, 'sorts (memory)', value, 0)) "Memory Sorts",
sum(decode(name, 'sorts (disk)', value, 0)) "Disk Sorts",
sum(decode(name, 'sorts (rows)', value, 0)) "Sorted Rows"
from v$sysstat;
prompt
prompt sorts (memory): Number of sorts small enough to be performed
prompt - entirely in sort areas without I/O to temporary
prompt - segments on disk.
prompt sorts (disk): Number of sorts too large to be performed entirely
prompt - in memory and requiring I/O to temporary segments
prompt - on disk.
prompt Goal: Minimum sorts to disk
ttitle '2.10 Full Table Scan Analysis' skip 2
prompt
prompt
select substr(name,1,30) "Name", value "Value"
from v$sysstat
where name like '%short tables%' or
name like '%long tables%';
prompt
prompt The value for short tables is the number of times a 'full table scan'
prompt has been performed on tables that are less than 5 blocks in size.
prompt
prompt The value for long tables is number of times a 'full table scan' has
prompt been performed on tables that are greater than 5 blocks in size.
prompt
prompt Goal: The goal is to take advantage of, or create indexes, and tune
prompt - the application to prevent full table scans on long tables.
prompt - You want the value for long table scans to be as close to
prompt - zero as possible.
prompt
prompt
prompt +---+
prompt | | Section 3: File and Tablespace Information
prompt +---+
prompt
ttitle left '3.01 Free Space By Tablespace' skip 1
prompt
set wrap off
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column "Tablespace Name" format a16
column bytes format 9,999,999,999,999 heading "Bytes"
column used format 99,999,999,999 heading "Used"
column free format 999,999,999,999 heading "Free"
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report
select a.tablespace_name "Tablespace Name", b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;
prompt
prompt Note: This report shows unallocated space within tablespaces.
prompt - Some of the allocated space in segments may be free as well.
prompt - This report shows free space that can be used for newly created
prompt - objects in a tablespace.
prompt
prompt
prompt +---+
prompt | | Section 4: Database Object Analysis
prompt +---+
ttitle left '4.01 Analyzed Tables per Database Owner' skip 2
prompt
prompt
column "Analyzed" format 999,999
column "Un Analyzed" format 999,999
break on report
compute sum of "Analyzed" on report
compute sum of "Un Analyzed" on report
select owner, sum(decode(num_rows,null,0,1)) "Analyzed",
sum(decode(num_rows,null,1,0)) "Un Analyzed"
from sys.dba_tables
group by owner;
prompt
prompt Goal: Analyzed tables when referenced in queries will generate
prompt - plans using the cost based optimizer. In general users
prompt - should analyze all tables or none of the tables. If only
prompt - one table of any query has statistics compiled the
prompt - query will use the cost based optimizer.
prompt
prompt
prompt +---+
prompt | | Section 5: Rollback Segment Information
prompt +---+
prompt
ttitle left '5.01 Rollback Segment Size and Status' skip 2
column kbytes format 999,999,999 heading 'K Bytes'
column segment_name heading 'Segment Name'
column tablespace_name heading 'Tablespace Name'
column status heading 'Status'
select a.segment_name, a.tablespace_name, a.status
from sys.dba_rollback_segs a, sys.dba_segments b
where b.segment_type = 'ROLLBACK' and
a.segment_name = b.segment_name
order by 1, 3;
ttitle left '5.02 Rollback Segment Sizes' skip 2
prompt
column name format a30
column extents format 999,999
column rssize format 99,999,999,999
column optsize format 99,999,999,999
select name "Rollback Segment Optimal Sizes", extents, rssize, optsize
from v$rollstat s, v$rollname n
where s.usn = n.usn;
ttitle left '5.03 Rollback Segment Growth and Shrinkages' skip 2
prompt
column name format a30
select name "Rollback Segment", extents, wraps, extends, shrinks
from v$rollstat s, v$rollname n
where s.usn = n.usn;
ttitle left '5.04 Rollback Segment Transaction Information' skip 2
prompt
column name format a30
column writes format 999,999,999 heading 'Writes'
column xacts format 999,999 heading 'Xacts'
column gets format 999,999,999 heading 'Gets'
column waits format 999,999 heading 'Waits'
select name "Rollback Segment", writes, xacts, gets, waits
from v$rollstat s, v$rollname n
where s.usn = n.usn;
ttitle left '5.05 Rollback Segment Contention' skip 2
prompt
prompt
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
prompt
prompt Goal: If any ratio is > .01 then more rollback segments are needed.
prompt
prompt
prompt
prompt +---+
prompt | | Section 6: MTS
prompt +---+
prompt
ttitle '6.01 Multi-threaded Server Queue' skip 2
prompt
set heading off
select 'Items currently in queue = '||to_char(queued,'999,999')
from v$queue
union
select 'No Items in queue' from dual
where not exists (select null from v$queue);
ttitle '6.02 Multi-threaded Server Analysis' skip 2
prompt
set heading on
column "protocol" format a10
column "Total busy rate" format 999,999.99
column "Action To Take" format a40
select network "protocol", sum(busy)/(sum(busy)+sum(idle)) "Total busy rate",
'No Action Necessary' "Action To Take"
from v$dispatcher
group by network
having sum(busy)/(sum(busy)+sum(idle))*100 < .5
union
select network "protocol", sum(busy)/(sum(busy)+sum(idle)) "Total busy rate",
'Add More Dispatcher Processes' "Action To Take"
from v$dispatcher
group by network
having sum(busy)/(sum(busy)+sum(idle))*100 >= .5
union
select 'none', 0, 'na' from dual
where not exists (select null from v$dispatcher);
prompt * Lack of information indicates you are not using MTS
prompt
prompt protocol: List of protocols used by multi-threaded server
prompt Percentage: Busy rate of the dispatcher (%)
prompt Goal: Keep less than 50% busy
ttitle left '6.03 Multi-threaded Server Analysis' skip 2
prompt
prompt
select decode(totalq,0,'No Requests',wait/totalq || '1/100 sec')
"Average wait per request"
from v$queue
where type='COMMON'
union
select 'No Requests' from dual
where not exists (select null from v$queue);
prompt
prompt Wait per request: Total waiting time for all requests that have ever
prompt - been in the queue (1/100 sec).
prompt Shared server processes: Number of shared server processes currently
prompt - running (Oracle spawns these processes, as needed,
prompt - up to the maximum number allowed).
prompt MTS_MAX_SERVERS: Number server processes allowed, init.ora parameter.
prompt Goal: Shared server processes less than MTS_MAX_SERVERS.
ttitle left '6.04 MTS Information' skip 2
prompt
prompt
column "Shared Server Processes" format 99999999999999999999990
column "MTS Max Servers" format a14
column "Action To Take" format a30
select count(*) "Shared Server Processes", b.value "MTS Max Servers",
'No Action Necessary' "Action To Take"
from v$shared_server a, v$parameter b
where a.status != 'QUIT' and b.name = 'mts_max_servers'
group by b.value
having count(*) < b.value
union
select count(*) "Shared Server Processes", b.value "MTS Max Servers",
'Increase MTS_MAX_SERVERS in init.ora' "Action To Take"
from v$shared_server a, v$parameter b
where a.status != 'QUIT' and b.name = 'mts_max_servers'
group by b.value
having count(*) >= b.value
union
select 0, '0', 'Na'
from dual
where not exists (select null
from v$shared_server
where status != 'QUIT');
prompt
prompt * Lack of information indicates you are not using MTS.
prompt
prompt
prompt
prompt +---+
prompt | | Section 7: File I/O
prompt +---+
prompt
ttitle '7.01 Datafiles by Tablespace' skip 2
prompt
column "File Name" format a30 wrap
break on "Tablespace Name" skip 1
compute sum of blocks on "Tablespace Name"
select file_name "File Name", tablespace_name "Tablespace Name",
status "Status", blocks "Blocks"
from sys.dba_data_files
order by tablespace_name, file_id
/
prompt
prompt Shows the mapping of datafiles to tablespaces and the total size
prompt of each tablespace.
prompt
ttitle '7.02 I/O by Data File' skip 2
prompt
column "File Name" format a30 wrap
column phyrds format 9,999,990 heading 'Phys|Reads|Writes'
column phyblkrd format 9,999,990 heading 'Phys|Blocks|Read|Writes'
column readtim format 9,999,990.99 heading 'Read|Write|Time|Secs'
column xxx format a4 heading 'IO|Type'
break on "File Name"
select a.name ||'('|| a.status || ')' "File Name", 'R' xxx, b.phyrds,
b.phyblkrd, b.readtim/100 readtim
from v$datafile a, v$filestat b
where a.file# = b.file# and a.name is not null
union all
select a.name ||'('|| a.status || ')' "File Name", 'W' xxx, b.phywrts,
b.phyblkwrt, b.writetim/100 writetim
from v$datafile a, v$filestat b
where a.file# = b.file# and a.name is not null
order by 1, 2
/
prompt
prompt Goal is to have IO spread evenly across all datafiles.
prompt
ttitle '7.03 Most Read Files' skip 2
prompt
column phyrds format 9,999,990 heading 'Phys|Reads'
column phyblkrd format 9,999,990 heading 'Phys|Blocks|Read'
column readtim format 9,990.99 heading 'Read|Time|Secs'
select a.name ||'('|| a.status || ')' "File Name", 'R' xxx, b.phyrds,
b.phyblkrd, b.readtim/100 readtim
from v$datafile a, v$filestat b
where a.file# = b.file# and a.name is not null and exists
(select NULL
from v$filestat c
where c.phyrds > b.phyrds
having count(*) <= 5)
order by 3 desc
/
prompt
prompt This shows the files such that there were no more than 5 files with
prompt more reads then them. These are your 'hot' files with the most
prompt read activity.
ttitle '7.04 Most Written Files' skip 2
prompt
column "File Name" format a30 wrap
column phywrts format 999,990 heading 'Phys|Writes'
column phyblkwrt format 999,990 heading 'Phys|Blocks|Writes'
column writetim format 9,999,990.99 heading 'Write|Time|Secs'
select a.name ||'('|| a.status || ')' "File Name", 'W' xxx, b.phywrts,
b.phyblkwrt, b.writetim/100 writetim
from v$datafile a, v$filestat b
where a.file# = b.file# and a.name is not null and exists
(select NULL
from v$filestat c
where c.phywrts > b.phywrts
having count(*) <= 5)
order by 3 desc
/
prompt
prompt This shows the files such that there were no more than 5 files with
prompt more writes then them. These are your 'hot' files with the most
prompt write activity.
prompt
prompt
prompt 7.05 Fragmented Database Objects
prompt
prompt Fragmentation report - If number of extents is approaching Max extents,
prompt you may want to defragment the table or take other action to prevent
prompt an error when reaching max extents.
prompt
column owner noprint new_value owner_var
column segment_name format a28 heading 'Object Name'
column segment_type format a9 heading 'Table/Indx'
column sum(bytes) format 999,999,999 heading 'Bytes Used'
column count(*) format 9999 heading 'No.'
break on owner skip page 2
ttitle center 'Table Fragmentation Report' skip 2 -
left 'creator: ' owner_var skip 2
select a.owner, segment_name, segment_type, sum(bytes), max_extents, count(*)
from dba_extents a, dba_tables b
where segment_name = b.table_name
having count(*) > 12
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
/
ttitle center 'Index Fragmentation Report' skip 2 -
left 'creator: ' owner_var skip 2
select a.owner, segment_name, segment_type, sum(bytes), max_extents, count(*)
from dba_extents a, dba_indexes b
where segment_name = index_name
having count(*) > 12
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
/
prompt
prompt
prompt
prompt +---+
prompt | | Section 8: Redo Log Activity
prompt +---+
prompt
ttitle '8.01 Log File Information' skip 2
prompt
column "Group|Thread|Sequence" format a10 heading "Group|Thread|Sequence"
column "Log Size in Bytes" format 999,999,990 heading "Log|Size|Bytes"
column "Number of Members in Log Group" format 9990 heading "Numr|Mbrs|in|Grp"
column "Is Archived" format a4 heading "Is|Arch"
column "Lowest SNC in the Log" format 999,999,990 heading "Lowest|SCN|in Log"
column "Time of first SCN in the Log" heading "Time of first|SCN in the Log"
select '('||a.group#||','||a.thread#||','||a.sequence#||')' "Group|Thread|Sequence",
a.bytes "Log Size in Bytes", a.members "Number of Members in Log Group",
a.archived "Is Archived", a.first_change# "Lowest SNC in the Log",
a.first_time "Time of first SCN in the Log"
from v$log a
/
prompt
select group# "Log Group Number", status "Status", member "Redo Log Member Name"
from v$logfile
/
prompt
ttitle '8.02 Logging Parameter Settings' skip 2
prompt
column parameter format a70 heading "Log Related Init.Ora Settings"
select rpad( initcap(translate(substr(name,5),'_',' ')), 40, '.' ) ||
lpad(decode(type,1,value,2,value,3,ltrim(
to_char(to_number(value),'999,999,990')),4,value),30,'.') parameter
from v$parameter
where name like 'log\_%' escape '\'
order by name
/
prompt
ttitle '8.03 Log Waits' skip 2
prompt
column value format 9,999,990
select substr(name,1,26) "Name", value,
decode(value, 0,'No Action Required', '*** Read the following')
"Action To Take"
from v$sysstat
where (name = 'redo log space requests' or
name = 'redo log space wait time')
/
prompt
prompt Goal is to minimize Redo Space Requests. The value of redo log
prompt space requests should be near 0. If this value increments consistently,
prompt processes have had to wait for space in the buffer. In this case,
prompt increase the size of the redo log buffer. The size of the redo log
prompt buffer is determined by the initialization parameter LOG_BUFFER. The
prompt value of this parameter is expressed in bytes. Try increasing the
prompt size of the redo log buffer by increments of 5% until the value of
prompt redo log space requests nears 0.
ttitle left '8.04 Latch Analysis' skip 2
prompt
prompt
column "Miss Ratio" format 999.99
column "Imm Miss Ratio" format 9.99
select substr(l.name,1,28) "Name", gets, misses "Misses",
(misses/(gets+.001))*100 "Miss Ratio",
(immediate_misses/(immediate_gets+.001))*100 "Imm Miss Ratio"
from v$latch l, v$latchname ln
where l.latch# = ln.latch# and
((misses/(gets+.001))*100 > .2 or
(immediate_misses/(immediate_gets+.001))*100 > .2)
order by l.name;
prompt
prompt Goal: If Miss Ratio or Immediate Miss Ratio > 1 then latch
prompt contention exists. Try decreasing log_small_entry_max_size.
prompt
prompt
prompt End Of Report
spool off
ttitle off
set heading on
set linesize 80
set pagesize 2000
set feedback on
set termout on
set wrap on
