 clear col
set head off
set pause off
set pages 0
set verify off
set feedback off

clear breaks
clear compute

column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy hh:miam') today
       from dual;

break on instance
column instance new_value instance_name
select substr(name,1,8) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 132 verify off
/*set space 2*/
ttitle left 'Date: ' format a18 dba_date -
       center 'DB Review(3) - ' format a8 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

set head on
set verify on
set feedback on
col table_name format a30
col ext format 990
col used format 99,990
col empty format 99,990
col used_pct format 990.99
col next format 99,999,990
spool db_revw3.rslt 

set echo on;
/* ******************************************************************* */
/*                                                                     */
/*                >>> Database Review; R.Kupcunas <<<                  */
/*                                                                     */
/*  Name:    db_revw3.sql                                              */
/*  Creator: Rick Kupcunas                                             */
/*  eMail:   RKupcunas@Deloitte.com                                    */
/*  Created: 2008-Nov-01                                               */
/*                                                                     */
/* this script will show various database extent parameters.           */
/*  SECTION 1 - Checking for Next Extents that will not                */
/*              fit in the current Tablespace                          */
/*  SECTION 2 - Count of Tables with PCTINCREASE > 0 %                 */
/*              fit in the current Tablespace                          */
/*  SECTION 3 - Checking For Tables with PCTINCREASE > 50 %            */
/*  SECTION 4 - Segments With More Than Twenty Extents...              */
/*   Criteria:                                                         */
/*    Condition One:                                                   */
/*       a. >200     Extents --> Warning                               */
/*       b. >2000    Extents --> Recreate                              */
/*    Condition Two:                                                   */
/*       a. (Extents/MaxExtents > 50%) --> Recreate (or warning if     */
/*       b.  the number of max extents <= 50)                          */
/*  SECTION 5 - Tablespace Utilization                                 */
/*  SECTION 6 - Calculation of Dictionary Cache Hit Ratio              */
/*  SECTION 7 - Calculating Table/Index Access Ratio                   */
/*       a. Ratio of Full Table Scans Queries to Index access queries  */
/*       b. Overall Table/Index access statistics                      */
/*  SECTION 8 - Sort Activity                                          */
/*  SECTION 9 - Calculating Buffer Busy Waits                          */
/*  SECTION 10 - Buffer Hit Ratios                                     */
/*  SECTION 11 - Test for redo latch contention                        */
/*  SECTION 12 - Test for low Library Cache Hit Ratio                  */
/*  SECTION 13 - Test for excessive reloads in library cache           */
/*  SECTION 14 - Test for dictionary cache getmisses                   */
/*  SECTION 15 - Test condition which cause fragmentation              */
/*  SECTION 16 - Test for invalid objects                              */
/*  SECTION 17 - Check for invalid user setup                          */
/*  SECTION 18 - Check for user objects in SYSTEM or TEMP              */
/*  SECTION 19 - Check for the highwater session statistics            */
/*  SECTION 20 - If in archive mode, make sure it is enabled           */
/*  SECTION 21 - Shared Pool Statistics                                */
/*                                                                     */
/* All rights reserved; no part of this script may be copied without   */
/*   the expressed written permission of R.Kupcunas                    */
/*                                                                     */
/* ******************************************************************* */

/* **************************************************** */
/*  SECTION 1 - Checking for Next Extents that will not */
/*              fit in the current Tablespace           */
/* **************************************************** */

set echo off;

drop table table_utilization;

create table table_utilization
    (tablespace_name      		varchar2(50),
     total_bytes          		number,
     free_bytes           		number,
     fragments            		number,
     largest_free_piece   		number,
     sum_10_largest_free_piece   	number,
     tenth_largest_piece          number,
     max_extent_size              number)
PCTFREE              10
STORAGE (initial     8k
         next        8k
         minextents  1
         maxextents  245
         pctincrease 0);

insert into table_utilization
    (tablespace_name, total_bytes) 
     select tablespace_name,sum(bytes)
       from dba_data_files
       group by tablespace_name;

update table_utilization b
  set free_bytes = (select sum(a.bytes) 
                      from dba_free_space a
                      where a.tablespace_name = b.tablespace_name),
      fragments  = (select count(*) 
                      from dba_free_space c
                      where c.tablespace_name = b.tablespace_name),
      largest_free_piece
                 = (select max(f.bytes)
			  from dba_free_space f
                     where f.tablespace_name = b.tablespace_name);

DECLARE 
    v_TABLESPACE_NAME		table_utilization.TABLESPACE_NAME%type;
    v_started               	VARCHAR2(200);
    v_bytes              		NUMBER;
    v_sum_bytes              	NUMBER;
    v_count				NUMBER;
    v_tenth_largest_piece		NUMBER;

    CURSOR PS_CURSOR_1 IS 
        select tablespace_name
	  from table_utilization
        order by 1 asc;
    PS_REC_1 PS_CURSOR_1%ROWTYPE;

    CURSOR PS_CURSOR_2 IS 
        select f.bytes
	  from dba_free_space f
        where f.tablespace_name = v_TABLESPACE_NAME
        order by 1 desc;
    PS_REC_2 PS_CURSOR_2%ROWTYPE;
BEGIN
    OPEN PS_CURSOR_1;
LOOP
    FETCH PS_CURSOR_1 INTO v_TABLESPACE_NAME;
    EXIT WHEN PS_CURSOR_1%NOTFOUND;

    /* v_started  := '     tablespace '||v_TABLESPACE_NAME; */
    /* DBMS_OUTPUT.PUT_LINE (v_started);                    */

    v_sum_bytes := 0;
    v_count 	   := 0;

    OPEN PS_CURSOR_2;
    LOOP
      FETCH PS_CURSOR_2 INTO v_bytes;
      EXIT WHEN PS_CURSOR_2%NOTFOUND;
      v_count := v_count + 1;
      if (v_count < 11) THEN
        v_sum_bytes := v_sum_bytes + v_bytes;
        v_tenth_largest_piece := v_bytes;
      END IF;
    END LOOP;
    CLOSE PS_CURSOR_2;

    /* v_started  := '     tablespace '||v_TABLESPACE_NAME||'('||to_char(v_sum_bytes)||')'; */
    /* DBMS_OUTPUT.PUT_LINE (v_started);                                                    */

    UPDATE table_utilization
         SET sum_10_largest_free_piece = v_sum_bytes,
             tenth_largest_piece       = v_tenth_largest_piece
         where tablespace_name = v_TABLESPACE_NAME;
    commit;
