Few More Queries
--Check query Performance
-- Query to generate collect stat statements for all indexes . Was bit tricky because of multi column indexes.It is bit complicated now considering it is a 3 step process ,it can be enhanced to make it to two step process
SEL
TOP 5
QueryId
,cast(QUERYTEXT as
varchar(5000))
,ElapsedTime
,MaxAMPCPUTime
,AMPCPUTime
, MaxAmpCpuTime*NumOfActiveAmps
,(MaxAmpCpuTime- (1.00*AmpCpuTime)/NumOfActiveAmps)
,(1-(1.00* AmpCpuTime/(MaxAmpCpuTime*NumOfActiveAmps)))*100
,(MaxAmpCpuTime*NumOfActiveAmps)/(AmpCpuTime)
FROM DBC.QRYLOG
WHERE MAXAMPCPUTIME>100
;
-- Query to find stats collected about a month ago .
Note : Original query picked up from Teradata Forum (As far as memory serves Original Author :Dieter Noeth ) .I have modified it a bit for my convenience .
Stats modified query
LOCKING ROW FOR ACCESS
SEL DISTINCT 'COLLECT STATS ON
' || DATABASENAME || '.' || TABLENAME||
' ;'
-- || 'COLUMN (' || COLUMNNAME ||');'
FROM
(SELECT
C.DATABASENAMEI AS DATABASENAME,
B.TVMNAMEI AS TABLENAME,
A.FIELDNAME AS COLUMNNAME,
CAST((CASE WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'D5'XB
THEN '2005-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'D6'XB
THEN '2006-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'D7'XB
THEN '2007-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'D8'XB
THEN '2008-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'D9'XB
THEN '2009-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'DA'XB
THEN '2010-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'DB'XB
THEN '2011-'
WHEN SUBSTR(FIELDSTATISTICS,1,1) = 'DC'XB
THEN '2012-'
ELSE NULL
END)||
(CASE WHEN SUBSTR(FIELDSTATISTICS,3,1) = '01'XB
THEN '01-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '02'XB
THEN '02-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '03'XB
THEN '03-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '04'XB
THEN '04-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '05'XB
THEN '05-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '06'XB
THEN '06-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '07'XB
THEN '07-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '08'XB
THEN '08-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '09'XB
THEN '09-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '0A'XB
THEN '10-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '0B'XB
THEN '11-'
WHEN SUBSTR(FIELDSTATISTICS,3,1) = '0C'XB
THEN '12-'
ELSE 'XX-'
END)||
(CASE WHEN SUBSTR(FIELDSTATISTICS,4,1) = '01'XB
THEN '01'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '02'XB
THEN '02'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '03'XB
THEN '03'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '04'XB
THEN '04'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '05'XB
THEN '05'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '06'XB
THEN '06'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '07'XB
THEN '07'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '08'XB
THEN '08'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '09'XB
THEN '09'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0A'XB
THEN '10'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0B'XB
THEN '11'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0C'XB
THEN '12'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0D'XB
THEN '13'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0E'XB
THEN '14'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '0F'XB
THEN '15'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '10'XB
THEN '16'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '11'XB
THEN '17'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '12'XB
THEN '18'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '13'XB
THEN '19'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '14'XB
THEN '20'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '15'XB
THEN '21'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '16'XB
THEN '22'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '17'XB
THEN '23'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '18'XB
THEN '24'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '19'XB
THEN '25'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1A'XB
THEN '26'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1B'XB
THEN '27'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1C'XB
THEN '28'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1D'XB
THEN '29'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1E'XB
THEN '30'
WHEN SUBSTR(FIELDSTATISTICS,4,1) = '1F'XB
THEN '31'
ELSE 'XX'
END)AS DATE ) AS COLLECTIONDATE,
CAST(SUBSTR(CAST(A.LASTALTERTIMESTAMP
AS CHAR(32)),1,10) AS DATE) AS LASTALTER,
DATE - COLLECTIONDATE AS FROMCURRENT,
LASTALTER - COLLECTIONDATE AS FROMALTER
FROM
DBC.TVFIELDS A
LEFT OUTER JOIN
DBC.TVM B
ON A.TABLEID = B.TVMID
LEFT OUTER JOIN
DBC.DBASE C
ON B.DATABASEID = C.DATABASEID
WHERE B.TABLEKIND = 'T'
AND A.FIELDSTATISTICS
IS NOT
NULL
AND DATABASENAME IN
('DW_COL_T','DW_AB_T','DW_STG_T','DW_T')
AND FROMCURRENT>35
)x
ORDER BY 1
;
DROP TABLE WORK_SPACE.INDICES_MAXPOSITION;
CREATE MULTISET TABLE WORK_SPACE.INDICES_MAXPOSITION
AS
(
SELECT DATABASENAME,TABLENAME,INDEXNUMBER,MAX(COLUMNPOSITION) AS MAXPOSITION
FROM DBC.INDICES
WHERE DATABASENAME LIKE '%DW_%'
GROUP BY 1,2,3
) WITH DATA
;
SELECT 'EXECUTE JSHAH.GENERATE_INDEX_STAT ( ' || '27'XC || TRIM(DATABASENAME ) ||'27'XC || ',' || '27'XC || TRIM(TABLENAME ) ||'27'XC || ');'
FROM DBC.TABLES
WHERE
DATABASENAME LIKE 'DW_D%'
AND TABLEKIND='T'
ORDER BY DATABASENAME,TABLENAME
;
HELP VIEW DBC.INDICES A
REPLACE MACRO JSHAH.GENERATE_INDEX_STAT (DATABASENAME VARCHAR(100) ,TABLENAME VARCHAR(100) )
AS
(
SEL FP (TITLE '')
FROM
(SEL A.INDEXNUMBER,
' COLLECT STATS ON ' || TRIM(A.DATABASENAME) ||TRIM( '.' )|| TRIM(A.TABLENAME) || ' INDEX ( ' || TRIM(COLUMNNAME) FP
FROM DBC.INDICES A
JOIN WORK_SPACE.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION=1
UNION ALL
SELECT A.INDEXNUMBER,' ,' || TRIM(COLUMNNAME) SP
FROM DBC.INDICES A
JOIN WORK_SPACE.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION <MAXPOSITION AND COLUMNPOSITION >1
UNION ALL
SELECT A.INDEXNUMBER, '); ' LP
FROM DBC.INDICES A
JOIN WORK_SPACE.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION =MAXPOSITION AND COLUMNPOSITION =1
UNION ALL
SELECT A.INDEXNUMBER, ', ' || COLUMNNAME || ');' LP1
FROM DBC.INDICES A
JOIN WORK_SPACE.INDICES_MAXPOSITION B
ON A.DATABASENAME=B.DATABASENAME
AND B.TABLENAME=A.TABLENAME
AND A.INDEXNUMBER=B.INDEXNUMBER
WHERE
A.DATABASENAME=:DATABASENAME
AND A.TABLENAME =:TABLENAME
AND COLUMNPOSITION =MAXPOSITION AND MAXPOSITION >1
ORDER BY 1
)X
ORDER BY INDEXNUMBER,FP
;
)
;
Comments
Post a Comment