Minimun | Average | Maximum
---------+---------+---------
42.591 | 43.241 | 49.801
You can get the output above from the query below:
SELECT
TO_CHAR ( min_disk_usage , 'FM999,999,999,990.000' ),
TO_CHAR ( avg_disk_usage , 'FM999,999,999,990.000' ),
TO_CHAR ( max_disk_usage , 'FM999,999,999,990.000' )
FROM
(
SELECT
100 * MIN ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2)) ) AS min_disk_usage,
100 * AVG ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2)) ) AS avg_disk_usage,
100 * MAX ( sds_used::numeric(38,10) / (_v_spudevicestate.sds_size::bigint * (1024^2)) ) AS max_disk_usage
FROM _v_dslice
left outer join _v_spudevicestate on
( _v_dslice.ds_prihwid = _v_spudevicestate.sds_hwid AND
_v_dslice.ds_priptid = _v_spudevicestate.sds_device )
)
AS the_subselect;
When, you have this info, how do you find out which table having this Max SPU disk usage. Below you can determine the table name for the Max data slice.
with
GetMaxDSlice (DS_ID, DS_PCT, ROW_NUM) as
(select ds.ds_id as DS_ID,
(((sp.EXTENTS_USED * 3)/(sp.EXTENT_COUNT * 3)::float)*100)::numeric(10,2) as DS_PCT,
row_number () over (order by (((sp.EXTENTS_USED * 3)/(sp.EXTENT_COUNT * 3)::float)*100)::numeric(10,2) desc ) as Row_Num
FROM _v_dslice ds LEFT OUTER JOIN _v_spudevicemap prm on (ds.ds_prihwid = prm.sdm_spuhwid AND ds.ds_priptid = prm.sdm_spudevice AND prm.sdm_role = 0) LEFT OUTER JOIN _v_spudevicemap mir on (ds.ds_prihwid = mir.sdm_spuhwid AND ds.ds_priptid = mir.sdm_spudevice AND mir.sdm_role = 1) LEFT OUTER JOIN _v_hwcomp hwp on (prm.sdm_sdhwid = hwp.hw_hwid) LEFT OUTER JOIN _v_hwcomp hwm on (mir.sdm_sdhwid = hwm.hw_hwid) LEFT OUTER JOIN _vt_disk_partition sp on (ds.ds_prihwid = sp.HWID AND ds.ds_priptid = sp.DEVICE AND sp.PART_NAME='PART_A')
),
GetTableId (TBL_ID, DS_ID, Row_Num) as
(select
tblid as TBL_ID,
dsid as DS_ID,
row_number () over (order by used_bytes desc) as row_num
from _vt_spu_table_info
),
GetTableName (Table_Name, Obj_Id) as
(select
objname as Table_Name, objid as Obj_Id
from _v_obj_relation_xdb
)
select GetTableName.Table_Name AS TableNameWithMaxUsageInDS,
GetMaxDSlice.ds_id AS DSWithMaxUsagePCT,
ds_pct AS PCTMaxForDS
from GetMaxDSlice, GetTableId, GetTableName
where GetMaxDSlice.Row_Num=GetTableId.Row_Num
and GetTableId.TBL_ID=GetTableName.obj_id
and GetMaxDSlice.Row_Num=1;
TABLENAMEWITHMAXUSAGEINDS | DSWITHMAXUSAGEPCT | PCTMAXFORDS
------------------------------------+-------------------+-------------
PREPAID_MSG_SERVICE_USAGE_BKP_0730 | 48 | 49.80