END LOOP;
COMMIT;
CLOSE PS_CURSOR_1;
END;
/
commit;

/* ********************************************************************** */
/* Derive the largest extent size that used to accomidate 10 next extents */
/*   - I am assuming the the PCTINCREASE is equal to zero; therefore, all */
/*     extents will be of the same size                                   */
/* ********************************************************************** */
 
DECLARE 
    v_TABLESPACE_NAME		table_utilization.TABLESPACE_NAME%type;
    v_started               	VARCHAR2(200);
    v_bytes              		NUMBER;
    v_sum_bytes              	NUMBER;
    v_count				NUMBER;
    v_tenth_largest_piece		NUMBER;

    v_10_extent_size_high		NUMBER;
    v_count_high 			NUMBER;
    v_rows_fetched 			NUMBER;
    DEBUG_LEVEL1			BOOLEAN := FALSE;
    DEBUG_LEVEL2			BOOLEAN := FALSE;
    DEBUG_LEVEL3			BOOLEAN := FALSE;

    CURSOR PS_CURSOR_1 IS 
        select tablespace_name
	  from table_utilization 
        order by 1 asc;
    PS_REC_1 PS_CURSOR_1%ROWTYPE;

    CURSOR PS_CURSOR_2 IS 
        select f.bytes
	  from dba_free_space f
        where f.tablespace_name = v_TABLESPACE_NAME
        order by 1 desc;
    PS_REC_2 PS_CURSOR_2%ROWTYPE;
BEGIN
  OPEN PS_CURSOR_1;
  LOOP
    FETCH PS_CURSOR_1 INTO v_TABLESPACE_NAME;
    EXIT WHEN PS_CURSOR_1%NOTFOUND;

    IF (DEBUG_LEVEL1 = TRUE) THEN
      v_started  := 'Tablespace ........ '||v_TABLESPACE_NAME; 
      DBMS_OUTPUT.PUT_LINE (v_started);                    
    END IF;

    v_sum_bytes := 0;
    v_count 	   := 0;

    v_10_extent_size_high := 200*1024*1024;	/* largest datafile will be 2GByte */

    v_count_high := 0;
    v_rows_fetched := 0;

    LOOP 
      OPEN PS_CURSOR_2;
      LOOP
        FETCH PS_CURSOR_2 INTO v_bytes;
         EXIT WHEN PS_CURSOR_2%NOTFOUND;

         v_rows_fetched := v_rows_fetched + 1;

         if (v_bytes < v_10_extent_size_high) THEN
           exit;
         END IF;

         v_count_high   := v_count_high + trunc(v_bytes/v_10_extent_size_high);

         IF (DEBUG_LEVEL2 = TRUE) THEN
           v_started  := '    HIGH ....... size: '||v_10_extent_size_high||' count: '||v_count_high; 
           DBMS_OUTPUT.PUT_LINE (v_started);                
         END IF;

         if (v_count_high >= 10) THEN
           UPDATE table_utilization
              SET max_extent_size = v_10_extent_size_high
            where tablespace_name = v_TABLESPACE_NAME;
           exit;
         END IF;
      END LOOP;			/* End loop from fetch #2 */
      CLOSE PS_CURSOR_2;

      /* ************************************** */
      /* If no rows fetched; then no space if   */
      /*   available for an extent of any size  */
      /* - very possible with autoextend set    */
      /*   for the tablespace                   */
      /* ************************************** */

      if (v_rows_fetched = 0) THEN
         UPDATE table_utilization
              SET max_extent_size = 0
            where tablespace_name = v_TABLESPACE_NAME;
           exit;
      END IF;

      /* ************************************** */
      /* See if we found the extent size that   */
      /*   can be fit 10x in the tablespace     */
      /* ************************************** */

      if (v_count_high >= 10) THEN
         EXIT;
      END IF;

	if (v_10_extent_size_high > 1*1024*1024) THEN
         v_10_extent_size_high := v_10_extent_size_high - 1*1024*1024;		/* decrease by 1 Mbyte */
      else
         v_10_extent_size_high := v_10_extent_size_high - 16*1024;	/* decrease by 16 Kbyte */
         if (v_10_extent_size_high < (16*1024)) THEN
            UPDATE table_utilization
              SET max_extent_size = 0
            where tablespace_name = v_TABLESPACE_NAME;
           exit;
         END IF;
      END IF;

      v_count_high := 0;
      v_rows_fetched := 0;
    END LOOP;			/* End loop from extrapolation */

    IF (DEBUG_LEVEL1 = TRUE) THEN
      v_started  := '      Next HIGH ....... size: '||v_10_extent_size_high; 
      DBMS_OUTPUT.PUT_LINE (v_started);     
    END IF;               
    commit;
  END LOOP;				/* End loop from fetching tablespace data */
  COMMIT;
  CLOSE PS_CURSOR_1;
END;
/

UPDATE table_utilization
SET free_bytes = 0
where free_bytes  is NULL;

UPDATE table_utilization
SET largest_free_piece = 0
where largest_free_piece  is NULL;

set echo on;

/* **************************************************** */
/*  SECTION 1 - Checking for Next Extents that will not */
/*              fit in the current Tablespace           */
/* **************************************************** */

set echo off;

col available_space 	heading Available 		format 999,999,990
col max_lar 			heading 'Largest|Piece (MB)' format 999,999.999
col segname 			heading 'Object Name'  		format a25
col segtype 			heading 'Type' 			format a7
col tablespace_name 	heading 'TableSp.' 			format a26
col available_space 	heading 'Available|(MB)' 		format 9,999.999
col max_nex 			heading 'Next|Extent(MB)' 		format 9,999.999
col max_lar 			heading 'Largest|Piece (MB)' 	format 9,999.999
col max_sum 			heading 'Next 10|Ext. Sum(MB)'  	format 9,999.999
col max_ext 			heading 'Max Ext|Size (MB)'       format 9,999.999

