SAP Sybase ASE 15.7 MDA Table Examples (Monitoring Data Access)




MDA Tables - Useful or a Just a Sea of Data?


I've went out to pick the 5 most immediately useful MDA tables for a DBA and provided example output.

Opinions my own!

The queries below don't have the master database specified ("use master" first).

monDeadLock: showing deadlocks

The deadlock history clears after one query ie it is not re-runnable.

As a deadlock example takes a number of steps to set up, I created a separate table:

select * into monDeadLock_history from master..monDeadLock

I created a deadlock as per the helpful web page:

http://sybaseblog.com/sybasewiki/index.php?title=Real_time_example_of_deadlock



The monDeadLock queries below identify the KPID (kernel process ID) values for the two spids in the deadlock.

These KPID values are used to show the SQL of the two spids involved in the deadlock via a query on monSysSQLText.

Saved the output to the history:

1> insert monDeadLock_history select * from master..monDeadLock
2> go
(2 rows affected)

1> sp_onerow monDeadLock_history
2> go


The two rows are shown below, broken up to make more readable:

Showing example data from table 'monDeadLock_history':

 colname                                                column_data
 ------------------------------------------------------ --------------------------------------------------
 DeadlockID                                             2
 VictimKPID                                             17629218
 InstanceID                                             0
 ResolveTime                                            Nov 19 2016 12:34PM
 ObjectDBID                                             11
 ObjectID                                               204524731
 PageNumber                                             43721
 RowNumber                                              0
 HeldFamilyID                                           0

 HeldSPID                                               17
 HeldKPID                                               17629218
 HeldInstanceID                                         0
 HeldProcDBID                                           11
 HeldProcedureID                                        1557636824
 HeldBatchID                                            4
 HeldContextID                                          1
 HeldLineNumber                                         1
 HeldStmtNumber                                         2
 HeldNumLocks                                           3
 WaitFamilyID                                           0

 WaitSPID                                               20
 WaitKPID                                               17235996
 WaitProcDBID                                           2
 WaitProcedureID                                        0
 WaitBatchID                                            3
 WaitContextID                                          1
 WaitLineNumber                                         1
 WaitStmtNumber                                         2
 WaitTime                                               486
 ObjectDBName                                           raw_test

 ObjectName                                             t1
 HeldUserName                                           sa
 HeldApplName                                           isql
 HeldHostName                                           centosdev.home
 HeldClientName
 HeldClientHostName
 HeldClientApplName
 HeldTranName                                           $user_transaction
 HeldLockType                                           exclusive page
 HeldCommand                                            INSERT
 HeldProcDBName                                         raw_test
 HeldProcedureName

 WaitUserName                                           sa
 WaitApplName                                           isql
 WaitHostName                                           centosdev.home
 WaitClientName
 WaitClientHostName
 WaitClientApplName
 WaitLockType                                           exclusive page
 WaitTranName
 WaitCommand                                            INSERT
 WaitProcDBName                                         tempdb
 WaitProcedureName
 HeldSourceCodeID                                       insert.c:917
 WaitSourceCodeID                                       Unknown:20241


(return status = 0)
1>


 colname                                                column_data
 ------------------------------------------------------ --------------------------------------------------
 DeadlockID                                             2
 VictimKPID                                             17629218
 InstanceID                                             0
 ResolveTime                                            Nov 19 2016 12:34PM
 ObjectDBID                                             11
 ObjectID                                               236524845
 PageNumber                                             43729
 RowNumber                                              0
 HeldFamilyID                                           0

 HeldSPID                                               20
 HeldKPID                                               17235996
 HeldInstanceID                                         0
 HeldProcDBID                                           11
 HeldProcedureID                                        1570160871
 HeldBatchID                                            3
 HeldContextID                                          1
 HeldLineNumber                                         1
 HeldStmtNumber                                         2
 HeldNumLocks                                           3
 WaitFamilyID                                           0

 WaitSPID                                               17
 WaitKPID                                               17629218
 WaitProcDBID                                           2
 WaitProcedureID                                        0
 WaitBatchID                                            4
 WaitContextID                                          1
 WaitLineNumber                                         1
 WaitStmtNumber                                         2
 WaitTime                                               26503
 ObjectDBName                                           raw_test

 ObjectName                                             t2
 HeldUserName                                           sa
 HeldApplName                                           isql
 HeldHostName                                           centosdev.home
 HeldClientName
 HeldClientHostName
 HeldClientApplName
 HeldTranName                                           $user_transaction
 HeldLockType                                           exclusive page
 HeldCommand                                            INSERT
 HeldProcDBName                                         raw_test
 HeldProcedureName

 WaitUserName                                           sa
 WaitApplName                                           isql
 WaitHostName                                           centosdev.home
 WaitClientName
 WaitClientHostName
 WaitClientApplName
 WaitLockType                                           exclusive page
 WaitTranName
 WaitCommand                                            INSERT
 WaitProcDBName                                         tempdb
 WaitProcedureName
 HeldSourceCodeID                                       insert.c:917
 WaitSourceCodeID                                       nofilename:0




