basicaly i am looking for CPU consumption by active query within session , i am using following query for the same
,((cast (current_timestamp as varchar(22) )(Timestamp (2))) - (CAST(ReqStartTime AS TIMESTAMP(2)) ) HOUR(4) TO SECOND(2) ) ELAPSED_TIME
,cast(Reqstarttime as timestamp(2)) as ReqStartTime
,AMPCPUSec (DECIMAL(18,2)) as AMPCPUSec
,AMPIO (DECIMAL(18,2)) as AMPIO
FROM TABLE (MonitorSession(-1,'*',0)) AS T1
where Reqstarttime <> '0000/00/00 00:00:00.00'
--and AMPCPUSec > 500000
but here CPU consumption is given for Session and not Query level. so how do i get Query level AMPCPUtime?
The AMPCPUTime value is for the active request, not the entire session.