select b.segment_name 									segname,
    substr(b.segment_type,1,7) 								segtype,
    a.tablespace_name 									tablespace_name,
    substr(to_char(b.next_extent/(1024*1024),'999,999.999'),1, 14)			max_nex,
    substr(to_char(a.largest_free_piece/(1024*1024),'999,999.999'),1, 14) 		max_lar,
    '*WARNING*'                                                                   Action 
from 	table_utilization  a,
    	sys.dba_segments   b
where a.tablespace_name 	= b.tablespace_name
and b.next_extent > a.largest_free_piece
order by 2 desc; 


set echo on;

/* **************************************************** */
/*  SECTION 1a - If we were to grow by 10%; find the    */
/*          Tablespace objects that would have problems */
/* **************************************************** */

set echo off;

col required_space 		heading 'Required|(MB)' 	format 999,999.999
col max_free 		heading 'Free Bytes| Sum(MB)' format 999,999.999
col segname 			heading 'Object Name'  		format a25
col segtype 			heading 'Type' 			format a7
col tablespace_name 	heading 'TableSp.' 			format a26
col available_space 	heading 'Available|(MB)' 		format 9,999.999
col max_nex 			heading 'Next|Extent(MB)' 		format 9,999.999
col max_lar 			heading 'Largest|Piece (MB)' 	format 9,999.999
col max_sum 			heading 'Next 10|Ext. Sum(MB)'  	format 9,999.999
col max_ext 			heading 'Max Ext|Size (MB)'       format 9,999.999

select b.segment_name 								segname,
    substr(b.segment_type,1,7) 								segtype,
    a.tablespace_name 								tablespace_name,
    substr(to_char(b.bytes*0.1/(1024*1024),'999,999.999'),1, 14)		required_space,
    substr(to_char(a.free_bytes/(1024*1024),'999,999.999'),1, 14) 		max_free 
from 	table_utilization  a,
    	sys.dba_segments   b
where a.tablespace_name 	= b.tablespace_name
and b.bytes*0.1 > a.free_bytes
order by 2 desc; 

set echo on;

/* **************************************************** */
/*  SECTION 1b - If we were to grow by 2 extents; find  */
/*      the Tablespace objects that would have problems */
/*  Max Ext ..... Not enough space to accomidate the    */
/*          creation of the next 10 extents (could fit  */
/*          some but not all)                           */  
/* **************************************************** */

set echo off;

col segname 			heading 'Object Name'  		format a25
col segtype 			heading 'Type' 			format a7
col tablespace_name 	heading 'TableSp.' 			format a26
col available_space 	heading 'Available|(MB)' 		format 9,999.999
col max_nex 			heading 'Next|Extent(MB)' 		format 9,999.999
col max_lar 			heading 'Largest|Piece (MB)' 	format 9,999.999
col max_sum 			heading 'Next 10|Ext. Sum(MB)'  	format 9,999.999
col max_ext 			heading 'Max Ext|Size (MB)'       format 9,999.999

select b.segment_name 									segname,
    substr(b.segment_type,1,7) 								segtype,
    a.tablespace_name 									tablespace_name,
    substr(to_char(b.next_extent/(1024*1024),'9,999.999'),1, 13)	      		max_nex,
    substr(to_char(a.largest_free_piece/(1024*1024),'9,999.999'),1, 13) 		max_lar, 
    substr(to_char(a.sum_10_largest_free_piece/(1024*1024),'999.999'),1, 11) 	max_sum, 
    substr(to_char(a.max_extent_size/(1024*1024),'999.999'),1, 12) 	 	       max_ext
from 	table_utilization  a,
    	sys.dba_segments   b
where a.tablespace_name 	= b.tablespace_name
and b.next_extent > a.max_extent_size
and b.next_extent > a.largest_free_piece/1
order by 2 desc; 
set echo on;

/* **************************************************** */
/*  SECTION 2 - Count of Tables with PCTINCREASE > 0%   */
/*              fit in the current Tablespace           */
/* **************************************************** */

set echo off;

col owner heading "Owner" format a10
col table_name heading "Table Name " format a30
col pct_increase heading "PctIncrease" format 990.90
col Next_extent heading "Next Extent" format 999,999,990
col extents heading "Extents" format 990

/* select a.owner, a.segment_name, a.pct_increase, b.pct_increase */

select count(*)  "OBJS with PCTINC(SYS) > 0   "
from dba_segments a,
     dba_tables b
where a.owner      = b.owner      and 
      a.owner      = 'SYS'        and
    a.segment_name = b.table_name and 
    b.pct_increase > 0            and 
    b.pct_increase != 100;

select count(*)  "OBJS with PCTINC(SYSTEM) > 0"
from dba_segments a,
     dba_tables b
where a.owner      = b.owner      and 
      a.owner      = 'SYSTEM'    and
    a.segment_name = b.table_name and 
    b.pct_increase > 0            and 
    b.pct_increase != 100;

select count(*)  "OBJS with PCTINC(OTHERS) > 0"
from dba_segments a,
     dba_tables b
where a.owner      = b.owner      and 
      a.owner      <> 'SYS'       and
      a.owner      <> 'SYSTEM'    and
    a.segment_name = b.table_name and 
    b.pct_increase > 0            and 
    b.pct_increase != 100;

select substr(a.owner,1,10)		"OWNER", 
       substr(a.segment_name, 1,25)	"OBJECT", 
       a.pct_increase			"Seg %Inc", 
       b.pct_increase			"Tab %Inc"
from dba_segments a,
     dba_tables b
where a.owner      = b.owner      and 
      a.owner      <> 'SYS'       and
      a.owner      <> 'SYSTEM'    and
    a.segment_name = b.table_name and 
    b.pct_increase > 0            and 
    b.pct_increase != 100;


