Finding table with Max SPU disk

Often time, when you issue a nz_storage_stats command or from nzadmin GUI under NPS, you noticed the following info

 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