PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

How to list down the unwanted stats collected on Teradata environment ?

Q 36 )  How to list down the unwanted stats collected on Teradata environment ?
Answer >

–drop table VTT_DBQLObjTbl_hst;
create VOLATILE TABLE VTT_DBQLObjTbl_hst
as(
sel
trim(obj.ObjectDatabaseName) as ObjectDatabaseName
, trim(obj.ObjectTableName) as ObjectTableName
,trim(Obj.ObjectColumnName) as ObjectColumnName
,obj.Logdate
,Obj.ProcID
,Obj.QueryID
from
PDCRINFO.DBQLOGTbl_hst DBlog
inner join
PDCRINFO.DBQLOBJTbl_hst obj
on
DBlog.Logdate=obj.Logdate
and DBlog.ProcID=obj.ProcID
and DBlog.QueryID=obj.QueryID
and DBlog.StatementType <> ‘Collect Statistics’ /* to avoid the object entry from Collect stats stmts*/
where
Obj.ObjectType=’Col’ /* column use*/
and obj.Logdate between ‘2013-09-20’ and ‘2014-03-19’/* 6 months of data is taken for checking*/
and Objectdatabasename like ‘%EDW1%’ /* Restricting only Critical DB*/
and (Objectdatabasename,Objecttablename) IN (
sel databasename, tablename from dbc.tables where
databasename in
(<<<*********** LIST OF DATABASENAME ***********>>>>>)
and tableKind=’T’
group by 1,2 )
) with data
primary index (ObjectDatabaseName,ObjectTableName,ObjectColumnName)
on commit preserve rows;
–drop table VTT_StatsView;
create VOLATILE TABLE VTT_StatsView
as(
sel
TRIM(SV. DatabaseName) AS DatabaseName,
TRIM(SV. TableName) AS TableName,
SUBSTRING(SV. ColumnName FROM 2 FOR (Length(SV. ColumnName)-2)) as ColumnName,
Sv.StatsType,
sv.CollectStatement,
Sv.HelpStatement,
Sv.CollectDate
from
PDCRINFO.StatsView SV
where
Sv.HelpStatement IS NOT NULL
and SV.StatsType=’Col’ /* for single cloumn Stats no index/partioning column as unused index are handled separately, Mcol needs to be taken care based on hight CPU usage*/
and SV.columnCount=1
and SV.databasename like ‘%EDW1%’ /* restricting to Critical DB*/
and (SV.databasename,SV.tablename) IN (
sel databasename, tablename from dbc.tables where
databasename in
(<<<*********** LIST OF DATABASENAME ***********>>>>>)
and tableKind=’T’
group by 1,2 )
) with data
primary index (DatabaseName,TableName,ColumnName)
on commit preserve rows;
/*******************************************************JUST LISITING unwanted STATS for Critical DB on sigle column at table level**********************************************/
–drop table Unwanted_STATS;
CREATE VOLATILE TABLE Unwanted_STATS
as (
sel
SV. DatabaseName,
SV. TableName,
SV. ColumnName,
Sv.StatsType,
sv.CollectStatement,
Sv.HelpStatement,
Sv.CollectDate
from
VTT_StatsView SV

where NOT EXISTS
( SEL 1
from
VTT_DBQLObjTbl_hst Obj
where
SV.databasename=obj.ObjectDatabaseName
and
SV.TableName = obj.ObjectTableName
and
SV. ColumnName = Obj.ObjectColumnName
)

group by 1,2,3,4,5,6,7
–order by 1,2
) with data
primary index (DatabaseName,TableName,ColumnName)
on commit preserve rows;

sel
SV. DatabaseName,
SV. TableName,
SV. ColumnName,
Sv.StatsType,
sv.CollectStatement,
Sv.HelpStatement,
Sv.CollectDate
from Unwanted_STATS SV
order by 1,2,3; /* report */;
/*
ins into WORK_DB.SRT_DBQLObjTbl_hst
sel * from VTT_DBQLObjTbl_hst;

ins into WORK_DB.SRT_StatsView
sel * from VTT_StatsView;

ins into WORK_DB.SRT_Unwanted_STATS
sel * from Unwanted_STATS;

*/

Add a Comment

Your email address will not be published. Required fields are marked *