set echo on;

/* ********************************************************* */
/*  SECTION 2a - Count of PARTITIONS with PCTINCREASE > 0 %  */
/* ********************************************************* */

/* **************** */
/* TABLE PARTITIONS */
/* **************** */

set echo off;

select count(*)  "TABLE PART with PCTINC > 0"
from dba_tab_partitions 
where table_owner      <> 'SYS'       and
      table_owner      <> 'SYSTEM'    and
      pct_increase > 0                and 
      pct_increase != 100;

select substr(table_owner,1,10)		"OWNER", 
       substr(table_name, 1,25)	       "TABLE", 
       substr(partition_name, 1,25)      "PARTITION", 
       pct_increase		  	       "Part %Inc"
from dba_tab_partitions b
where table_owner      <> 'SYS'       and
      table_owner      <> 'SYSTEM'    and
      pct_increase > 0                and 
      pct_increase != 100;

set echo on;

/* **************** */
/* INDEX PARTITIONS */
/* **************** */

set echo off;

select count(*)  "INDEX PART with PCTINC > 0"
from dba_ind_partitions b
where b.index_owner      <> 'SYS'       and
      b.index_owner      <> 'SYSTEM'    and
      b.pct_increase > 0            and 
      b.pct_increase != 100;

select substr(index_owner,1,10)		"OWNER", 
       substr(index_name, 1,25)	       "INDEX", 
       substr(partition_name, 1,25)      "PARTITION", 
       pct_increase		  	       "Part %Inc"
from dba_ind_partitions b
where b.index_owner      <> 'SYS'       and
      b.index_owner      <> 'SYSTEM'    and
      b.pct_increase > 0            and 
      b.pct_increase != 100;

set echo on;

/* ********************************************************* */
/*  SECTION 3 - Checking For Tables with PCTINCREASE > 50 %  */
/* ********************************************************* */

set echo off;

select b.owner,
    b.table_name,
    b.pct_increase,
    a.extents,decode(sign(a.extents-2),
    -1,
    a.next_extent,
    CEIL(a.next_extent*((100+b.pct_increase)/100))) Next_extent  
from dba_segments a,
    dba_tables b
where a.owner      = b.owner      and 
    a.segment_name = b.table_name and 
    b.pct_increase > 50           and 
    b.pct_increase != 100
order by 1 asc, 2 asc;

clear breaks
clear compute
col segment_name format a30
col extents format 990 

set head on
set verify on
set feedback on

col owner 			heading "Owner     " format a10
col blocks 			heading Blk format 999,990
col segment_name 		heading "Segment Name                 " format a30
col extents 			heading Ext format 990
col act_blocks 		heading "A.Blk" format 999,990
col pct 			heading Pctg format 990.90
col segment_type 		heading "SegmentType"format a10


col segment_type 		heading "Type" format a12
col action 			format a12
col owner 			format a10
col extents 			heading "Ext" format 990
col max_extents 		heading "Max" format 9,990
col table_name 		heading "Table Name" format a25
col object_name 		heading "Object Name" format a35
col part_name 		heading "Partition Name" format a27

break on "Owner" on object_name on part_name;
set pagesize 60 linesize 132 verify off
col pct_max 			heading 'Pct of|Max(%)'        format 99.99

set echo on;

/* ********************************************************* */
/*  SECTION 4a - Segments With More Than Twenty Extents...   */
/*     Criteria:                                             */
/*                                                           */
/*      Condition One:                                       */
/*       a.    >200     Extents --> Warning                  */
/*       b.    >2000    Extents --> Recreate                 */
/*      Condition Two:                                       */
/*       a.    (Extents/MaxExtents > 50%) -->                */
/*                Recreate (or warning if the                */
/*       b.       number of max extents <= 50)               */
/*                                                           */
/* NOTE: excessive extents are not in of themselves problems */
/*      since a pointer is used to "join" one extent to      */
/*      another the I/O of the excessive extents is small    */
/*      unless we have an exceptionally large number of      */
/*      extents in which the sum of the "joins" can translate*/
/*      into a performance issue.                            */
/*                                                           */
/*      A truncate will increase in time as the number of    */
/*      extents increase.... (not to mention the impact with */
/*      indexes)                                             */
/*                                                           */
/* ********************************************************* */

set echo off;

select owner,
   segment_name object_name,
   segment_type,
   extents,
   max_extents ,
  (extents/max_extents)*100	  		pct_max,
   decode(sign(extents-2000),1,'**MODIFY1**','*WARNING*') Action
from dba_segments
where extents > 200 and 
   segment_type in ('TABLE','INDEX','CLUSTER')
UNION
select owner,
   segment_name object_name,
   segment_type,
   extents,
   max_extents ,
  (extents/max_extents)*100	  		pct_max,
   decode(sign((extents/max_extents*100)-50),1,'**MODIFY2**','*WARNING*') Action
from dba_segments
where (extents/max_extents*100) > 50 and 
   segment_type in ('TABLE','INDEX','CLUSTER')
order by 6 desc;

set feedback off;
set verify off;

set echo on;

/* ********************************************************* */
/*  SECTION 4a - partitions With More Than Twenty Extents... */
/*     Criteria:                                             */
/*                                                           */
/*      Condition One:                                       */
/*       a.    >200     Extents --> Warning                  */
/*       b.    >2000    Extents --> Recreate                 */
/*      Condition Two:                                       */
/*       a.    (Extents/MaxExtents > 50%) -->                */
/*                Recreate (or warning if the                */
/*       b.       number of max extents <= 50)               */
/* ********************************************************* */

set echo off;

select owner,
   segment_name 			object_name,
   PARTITION_NAME			part_name,
   substr(segment_type,1,10)	"Type",
   extents,
   max_extents ,
  (extents/max_extents)*100	  		pct_max,
   decode(sign(extents-2000),1,'**MODIFY**','*WARNING*') Action
from dba_segments
where extents > 200 and 
   segment_type in ('TABLE PARTITION', 'INDEX PARTITION')
