guys,
is there a way to find the usr with highest reads in the database? We haven't enabled _usrtablestat and we are reluctant to use that option. We are now scripting the promon options page after page on the "I/O Operations by User by Table" option. There are like millions of pages we have to go through. I am just wondering how you guys do it.
thanks
They give you a tool for the purpose, but you don't want to use it?
You do recognize that this is necessarily snapshot data?
yes I know it is snapshot, any other sugggestion?
gangs schrieb:
guys,
We haven't enabled _usrtablestat and we are reluctant to use that option.
Would you be willing to share why you are reluctant to use the _usrtablestat?
Did you check Tom Bascom's ProTop or ProTop.NET? http://www.dbappraise.com/protop.html
If you need to collect data over a longer period of time, DBAppraise should be what you are waiting for.
thanks for the suggestion.
we have 800 tables and 4000 users, and the _usertablestat doesn't have index, to query it often might cause performance issue, at least we are concerned with that.
another reason is that we don't have it this table available to us because we haven't had a chance to updatevst yet.
You dont have to query _usertablestat a lot of times. It accumulates user activitity from the beginning of session. So it is convinient to see what is total user activity.
If you have to monitor activity at current moment It is better to use system table _UserIO
FOR EACH _UserIO NO-LOCK WHERE _UserIO._UserIO-usr NE ?
BREAK BY _UserIO-usr:
FIND FIRST users WHERE users.sys-name = _UserIO-Name NO-LOCK NO-ERROR.
IF AVAILABLE users THEN
DO:
user-name = users.Name.
rid-user = users.rid-user.
END.
ELSE
user-name = _UserIO-Name.
FIND FIRST work-users1 WHERE work-users1.id-proc = _UserIO-Usr NO-ERROR.
IF AVAILABLE work-users1 THEN
ASSIGN
work-users1.Delta_dbAccess = _UserIO-DbAccess - work-users1.dbAccess
work-users1.dbAccess = _UserIO-DbAccess
work-users1.Delta_dbRead = _UserIO-DbRead - work-users1.dbRead
work-users1.dbRead = _UserIO-DbRead
work-users1.Delta_dbWrite = _UserIO-DbWrite - work-users1.dbWrite
work-users1.dbWrite = _UserIO-DbWrite
work-users1.Delta_BiRead = _UserIO-BiRead - work-users1.BiRead
work-users1.BiRead = _UserIO-BiRead
work-users1.Delta_BiWrite = _UserIO-BiWrite - work-users1.BiWrite
work-users1.BiWrite = _UserIO-BiWrite
work-users1.modify = true.
ELSE
CREATE work-users1.
ASSIGN
work-users1.user-name = user-name
work-users1.rid-user = rid-user
work-users1.id-proc = _UserIO-Usr
work-users1.dbAccess = _UserIO-DbAccess
work-users1.dbRead = _UserIO-DbRead
work-users1.dbWrite = _UserIO-DbWrite
work-users1.BiRead = _UserIO-BiRead
work-users1.BiWrite = _UserIO-BiWrite.
END.
thanks guys.