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
/