UNION
select owner,
   segment_name 			object_name,
   PARTITION_NAME			part_name,
   substr(segment_type,1,10)	"Type",
   extents,
   max_extents ,
  (extents/max_extents)*100	  		pct_max,
   decode(sign((extents/max_extents*100)-50),1,'**MODIFY**','*WARNING*') Action
from dba_segments
where (extents/max_extents*100) > 50 and 
   segment_type in ('TABLE PARTITION', 'INDEX PARTITION')
order by 7 desc;

set feedback off;
set verify off;

set echo on;

/* ********************************************************* */
/*  SECTION 5 - Tablespace Utilization                       */
/* ********************************************************* */

       /* ************************************************** */
       /*  SECTION 5a - All Tablespace Data                  */
       /* ************************************************** */

set echo off;
set feedback on
set verify on

col tablespace_name heading "TableSpace|Name" format a30
col total_bytes heading "Size|(Kbytes)" format 999,999,990
col free_bytes heading "Free|(KBytes)" format 999,999,990
col largest_bytes heading "Largest Piece|(KBytes)" format 999,999,990
col pct heading "%|Util" format 990.990
col fragments heading "Fragments" format 9990

col dummy 	noprint format a1;

delete from table_utilization
  where tablespace_name = '*** Total ***';

insert into table_utilization
(tablespace_name, total_bytes, free_bytes)
select '*** Total ***',
    SUM(total_bytes),
    SUM(free_bytes)
from table_utilization;
 
select 0 				dummy,
	tablespace_name,
    	total_bytes/1024             total_bytes,
    	free_bytes/1024               free_bytes,
    	(total_bytes-free_bytes)/total_bytes * 100 PCT,
    	largest_free_piece/1024	largest_bytes,
    	fragments
from table_utilization
where fragments is not null
UNION
select 1 				dummy,
	'--------------------------'		tablespace_name,
    	0             total_bytes,
    	0               free_bytes,
      0 PCT,
    	0	largest_bytes,
    	0
from table_utilization
where fragments is null
UNION
select 2 				dummy,
	tablespace_name,
    	total_bytes/1024             total_bytes,
    	free_bytes/1024               free_bytes,
    	(total_bytes-free_bytes)/total_bytes * 100 PCT,
    	0	largest_bytes,
    	0
from table_utilization
where fragments is null
order by 1,2 asc; 

set echo on;

       /* ************************************************** */
       /*  SECTION 5b - Those that need attention NOW!!!     */
       /*             - Those with 90%+ utalization ratio    */
       /* ************************************************** */

set echo off;
set feedback on
set verify on

select tablespace_name,
    total_bytes/1024             								total_bytes,
    free_bytes/1024               							free_bytes,
    (total_bytes-free_bytes)/total_bytes * 100 						PCT,
    largest_free_piece/1024								largest_bytes,
    fragments,
    '*WARNING*'                                                                   Action 
from table_utilization
where (total_bytes-free_bytes)/total_bytes * 100 >= 90
order by 4 desc, 1 asc;

set verify off
set feedback off

set echo on;

/* ********************************************************* */
/*  SECTION 6 - Calculation of Dictionary Cache Hit Ratio    */
/* ********************************************************* */

set echo off;

column count  format 999,990 heading Count
column usage format 99,990 heading Usage  
column gets  format 99,999,990 heading Gets
column getmisses format 99,999,990 heading Misses
column a format 990.99 heading "Hit Ratio"

select substr(parameter,1,20) parameter,
    count,
    usage,
    decode(count-usage,0,'*',' ') Rec,
    gets,
    getmisses,
    ((1-(decode(gets,0,0,getmisses))/decode(gets,0,1,gets))*100) a
from v$rowcache
order by 6 desc;

set echo on;

/* ********************************************************* */
/*  SECTION 7 - Calculating Table/Index Access Ratio         */
/* ********************************************************* */

set echo off;

column avalue format 999,999,999,990 heading "Table Hits"
column bvalue format 999,999,999,990 heading "Index Hits"
column ht format 990.99 heading "Table %"
column it format 990.99 heading "Index %"
col acr heading "Access"

select a.value avalue,b.
    value bvalue,
    (1-b.value/(a.value+b.value))*100 ht,
    (1-a.value/(a.value+b.value))*100 it,
    decode (sign(75 - CEIL( (1-a.value/(a.value+b.value))*100)),1,'*BAD ACCESS RATIO*',0,'*AVG ACCESS RATIO*','*GOOD ACCESS RATIO*') ACR
from v$sysstat a,
    v$sysstat b
where a.name = 'table scan rows gotten'
and b.name = 'table fetch by rowid';

set echo on;

/* ********************************************************* */
/*  SECTION 7a - Small table scans vs. Large Table scans     */
/*   - short scans are okay (actually preferred) but long    */
/*     table scans are usually due to the lack of indexes or */
/*     inefficient queries                                   */
/* ********************************************************* */

set echo off;

column avalue format 999,999,999,990 heading "Short Tbl Scans"
column bvalue format 999,999,999,990 heading "Long Tbl Scans"
column ht format 990.99 heading "Short %"
column it format 990.99 heading "Long %"
col acr heading "Access"

select a.value avalue,
    b.value bvalue,
    (1-b.value/(a.value+b.value))*100 ht,
    (1-a.value/(a.value+b.value))*100 it,
    decode (sign(75 - CEIL( (1-b.value/(a.value+b.value))*100)),1,'*BAD SCAN RATIO*',0,'*AVG SCAN RATIO*','*GOOD SCAN RATIO*') ACR
from v$sysstat a,
    v$sysstat b
where a.name = 'table scans (short tables)'
and b.name = 'table scans (long tables)';

set echo on;

/* ********************************************************* */
/*  SECTION 7b - Overall Table/Index Access Statistics       */
/* ********************************************************* */

set echo off;

column name format a40 heading 'Statistic Name'
column value format 999,999,999,999,999 heading 'Result'
select a.name	"Statistic Name", 
       value
from v$statname a , v$sysstat
where a.statistic# = v$sysstat.statistic#
   and value > 0
   and a.name like 'table%';