monSysSQLText: What SQL has been run in the past?


This MDA table clears down after each query so a query on the table is not re-runnable.

For finding a possible "problem query" a job running every minute to store this information would be helpful.

In the example below I've run a select into a new table monSysSQLText_history from master..monSysSQLText

and singled out KPID values referenced in the deadlock rows shown above.

The output below has been indented for readability:
select convert(varchar(20), KPID) + " | spid=" + convert(varchar(4), SPID)
+ " | suser=" + suser_name(ServerUserID) +  " | SQLText="
+ SQLText
from monSysSQLText_history
where KPID in (17235996, 17629218)


 17235996 | spid=20 | suser=sa | SQLText=use raw_test
                                                                                                                                                                                                                                                                                                                          
 17629218 | spid=17 | suser=sa | SQLText=use raw_test                                                                                                                                                                                                                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                                                                                                          
 17629218 | spid=17 | suser=sa | SQLText=begin tran
                                         insert into t1 values(1,2)
                                                                                                                                                                                                                                                                                                                          
 17235996 | spid=20 | suser=sa | SQLText=begin tran
                                         insert into t2 values(11,22)
                                                                                                                                                                                                                                                                                                                          
 17629218 | spid=17 | suser=sa | SQLText=insert into t2 values(1,2)
                                                                                                                                                                                                                                                                                                                          
 17235996 | spid=20 | suser=sa | SQLText=insert into t1 values(11,22)
                                                                       


The above is quite alot more informative than:

