使用郵件發送已耗用時間久的SQL語句，郵件發送sql

使用郵件發送已耗用時間久的SQL語句，郵件發送sql

ALTER proc [dbo].[usp_EmailLongRunningSQL]asbegindeclare@LongRunningQueries AS TABLE(  lrqId int IDENTITY(1,1) PRIMARY KEY,  spid int NULL,  batch_duration bigintNULL,  program_namenvarchar(500) NULL,  hostname nvarchar(100) NULL,  loginame nvarchar(100) NULL,  sqltext nvarchar(max) NULL)-- variabledeclaratuionsDECLARE @exectime DATETIMEDECLARE @tableHTMLNVARCHAR(MAX)DECLARE @Handle VARBINARY (85)DECLARE @SPID INTDECLARE @sqltext NVARCHAR(MAX)DECLARE @timeLimitsmallintdeclare @Sub as varchar(100)set @Sub = @@servername + 'Long Running  Query found'SET @timeLimit=(3*60) -- minutes -- WAITFOR DELAY'00:01:05' -- uncomment for testing (1min:5sec) -- populate thetable with execution info, you don't have to use top 1INSERT INTO @LongRunningQueries(spid, batch_duration, program_name, hostname, loginame) SELECT top 5    P.spid  , convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000  , P.program_name  , P.hostname  , P.loginameFROM master.dbo.sysprocesses P WITH(NOLOCK)WHERE (P.spid > 50)AND P.status NOT IN('background', 'sleeping')AND P.cmd NOT IN('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')AND convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000 >@timeLImit  -- use a cursorto update the sqltext for each pidDECLARE @lrqId intDECLARE mycur cursor for  SELECT lrqId from @LongRunningQueries  ORDER BY lrqId OPEN mycurFETCH NEXT FROM mycur INTO @lrqId WHILE @@FETCH_STATUS=0BEGIN   SET @SPID =(SELECT spid from @LongRunningQueries WHERElrqId=@lrqId)   -- get the SQL theSPID is executing  SELECT@Handle = sql_handleFROM master.dbo.sysprocesses WHEREspid = @SPID   UPDATE@LongRunningQueries  SET sqltext =(SELECT text FROM sys.dm_exec_sql_text(@Handle))  WHERE lrqId = @lrqId   FETCH NEXT FROM mycur INTO @lrqIdENDCLOSE mycurDEALLOCATE mycur DELETE FROM @LongRunningQueries WHEREsqltext IS NULLOR sqltext='' OR  program_nameLIKE '%DatabaseMail%' IF EXISTS(SELECT * FROM@LongRunningQueries WHERE sqltext IS NOT NULL OR sqltext<>'')BEGIN-- populate atable with it's info and mail itSET @tableHTML=  N'<H1>LongRunning  Querys</H1>' +  N'<tableborder="1">' +  N'<tr><th>SPID</th>'+  N'<th>Duration</th>'+  N'<th>Application</th>'+  N'<th>HostName</th>'+  N'<th>Login</th>'+  N'<th>SQLExecuting</th></tr>' +  CAST(( SELECT td = T.spid, '',  td = T.batch_duration, '',  td = T.[program_name], '',  td = T.hostname, '',  td = T.loginame, '',  td = T.sqltext, ''  FROM  @LongRunningQueries T  FOR XML PATH('tr'), TYPE  ) AS NVARCHAR(MAX) ) +  N'</table>' -- if @tableHTMLis NULL, mail will not get sentEXEC msdb.dbo.sp_send_dbmail,@recipients= 'smith.liu@126.com'@body = @tableHTML,@body_format = 'HTML';END end

oracle 怎得到sql語句已耗用時間

在sqlplus中執行
SQL>set timing on --顯示執行時間
SQL>set autorace on ?C顯示執行計畫
SQL>set autorace on ?C顯示執行計畫
SQL>set autotrace traceonly ?C只顯示執行計畫即不顯示查詢出來的資料
設定完畢後執行SQL語句就會顯示執行計畫資訊及相應的統計資訊(需要設定顯示該選項)
 
SQL2000裡怎計算SQL語句啟動並執行時間

但我還是不懂~~~ 第二種方法：SET STATISTICS TIME顯示分析、編譯和執行各語句所需的毫秒數。文法SET STATISTICS TIME { ON | OFF }注釋當SET STATISTICS TIME 為 ON 時，顯示語句的時間統計。為 OFF 時，不顯示時間統計。SET STATISTICS TIME 的設定是在執行或運行時設定，而不是在分析時設定。Microsoft?? SQL Server?? 不能在Fiber 模式下提供準確的統計，而Fiber 模式在啟用 lightweight pooling 配置選項時啟用。只有當使用 SET STATISTICS TIME ON 執行查詢時才更新 sysprocesses 表中的 cpu 列。當 SET STATISTICS TIME 為 OFF 時，將返回 0。ON 和 OFF 設定也影響 SQL Server 企業管理器內的"當前活動的進程資訊視圖"中的 CPU 列。