/* ************************************************ */
/*                                                  */
/* format the report:                               */
/*        Extent and Tablespace Report              */
/*                                                  */
/* name:    @c:\oracle\sql\ext_stat.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (BSG)                     */
/*                                                  */
/* ************************************************ */

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,4) instance
      from v$database;

clear breaks
set termout on

set pagesize 60 linesize 80 verify off
set space 2
ttitle left 'Date: ' format a18 dba_date -
       center 'Extent/Tablespace Report - ' format a4 instance_name -
       right 'Page: ' format 999 sql.pno skip 2

spool ext_stat.rslt

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

Prompt *****************************************************************************
Prompt
Prompt =========> EXT_STAT.sql .... space and extent management report 
Prompt
Prompt *****************************************************************************
Prompt *****************************************************************************
Prompt TEST #1
Prompt
Prompt Checking for Next Extents that will not fit in the current Tablespace
Prompt
Prompt *****************************************************************************
Prompt

col available_space heading 'Avail(Mb)' format 999.990
col tablespace_name heading TableSp. format a12
col segname heading 'Name'  format a20
col segtype heading 'Type' format a10
col max_nex heading 'Next(Mb)' format 999.990

select b.segment_name 			segname,
    b.segment_type 			segtype,
    a.tablespace_name 			tablespace_name,
    max(a.bytes)/(1024*1024) 		available_space, 
    max(b.next_extent)/(1024*1024)  	max_nex
from sys.dba_free_space a,
    sys.dba_segments b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name,
    b.segment_name,
    b.segment_type
having max(b.next_extent) > max(a.bytes)
order by 2 desc;

Prompt
Prompt *****************************************************************************
Prompt TEST #2
Prompt
Prompt a. Count of Tables with PCTINCREASE > 0 %
Prompt b. Checking For Tables with PCTINCREASE > 50 %
Prompt *****************************************************************************             
Prompt

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

Prompt
Prompt *****************************************************************************
Prompt
Prompt ====> a. Count of Tables with PCTINCREASE > 0 %
Prompt
Prompt *****************************************************************************             
Prompt

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

Prompt
Prompt *****************************************************************************
Prompt
Prompt ====> b. Checking For Tables with PCTINCREASE > 50 %
Prompt
Prompt *****************************************************************************             
Prompt

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 a10
col owner format a10
col extents heading "Ext" format 990
col max_extents heading "Max" format 990
col table_name heading "Table Name" format a25
col object_name heading "Object Name" format a27

Prompt
Prompt *****************************************************************************
Prompt TEST #3
Prompt
Prompt Segments With More Than Twenty Extents...
Prompt Criteria:
Prompt
Prompt Condition One:
Prompt a.    20 - 50  Extents --> Warning
Prompt b.         51+ Extents --> Recreate
Prompt Condition Two:
Prompt a.    (Extents/MaxExtents > 50%) --> Recreate (or warning if the
Prompt b.                                   number of max extents <= 50)
Prompt 
Prompt *****************************************************************************
Prompt

select owner,
   segment_name object_name,
   segment_type,
   extents,
   max_extents ,
   decode(sign(extents-50),1,'*RECREATE*','*WARNING*') Action
from dba_segments
where extents > 20 and 
   segment_type in ('TABLE','INDEX','CLUSTER')
UNION
select owner,
   segment_name object_name,
   segment_type,
   extents,
   max_extents ,
   decode(sign(extents-50),1,'*RECREATE*','*WARNING*') Action
from dba_segments
where (extents/max_extents*100) > 50 and 
   segment_type in ('TABLE','INDEX','CLUSTER')
order by 4 desc;

set feedback off;
set verify off;

Prompt
Prompt *****************************************************************************
Prompt TEST #4
Prompt 
Prompt Tablespace Utilization
Prompt
Prompt *****************************************************************************
Prompt

create table table_utilization
    (tablespace_name      varchar2(50),
     total_bytes          number,
     free_bytes           number,
     fragments            number);

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);

commit;

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 pct heading "%|Util" format 990.990
col fragments heading "Fragments" format 9990

select tablespace_name,
    total_bytes/1024             total_bytes,
    free_bytes/1024               free_bytes,
    (total_bytes-free_bytes)/total_bytes * 100 PCT,
    fragments
from table_utilization
order by 1 asc;

set verify off
set feedback off

drop table table_utilization;

Prompt
Prompt *****************************************************************************
Prompt TEST #5
Prompt
prompt Calculation of Dictionary Cache Hit Ratio
Prompt
Prompt *****************************************************************************
Prompt

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;

Prompt
Prompt *****************************************************************************
Prompt TEST #6
prompt
prompt Calculating Table/Index Access Ratio 
Prompt *****************************************************************************
Prompt

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';

prompt
set head off

Prompt
Prompt *****************************************************************************
Prompt TEST #7
Prompt
prompt Sort Activity
Prompt *****************************************************************************
Prompt

column value format 999,999,990

select 'Sort Area Size : '||value
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 "Disk%"
ttitle off

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
from v$sysstat a,
     v$sysstat b
where a.name = 'sorts (memory)' and
      b.name = 'sorts (disk)' ;

Prompt
Prompt *****************************************************************************
Prompt TEST #8
prompt
prompt Calculating Buffer Busy Waits
prompt 
Prompt *****************************************************************************
Prompt

col count format 999,999,990
ttitle on

select class,
    count
from v$waitstat
where class in ('free list',   'system undo header',
                'system undo block','undo header','undo block');


Prompt
Prompt *****************************************************************************
prompt
prompt =========>   END OF EXTENT REPORT  
prompt 
Prompt *****************************************************************************

spool off;
clear col;

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

ttitle off;
commit;