set echo on;


/* ********************************************************* */
/*  SECTION 8 - Sort Activity                                */
/* ********************************************************* */

set echo off;

column value format 999,999,990

select substr('Sort Area Size : '||value, 1,45)	"SORT AREA SIZE"
from v$parameter
where name like '%sort_area_size%';

set head on
column avalue format 999,999,990 heading Sorts(Memory)
column bvalue format 999,999,990 heading Sorts(Disks)
column ht format 990.99 heading "Memory%"
column it format 990.99 heading "Disks%"
ttitle off

select a.value avalue,
    b.value bvalue,
    decode((a.value+b.value), 0, 100, (1-b.value/(a.value+b.value))*100) ht,
    decode((a.value+b.value), 0, 0,   (1-a.value/(a.value+b.value))*100) it
from v$sysstat a,
     v$sysstat b
where a.name = 'sorts (memory)'
and b.name = 'sorts (disk)';

set echo on;

/* ********************************************************* */
/*  SECTION 9 - Calculating Buffer Busy Waits                */
/*              This will show wait stats for certain kernel */
/*              instances.  This prompt may show the need for*/
/*              additional rbs, wait lists, db_buffers       */ 
/* ********************************************************* */

set echo off;

column class  heading 'Class Type'  
column count  heading 'Times Waited'  format 99,999,999 
column time   heading 'Total Times'   format 99,999,999  
ttitle on

select class,  count,  time  
from   v$waitstat  
where  count > 0  
order  by class;

set echo off;
/* ********************************************************* */
/*  SECTION 9a - format the columns for output               */
/* ********************************************************* */

COLUMN tablespace_name 	FORMAT A15 	HEADING TABLESPACE
COLUMN segment_type 	FORMAT A6 	HEADING TYPE
COLUMN segment_name 	FORMAT A15 	HEADING OBJECT
COLUMN object_name 		FORMAT A15 	HEADING OBJECT
COLUMN owner 		FORMAT A15
COLUMN free_percent 	FORMAT .999
COLUMN block_gets 		NEW_VALUE b_gets NOPRINT
COLUMN gets 			NEW_VALUE c_gets NOPRINT
COLUMN reads 		NEW_VALUE p_reads NOPRINT
COLUMN ratio 		FORMAT 999.90

set heading off;
SELECT value block_gets FROM v$sysstat WHERE name = ('db block gets') ;
SELECT value gets       FROM v$sysstat WHERE name = ('consistent gets');
SELECT value reads      FROM v$sysstat WHERE name = ('physical reads');

set heading on;
set echo on;
/* ********************************************************* */
/*  SECTION 10 - Buffer Hit Ratios                           */
/* ********************************************************* */
set echo off;

COLUMN block_gets 	NEW_VALUE b_gets 	NOPRINT
COLUMN gets 		NEW_VALUE c_gets 	NOPRINT
COLUMN reads 	NEW_VALUE p_reads 	NOPRINT

SELECT 'Buffer Cache Hit Ratio = '||ROUND(1-(phy.value/(log.value+con.value)),4)*100||'%' "BUFFER CACHE RATIO"
FROM 	v$sysstat phy, 
	v$sysstat log, 
	v$sysstat con
WHERE phy.name = 'physical reads' 
AND log.name='db block gets' 
AND con.name = 'consistent gets';

SELECT 'Warning: Buffer Cache Hit Ratio = '||ROUND(ROUND(1-(phy.value/(log.value+con.value)),3)*100,4)||'%' "BUFFER CACHE RATIO Problem"
FROM v$sysstat phy, v$sysstat log, v$sysstat con
WHERE phy.name = 'physical reads' 
AND log.name='db block gets' 
AND con.name = 'consistent gets'
AND ROUND(1-(phy.value/(log.value+con.value)),3) <= .9;

set echo on;
/* ********************************************************* */
/*  SECTION 11 - Test for redo latch contention              */
/* ********************************************************* */
set echo off;

COL wpct HEADING 'Wait|%' FORMAT 990.99 JUS CEN
COL ipct HEADING 'Imm.|%' FORMAT 990.99 JUS CEN

SELECT '*** Warning: '||name||' contention exists. % misses = '||ROUND(SUM(misses)/(SUM(gets)+1)*100,2)||' and % immediate misses = '|| ROUND(SUM(immediate_misses)/(SUM(immediate_gets)+1)*100,2)||'
    immediate_misses: '||SUM(immediate_misses)||' immediate_gets: '||SUM(immediate_gets)||' misses: '||SUM(misses)||' gets: '||SUM(gets)  					"DATABASE CONTENTION"
FROM v$latch 
WHERE name LIKE 'redo%'
HAVING (SUM(misses)/(SUM(gets)+1) >= .01 OR
       SUM(immediate_misses)/(SUM(immediate_gets)+1) >= .01)
   and SUM(GETS) > 100
GROUP BY name
UNION
SELECT 'Informational: Potential '||name||' Latch Contention; miss ratio of '||ROUND(SUM(misses)/(SUM(gets)+1)*100,2)||'%  <-- too few misses to be an issue'  "DATABASE CONTENTION"
FROM v$latch 
WHERE name LIKE 'redo%'
HAVING (SUM(misses)/(SUM(gets)+1) >= .01 OR
       SUM(immediate_misses)/(SUM(immediate_gets)+1) >= .01)
   and SUM(GETS) < 100
GROUP BY name
UNION
SELECT 'Informational: No '||name||' Latch Contention; miss ratio of '||ROUND(SUM(misses)/(SUM(gets)+1)*100,2)||'%'  "DATABASE CONTENTION"
FROM v$latch 
WHERE name LIKE 'redo%' 
HAVING SUM(misses)/(SUM(gets)+1) < .01 and
       SUM(immediate_misses)/(SUM(immediate_gets)+1) < .01
GROUP BY name;

set echo on;
/* ********************************************************* */
/*  SECTION 12 - Test for low Library Cache Hit Ratio        */
/* ********************************************************* */
set echo off;

