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

How to find the performance logs of a query ? How to identify if query is bad

25) How to find the performance logs of a query ? How to identify if query is bad ?

Answer>

SELECT

USERNAME,

QUERYBAND,

CLIENTID,

STARTTIME,

SESSIONID,

ERRORCODE,ERRORTEXT,

NUMRESULTROWS,

STATEMENTTYPE,

((FIRSTRESPTIME – STARTTIME) HOUR(4) TO SECOND) (NAMED ELAPSEDTIME), — ELAPSEDTIME

EXTRACT(SECOND FROM ELAPSEDTIME)+(60*EXTRACT(MINUTE FROM ELAPSEDTIME)) + (3600*EXTRACT(HOUR FROM ELAPSEDTIME)) AS RUNTIMESECONDS,– RUNTIMESECONDS

AMPCPUTIME (FORMAT ‘ZZ,ZZZ,ZZ9.999′) AS TOTALCPUTIME, –TOTALCPUTIME

TOTALIOCOUNT,

SPOOLUSAGE,

CASE WHEN TOTALCPUTIME < 1 OR (TOTALCPUTIME/(HASHAMP()+1)) =0 THEN 0 ELSE MAXAMPCPUTIME/NULLIFZERO((TOTALCPUTIME/(HASHAMP()+1))) END (DEC(8,2)) AS CPUSKEW,–CPUSKEW

CASE WHEN TOTALCPUTIME < 1 OR (TOTALIOCOUNT/(HASHAMP()+1)) =0 THEN 0 ELSE MAXAMPIO/NULLIFZERO((TOTALIOCOUNT/(HASHAMP()+1))) END (DEC(8,2)) AS IOSKEW,–IOSKEW

CASE WHEN TOTALCPUTIME < 1 OR TOTALIOCOUNT = 0 THEN 0 ELSE (TOTALCPUTIME *1000)/NULLIFZERO(TOTALIOCOUNT) END AS PJI,–PJI

CASE WHEN TOTALCPUTIME < 1 THEN 0 ELSE TOTALIOCOUNT/NULLIFZERO((TOTALCPUTIME *1000)) END AS UII,–UII

MAXAMPCPUTIME * (HASHAMP()+1) AS CPUIMPACT,–CPUIMPACT

MAXAMPIO * (HASHAMP()+1) AS IOIMPACT, –IOIMPACT,

QUERYTEXT

—FROM dbc.DBQLOGTBL TBL1 ************* Uncomment it if you want same day log’s ***

–FROM PDCRINFO.DBQLOGTBL_HST TBL1 ********* Uncomment it if you want histrory log’s***

WHERE

–logdate between date -5 and date

and username=’XXXXXXX’

Add a Comment

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