Sunday, March 8, 2009

Script: Free Space Report for all Datafiles and if AUTOEXTENT is ON

-- ################################################################
--
-- %Purpose: Free Space Report for all Datafiles and if AUTOEXTENT is ON
--
--################################################################
--
clear columns -
      breaks -
      computes
set pagesize 100

column file_name format a32
column tablespace_name format a15
column status format a3 trunc
column t format 999,999.000 heading "Total MB"
column a format a4 heading "Aext"
column p format 990.00 heading "% Free"

SELECT  df.file_name,
        df.tablespace_name,
        df. status,
        (df.bytes/1024000) t,
        (fs.s/df.bytes*100) p,
        decode (ae.y,1,'YES','NO') a
  FROM  dba_data_files df,
        (SELECT file_id,SUM(bytes) s
           FROM dba_free_space
           GROUP BY file_id) fs,
        (SELECT file#, 1 y
           FROM sys.filext$
           GROUP BY file#) ae
  WHERE df.file_id = fs.file_id
    AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_id;

column file_name clear
column tablespace_name clear
column status clear
column t clear
column a clear
column p clear
ttitle off

Script: DB Access Monitoring Report

-- ################################################################
--
-- %Purpose: DB Access Monitoring Report 
--           (Full Table and Index Scans, Chained Rows)
--
-- Use:      Needs Oracle DB Access
--
-- ################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
set termout on;

column  STATISTIC#  form 999        head 'Id'
column  NA          form a32        head 'Statistic'
column  RIA         form 990.90     head 'Row Access via|Index [%]'
column  RTS         form 990.90     head 'Row Access via|Table Scan [%]'
column  RA          form 9999999990 head 'Rows accessed'
column  PCR         form 990.90     head 'Chained|Rows [%]'
colum   CL          form 990.90     head 'Cluster|Length'

ttitle  left  'DB Access Monitoring Report'  skip 2

spool   db_access.log

select  rpad (NAME, 32, '.') as NA,
        VALUE
  from  V$SYSSTAT
 where  NAME like '%table scan%'
    or  NAME like '%table fetch%'
    or  NAME like '%cluster%';

ttitle  off

select  A.VALUE + B.VALUE                     as RA,
        A.VALUE / (A.VALUE + B.VALUE) * 100.0 as RIA,
        B.VALUE / (A.VALUE + B.VALUE) * 100.0 as RTS,
        C.VALUE / (A.VALUE + B.VALUE) * 100.0 as PCR,
        E.VALUE / D.VALUE                     as CL
  from  V$SYSSTAT A,
        V$SYSSTAT B,
        V$SYSSTAT C,
        V$SYSSTAT D,
        V$SYSSTAT E
 where  A.NAME = 'table fetch by rowid'
   and  B.NAME = 'table scan rows gotten'
   and  C.NAME = 'table fetch continued row'
   and  D.NAME = 'cluster key scans'
   and  E.NAME = 'cluster key scan block gets'
/

Script: ANALYZE TABLE with Estimate or Compute Statistic, depending on table size

###############################################################
--
-- %Purpose: This script will help us in ANALYZE table
--                 with estimate or compute statistic, 
--                 depending on table size, see SIGN(n)
--
-- Use:    Any table less than 10 MB in total size has statistics computed
--           while tables larger than 10 MB have statistics estimated.
--           SIGN(n) ==> if n <>
--                       if n = 0 the functions returns 0
--                       if n > 0 the functions returns 1
--
-- #############################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
spool compute_or_estimate_stat.sql
--
SELECT 'ANALYZE TABLE '||owner||'.'||table_name||' '||
       DECODE(SIGN(10485760 - initial_extent),1,'COMPUTE STATISTICS;',
                                    'ESTIMATE STATISTICS;')
FROM   sys.dba_tables
WHERE  owner NOT IN ('SYS','SYSTEM');
/
--
spool off;
set feed on;
@compute_or_estimate_stat.sql