Saturday, May 4, 2013

High water mark and suspicious large indexes.

---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
--
--   NAAM:     show_hwm_simple_and_index.sql
--   DOEL:     HighWaterMark info.
--   TYPERING: select
--   PARAMETERS: -
--   BENODIGDE PRIVILEGES select_catalog_role
--   ANDERE BEPERKINGEN: -
--   OPMERKINGEN: -
--
--   WIJZIGINGSHISTORIE:
--  
--   Versie     Datum       Auteur           Omschrijving
--   ---------  ----------  ---------------  ------------------------------------
--   1.0        25-03-2002  Dik              eerste oplevering
--   2.0        2013             Dik              added index information
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
col table_name format a30
col owner format a12
col percentage_waisted format 999.99
col hwm_MB format 9999999.99
col rows_length_MB format 9999999.99
set verify off
undefine percentage_waisted_allowed
undefine min_number_blocks_to_check_on
set lines 150
prompt '***********************************************************************************'
prompt this is a simple script for looking at suspicious hwm
prompt on ixora there is a sparse_tables_8.sql which takes also initrans etc. into account
prompt BASED ON LAST STATISTICS !!
prompt Dik Pater
prompt '***********************************************************************************'
select d.owner
,      d.table_name
-- ,      d.blocks
-- ,      d.empty_blocks
,      d.blocks*p.value/1024/1024 hwm_MB
,      d.num_rows*avg_row_len/1024/1024 rows_length_MB
,      decode(
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
            ),0,100,
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
                )
) percentage_waisted
,     decode(to_char(num_rows),'0','!! '||num_rows||' !!!',to_char(num_rows)) records
, sel_index.*
from dba_tables  d
,    v$parameter p
, (  SELECT i.owner, i.index_name, table_owner, table_name, s1.bytes index_omvang, s2.bytes table_omvang , s2.bytes - s1.bytes table_minus_index
FROM dba_indexes i
, dba_segments s1
, dba_segments s2
WHERE i.table_owner= '&OWNER'
AND i.index_type= 'NORMAL'
and  s1.owner = i.owner
and  s1.segment_name = i.index_name
and  s2.owner = i.table_owner
AND  s2.segment_name = i.table_name
AND  s1.segment_type <> 'TABLE PARTITION'
AND  s2.segment_type <> 'TABLE PARTITION' ) sel_index
where p.name = 'db_block_size'
and     decode(
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
            ),0,100,
      100-100/(
     (d.blocks*p.value)/
decode((d.num_rows*avg_row_len)
       ,0
       ,d.blocks*p.value
       ,(d.num_rows*avg_row_len)
      )
                )
) > &percentage_waisted_allowed
and  d.blocks > &min_number_blocks_to_check_on
AND  d.BLOCKS > 1    -- no sense in checking empty tables with no blocks allocated
AND sel_index.table_owner = d.owner
and sel_index.table_name = d.table_name
order by d.blocks*p.value/1024/1024 desc
/

No comments: