DBpulse Manual - thresh_file


THRESH file - format of THRESH file used by DBpulse


THRESH files are used by DBpulse to store audit and diagnostic information. They are rotated on a 15 minute basis. If dbpulse restarts, the same 15 minute THRESH file is reused (appended to). These files can grow very quickly (especially in audit mode) so they need to be pruned on a regular basis to conserve space.

The contents of a THRESH file are as follows:

     1. Query type  ([A]ttention, [L]ogin, [M]arker, [Q]uery, [R]PC)
     2. Query end time  (Unix epoch time to the microsecond)
     3. Query elapsed time  (float in seconds)
     4. Query status  (0 - success, 1 - error, 2 - cancelled)
     5. Source IP address
     6. Servername (Instance Name)
     7. Internal Session ID  (positive integer)
     8. Client host name
     9. Application name
    10. Server.Database.Username
    11. Query threshold value  (float in seconds)
    12. Result set row count  (integer)
    13. Packets to Server  (integer)
    14. Bytes to Server  (integer)
    15. Packets to Client  (integer)
    16. Bytes to Client  (integer)
    For type 'A'
    For type 'L'
    17. LOGIN
    For type 'M'
    17. Marker name
    18. Marker start description
    19. Marker end description
    For type 'Q'
    17. QUERY: {actual query string}
    For type 'R'
    17. RPC: {actual query string}

The attributes are TAB delimited. The user information is single-quoted for the Servername, Database, and Username (e.g. 'PROD.master.sa'). In general, any item that could have white space is quoted (except the query string). Items 17, 18, and 19 for type 'M`` are used when a Marker is found. A Marker is a SQL no-op that delineates the start or end of a transaction.


    Q 1101705851.808586 2.783646 0 DBMS2 1536 unimatrix2 'DBMS2.foresight.fsinjector' 1.000000 0 1 191 2 370 QUERY: execute cpuperf_insert   @_server_id   =   40,   @_start_time   =   '11/29/2004 05:00:00:0', @cpunum = '0', @percent_user = '1', @percent_system = '1', @percent_idle = '97', @percent_iowait = '1'

This record indicates that a query

    execute cpuperf_insert @_server_id =  40,  @_start_time  =  '11/29/2004 05:00:00:0', @cpunum = '0', @percent_user = '1', @percent_system = '1', @percent_idle = '97', @percent_iowait = '1'

finished on Nov 28, 2004 at 21:24:11.808586, with a success (0) status, and took 2.783646 seconds to run with a threshold of 1.000000 seconds between client and server DBMS2 with an internal session_id of 1536 returning 0 rows, sending 1 packet (191 bytes) to the server and sending 2 packets (370 bytes) to the client, logged in as 'fsinjector' to database 'foresight'.


Tells where the head of the software distribution is. Typically this is /foresight for Unix and C:/foresight for Windows. Without this value set, dbpulse does not know where to store the THRESH files.


The Work In Progress directory. This is where the current THRESH file is stored. THRESH files in wip are typically still open and being updated to. When the run interval is reached, the THRESH file is moved to the `fg' directory.

The Finished Goods directory. This is where all completed THRESH files reside. They wait here to be processed for the TopN Query Report. Once the TopN queries are identified, the THRESH files are moved to the `done' directory.

The Done directory. This is where THRESH files that are no longer needed (i.e. all available processing has been done to them) are stored. The THRESH files here should be pruned on a nightly basis.

The file naming convention of the actual THRESH file is as follows:

    THRESH#I<{Unique Name}>#I<{Interval}>.

The field separator is the pound (#) sign.

For example: THRESH#SQL_PROD#1218333