Sybase system queries

Sybase JDBC showplan

in dbvisualizer
set showplan on;
set noexec on;


infocenter.sybase.com

select 
  name, fullname, totio, totcpu, lastlogindate
from master..syslogins
where totio > 0
order by totio

select
  hashkey, sequence, 
  elap_min, elap_max, elap_avg, 
  cnt, abort_cnt, qtext
from sysquerymetrics

sp_lock
go

SELECT *
FROM master..sysprocesses
WHERE time_blocked > 0 
ORDER BY time_blocked DESC


//need select on monlocks AND mon_role
select count(*) from master..monlocks

//who is holding locks
SELECT H.spid 'Holding ID',
H.fid 'Holding FID',
SUSER_NAME(H.suid) 'Holding User',
W.spid 'Waiting ID',
W.fid 'Waiting FID',
SUSER_NAME(W.suid) 'Waiting User',
H.status 'Status',
V.name 'Lock Type',
W.time_blocked 'Time Blocked',
DB_NAME(L.dbid) 'Database',
OBJECT_NAME(L.id, L.dbid) 'Object Name',
L.page 'Lock Page',
L.class 'Lock Class',
H.hostname 'Holding Host',
W.hostname 'Waiting Host',
H.program_name 'Holding Program',
W.program_name 'Waiting Program',
H.cmd 'Holding Command',
W.cmd 'Waiting Command',
H.cpu 'Machine',
H.physical_io 'I/O',
H.memusage 'Mem Usage',
H.tran_name 'Transaction' 
FROM master.dbo.syslocks L, 
master.dbo.sysprocesses H, 
master.dbo.sysprocesses W, 
master.dbo.spt_values V 
WHERE L.spid = W.blocked 
AND H.spid = W.blocked 
AND L.type = V.number 
AND V.type = 'L' 
ORDER BY 1

SELECT
 a.blocking_spid,
 a.blocked_count,
 a.max_time_blocked,
 a.min_time_blocked,
 l.type lock_type_mask,
 l.context locked_context_mask,
 OBJECT_NAME(l.id, 4) locked_table,
 l.page locked_page_no,
 l.row locked_row_no,
 l.partitionid locked_partition
FROM
 master..syslocks l,
(
SELECT
 blocked blocking_spid,
 count(blocked) blocked_count,
 max(time_blocked) max_time_blocked,
 min(time_blocked) min_time_blocked
FROM
 master..sysprocesses
WHERE
 dbid = 4 /*some db*/
 AND time_blocked > 0 /*threashold*/
GROUP BY
 blocked
) a
WHERE
 l.spid = a.blocking_spid
 AND l.type & 256 = 256 /*blocking lock*/


SELECT
 w.spid waiting_spid,
 w.blocked blocking_spid,
 w.time_blocked,
 h.status blocker_status,
 h.suid blocker_suid,
 h.hostname blocker_hostname,
 h.hostprocess blocker_hostprocess,
 h.cmd blocker_cmd,
 l.type lock_type_mask,
 l.context lock_context_mask,
 OBJECT_NAME(l.id, 4) locked_table,
 l.page locked_page_no,
 l.row locked_row_no,
 l.partitionid locked_partition
FROM
 master..syslocks l,
 master..sysprocesses w,
 master..sysprocesses h
WHERE
 w.dbid = 4 /*some db*/
 AND w.time_blocked > 0 /*threashold*/
 AND l.dbid = 4 /*some db*/
 AND l.spid = w.blocked
 AND l.type & 256 = 256 /*blocking lock*/
 AND h.dbid = 4 /*some db*/
 AND h.spid = w.blocked
ORDER BY
 w.time_blocked DESC,
 w.blocked

//Show query for spid 533 need sa_role
dbcc traceon(3604)
dbcc sqltext(533)
dbcc traceoff(3604)

monLocks

Bit mask queries:

select 4&5

returns 4

select * from
(select 5 foo) a
where 6&4&foo = 4

returns 5

Group/Having quere:

select field1, count(*) cnt 
from someTable 
group by field1 
having count(*) > 500 
order by cnt desc