SELECT 'Warning: System-wide Library Cache Hit Ratio = '||
     ROUND((SUM(b.pins - b.reloads))/SUM(b.pins)*100,2)||
     '%. Increase SHARED_POOL_SIZE'		"LOW LIBRARY CACHE"
FROM v$librarycache b
HAVING (SUM(b.pins - b.reloads))/SUM(b.pins) < 0.9
UNION
SELECT 'No System-wide Library Cache Hit problems; hit ratio of '||
     ROUND((SUM(b.pins - b.reloads))/SUM(b.pins)*100,2)||
     '%'                          		"LOW LIBRARY CACHE"
FROM v$librarycache b
HAVING (SUM(b.pins - b.reloads))/SUM(b.pins) >= 0.9;

set echo on;
/* ********************************************************* */
/*  SECTION 13 - Test for excessive reloads in library cache */
/* ********************************************************* */
set echo off;

SELECT 'Warning: System-wide Library Cache Reload Ratio = '||
        ROUND(SUM(reloads)/SUM(pins),2)||'%. Increase SHARED_POOL_SIZE'  "LOW LIBRARY PINS"
FROM v$librarycache
HAVING SUM(reloads)/SUM(pins) > .05
union
SELECT 'No System-wide Library Cache Reload Ratio; reload ratio of '||
        ROUND(SUM(reloads)/SUM(pins),2)||'%'  "LOW LIBRARY PINS"
FROM v$librarycache
HAVING SUM(reloads)/SUM(pins) <= .05;

set echo on;
/* ********************************************************* */
/*  SECTION 14 - Test for dictionary cache getmisses         */
/* ********************************************************* */
set echo off;

SELECT 'Warning: System-wide Dictionary Cache Hit Ratio = '||
       ROUND((SUM(gets-getmisses-usage-fixed))/SUM(gets)*100,2)||
       '%. Increase SHARED_POOL_SIZE' 		"DICTIONARY CACHE HIT RATIO"
FROM v$rowcache
HAVING (SUM(gets-getmisses-usage-fixed))/SUM(gets) < .90
union
SELECT 'No System-wide Dictionary Cache Hit Ratio; hit ratio of '||
       ROUND((SUM(gets-getmisses-usage-fixed))/SUM(gets)*100,2)||
       '%'                           		"DICTIONARY CACHE HIT RATIO"
FROM v$rowcache
HAVING (SUM(gets-getmisses-usage-fixed))/SUM(gets) < .90;

set echo on;
/* ********************************************************* */
/*  SECTION 15 - Test condition which may cause fragmentation*/
/* ********************************************************* */
set echo off;

SELECT 'Warning: Default PCT_INCREASE for tablespace '||tablespace_name||
                ' is '||pct_increase||'.' "TABLESPACE PCTINCREASE"
FROM  sys.dba_tablespaces
WHERE  pct_increase != 1 and
       pct_increase != 0
AND 	tablespace_name NOT LIKE 'TEMP%' 
AND 	tablespace_name not in 
                (SELECT tablespace_name 
			from dba_rollback_segs)
ORDER BY  tablespace_name;

SELECT 'Warning: '||segment_type||' '||owner||'.'||segment_name||
                ' has reached '||extents||' extents of '||max_extents||'.' "EXCESSIVE EXTENTS(GT 75%)"
FROM sys.dba_segments  
WHERE   (extents >= .75 * max_extents) 
AND 	(extents <= .95 * max_extents) 
AND	segment_type in ('TABLE','INDEX') 
AND 	tablespace_name not in 
                (SELECT tablespace_name 
			FROM sys.dba_rollback_segs)
ORDER BY extents;

SELECT 'Notice: '||segment_type||' '||owner||'.'||segment_name||
                ' has reached '||extents||' extents of '||max_extents||'.' "EXCESSIVE EXTENTS(GT 25%)"
FROM sys.dba_segments
WHERE (extents >= .25 * max_extents) 
AND 	(extents < .75 * max_extents) 
AND	segment_type in ('TABLE','INDEX') 
AND 	tablespace_name not in 
                (SELECT tablespace_name 
			FROM sys.dba_rollback_segs)
ORDER BY extents;

SELECT  'Warning: '||A.segment_type||' '||A.owner||'.'||A.segment_name||
                ' has PCT_INCREASE set to '||A.pct_increase	"OBJECT PCTINCREASE != 0"
FROM  sys.dba_segments A
WHERE   A.pct_increase != (0) 
AND  	A.segment_type in ('TABLE')
AND 	A.owner not in ('SYSTEM', 'SYS')
union
SELECT  'Warning: '||A.segment_type||' '||A.owner||'.'||A.segment_name||
                ' has PCT_INCREASE set to '||A.pct_increase	"OBJECT PCTINCREASE != 0"
FROM  sys.dba_segments A,
      sys.dba_indexes  B
WHERE   A.pct_increase 	!= (0) 
AND  	A.segment_type in ('INDEX')
AND 	A.owner not in ('SYSTEM', 'SYS')
AND     B.index_name    = A.segment_name
AND     B.index_type 	!= 'IOT - TOP' 
ORDER BY 1;

set echo on;
/* ********************************************************* */
/*  SECTION 16 - Test for invalid objects                    */
/* ********************************************************* */

set echo off;

SELECT 'Notice: '||object_type||' '||owner||'.'||object_name||
                ' is INVALID.'	"INVALID OBJECTS"
FROM sys.dba_objects
WHERE  status = 'INVALID'; 

set echo on;
/* ********************************************************* */
/*  SECTION 17 - Check for invalid user setup                */
/* ********************************************************* */
set echo off;

SELECT 'Error: User '||username||
                ' has its DEFAULT TABLESPACE set to '||
                default_tablespace||'.'		"INVALID DEFAULT TABLESPACE"
FROM sys.dba_users
WHERE (default_tablespace = 'SYSTEM' OR 
                default_tablespace like '%TEMP%' OR 
                default_tablespace like 'TMP%') AND 
                username not in ('SYS', 'SYSTEM');

