|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/13/2007 3:10:00 PM
Posts: 5,
Visits: 1
|
|
Is there a SQL to find THE SQL's which ran for the longest running time or which have taken most CPU time in the past 1 day.
Any help will be appreciated.
Thanks
AC
Thanks Aman
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
|
if you enable DBQL, you will get most of the info you are looking from the Qry logs views ..
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/13/2007 3:10:00 PM
Posts: 5,
Visits: 1
|
|
DBQL is enabled.
Can you provide me any SQL query which can help me accurately find this info.
Thanks
Thanks Aman
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
There's a query that will you the Top 10 performers on time in the past one day ....
SELECT UserName, QueryText, ElapsedTime, RANK() OVER(ORDER BY ElapsedTime DESC) AS RNK
FROM DBC.QRYLOG
WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10
;
And this one for the CPU crunchers .....
SELECT UserName, QueryText, TotalCPUTime, RANK() OVER(ORDER BY TotalCPUTime DESC) AS RNK
FROM DBC.QRYLOG
WHERE STARTTIME(DATE) >= CURRENT_DATE-1
QUALIFY RNK <= 10
;
There are lots of other columns in DBC.QryLog to play around with depending up on what you are looking for ...
You might have to join with the DBC.QryLogSQL (on query id and proc id) depending on whether DBQL was enabled to log no SQL text on QryLog and SQLs are logged on QryLogSQL.
That should get you started ...
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/13/2007 3:10:00 PM
Posts: 5,
Visits: 1
|
|
Thanks a lot for your help!
Thanks Aman
|
|
|
|