Your server command (family id #0, process id #17) encountered a deadlock situation. Please re-run your command.


monLocks: showing how long a spid has been waiting

select
convert(char(4), SPID) spid,
convert(char(10), DBName) dbname,
convert(char(10), object_name(ObjectID, DBID)) object_name,
convert( char(9), LockState) LockState,
convert(numeric(6,2), 1.0 * WaitTime/60) Wait_in_Mins,
WaitTime Wait_in_Secs,
convert(char(11), BlockedState) BlockedState,
convert(char(10), LockLevel )  LockLevel
from monLocks where BlockedState != NULL


 spid     dbname               object_name          LockState          Wait_in_Mins Wait_in_Secs BlockedState           LockLevel
 -------- -------------------- -------------------- ------------------ ------------ ------------ ---------------------- --------------------
 22       raw_test             tab_1                Requested                 15.95          957 Blocked                PAGE
 30       raw_test             tab_1                Granted                    NULL         NULL Blocking               PAGE
 37       raw_test             junk                 Requested                 15.95          957 Blocked                PAGE
 30       raw_test             junk                 Granted                    NULL         NULL Blocking               PAGE




monOpenDatabases: Are databases being backed up on a dataserver?


Could be useful if on a new site to get a quick idea as to what is being backed up.

select DBName, BackupStartTime, LastTranLogDumpTime from monOpenDatabases


 DBName                                                       BackupStartTime                 LastTranLogDumpTime
 ------------------------------------------------------------ ------------------------------- -------------------------------
 master                                                                   Nov 17 2016  4:34PM                            NULL
 sybsystemdb                                                                             NULL                            NULL
 model                                                                    Nov 17 2016  4:34PM                            NULL
 tempdb                                                                                  NULL                            NULL
 sybsecurity                                                                             NULL                            NULL
 sybsystemprocs                                                           Nov 17 2016  4:34PM                            NULL
 pushpullsigns                                                                           NULL                            NULL
 segtest                                                                                 NULL                            NULL
 MOVE_TEST                                                                               NULL                            NULL
 altest_new                                                                              NULL                            NULL
 raw_test                                                                                NULL                            NULL
 second_disk_fs                                                                          NULL                            NULL
 AL2016                                                                                  NULL                            NULL


monProcessSQLText: What SQL is currently running?


select "SPID=" + convert(char(4), SPID) + "login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from monProcessSQLText

                                                                                                                                                                                                                                                                                                                                                                                                      
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 SPID=22  login=sa SQLText=begin tran                                                                                                                                      

monProcess: Which SPIDS are blocking?


select SPID, convert(char(8), Login) Login, convert( char(16), Command) Command, SecondsWaiting, WaitEventID, BlockingSPID, DBName from monProcess where Login != NULL and Login != "probe" order by DBName, BlockingSPID



 SPID        Login            Command                          SecondsWaiting WaitEventID BlockingSPID DBName
 ----------- ---------------- -------------------------------- -------------- ----------- ------------ ------------------------------------------------------------
          29 sa               AWAITING COMMAND                           2327         250         NULL master
          37 sa               SELECT                                     NULL           0         NULL master
          30 sa               DELETE                                     1354         150           29 raw_test
          22 sa               INSERT                                    11197         150           30 raw_test

monSysPlanText: What was the query plan?

This table allows a new query plan to be logged.

As per the other "Sys" type tables it clears down after query.
select convert( char(4), SPID ) + " " + convert( char(80), PlanText)
from monSysPlanText


 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 29   QUERY PLAN FOR STATEMENT 1 (at line 1).

 29       STEP 1

 29           The type of query is EXECUTE.

 29           Executing a newly cached statement (SSQL_ID = 738157907).

 29   QUERY PLAN FOR STATEMENT 1 (at line 1).

 29   Optimized using Serial Mode

 29       STEP 1

 29           The type of query is SELECT.

 29          |   |  FROM TABLE

 29          |   |  junk

 29          |   |  Table Scan.

 29          |   |  Forward Scan.

 29          |   |  Positioning at start of table.

 29          |   |  Using I/O Size 4 Kbytes for data pages.

 29          |   |  With LRU Buffer Replacement Strategy for data pages.


monDeviceIO: What are the most and least used devices?



select * from monDeviceIO order by WriteTime desc


 InstanceID Reads       APFReads    Writes      DevSemaphoreRequests DevSemaphoreWaits IOTime      ReadTime    WriteTime   LogicalName
         PhysicalName                                                                                                                                                                                                           
 ---------- ----------- ----------- ----------- -------------------- ----------------- ----------- ----------- ----------- ------------------------------------------------------------
         ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          0      254435      169746      316422                    2                 0     2712856     1110205     1602651 RAW_DATA002
         /home/sybase/devices/data_450m_2016                                                                                                                                                                                    
          0      165248      118540      306040                    2                 0     3456864     2084256     1372608 RAW_LOG001
         /home/sybase/devices/log001                                                                                                                                                                                            
          0      126055       43610      132788                    2                 0      820250      237740      582510 RAW_DATA001
         /home/sybase/devices/data001                                                                                                                                                                                           
          0        5178         935        3001                    3                 0       37703       16206       21497 master
         /home/sybase/SYB157/data/master.dat                                                                                                                                                                                    
          0      715572      460868      922724                    2                 0       30508       12082       18426 tempdev20
         /var/apps/devices/tempdev20                                                                                                                                                                                            
          0        5523        1954        2785                    2                 0       29690       14563       15127 sysprocsdev


monState: How do you get a dataserver summary?



The counters below get reset after a dataserver restart.

This is possibly another MDA table which could benefit by a history table.

1> sp_onerow monState
2> go

Showing example data from table 'monState':

 colname                                                column_data
 ------------------------------------------------------ --------------------------------------------------
 InstanceID                                             0
 LockWaitThreshold                                      5
 LockWaits                                              0
 DaysRunning                                            4
 CheckPoints                                            0

 NumDeadlocks                                           2
 DiagnosticDumps                                        0
 Connections                                            3
 MaxRecovery                                            5
 Transactions                                           54453

 Rollbacks                                              32
 Selects                                                8569
 Updates                                                282
 Inserts                                                853
 Deletes                                                57
 Merges                                                 0
 TableAccesses                                          3588511
 IndexAccesses                                          530567
 TempDbObjects                                          143
 WorkTables                                             452

 ULCFlushes                                             2550189
 ULCFlushFull                                           2327217
 ULCKBWritten                                           9328994
 PagesRead                                              2808191
 PagesWritten                                           3015990
 PhysicalReads                                          1317327
 PhysicalWrites                                         1732555
 LogicalReads                                           79278857
 StartDate                                              Nov 15 2016  5:47PM
 CountersCleared


monTables: The Complete list of MDA tables


This table shows the complete list of MDA tables with description.

Conclusion

I found more than five MDA tables which could be useful from the perspective of a DBA.

See this link for a good diagram of the MDA tables:
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc70002.1550/pdf/asemtpst.pdf

Some of The Rest


There are over 80 MDA tables listed in monTables.
These are some that I considered which "missed the cut".

monCachedProcedures. Produced unexpected results for "ExecutionTime".
monSysStatement. Another table which clears down after a query.
monCachedStatement.
monProcedureCacheMemoryUsage
monCachedObject. Could be useful to see which data cache, if any, a table is using.

1> select SSQLID, MaxElapsedTime from monCachedStatement order by MaxElapsedTime desc
2> go
 SSQLID      MaxElapsedTime
 ----------- --------------
   338156482        5160507
  2057114601          48780
   569109300          27358
   258156197           2255
   665109642           2137


Disclaimer



No liability can be accepted for any loss due to any information found on this page.





Push Symbol. Push Symbol. Door logos



Alastair Cook email:
cookalastair @ btinternet.com

Page created - 18th Nov 2016


Push Pull Door