REM ===============================================================================
REM = Script Name : db_status.sql =
REM = Usage : This script is to be used to give an overview of the current status =
REM = of a given database. Things like suspect default and temporary =
REM = tablespaces, tablespaces that have less than 20% free, objects that =
REM = will not be able to extend are covered in this script. =
REM ===============================================================================
clear columns buffer breaks
set feedback off verify off heading off linesize 80 pagesize 9999
set time off
set timing off
spool db_status.log
select ‘Server : ‘||vs.machine
from v$session vs, v$bgprocess vb
where vs.paddr = vb.paddr
and vb.paddr <> ’00’
and name = ‘SMON’;
select ‘Oracle Instance : ‘||name from v$database;
select ‘Date Run : ‘||to_char(sysdate,’HH24:MM – DD/MM/YY’) from dual;
REM select ‘Best Cache Hit Ratio ‘||hitratio||’% – ‘||’Database Running for ‘||uptime||’ Hrs’
REM from kpi_table
REM where hitratio = (select max(hitratio) from kpi_table);
set head on
prompt
prompt =========================================================================
prompt SPACE MANAGEMENT INFORMATION
prompt =========================================================================
prompt
prompt
prompt Users With Suspect Default Storage Definitions
prompt ———————————————–
col username format a20 heading ‘User Name’
col default_tablespace format a28 heading ‘Default Tablespace’
col temporary_tablespace format a28 heading ‘Temporary Tablespace’
select username, default_tablespace, temporary_tablespace
from dba_users
where username not in (‘SYS’,’SYSTEM’)
and ((default_tablespace=’SYSTEM’
or default_tablespace like ‘%TEMP%’
or default_tablespace like ‘%TMP%’
or default_tablespace like ‘%RBS%’
or default_tablespace like ‘%ROLLBACK%’)
or (temporary_tablespace not like ‘%TEMP%’
and temporary_tablespace not like ‘%TMP%’))
/
prompt
prompt
prompt
prompt Tablespaces With 20% Or Less Of Their Total Space Free
prompt ——————————————————-
set feedback off verify off linesize 78 pagesize 999
clear buffer breaks columns
col psm format 99990.99 heading ‘ Total |Space (Mb)’
col fsm format 99990.99 heading ‘Total Free|Space (Mb)’
col pcu format 990 heading ‘Pct Used’
col mfs format 99990.99 heading ‘Max Free|Chunk (Mb)’
break on report
compute sum of psm fsm on report
select a.tablespace_name, a.physsp/(1024*1024) psm,
nvl(b.freesp,0)/(1024*1024) fsm,
nvl(b.mfreecnk,0)/(1024*1024) mfs,
100-((100*nvl(b.freesp,0))/a.physsp) pcu
from (select tablespace_name, sum(bytes) physsp
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) freesp, max(bytes) mfreecnk
from dba_free_space
group by tablespace_name) b
where b.tablespace_name (+) = a.tablespace_name
and 100-((100*nvl(b.freesp,0))/a.physsp) >79.99
/
prompt
prompt
prompt Tablespace Fragmentation (Defrag if FSFI < 30%)
prompt ———————————————–
col FSFI format 999.99
select tablespace_name, sqrt(max(blocks)/sum(blocks)) * (100 /sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name
order by 1
/
prompt
prompt
prompt
prompt Objects That Will Not Be Able To Allocate Their Next Extent
prompt ————————————————————
prompt
prompt The following objects cannot extend because the tablespace they have
prompt been created in does not have enough contiguous free space left
clear columns
set feedback on
col nm heading ‘Segment Name’ format a30
col i_m heading ‘Ini Ext|Size(M)’ format 9990.99
col n_m heading ‘Nxt Ext|Size(M)’ format 9990.99
col tsp_n heading ‘TableSpace|Name’ format a15
col maxbm heading ‘Max Free|Chunk(M)’ format 9990.99
select a.owner||’.’||a.segment_name nm,
a.initial_extent/(1024*1024) i_m,
a.next_extent/(1024*1024) n_m,
a.tablespace_name tsp_n, b.maxbm
from dba_segments a,
(select tablespace_name, max(bytes)/(1024*1024) maxbm
from dba_free_space
group by tablespace_name) b
where b.tablespace_name = a.tablespace_name
and a.next_extent/(1024*1024) > b.maxbm
order by 1,2
/
prompt
prompt
prompt
prompt Objects With More Than 14 extents Allocated
prompt ——————————————–
prompt
prompt The following output lists the objects that have greater than 14 extents
set feedback on echo off TERMOUT ON pagesize 9999
clear columns breaks buffer
col Owner format a12 heading ‘Owner’
col Tablespace_name format a15 heading ‘Tablespace’
col SEGMENT_NAME FORMAT A15 HEADING ‘Segment|Name’
col SEGtype FORMAT A3 HEADING ‘Seg|Typ’
col sizem FORMAT 9990.90 HEADING ‘Size|(Mb)’
col nem FORMAT 9990.90 HEADING ‘Nxt|(Mb)’
col num_ext FORMAT 990 HEADING ‘Num|Ext’
col me FORMAT 990 HEADING ‘Max|Ext’
select Owner,
Tablespace_name,
segment_name,
substr(initcap(segment_type),1,3) segtype,
bytes/(1024*1024) sizem,
next_extent/(1024*1024) nem,
max_extents me,
extents num_ext
from dba_segments
where extents > 14
order by num_ext desc
/
clear columns
prompt
prompt
prompt
prompt Objects Which Have Allocated More Than Half Of Their Maximum Extents
prompt ———————————————————————
column segment_name format a25 HEADING ‘Segment Name’
column owner format a10 HEADING ‘Owner’
column bytesmb format 99,999.99 HEADING ‘Total | Size(MB)’
column max_extents format 999,999,999 HEADING ‘Maximum | Extents ‘
column extents format 999,999 HEADING ‘Extents |Allocated’
column percent format 999 HEADING ‘ % ‘
select owner,
segment_name,
bytes/1024/1024 bytesmb,
extents,
max_extents,
(100/max_extents) * extents percent
from dba_segments
where extents > max_extents/2
and max_extents != 0
order by percent DESC
/
clear columns
prompt
prompt
prompt Objects that are within 10 extents of their Maximum
prompt —————————————————
column segment_name format a25 heading ‘Segment Name’
column segment_type format a12 heading ‘Segment Type’
column owner format a10 heading ‘Owner’
column extents format 999,999,999 heading ‘Current Extents’
column max_extents format 999,999,999 heading ‘Maximum Extents’
select owner, segment_name, segment_type, extents, max_extents
from dba_segments
where owner not in (‘SYS’,’SYSTEM’) and
(max_extents – extents) <= 10
/
clear columns
prompt
prompt
prompt Objects That Have a LOW Max Extent Size
prompt —————————————
column segment_name format a25 heading ‘Segment Name’
column segment_type format a10 heading ‘Type’
column owner format a15 heading ‘Owner’
column max_extents format 9999 heading ‘Max Ext’
select owner, segment_name, segment_type, max_extents
from dba_segments
where owner not in (‘SYS’,’SYSTEM’) and
max_extents < 100
/
clear columns
prompt
prompt
prompt
prompt Current INVALID Objects
prompt ———————–
column object_name format a30 HEADING ‘Object Name’
column owner format a20 HEADING ‘Owner’
column object_type format a20 HEADING ‘Object Type’
select owner, object_name, object_type
from dba_objects
where status = ‘INVALID’
/
clear columns
prompt
prompt
prompt Current DISABLED Constraints
prompt —————————-
column owner format a15 heading ‘Owner’
column constraint_name for a25 heading ‘Constraint Name’
column table_name for a25 heading ‘Table Name’
column constraint_type for a4 heading ‘Type’
select owner, constraint_name, table_name, constraint_type
from dba_constraints
where status = ‘DISABLED’
/
clear columns
prompt
prompt
prompt
prompt Current DISABLED Triggers
prompt ————————-
column owner for a15 heading ‘Owner’
column trigger_name for a20 heading ‘Trigger Name’
column table_name for a20 heading ‘Table Name’
column table_owner for a18 heading ‘Table Owner’
select owner, trigger_name, table_name, table_owner
from dba_triggers
where status = ‘DISABLED’
/
clear columns
prompt
prompt
prompt
prompt Sequences that are close to Maximum
prompt ———————————–
col sequence_owner for a15 heading “Seq. Owner”
col sequence_name for a20 heading “Seq. Name”
col last_number for 99999999 heading “Currently”
col max_value for 99999999 heading “Maximum”
select sequence_owner, sequence_name, last_number, max_value
from dba_sequences
where (max_value – last_number) < 100
/
clear columns
prompt
prompt
prompt Permanent Objects Resident In Temporary Tablespaces
prompt —————————————————-
column segment_name format a35 HEADING ‘Segment Name’
column owner format a10 HEADING ‘Owner’
column segment_type format a4 HEADING ‘Typ’
column bytesmb format 99,999.99 HEADING ‘Total Size (Bytes)’
select owner,
segment_name,
decode(substr(segment_type,1,3), ‘TAB’, ‘Tbl:’,’IND’, ‘Idx:’,
substr(segment_type,1,3)||’:’) ob_type,
tablespace_name,
bytes/1024/1024 bytesmb
from dba_segments
where (tablespace_name like ‘%TEMP%’
or tablespace_name like ‘%TMP%’)
and segment_type != ‘TEMPORARY’
/
clear columns
prompt
prompt
prompt
prompt Indexes Created In Data Tablespaces
prompt ————————————
prompt
prompt N.B. This list is based on the assumption that tablespaces which
prompt contain the string ‘DATA’ should contain tables only.
prompt This report is, therefore, only a guide and should be
prompt interpreted in the light of the individual database organisation.
column type format a8
column tablespace_name format a10
column owner format a10
column segment_name format a30
column bytesmb format 999,999,999,999.99 HEADING ‘Size (MB)’
select tablespace_name,owner,segment_name,
bytes/1024/1024 bytesmb
from dba_segments
where tablespace_name like ‘%DATA%’
and segment_type =’INDEX’
and tablespace_name not like ‘%IND%’
and tablespace_name not like ‘%IDX%’
and tablespace_name not like ‘%INX%’
order by tablespace_name;
clear columns
prompt
prompt
prompt
prompt Objects With Non-Zero PCTINCREASE Settings
prompt ——————————————-
col owner format a15 heading ‘Owner’
col segment_name for a25 heading ‘Segment Name’
col segment_type for a20 heading ‘Type’
col pct_increase for 999 heading ‘PCT Inc’
prompt
prompt N.B. There may be very good reasons why any objects listed here have
prompt non-zero PCTINCREASE settings but generally a setting of zero
prompt will lead to less fragmentation.
select owner, segment_name, segment_type, pct_increase
from dba_segments
where owner not in (‘SYS’, ‘SYSTEM’)
and pct_increase > 0
/
prompt
clear columns
prompt
prompt Objects Created In The System Tablespace
prompt —————————————–
prompt Objects other than those owned by SYS or SYSTEM should exist in
prompt their own tablespace
col Owner format a17 heading ‘Owner’
col ob_type format a4 heading ‘Type’
col ob_name format a30 heading ‘Object’
col size_m format 990.90 heading ‘Size M’
select owner, decode(substr(segment_type,1,3), ‘TAB’, ‘Tbl:’,
‘IND’, ‘Idx:’,
substr(segment_type,1,3)||’:’) ob_type,
segment_name ob_name, bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = ‘SYSTEM’
and owner not in (‘SYS’, ‘SYSTEM’)
order by 1,3
/
prompt
prompt
REM This script examines various V$ parameters. The script makes suggestions
REM on mods that can be made to your system if specific conditions exist. The
REM report should be run after the system has been up for at least 10 hours
REM and should be run over a period of time to get a real feel for what the
REM real condition of the database is. A one-time sample run on an inactive
REM system will not give an accurate picture of what is really occuring within
REM the database.
REM
REM If the database is shut down on a nightly basis for backups, the script can
REM be run just prior to shutdown each night to enable trending analysis.
REM
REM This script can be run on any platform but is tailored to evaluate an
REM Oracle7.0.x database. It has not yet been run on in an Oracle7.1
REM environment. The script assumes that you are running it from a DBA
REM account where CATDBSYN.SQL has been run.
REM
REM Marlene L. Theriault – Oracle Corporation – 02 November 1993
REM
REM **NOTE: Be sure to change the “spool” path to reflect your directory
REM structure.
REM
set pages 60
set verify off
set head off
col name format a45
col dbname format a10
col phyrds format 9,999,999
col phywrts format 9,999,999
set echo off termout off feedback off
set newpage 1
set linesize 80
define cr=chr(10)
REM
REM *** Change the spool path/output name here **
REM
spool $RPT/tuning_stats.txt
REM
ttitle ‘SYSTEM STATISTICS FOR ORACLE7’
select ‘DATABASE: ‘,value dbname from v$parameter where name like ‘%db_name%’;
ttitle off
select ‘LIBRARY CACHE STATISTICS:’ from dual;
select ‘PINS – # of times an item in the library cache was executed – ‘|| sum(pins),
‘RELOADS – # of library cache misses on execution steps – ‘||sum(reloads)||&cr||&cr,
‘RELOADS / PINS * 100 = ‘||round((sum(reloads) / sum(pins) * 100),2)||’%’
from v$librarycache
/
prompt Increase memory until RELOADS is near 0 but watch out for Paging/swapping.
prompt To increase library cache, increase SHARED_POOL_SIZE
prompt
prompt ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size on VMS.
prompt
prompt Library Cache Misses indicate that the Shared Pool is not big
prompt enough to hold the shared SQL area for all concurrently open cursors.
prompt If you have no Library Cache misses (PINS = 0), you may get a small
prompt increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which
prompt prevents ORACLE from deallocating a shared SQL area while an application
prompt cursor associated with it is open.
prompt
prompt For Multi-threaded server, add 1K to SHARED_POOL_SIZE per user.
prompt
prompt ——————————————————————————–
column xn1 format a50
column xn2 format a50
column xn3 format a50
column xv1 new_value xxv1 noprint
column xv2 new_value xxv2 noprint
column xv3 new_value xxv3 noprint
column d1 format a50
column d2 format a50
prompt HIT RATIO:
prompt
prompt Values Hit Ratio is calculated against:
prompt
select lpad(name,20,’ ‘)||’ = ‘||value xn1, value xv1
from
v$sysstat
where
statistic# = 37
/
select lpad(name,20,’ ‘)||’ = ‘||value xn2, value xv2 from
v$sysstat
where
statistic# = 38
/
select lpad(name,20,’ ‘)||’ = ‘||value xn3, value xv3 from
v$sysstat b
where
statistic# = 39
/
set pages 60
select ‘Logical reads = db block gets + consistent gets ‘, lpad(‘Logical Reads = ‘,24,’ ‘)||to_char(&xxv1+&xxv2) d1 from dual
/
select ‘Hit Ratio = (logical reads – physical reads) / logical reads’,lpad(‘Hit Ratio = ‘,24,’ ‘)||
round( (((&xxv2+&xxv1) – &xxv3) / (&xxv2+&xxv1))*100,2 )||’%’ d2
from dual
/
prompt If the hit ratio is less than 60%-70%, increase the initialization
prompt parameter DB_BLOCK_BUFFERS. ** NOTE: Increasing this parameter will
prompt increase the SGA size.
prompt
prompt ——————————————————————————–
col name format a30
col gets format 9,999,999
col waits format 9,999,999
prompt ROLLBACK CONTENTION STATISTICS:
prompt
prompt GETS – # of gets on the rollback segment header prompt WAITS – # of waits for the rollback segment header
set head on;
select name, waits, gets
from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn
/
set head off
select ‘The average of waits/gets is ‘||
round((sum(waits) / sum(gets)) * 100,2)||’%’ from v$rollstat
/
prompt
prompt If the ratio of waits to gets is more than 1% or 2%, consider
prompt creating more rollback segments
prompt
prompt Another way to gauge rollback contention is:
prompt
column xn1 format 9999999
column xv1 new_value xxv1 noprint
set head on
select class, count
from v$waitstat
where class in (‘system undo header’,’system undo block’,’undo header’,
‘undo block’)
/
set head off
select ‘Total requests = ‘||sum(count) xn1, sum(count) xv1 from v$waitstat
/
select ‘Contention for system undo header = ‘||
(round(count/(&xxv1+0.00000000001),4)) * 100||’%’
from v$waitstat
where class = ‘system undo header’
/
select ‘Contention for system undo block = ‘||
(round(count/(&xxv1+0.00000000001),4)) * 100||’%’
from v$waitstat
where class = ‘system undo block’
/
select ‘Contention for undo header = ‘||
(round(count/(&xxv1+0.00000000001),4)) * 100||’%’
from v$waitstat
where class = ‘undo header’
/
select ‘Contention for undo block = ‘||
(round(count/(&xxv1+0.00000000001),4)) * 100||’%’
from v$waitstat
where class = ‘undo block’
/
prompt
prompt If the percentage for an area is more than 1% or 2%, consider
prompt creating more rollback segments. Note: This value is usually very small
prompt and has been rounded to 4 places.
prompt
prompt ——————————————————————————–
prompt REDO CONTENTION STATISTICS:
prompt
prompt The following shows how often user processes had to wait for space in
prompt the redo log buffer:
select name||’ = ‘||value||&cr
from v$sysstat
where name = ‘redo log space requests’
/
prompt
prompt This value should be near 0. If this value increments consistently,
prompt processes have had to wait for space in the redo buffer. If this
prompt condition exists over time, increase the size of LOG_BUFFER in the
prompt init.ora file in increments of 5% until the value nears 0.
prompt ** NOTE: increasing the LOG_BUFFER value will increase total SGA size.
prompt
prompt ——————————————————————————–
col name format a15
col gets format 999999999
col misses format 9999999
col immediate_gets heading ‘IMMED GETS’ format 999999999
col immediate_misses heading ‘IMMED MISS’ format 9999999
col sleeps format 999999
prompt LATCH CONTENTION:
prompt
prompt GETS – # of successful willing-to-wait requests for a latch
prompt MISSES – # of times an initial willing-to-wait request was unsuccessful
prompt IMMEDIATE_GETS – # of successful immediate requests for each latch
prompt IMMEDIATE_MISSES = # of unsuccessful immediate requests for each latch
prompt SLEEPS – # of times a process waited and requests a latch after an initial
prompt willing-to-wait request
prompt
prompt If the latch requested with a willing-to-wait request is not
prompt available, the requesting process waits a short time and requests again.
prompt If the latch requested with an immediate request is not available,
prompt the requesting process does not wait, but continues processing
prompt
set head on
select name, gets, misses, immediate_gets, immediate_misses, sleeps
from v$latch
where name in (‘redo allocation’,’redo copy’)
/
set head off
select ‘Ratio of MISSES to GETS: ‘||
round((sum(misses)/(sum(gets)+0.00000000001) * 100),2)||’%’
from v$latch
where name in (‘redo allocation’,’redo copy’)
/
select ‘Ratio of IMMEDIATE_MISSES to IMMEDIATE_GETS: ‘||
round((sum(immediate_misses)/
(sum(immediate_misses+immediate_gets)+0.00000000001) * 100),2)||’%’
from v$latch
where name in (‘redo allocation’,’redo copy’)
/
prompt
prompt If either ratio exceeds 1%, performance will be affected.
prompt
prompt Decreasing the size of LOG_SMALL_ENTRY_MAX_SIZE reduces the number of
prompt processes copying information on the redo allocation latch.
prompt
prompt Increasing the size of LOG_SIMULTANEOUS_COPIES will reduce contention for
prompt redo copy latches.
rem
rem This shows the library cache reloads
rem
set head on
prompt
prompt ——————————————————————————–
prompt
prompt Look at gethitratio and pinhit ratio
prompt
prompt GETHITRATIO is number of GETHTS/GETS
prompt PINHIT RATIO is number of PINHITS/PINS – number close to 1 indicates
prompt that most objects requested for pinning have been cached. Pay close
prompt attention to PINHIT RATIO.
prompt
column namespace format a20 heading ‘NAME’
column gets format 99999999 heading ‘GETS’
column gethits format 99999999 heading ‘GETHITS’
column gethitratio format 999.99 heading ‘GET HIT|RATIO’
column pins format 999999999 heading ‘PINHITS’
column pinhitratio format 999.99 heading ‘PIN HIT|RATIO’
select namespace, gets, gethits, gethitratio, pins, pinhitratio
from v$librarycache
/
rem
rem
rem This looks at the dictionary cache miss rate
rem
prompt
prompt ——————————————————————————–
prompt THE DATA DICTIONARY CACHE:
prompt
prompt
prompt Consider keeping this below 5% to keep the data dictionary cache in
prompt the SGA. Up the SHARED_POOL_SIZE to improve this statistic. **NOTE:
prompt increasing the SHARED_POOL_SIZE will increase the SGA.
prompt
rem
column dictcache format 999.99 heading ‘Dictionary Cache | Ratio %’
select sum(getmisses) / (sum(gets)+0.00000000001) * 100 dictcache
from v$rowcache
/
prompt
prompt ——————————————————————————–
prompt
prompt SYSTEM EVENTS:
prompt
prompt Not sure of the value of this section yet but it looks interesting.
prompt
col event format a30 heading ‘Event’
col total_waits format 9999999999 heading ‘Total|Waits’
col time_waited format 999999999999 heading ‘Time Wait|In Hndrds’
col total_timeouts format 999999 heading ‘Timeout’
col average_wait heading ‘Average|Time’ format 999999.999
set pages 999
select * from v$system_event
order by time_waited desc
/
prompt
prompt ——————————————————————————–
rem
rem
rem This looks at the sga area breakdown
rem
prompt THE SGA AREA ALLOCATION:
prompt
prompt
prompt This shows the allocation of SGA storage. Examine this before and
prompt after making changes in the INIT.ORA file which will impact the SGA.
prompt
rem
col name format a40
select name, bytes
from v$sgastat
/
set head off
select ‘total of SGA ‘||sum(bytes)
from v$sgastat
/
prompt
prompt ——————————————————————————–
rem
rem Displays all the base session statistics
rem
set head on
set pagesize 110
column name format a55 heading ‘Statistic Name’
column value format 9,999,999,999,999 heading ‘Result’
column statistic# heading ‘Stat#’ format 9999
ttitle center ‘Instance Statistics’ skip 2
prompt
prompt Below is a dump of the core Instance Statistics that are greater than 0.
prompt Although there are a great many statistics listed, the ones of greatest
prompt value are displayed in other formats throughout this report. Of interest
prompt here are the values for:
prompt
prompt cumulative logons
prompt (# of actual connections to the DB since last startup – good volume-of-use
prompt statistic)
prompt
prompt #93 table fetch continued row
prompt (# of chained rows – will be higher if there are a lot of long fields – if the
prompt value goes up over time, it is a good signaller of general database
prompt fragmentation)
prompt
select statistic#, name, value
from v$sysstat
where value > 0
/
prompt
prompt ——————————————————————————–
set pages 66;
set space 3;
set heading on;
prompt
prompt Parse Ratio usually falls between 1.15 and 1.45. If it is higher, then
prompt it is usually a sign of poorly written Pro* programs or unoptimized
prompt SQL*Forms applications.
prompt
prompt Recursive Call Ratio will usually be between
prompt
prompt 7.0 – 10.0 for tuned production systems
prompt 10.0 – 14.5 for tuned development systems
prompt
prompt Buffer Hit Ratio is dependent upon RDBMS size, SGA size and
prompt the types of applications being processed. This shows the %-age
prompt of logical reads from the SGA as opposed to total reads – the
prompt figure should be as high as possible. The hit ratio can be raised
prompt by increasing DB_BUFFERS, which increases SGA size. By turning on
prompt the “Virtual Buffer Manager” (db_block_lru_statistics = TRUE and
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters),
prompt you can determine how many extra hits you would get from memory as
prompt opposed to physical I/O from disk. **NOTE: Turning these on will
prompt impact performance. One shift of statistics gathering should be enough
prompt to get the required information.
prompt
ttitle left ‘Ratios for this Instance’ skip 2
column pcc heading ‘Parse|Ratio’ format 99.99
column rcc heading ‘Recsv|Cursr’ format 99.99
column hr heading ‘Buffer|Ratio’ format 999,999,999,999.999
column rwr heading ‘Rd/Wr|Ratio’ format 999,999.9
column bpfts format 999,999 heading ‘Blks per|Full TS’
REM Modified for O7.1 to reverse ‘cumulative opened cursors’ to
REM ‘opened cursors cumulative’
REM was:sum(decode(a.name,’cumulative opened cursors’,value,.00000000001)) pcc,
REM and:sum(decode(a.name,’cumulative opened cursors’,value,.00000000001)) rcc,
select
sum(decode(a.name,’parse count’,value,0)) /
sum(decode(a.name,’opened cursors cumulative’,value,.00000000001)) pcc,
sum(decode(a.name,’recursive calls’,value,0)) /
sum(decode(a.name,’opened cursors cumulative’,value,.00000000001)) rcc,
(1-(sum(decode(a.name,’physical reads’,value,0)) /
sum(decode(a.name,’db block gets’,value,.00000000001)) +
sum(decode(a.name,’consistent gets’,value,0))) * (-1)) hr,
sum(decode(a.name,’physical reads’,value,0)) /
sum(decode(a.name,’physical writes’,value,.00000000001)) rwr,
(sum(decode(a.name,’table scan blocks gotten’,value,0)) –
sum(decode(a.name,’table scans (short tables)’,value,0)) * 4) /
sum(decode(a.name,’table scans (long tables)’,value,.00000000001)) bpfts
from v$sysstat a
/
prompt
prompt ——————————————————————————–
prompt This looks at overall i/o activity against individual files within a
prompt tablespace
prompt
prompt Look for a mismatch across disk drives in terms of I/O
prompt
prompt Also, examine the Blocks per Read Ratio for heavily accessed
prompt TSs – if this value is significantly above 1 then you may have
prompt full tablescans occurring (with multi-block I/O)
prompt
prompt If activity on the files is unbalanced, move files around to balance
prompt the load. Should see an approximately even set of numbers across files.
prompt
set pagesize 100;
set space 1
column pbr format 99999999999 heading ‘Physical|Blk Read’
column pbw format 999999999 heading ‘Physical|Blks Wrtn’
column pyr format 999999 heading ‘Physical|Reads’
column readtim format 99999999 heading ‘Read|Time’
column name format a35 heading ‘DataFile Name’
column writetim format 99999999 heading ‘Write|Time’
ttitle center ‘Tablespace Report’ skip 2
compute sum of f.phyblkrd, f.phyblkwrt on report
rem
select fs.name name,f.phyblkrd pbr,f.phyblkwrt pbw,f.readtim, f.writetim
from v$filestat f, v$datafile fs
where f.file# = fs.file#
order by fs.name
/
prompt
prompt ——————————————————————————–
prompt GENERATING WAIT STATISTICS:
prompt
prompt This will show wait stats for certain kernel instances. This may show
prompt the need for additional rbs, wait lists, db_buffers
prompt
ttitle center ‘Wait Statistics for the Instance’ skip 2
column class heading ‘Class Type’
column count format 99,999,999 heading ‘Times Waited’
column time heading ‘Total Times’ format 99,999,999
select class, count, time
from v$waitstat
where count > 0
order by class
/
prompt
prompt Look at the wait statistics generated above (if any). They will
prompt tell you where there is contention in the system. There will
prompt usually be some contention in any system – but if the ratio of
prompt waits for a particular operation starts to rise, you may need to
prompt add additional resource, such as more database buffers, log buffers,
prompt or rollback segments
prompt
prompt ——————————————————————————–
prompt ROLLBACK STATISTICS:
prompt
ttitle off;
set linesize 132
column extents format 999 heading ‘Extents’
column rssize format 999,999,999 heading ‘Size in|Bytes’
column optsize format 999,999,999 heading ‘Optimal|Size’
column hwmsize format 99,999,999 heading ‘High Water|Mark’
column shrinks format 9,999 heading ‘Number of|Shrinks’
column wraps format 9,999 heading ‘Number of|Wraps’
column extends format 999,999 heading ‘Number of|Extends’
column aveactive format 999,999,999 heading ‘Average size|Active Extents’
column rownum noprint
select rssize, optsize, hwmsize, shrinks, wraps, extends, aveactive
from v$rollstat
order by rownum
/
rem
prompt
prompt ——————————————————————————–
set linesize 80
break on report
compute sum of gets waits writes on report
ttitle center ‘Rollback Statistics’ skip 2
select rownum, extents, rssize, xacts, gets, waits, writes
from v$rollstat
order by rownum
/
ttitle off
set heading off
ttitle off
prompt
prompt ——————————————————————————–
prompt
prompt SORT AREA SIZE VALUES:
prompt
prompt To make best use of sort memory, the initial extent of your Users
prompt sort-work Tablespace should be sufficient to hold at least one sort
prompt run from memory to reduce dynamic space allocation. If you are getting
prompt a high ratio of disk sorts as opposed to memory sorts, setting
prompt sort_area_retained_size = 0 in init.ora will force the sort area to be
prompt released immediately after a sort finishes.
prompt
column value format 999,999,999,999
select ‘INIT.ORA sort_area_size: ‘||value
from v$parameter where name like ‘sort_area_size’;
select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in (‘sorts (disk)’,’sorts (memory)’,’sorts (rows)’)
/
prompt
prompt ——————————————————————————–
set heading on
set space 2
prompt
prompt This looks at Tablespace Sizing – Total bytes and free bytes
prompt
ttitle center ‘Tablespace Sizing Information’ Skip 2
column tablespace_name format a30 heading ‘TS Name’
column sbytes format 999,999,999,999 heading ‘Total Bytes’
column fbytes format 9,999,999,999 heading ‘Free Bytes’
column kount format 999 heading ‘Ext’
compute sum of fbytes on tablespace_name
compute sum of sbytes on tablespace_name
compute sum of sbytes on report
compute sum of fbytes on report
break on report
rem Here must grant select on DBA_FREE_SPACE directly to the
rem creator of this view, else error occurs indicating that you cannot create
rem view using role privs
create or replace view sum_tbsp_free as
select sum(bytes) bytes, count(bytes) num_chks, tablespace_name
from sys.dba_free_space
group by tablespace_name
/
rem kept Marlenes query, just substituted the view for DBA_FREE_SPACE and
rem removed SUMS/COUNT verb from associated attributes
rem group by using “b” items works because of 1-to-1 relationship
select a.tablespace_name, sum(a.bytes) sbytes, b.bytes fbytes,
b.num_chks kount
from dba_data_files a, sum_tbsp_free b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.bytes,b.num_chks
order by a.tablespace_name
/
set linesize 80
prompt
prompt A large number of Free Chunks indicates that the tablespace may need
prompt to be defragmented and compressed.
prompt
prompt ——————————————————————————–
set heading off
ttitle off
column value format 99,999,999,999
select ‘Total Physical Reads’, value
from v$sysstat
where statistic# = 39
/
prompt
prompt If you can significantly reduce physical reads by adding incremental
prompt data buffers…do it. To determine whether adding data buffers will
prompt help, set db_block_lru_statistics = TRUE and
prompt db_block_lru_extended_statistics = TRUE in the init.ora parameters.
prompt You can determine how many extra hits you would get from memory as
prompt opposed to physical I/O from disk. **NOTE: Turning these on will
prompt impact performance. One shift of statistics gathering should be enough
prompt to get the required information.
prompt
set heading on
clear computes
ttitle off
prompt
prompt ——————————————————————————–
prompt CHECKING FOR FRAGMENTED DATABASE OBJECTS:
prompt
prompt Fragmentation report – If number of extents is approaching Maxextents,
prompt it is time to defragment the table.
prompt
column owner noprint new_value owner_var
column segment_name format a30 heading ‘Object Name’
column segment_type format a9 heading ‘Table/Indx’
column sum(bytes) format 999,999,999 heading ‘Bytes Used’
column count(*) format 999 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(*) > 3
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(*) > 3
group by a.owner, segment_name, segment_type, max_extents
order by a.owner, segment_name, segment_type, max_extents
/
prompt
clear columns
prompt
prompt =========================== E N D O F F I L E ==============================
spool off
set echo on
set numwidth 9
Set head on
set feedback 1
set verify on
set time on
set timing on