sybase的mda表使用.doc

sybase mdaAdaptive ServerASE.mdatable.mda licensemda mastermonmdaasemda$SYBASE/ASE-12_5/scripts NT %SYBASE%ASE-12_5scripts installmontables sp_addserver loopback, null, servernameloopbackmdamon_rolegrant role mon_role to loginnameenable monitoringmdasp_configure enable monitoring,1sp_configure Monitoringmdamdaasemda monTablesmonTableParametersmonTableColumnsmonState Adaptive Server monEngine Adaptive Server monDataCache Adaptive Server monProcedureCache Adaptive Server monOpenDatabasesmonSysWorkerThreadmonNetworkIO I/O monErrorLog Adaptive Server monLocksmonDeadLock Adaptive Server monWaitClassInfomonWaitEventInfo Adaptive Server monCachedObjectmonCachePoolmonOpenObjectActivitymonIOQueue I/O I/OmonDeviceIOmonSysWaitsmonProcessmonProcessLookupmonProcessActivitymonProcessNetIO I/O monProcessObjectmonProcessWaitsmonProcessStatementmonProcessSQLText SQL monSysPlanTextmonSysStatementmonCachedProceduresmonSysSQLText SQL SQL monProcessProcedurescpuselect , , from master.monProcessSQLText pst,master.monProcessStatementpswhere = and = (select max(CpuTime)frommaster.monProcessStatement)order by SPID, LineNumbermdasypronset nocount ongoset flushmessage ongo- check we have sa_roleif charindex(sa_role, show_role() = 0begin print print print This script requiressa_role. print Aborting. print print print set backgroundon- terminate this scriptnowendgo- We need to be in +- First test for to avoid a syntax error onlicense_enabled()if isnull(object_id(),99)>= 99begin print print print This script requiresASE or later. print Aborting. print print print set backgroundon- terminate this scriptnowendgo- We need to be in +if license_enabled(ASE_XRAY) = NULLbegin print print print This script requiresASE or later. print Aborting. print print print selectsyb_quit()- terminate thisscript nowendgo- Check whether the user has already installed the MDAtablesif object_id(master.monTables) = NULLbegin print print print This script requiresthe MDA tables to be installed. print Among other things,this involves executing the installmontables print script. print See the ASEdocumentation, or url=nl/mdanl/mda/url,for more print information. print print Aborting. print print print selectsyb_quit()- terminate thisscript nowendgouse sybsystemprocsgoprint print Installing sp_mda_help .goif object_id(sp_mda_help) <>NULL drop proc sp_mda_helpgocreate proc sp_mda_help tab varchar(30) = NULL, col varchar(30) = NULLasbegindeclare n int, matchvarchar(40), TabID intif tab = ?or (tab = NULL and col =NULL)begin print print Usage: sp_mda_help table_name_pattern |column_name_pattern print print This procedure displays information aboutMDA tables and/or columns print print Arguments: print table_name_pattern- pattern matching MDA table names print tcolumn_name_pattern - pattern matching MDAcolumn names print printExamples: print Findall tables with cache information: print sp_mda_helpcache print print Findall columns related to SQL text or statements: print sp_mda_helpnull, sql print print Copyright (c) 2003-2005 RobVerschoor/Sypron . print Visiturl=nl/mdanl/mda/url print return 0endif tab = NULL select tab =%if col = NULL select col =%if tab != %beginselect match= upper(tab)select n =count(*)frommaster.monTableswhereupper(TableName) like matchif n =0begin select match= %+upper(tab)+% select n =count(*) frommaster.monTables whereupper(TableName) like matchendif n =1begin selectTabID = TableID frommaster.monTables whereupper(TableName) like match selectTableName, Description frommaster.monTables whereupper(TableName) like match selectColumnName + +substring(TypeName,1,9)+ + Description frommaster.monTableColumns whereTableID = TabID selectParameterName + +substring(TypeName,1,9)+ + Description frommaster.monTableParameters whereTableID = TabIDendif n> 1begin selectTableName = TableName + +Description frommaster.monTables whereupper(TableName) like %+upper(tab)+% order byTableNameendendif col != %beginselectTableName, ColumnName, substring(TypeName,1,9) TypeNamefrommaster.monTableColumnswhereupper(ColumnName) like %+upper(col)+%order by1,2endendgogrant execute on sp_mda_help topublic- mon_role is neededanywaygodump tran sybsystemprocs with truncate_onlygo-if object_id(sp_mda_io) <>NULL drop proc sp_mda_iogoprint print Installing sp_mda_io .gocreate procedure sp_mda_io p_cmd varchar(16384) =NULL, debug int = 0asbegin declare cmdvarchar(1000) declare tabname varchar(60),kpid_this int, init int set nocount on select init = 0 if p_cmd = ? begin print print Usage: sp_mda_io <T-SQLcommand> , debug print print This procedure displays information aboutlogical/physical I/Os used by T-SQL statements print print Arguments: print <T-SQL command> -a T-SQL command batch print debug- when set to 1, print debugging info for this procedure print print Copyright (c) 2003-2005 RobVerschoor/Sypron . print Visiturl=nl/mdanl/mda/url print return 0 end - - Determine name for temptable. - We cannot use a #temp tablesince this cannot be created from - within exec-immediate(well, it can, but itdbedropped - immediately again whenexiting the exec-imm scope) - select tabname =db_name(tempdbid) + . + convert(varchar,spid) - We need the kpid to verifythe table is this session select kpid_this = kpid from where spid = spid - debug if debug != 0 begin print Tabname= %1! ; kpid= %2!, tabname,kpid_this end if object_id(tabname) =NULL begin select init = 1 exec(select kpid = 0, dt = getdate(), + LogicalReads= 0, PhysicalReads = 0, PhysicalWrites = 0 into + tabname ) end select cmd = declare kpid_mda int declarelio int, pio int, pwr int, init int, secs int select init =0 + select kpid_mda = kpid from + tabname + + if kpid_mda != +convert(varchar,kpid_this) + + begin select init = 1 printInitialised I/O monitoring for this session end + update + tabname + + set kpid = +convert(varchar,kpid_this)+ +, dt =getdate() +,LogicalReads = +,PhysicalReads = +,PhysicalWrites = +, secs =ceiling(datediff(ms, , getdate()/) +, lio = - +, pio = - +, pwr = - + from + tabname + t, m where SPID=spid + if init = 0 begin print print spid=%1! #secs=%2!#= %3! #= %4! #= %5!, spid, secs, lio, pio,pwrend if debug != 0 begin print cmd=%1!, cmd end exec(cmd) if p_cmd = NULL begin return (0) end exec(p_cmd) exec(cmd) return (0)endgogrant execute on sp_mda_io topublic- mon_role is neededanywaygodump tran sybsystemprocs with truncate_onlygo-if object_id (sp_mda_wait) != NULL drop proceduresp_mda_waitgoprint print Installing sp_mda_wait .gocreate procedure sp_mda_wait p1 varchar(20) = NULL, interval char(8) =00:00:10, top_n int = 20, debug int =0 - if<> 0, prints raw sampling dataasbegin declare dt1 datetime, dt2datetime declare c varchar(100),suid int, kpid int, kpid2 int declare vvarchar(30), groupwait int, delta_secs int declaretot_spids int, sys_spids int, user_spids int declare spidint set nocounton select p1 =lower(ltrim(rtrim(p1) ifisnull(p1, ?) = ? or (p1 not in(all, server) and (p1 not like 0-9%) begin print print Usage:sp_mda_wait server |<spid_no> | all , hh:mm:ss, top_N print Displays wait event details over a certain interval (default = 10seconds). print top_N applies only to the server and<spid_no> parameters and shows onlythe print topN (default = 20) wait events. print print Notes: print sp_mda_wait server-displays cumulative wait eventinfo for the entire ASE server print print sp_mda_wait<spid_no> - displays wait eventinfo for the specified spid print print sp_mda_wait all - displayswait event info for all spids, including session details print such ascurrently executing SQL print print print Copyright (c) 2005 Rob Verschoor/Sypron . print Visiturl=nl/mdanl/mda/url return0 end if p1 like 0-9% begin select spid= convert(int, p1) selectsuid = suid, kpid = kpid from where spid= spid ifrowcount = 0 begin print Spid%1! not found, spid return-1 end end - prepare #temp tables selectWaitTime, Waits, WaitEventID, SPID = 0, KPID = 0 into #t from where 1 =0 selectWaitTime, Waits, WaitEventID, SPID = 0, KPID = 0 into#t2 from where 0 =1 - takesnapshot if p1 =server begin - get wait cumulative event details for theentire server insert#t selectWaitTime, Waits, WaitEventID, 0, 0 from end else begin - get wait event detail per process insert#t selectWaitTime, Waits, WaitEventID, SPID, KPID from end if debug !=0 select * from #t - wait . select dt1 =getdate() waitfor delayinterval select dt2 =getdate() - ready waiting, takesnapshot again if p1 =server begin insert#t2 selectWaitTime, Waits, WaitEventID, 0, 0 from end else begin if spid> 0 begin selectkpid2 = kpid from where spid =spid if kpid2 !=kpid begin print Spid%1! has changed during the waiting interval. Sorry., spid return-1 end end - get wait event details per process insert#t2 selectWaitTime, Waits, WaitEventID, SPID, KPID from - get some details about each process select* into#sp frommaster.sysprocesses - getdetails about the SQL currently being executed select * into#st from select * into#sq from end if debug !=0 select * from #t2 - Calculatethe wait event time deltas - Note:were only including wait events that existed both at thestart - and theend of the wait interval. - select#, #, WaitTime =# - #, Waits =# - #, WaitEventID= #, sortid =identity(9) into#t3 from #t,#t2 where# = # and #= # and #= # order by SPID, (#- #) desc, (# - #) desc - if taken frommonProcessWaits, convert the milliseconds to seconds if p1 !=server begin update#t3 set WaitTime= (WaitTime + 500) / 1000 end if debug !=0 select * from #t3 - reportresults selectdelta_secs = floor(datediff(ms, dt1, dt2)+100)/) -selectconvert(varchar,dt1,109) dt1, convert(varchar,dt2,109) dt2 print select v =( + substring(substring(version,28,50), 1, charindex(/,substring(version,38,20)+9) + ) select c =ASE server: + servername + + v printc select c =Sampling period: + str_replace(convert(varchar,dt1,106),-) + + convert(varchar,dt1,108) + - +convert(varchar,dt2,108) + ( +convert(varchar,delta_secs)+ seconds) printc if p1 =server begin select c =Wait event times for: entire ASE server end else begin if p1 =all begin select c =Wait event times for: all spids end else begin select c =Wait event times for: spid + convert(varchar, spid) + ( +suser_name(suid) + ) end end printc if p1 !=server begin print Spidof current session: %1!, spid end print if p1 !=all begin - reportwait event info for a single spid or for overall ASE server if spid> 0 begin delete#t3 where SPID!= spid end - reportdetailed wait events setrowcount top_n select WaitSecs =, -str(*delta_secs),3)%, NrWaits =, WaitEvent = , -, from #t3sw, wei, wci where = and = and( > 0 or >0) order by desc, desc setrowcount 0 end else begin - reportdetailed wait events for all spids - setrowcount top_n select sortcol =convert(numeric(30), (SPID * 100000) + 10 + ), SPID =-1, SPIDstr =space(5), Info =right(space(10) + convert(varchar,),10) + / +right(space(5) + convert(varchar, ),5) + : + + ( + convert(varchar(3),) +) into#t4 from #t3sw, wei, wci where = and = and( > 0 or >0) insert#t4 selectdistinct (# * 100000), #, SPIDstr =right(space(5) + convert(varchar, #),5), ltrim(suser_name(suid)+ + cmd) + + status + + hostname + + ipaddr + +program_name from #t3,#sp where# = # and #= # if debug !=0 select * from #t4 insert#t4 selectdistinct (SPID * 100000) + 99, -1, , from#t4 where SPID>= 0 insert#t4 select(# * 100000) + 1, -1, , Proc: +db_name(DBID) + . +object_name(ProcedureID,DBID) +Line# in proc: +convert(varchar,#) from #st,#sq where# = # and #= # and# = # andobject_name(ProcedureID,DBID) != NULL order by# insert#t4 select(# * 100000) + 1, -1, , Line# inbatch: + convert(varchar,#) from #st,#sq where# = # and #= # and# = # andobject_name(ProcedureID,DBID) = NULL order by# insert#t4 selectsortcol = (# * 100000) + 2, SPID= -1, SPIDstr = ,