Few More Queries

--Check query Performance


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
;


-- 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

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

Popular posts from this blog

The Secret behind A B I N I T I O.

Background on Abinitio : Abinitio is considered one of the major ETL players  in line with informatica ,datastage ,SSIS ( if it can be considered major ETL player ;-) ) .In fact, in most respects it is considered better than all of the above . It has user interface and debugging simpler than Informatica  ( one of the positive points about informatica ) ,It has parallelism which data stage may still take years to reach . Superiority : (HearSay ) It has the ability to convert a 6 people ,6 month ETL project to 2 month 4 people ETL project . (Sounds hyperbole ,But yet it has been established in many cases that it takes half the development time compared to INFA with much better quality of code ) . Other statistics suggest that if you call Abinitio ,there is 75% chance you will be talking to a PhD holder.In other words it is considered to be ingenously simple. Secrecy : In spite of all this there is very little you would know about it (If at all ) . Reason : It is so because Abi
Read more

Finding Skewed Tables in Teradata

       Skewed Tables in Teradata                                                                  Teradata distributes its data for a given table based on its Primary Index and Primary Index alone .If this Primary Index is not selected appropriately it can cause performance bottle necks. Following are general parameters we should consider while creating Primary Indexes       1:Access Path       2:Volatility       3: Data Distribution        Volatility is usually not an issue in a well designed database .That is to say we do not expect update clauses updating the primary index itself. This usually leaves us with data distribution and Access Path.       Access Path implies particular column ( set of columns ) are always used in join conditions .Advantage of using these columns as PI is that it will avoid redistribution of data during join .( One of the most expensive operation for teradata ) and therefore can reduce usage of Spool files.        Data distribution implies
Read more

Macro to generate Mload Script

DROP MACRO MLOAD_EXPORT REPLACE MACRO MLOAD_EXPORT ( DB VARCHAR(100), TB VARCHAR(100), PATH VARCHAR(100), FS VARCHAR(100) ) AS ( SEL 1,CAST(1 AS BIGINT), CAST('.DATEFORM ANSIDATE; .LOGON server/username,password; .LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1; DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1; DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1; DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1; .ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ; .BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || ' WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1 ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1 ERRLIMIT          0 CHECKPOINT     100000 TENACITY 10000 SESSIONS        10 SLEEP 6 ; .LAYOUT InputFileLayout; ' AS VARCHAR(6000)) FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=DATE UNION SEL 2,CAST(COLUMNID AS BIGINT), CAST('.FI
Read more