Consultor Eletrônico

Status: Verified

SYMPTOM(s):

Performance problems when using _UserTableStat VST

_UserTableStat has lots of records

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.1B

CAUSE:

_UserTableStat is a Virtual System Table and the current implementation does not have indexes, so performance issues can be expected when trying to read lot's of records from _UserTableStat.

FIX:

Before considering the code example that follows please note:

1. If the database was previously 10.1B database, the update vsts utility needs to be run offline in order to access the latest vsts: proutil dbname -C updatevst

2. The number of records in the _UserTableStat table is: ( -tablerangesize * (-n + -Mn +1))

By default the -tablerangesize is 50, or as specified by the -tablerangesize database startup parameter.
-n is the total number of connections specified by the database startup parameters (default 20)
-Mn is the total remote servers specified by the database startup parameters (default 4)

If the -tablerangesize is not specified or under-specified on the database startup parameters, you won't get output against tables that fall outside that range. Check the -tablerangesize setting needed by finding the highest table number in your database:

FOR EACH _file NO-LOCK WHERE _file-num < 10000 BY _file-num DESCENDING:
DISPLAY _file-num LABEL "SET -tablerangesize = " WITH SIDE-LABELS.
LEAVE.
END.

To find the current -tablerangesize set:

FOR LAST _TableStat no-lock:
DISPLAY RECID(_TableStat).
end.

3. Unlike _tablestat, as soon as the user is disconnected, their USER stats clearout, please refer to Solution P128146, "Differences between _tablestat statistics and _usertablestat statistics"

3. To improve performance, use a temp-table with indexes and populate data from _UserTableStat.

For example:


DEFINE VARIABLE rr AS INTEGER NO-UNDO.
DEF TEMP-TABLE ustat LIKE _usertablestat
FIELD Totalactivity AS INT64
INDEX _UserTableStat-Num IS PRIMARY
_UserTableStat-Num
INDEX _UserTableStat-Id
_UserTableStat-Id ASCENDING
INDEX tactidx
Totalactivity DESCENDING
.
DO rr = 1 TO 3:
PAUSE 3.

FOR EACH _usertablestat NO-LOCK:
IF _usertablestat._usertablestat-create <> 0 OR
_usertablestat._usertablestat-delete <> 0 OR
_usertablestat._UserTableStat-update <> 0 OR
_usertablestat._usertablestat-read <> 0
THEN DO:
CREATE ustat.
BUFFER-COPY _UserTableStat TO ustat.
ustat.Totalactivity = ustat._usertablestat-create + ustat._usertablestat-delete + ustat._UserTableStat-update + ustat._usertablestat-read.
END.
END.
OUTPUT TO VALUE("ttt" + STRING(rr) + ".txt").
PUT UNFORMATTED "executed at: " STRING(TIME,"HH:MM:SS") SKIP .
FOR EACH ustat BREAK BY ustat._usertablestat-num BY ustat._UserTableStat-Conn :
ACCUMULATE
ustat._usertablestat-create (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
ustat._usertablestat-delete (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
ustat._usertablestat-update (TOTAL BY ustat._usertablestat-num BY ustat._UserTableS.tat-Conn)
ustat._usertablestat-read (TOTAL BY ustat._usertablestat-num BY ustat._UserTableStat-Conn)
ustat.Totalactivity (TOTAL BY ustat._UserTableStat-num BY ustat._UserTableStat-Conn).
IF LAST-OF(ustat._usertablestat-Conn) THEN
DISPLAY ustat._usertablestat-num ustat._usertablestat-Conn
" " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-create LABEL "CREATE"
" " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-delete LABEL "DELETE"
" " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-update LABEL "UPDATE"
" " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat._usertablestat-read LABEL "READ"
" " ACCUM TOTAL BY ustat._UserTableStat-Conn ustat.Totalactivity LABEL "busyuser"
WITH DOWN FRAME yyy WIDTH 250.
IF LAST-OF(ustat._usertablestat-num) THEN
DISPLAY ustat._usertablestat-num
" " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-create LABEL "CREATE"
" " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-delete LABEL "DELETE"
" " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-update LABEL "UPDATE"
" " ACCUM TOTAL BY ustat._UserTableStat-num ustat._usertablestat-read LABEL "READ"
" " ACCUM TOTAL BY ustat._UserTableStat-num ustat.Totalactivity LABEL "busytable"
WITH DOWN FRAME zzz WIDTH 250.
END.
OUTPUT CLOSE.
EMPTY TEMP-TABLE ustat.
END.
.