I can see in Sybase Central, Overview panel, in Connected Users frame, Top 5 transaction times. But, it shows Transaction Time with values like 1100 ou more seconds. What does it mean? Does it mean that the connection is executing for 1100 seconds and did not finished yet??? Or does it mean the time the connection is open but it is executing any commands during this time?
asked 29 May '13, 01:56
As Mikel indicates, the "Trans. Time" title in Sybase Central is not correct. For example, this screenshot shows four connections that have no outstanding transactions in progress, yet two of the "Trans. Time" numbers keep growing:
What Sybase Central is (apparently) displaying is an elapsed time calculated from the LastReqTime connection-level property. LastReqTime is the timestamp when the last request was started for this connection.
In other words, it has absolutely ... nothing ... to ... do ... with transaction time. That can be calculated from the TransactionStartTime connection-level property.
In many cases, LastReqTime gives a pretty good idea of which connections are idle, especially when TransactionStartTime is also empty.
However, the ReqStatus connection level property also gives a clue, especially since one of its values is 'Idle' :)
In other words, you have to carefully define what you mean by "idle"; for example, do you count connections that are doing nothing except blocking hundreds of other connections?
Then, once you have a definition, and you understand what statistics SQL Anywhere makes available to you, THEN you can start coding queries...
(Note: this query shows an actual transaction in progress; it was NOT run at the same time as the Sybase Central screenshot above.)
SELECT Number, Name, Userid, LastReqTime, IF TRIM ( COALESCE ( LastReqTime, '' ) ) = '' THEN 0 ELSE DATEDIFF ( SECOND, CAST ( LastReqTime AS TIMESTAMP ), CURRENT TIMESTAMP ) END IF AS "Seconds since Last Request", CONNECTION_PROPERTY ( 'TransactionStartTime', Number ) AS TransactionStartTime, IF TRIM ( COALESCE ( TransactionStartTime, '' ) ) = '' THEN 0 ELSE DATEDIFF ( SECOND, CAST ( TransactionStartTime AS TIMESTAMP ), CURRENT TIMESTAMP ) END IF AS "Transaction Time in seconds", CONNECTION_PROPERTY ( 'ReqStatus', Number ) AS ReqStatus FROM sa_conn_info() ORDER BY Number; Number,Name,Userid,LastReqTime,Seconds since Last Request,TransactionStartTime,Transaction Time in seconds,ReqStatus 1,'ddd16-2','DBA','2013-05-30 06:41:35.424',3594,'2013-05-30 06:41:35.385',3594,'Idle' 2,'ddd16-1','DBA','2013-05-30 07:41:29.765',0,'',0,'Executing' 3,'Foxhound-p001','DBA','2013-05-30 07:41:23.930',5,'',0,'Idle' 4,'Sybase Central 2','DBA','2013-05-30 07:41:29.047',0,'',0,'Idle'
For the record, Foxhound shows all three; "Time Since Last Request" based on LastReqTime, "Current Req Status" based on ReqStatus, and "Transaction Running Time" based on TransactionStartTime.
(Note: This screenshot also shows an actual transaction in progress; it was NOT taken at the same time as the query above, or Sybase Central screenshot)
Foxhound also lets you run adhoc queries on historical performance data, like this...
-- Long-running queries. -- Each connection is uniquely identified by sampling_id, connection_number and LoginTime. -- Each query is uniquely identified by sampling_id, connection_number, LoginTime and LastReqTime. WITH long_running_query AS ( SELECT sample_connection.sampling_id AS sampling_id, sample_connection.connection_number AS connection_number, sample_connection.LoginTime AS LoginTime, sample_connection.LastReqTime AS LastReqTime, MIN ( sample_connection.sample_set_number ) AS from_sample_set_number, MAX ( sample_connection.sample_set_number ) AS to_sample_set_number FROM sample_connection WHERE sample_connection.ReqStatus = 'Executing' AND sample_connection.time_since_last_request > 0 AND TRIM ( COALESCE ( sample_connection.LastStatement, '' ) ) <> '' GROUP BY sample_connection.sampling_id, sample_connection.connection_number, sample_connection.LoginTime, sample_connection.LastReqTime HAVING from_sample_set_number <> to_sample_set_number ) SELECT long_running_query.sampling_id AS sampling_id, IF sampling_options.selected_tab = 1 THEN 'DSN' ELSE 'String' END IF AS connection_type, sampling_options.selected_name AS target_database, long_running_query.connection_number AS connection_number, long_running_query.to_sample_set_number AS sample_set_number, long_running_query.LoginTime AS LoginTime, long_running_query.LastReqTime AS started_at, sample_header.sample_finished_at AS recorded_at, CAST ( sample_connection.time_since_last_request / 1000 AS BIGINT ) AS elapsed_seconds, sample_connection.LastStatement AS LastStatement FROM sampling_options INNER JOIN long_running_query ON long_running_query.sampling_id = sampling_options.sampling_id INNER JOIN sample_header ON sample_header.sampling_id = sampling_options.sampling_id AND sample_header.sample_set_number = long_running_query.to_sample_set_number INNER JOIN sample_connection ON sample_connection.sample_set_number = long_running_query.to_sample_set_number AND sample_connection.connection_number = long_running_query.connection_number;
Once again, I recommend this blog post.
The value displayed under Transaction Time is time since the last request was received on that connection. Large numbers under transaction time should not be a cause for concern as they may just be idle clients.
answered 29 May '13, 15:54