SELECT 'Error: User '||username||
                ' has its TEMPORARY TABLESPACE set to SYSTEM.' "INVALID TEMP TABLESPACE"
FROM sys.dba_users
WHERE (temporary_tablespace = 'SYSTEM') AND 
                username not in ('SYS', 'SYSTEM');

set echo on;
/* ********************************************************* */
/*  SECTION 18 - Check for user objects in SYSTEM or TEMP    */
/* ********************************************************* */
set echo off;

SELECT 'Error: '||segment_type||' '||owner||'.'||segment_name||
                ' located in '||tablespace_name||'.'		"MISPLACED OBJECTS"
FROM sys.dba_segments
WHERE owner not in ('SYS', 'SYSTEM') AND 
                (tablespace_name = 'SYSTEM' OR tablespace_name like '%TEMP%' 
                OR tablespace_name like '%TMP%') AND segment_type in 
                ('TABLE','INDEX')
ORDER BY owner, tablespace_name, segment_type, segment_name;

set echo on;

/* ********************************************************* */
/*  SECTION 19 - Check for maximium session count            */
/*    problem threshold is set to 85% of maximium allowed    */
/*    to correct: either                                     */
/*       1. increase the PROCESSES parameter and bounce DB   */
/*          can derive SESSIONS= 1.1*PROCESSES+5 (PREFERRED) */
/*       2. increase the SESSIONS parameter and bounce DB    */
/* ********************************************************* */
set echo off;

select A.SESSIONS_CURRENT			"Current",
       A.SESSIONS_HIGHWATER		"Max Thus far",
       substr(B.VALUE,1,12)		"Max Allowed",
       to_char((A.SESSIONS_CURRENT/B.VALUE)*100,'99.999') "Cur(%)",
       to_char((A.SESSIONS_HIGHWATER/B.VALUE)*100,'99.999') "Max(%)",
       decode(sign((B.VALUE*0.8)-A.SESSIONS_HIGHWATER), -1, '*** Potential Problem ***', 'Plenty of Sessions') "Session Status"
from  v$license  	A,
      v$parameter 	B
where B.name='sessions';

set echo on;

/* ********************************************************* */
/*  SECTION 20 - Check the archive mode status               */
/* ********************************************************* */

set echo off;

select substr(a.value,1,10)		"INIT.ORA",
       b.log_mode				"DB ARCH MODE",
       decode(decode(a.value, 'TRUE', 2, 0) + decode(b.log_mode, 'ARCHIVELOG', 1, 0), 
				0, 'ARCHIVING is OFF',
                           1, 'ARCHIVE MODE is ON but INIT.ORA says otherwise',
                           2, 'ARCHIVING not enabled - problem',
                           3, 'ARCHIVING is ON',
                              'UNKNOWN')	"SITUATION"
from v$parameter a,
     v$database b
where a.name = 'log_archive_start';

set echo on;

/* ********************************************************* */
/*  SECTION 21 - Shared pool Statistics                      */
/* ********************************************************* */
set echo off;

set heading off termout off echo off verify off;
set linesize 77;
clear col;
clear breaks;
clear compute;
set pause off;
ttitle off;

col val1 new_val x_sp_size noprint
select Value val1
  from V$PARAMETER
 where Name='shared_pool_size';

col val2 new_val x_sp_used noprint
select SUM(Sharable_Mem+Persistent_Mem+Runtime_Mem) val2
  from V$SQLAREA;

col val3 new_val x_sp_used_shr noprint
col val4 new_val x_sp_used_per noprint
col val5 new_val x_sp_used_run noprint
col val6 new_val x_sp_no_stmts noprint
select SUM(Sharable_Mem) val3,
       SUM(Persistent_Mem) val4,
       SUM(Runtime_Mem) val5,
       COUNT(*) val6
  from V$SQLAREA;

col val7 new_val x_sp_no_obj noprint
select COUNT(*) val7
  from V$DB_OBJECT_CACHE;

col val8 new_val x_sp_avail noprint
select &x_sp_size-&x_sp_used val8
  from DUAL;

col val9 new_val x_sp_no_pins noprint
select COUNT(*) val9
  from V$SESSION A, V$SQLTEXT B
 where A.SQL_Address||A.SQL_Hash_Value = B.Address||B.Hash_Value;

col val10 new_val x_sp_sz_pins noprint
select SUM(Sharable_Mem+Persistent_Mem+Runtime_Mem) val10
  from V$SESSION A,
       V$SQLTEXT B,
       V$SQLAREA C
 where A.SQL_Address||A.SQL_Hash_Value = 
         B.Address||B.Hash_Value 
   and B.Address||B.Hash_Value = C.Address||C.Hash_Value;

/* Flag the Percent Used if it exceeds 80% */

set termout on
ttitle -
  center  'Shared Pool Library Cache Information' skip 2

select  'Size                                         : '
  ||&x_sp_size sp_size,
        'Used (total)                                 : '
  ||&x_sp_used||'   ('||to_char(((100*&x_sp_used)/&x_sp_size),'99.99')||'%)'
  ||decode(sign(((100*&x_sp_used)/&x_sp_size)-80), 1, ' *WARNING*', ' '),
        '     sharable                                : '
  ||&x_sp_used_shr sp_used_shr,
        '     persistent                              : '
  ||&x_sp_used_per sp_used_per,
        '     runtime                                 : '
  ||&x_sp_used_run sp_used_run,
        'Available                                    : '
  ||&x_sp_avail sp_avail,
        'Number of SQL statements                     : '
  ||&x_sp_no_stmts sp_no_stmts,
        'Number of programatic constructs             : '
  ||&x_sp_no_obj sp_no_obj,
        'Pinned statements                            : '
  ||&x_sp_no_pins sp_no_pins,
        'Pinned statements size                       : '
  ||&x_sp_sz_pins sp_sz_pins
  from DUAL;


/* ********************************************************* */
/*  SECTION 999 - Job Housekeeping                           */
/* ********************************************************* */

spool off;
clear col;
undefine CLIENT;
clear breaks;
clear compute;
set head on;
set pause off;

ttitle off;
commit;
