Help for stats!

Database
Enthusiast

Help for stats!

Hi Dieter!

Need your help desperately! I need to gather data for foll:
1. table siza and skew - Please let me know if the skew factor calculation is corrct.
select databasename,tablename,
cast((100 - (AVG(a.CurrentPerm)/MAX(a.CurrentPerm)*100)) as integer) AS SkewFactor
,SUM(CURRENTPERM) as TableSize
From dbc.tablesize a where databasename='databasename'-- and tablename ='TableName'
group by 1,2 order by 3,4 desc;
2. Tables without a PI (defaulting to the first coln).
3. Tables with stale stats , tables with no stats, last access dt and the daily avg access cnt.

Thanks!!!

5 REPLIES
Junior Contributor

Re: Help for stats!

These are some queries i use:

1.
This is the calculation used by Teradata administrator, i use a slightly different one to look for skewed tables:

/*** Skewed tables greater than x (1) GB, modify values according to your needs ***/
SELECT
DatabaseName
,TableName
,SUM(CurrentPerm) / 1024**3 AS GB
,(MAX(CurrentPerm) / NULLIF(AVG(CurrentPerm),0)) AS Skew_Factor
-- ,(100 - ( AVG(CurrentPerm) / NULLIF(MAX(CurrentPerm),0) * 100)) AS Skew_Factor --WinDDI
,(HASHAMP() + 1) * MAX(CurrentPerm) / 1024**3 AS GB_Skewed -- actual size needed in database due to skew
,'SHOW TABLE '|| TRIM(DatabaseName) || '.' || TRIM(TableName) || ';' AS Show_Table
FROM dbc.TableSize
GROUP BY 1,2
HAVING GB_Skewed > 1
AND Skew_Factor > 1.1 -- 10% more data on the max AMP vs. avg AMP
ORDER BY Skew_Factor DESC
;

2.
This returns the tables with a NUPI on the first column, but you don't know if it's because there was no PI definition.

-- View based version
SELECT DatabaseName, TableName
FROM dbc.indicesV AS i
WHERE IndexNumber = 1
AND UniqueFlag = 'N'
GROUP BY 1,2
HAVING COUNT(*) = 1
AND MIN(ColumnName)
= (SELECT ColumnName FROM dbc.ColumnsV AS c
WHERE i.DatabaseName = c.DatabaseName
AND i.TableName = c.TableName
AND ColumnId = 1025)
;

3.
/*** Tables without statistics on PI ***/
/*** Modified version of dbc.IndexStats view, column IndexStatistics changed ***/

REPLACE VIEW AllIndexStats AS
SELECT
d.DatabaseNameI AS DatabaseName
,t.TVMNameI AS TableName
,i.IndexNumber
,i.Name AS IndexName
,i.IndexType
,i.UniqueFlag
,i.FieldPosition AS ColumnPosition
,f.FieldName AS ColumnName
,f.FieldType AS ColumnType
,f.MaxLength AS ColumnLength
,f.FieldFormat AS ColumnFormat
,f.TotalDigits AS DecimalTotalDigits
,f.ImpliedPoint AS DecimalFractionalDigits
,COALESCE(f.FieldStatistics, i.IndexStatistics) AS IndexStatistics
FROM DBC.TVM AS t
JOIN DBC.Dbase AS d
ON t.DatabaseId = d.DatabaseId
JOIN DBC.TVFields AS f
ON t.TVMid = f.tableid
JOIN DBC.Indexes AS i
ON f.tableid = i.tableid
AND f.fieldid = i.fieldid
WHERE
i.indextype <> 'M'
;

/*** Stats data is always stored within the row with ColumnPosition 1 ***/
SELECT
DatabaseName
,TableName
FROM AllIndexStats
WHERE
ColumnPosition = 1
AND
IndexNumber = 1
AND
IndexStatistics IS NULL
AND
/*** add any databases to exclude ***/
DatabaseName NOT IN
('dbc','dbcmanager','systemfe')
;

"last access dt and daily avg access cnt"
You need to query the DBQL or maintain/query a history table or the LastAccessTimestamp/AccessCount to get that info.

Dieter

Enthusiast

Re: Help for stats!

Hi Dieter!

I used the query you mentioned except that I had to change the dbc.columnsv to dbc.columns etc. It did return me some table,s but if I do a show table on these I can see the PI in the DDL. I was expecting no PI in the DDL.
Can you please lte me know why I see the PI?

Thanks!!
Enthusiast

Re: Help for stats!

All tables have a PI. If none is specified, it uses any unique columns (as defined by a unique constraint). If none are specified, it defaults to the first column. But that column IS the PI so a show table will show you that column in the PI clause.
Enthusiast

Re: Help for stats!

So how do we differentiate between the tables where the PI was defined as the first column and the the tables where the PI defaulted to teh first column bcuz no PI was defined?
Enthusiast

Re: Help for stats!

Hi Dieter,

Whats the difference in Skew Factors :

,(MAX(CurrentPerm) / NULLIF(AVG(CurrentPerm),0)) AS Skew_Factor

V/S

,(100 - ( AVG(CurrentPerm) / NULLIF(MAX(CurrentPerm),0) * 100)) AS Skew_Factor --WinDDI

I have considered the first query you provided for looking for skewed tables.

Thanks in advance.

Regards,

Ansh