数据库当前会话查询汇总(当前性能问题快速分析)
Tags: DBA脚本MSSQLMySQLOraclePostgreSQLSQL Server会话信息会话查询当前会话快速分析性能性能分析性能排查执行计划数据库杀会话汇总
Oracle
结合v$session 、V$ACTIVE_SESSION_HISTORY 、 v$sql_monitor等视图分析。
常用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.username is not null and a.PROGRAM='sqlplus.exe'; select a.LAST_CALL_ET,a.sid,a.status,a.sql_id,a.USERNAME,a.EVENT from v$session a where a.SQL_ID='3vn4sbyvsd08y'; select * from v$sql_monitor d where d.status not like 'DONE%'; SELECT dbms_sqltune.report_sql_monitor(sql_id => '99qfh6psd3cpy',type => 'html',report_level => 'all') FROM dual; -- 慢SQL SELECT a.USERNAME, (SELECT upper(nb.OSUSER) FROM v$session nb WHERE nb.SID = a.sid) OSUSER, (SELECT nb.sid || ',' || nb.SERIAL# || ',' || pr.SPID FROM v$process pr, v$session nb WHERE nb.PADDR = pr.ADDR and nb.sid = a.SID and nb.SERIAL# = a.SERIAL#) session_info, a.opname, to_char(a.START_TIME, 'YYYY-MM-DD HH24:MI:SS') start_time, round(a.SOFAR * 100 / a.TOTALWORK, 2) || '%' AS progress, a.TIME_REMAINING TIME_REMAINING, a.elapsed_seconds elapsed_seconds, message message, (SELECT nb.EVENT FROM V$session_Wait nb WHERE nb.SID = a.SID) wait_event, (SELECT nb.STATUS FROM v$session nb WHERE nb.SID = a.SID) STATUS FROM v$session_longops a WHERE a.time_remaining <> 0 ORDER BY status, a.TIME_REMAINING DESC, a.SQL_ID, a.sid; -- 表空间 WITH wt1 AS (SELECT ts.TABLESPACE_NAME, df.all_bytes, decode(df.TYPE, 'D', nvl(fs.FREESIZ, 0), 'T', df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ, df.MAXSIZ, ts.BLOCK_SIZE, ts.LOGGING, ts.FORCE_LOGGING, ts.CONTENTS, ts.EXTENT_MANAGEMENT, ts.SEGMENT_SPACE_MANAGEMENT, ts.RETENTION, ts.DEF_TAB_COMPRESSION, ts.bigfile, df.ts_df_count FROM dba_tablespaces ts, (SELECT 'D' TYPE, TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ FROM dba_data_files d GROUP BY TABLESPACE_NAME UNION ALL SELECT 'T', TABLESPACE_NAME, COUNT(*) ts_df_count, SUM(BYTES) all_bytes, SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) FROM dba_temp_files d GROUP BY TABLESPACE_NAME) df, (SELECT TABLESPACE_NAME, SUM(BYTES) FREESIZ FROM dba_free_space GROUP BY TABLESPACE_NAME UNION ALL SELECT tablespace_name, SUM(d.BLOCK_SIZE * a.BLOCKS) bytes FROM gv$sort_usage a, dba_tablespaces d WHERE a.tablespace = d.tablespace_name GROUP BY tablespace_name) fs WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)) SELECT (SELECT A.TS# FROM V$TABLESPACE A WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#, t.TABLESPACE_NAME TS_Name, round(t.all_bytes / 1024 / 1024) ts_size_M, round(t.freesiz / 1024 / 1024) Free_Size_M, round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 2) Used_per, round(MAXSIZ / 1024 / 1024 / 1024, 2) MAX_Size_g, round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 / MAXSIZ, 2) USED_per_MAX, round(t.BLOCK_SIZE) BLOCK_SIZE, t.LOGGING, t.FORCE_LOGGING, t.CONTENTS, t.EXTENT_MANAGEMENT, t.SEGMENT_SPACE_MANAGEMENT, t.RETENTION, t.DEF_TAB_COMPRESSION, t.bigfile, t.ts_df_count FROM wt1 t UNION ALL SELECT to_number('') TS#, 'ALL TS:' TS_Name, round(SUM(t.all_bytes) / 1024 / 1024, 2) ts_size_M, round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m, round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M, round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 2) Used_per, round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size, to_number('') "USED,% of MAX Size", to_number('') BLOCK_SIZE, '' LOGGING, '' FORCE_LOGGING, '' CONTENTS, '' EXTENT_MANAGEMENT, '' SEGMENT_SPACE_MANAGEMENT, '' RETENTION, '' DEF_TAB_COMPRESSION, '' bigfile, to_number('') ts_df_count FROM wt1 t order by CONTENTS,TS# ; |
V$SESSION
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | CREATE OR REPLACE VIEW VW_SESSION_LHR AS SELECT a.username, a.SID, a.serial#, (SELECT p.SPID FROM v$process p WHERE p.ADDR = a.PADDR) SPID, a.STATUS, (SELECT v.name FROM SYS.AUDIT_ACTIONS v WHERE v.action = a.COMMAND) COMMAND, (SELECT d.OBJECT_NAME || ':' || d.OBJECT_TYPE FROM dba_objects d WHERE d.OBJECT_ID = a.ROW_WAIT_OBJ#) 等待对象, CASE WHEN (SELECT b.session_id FROM dba_scheduler_running_jobs b WHERE b.session_id = a.SID AND rownum = 1) > 0 THEN 'DBMS_SCHEDULER_JOB' WHEN (SELECT b.sid FROM dba_jobs_running b WHERE b.SID = a.SID AND rownum = 1) > 0 THEN 'DBMS_JOB' WHEN b.sid > 0 THEN 'DBMS_JOB' WHEN a.MODULE != 'JDBC Thin Client' THEN a.MACHINE || '---' || a.TERMINAL || '---' || a.MODULE || a.ACTION ELSE a.MODULE END 会话类型, CASE WHEN (SELECT COUNT(1) FROM v$px_session cn WHERE cn.SID = cn.QCSID AND cn.SID = a.SID) > 0 THEN 'Y' WHEN (SELECT COUNT(1) FROM v$px_session cn WHERE cn.SID = a.SID) > 0 THEN 'N' END AS 是否并行主进程, a.logon_time, (SYSDATE - a.LOGON_TIME)*24*60*60 会话持续时间, a.osuser "客户端用户名", a.PROCESS, a.machine, a.terminal, a.program, a.module, a.action, a.client_info, a.AUDSID, a.SADDR, a.WAIT_CLASS, a.EVENT, a.STATE, a.WAIT_TIME, a.SECONDS_IN_WAIT, a.P1, a.P1TEXT, a.p2, a.P2TEXT, a.p3, a.P3TEXT, A.BLOCKING_SESSION_STATUS, A.BLOCKING_SESSION, A.BLOCKING_INSTANCE, (SELECT NB.SQL_TEXT FROM V$SQL NB WHERE NB.SQL_ID = A.SQL_ID AND rownum = 1) SQL_TEXT, (SELECT NB.SQL_FULLTEXT FROM V$SQL NB WHERE NB.SQL_ID = A.SQL_ID AND rownum = 1) SQL_FULLTEXT, (SELECT NB.executions FROM V$SQL NB WHERE NB.SQL_ID = A.SQL_ID AND NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER AND rownum = 1) 语句执行次数, a.SQL_ID SQL_ID, a.SQL_ADDRESS, a.SQL_HASH_VALUE, a.PREV_HASH_VALUE, a.PREV_SQL_ADDR, a.PREV_SQL_ID, a.ACTION_HASH session_action_hash, (SELECT NB.ACTION_HASH FROM V$SQL NB WHERE NB.SQL_ID = A.SQL_ID AND NB.CHILD_NUMBER = A.SQL_CHILD_NUMBER AND rownum = 1) sql_ACTION_HASH, (SELECT se.PHYSICAL_READS FROM v$sess_io se WHERE se.SID = a.SID) PHYSICAL_READS, (SELECT se.block_changes FROM v$sess_io se WHERE se.SID = a.SID) block_changes, 'select * from v$transaction v where v.ADDR=''' || a.TADDR || ''';' "事务信息", a.MODULE_HASH session_MODULE_HASH, (SELECT B.MODULE_HASH FROM V$SQL B WHERE b.ADDRESS = a.SQL_ADDRESS AND b.HASH_VALUE = a.SQL_HASH_VALUE AND rownum = 1) sql_MODULE_HASH, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || a.SQL_ID ||''',' || a.SQL_CHILD_NUMBER || ',''advanced''));' sql_plan, 'ALTER SYSTEM KILL SESSION ''' || a.SID || ',' || a.serial# || ''' IMMEDIATE;' kill_session, (SELECT (NB.VALUE) FROM v$sesstat Nb, v$statname Nc WHERE NB.STATISTIC# = NC.STATISTIC# AND NB.SID = A.SID AND NC.NAME = 'session uga memory') "Current UGA MEMORY", (SELECT (NB.VALUE) FROM v$sesstat Nb, v$statname Nc WHERE NB.STATISTIC# = NC.STATISTIC# AND NB.SID = A.SID AND NC.NAME = 'session pga memory') "Current PGA MEMORY", a.PDML_ENABLED, a.PDML_STATUS, a.PDDL_STATUS, a.PQ_STATUS FROM v$session a LEFT OUTER JOIN DBA_JOBS_RUNNING b ON a.SID = b.sid WHERE a.username IS NOT NULL ORDER BY a.logon_time DESC, a.SID DESC ; |
V$SQL_MONITOR
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 | CREATE OR REPLACE VIEW VW_SQL_PP_LHR AS -- 记录所有正在运行中的性能差的SQL语句 WITH TMPS AS (SELECT WB.INST_ID INST_ID, WB.SID SID, WB.SERIAL#, WB.SPID, WB.OSUSER, WB.USERNAME, WA.PLAN_DEPTH, WA.PLAN_OPERATION PLAN_OPERATION, WA.PLAN_OPTIONS, WA.PLAN_PARTITION_START, WA.PLAN_PARTITION_STOP, WA.STARTS, WA.PLAN_COST, WA.PLAN_CARDINALITY, NVL(WB.SQL_ID, WA.SQL_ID) SQL_ID, WB.SQL_EXEC_START, WA.PX_SERVERS_REQUESTED, WA.PX_SERVERS_ALLOCATED, WA.PX_MAXDOP, WA.ELAPSED_TIME_S ELAPSED_TIME_S, WA.CPU_TIME CPU_TIME, WA.BUFFER_GETS, WA.PHYSICAL_READ_BYTES, WA.PHYSICAL_WRITE_BYTES, WA.USER_IO_WAIT_TIME USER_IO_WAIT_TIME, NVL((SELECT NS.SQL_TEXT FROM GV$SQLAREA NS WHERE NS.SQL_ID = WB.SQL_ID AND NS.INST_ID = WB.INST_ID),WA.SQL_TEXT) SQL_TEXT, WB.LOGON_TIME, WB.SQL_EXEC_ID, WB.EVENT, WB.BLOCKING_INSTANCE BLOCKING_INSTANCE, WB.BLOCKING_SESSION BLOCKING_SESSION, WB.BLOCKING_SESSION_SERIAL# BLOCKING_SESSION_SERIAL#, WB.TADDR, WB.SADDR, WB.LAST_CALL_ET, (WB.SESSION_INFO || '--' || WB.SESSION_TYPE || '--' || WB.MACHINE) SESSION_INFO, (SELECT NS.EXECUTIONS FROM GV$SQLAREA NS WHERE NS.SQL_ID = WB.SQL_ID AND NS.INST_ID = WB.INST_ID) EXECUTIONS, 'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(''' || WB.SQL_ID || ''',' || WB.SQL_CHILD_NUMBER || ',''advanced''));' SQL_PLAN, WB.ASH_COUNTS, WB.SESSION_STATE FROM (SELECT A.INST_ID, A.SID, A.PLAN_DEPTH, A.PLAN_OPERATION PLAN_OPERATION, A.PLAN_OPTIONS, A.PLAN_PARTITION_START, A.PLAN_PARTITION_STOP, A.STARTS, MAX(A.PLAN_COST) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_COST, MAX(A.PLAN_CARDINALITY) OVER(PARTITION BY A.INST_ID, A.SID, A.KEY, A.SQL_EXEC_ID, A.SQL_ID) AS PLAN_CARDINALITY, A.SQL_ID, A.SQL_EXEC_START, B.PX_SERVERS_REQUESTED, B.PX_SERVERS_ALLOCATED, B.PX_MAXDOP, (B.ELAPSED_TIME / 1000000) ELAPSED_TIME_S, (B.CPU_TIME / 1000000) CPU_TIME, B.BUFFER_GETS, B.PHYSICAL_READ_BYTES, B.PHYSICAL_WRITE_BYTES, (B.USER_IO_WAIT_TIME / 1000000) USER_IO_WAIT_TIME, B.SQL_TEXT SQL_TEXT, (B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' || B.PROCESS_NAME || '--' || B.CLIENT_IDENTIFIER || '--' || B.CLIENT_INFO || '--' || B.SERVICE_NAME) SESSION_INFO, A.SQL_EXEC_ID FROM GV$SQL_PLAN_MONITOR A, GV$SQL_MONITOR B WHERE A.SID = B.SID AND A.KEY = B.KEY AND A.INST_ID = B.INST_ID AND A.SQL_EXEC_ID = B.SQL_EXEC_ID AND A.STATUS IN ('EXECUTING', 'DONE(ERROR)') AND B.STATUS IN ('EXECUTING', 'DONE(ERROR)') AND B.PROCESS_NAME NOT LIKE 'p%') WA RIGHT OUTER JOIN (SELECT ASH.INST_ID, ASH.SESSION_ID SID, ASH.SESSION_SERIAL# SERIAL#, (SELECT PR.SPID FROM GV$PROCESS PR WHERE GVS.PADDR = PR.ADDR AND PR.INST_ID = ASH.INST_ID) SPID, ASH.SESSION_TYPE, ASH.USER_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, ASH.SQL_EXEC_ID, NVL(ASH.EVENT, GVS.EVENT) EVENT, ASH.SESSION_STATE, ASH.BLOCKING_SESSION, ASH.BLOCKING_SESSION_SERIAL#, ASH.BLOCKING_INST_ID BLOCKING_INSTANCE, ASH.CLIENT_ID, ASH.MACHINE, GVS.LAST_CALL_ET, GVS.TADDR, GVS.SADDR, GVS.LOGON_TIME, GVS.USERNAME, GVS.OSUSER, GVS.SQL_EXEC_START, (GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' || GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' || GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME) SESSION_INFO, COUNT(*) ASH_COUNTS FROM GV$ACTIVE_SESSION_HISTORY ASH, GV$SESSION GVS WHERE ASH.INST_ID = GVS.INST_ID AND GVS.SQL_ID = ASH.SQL_ID AND GVS.SQL_EXEC_ID = ASH.SQL_EXEC_ID AND ASH.SESSION_ID = GVS.SID AND ASH.SESSION_SERIAL# = GVS.SERIAL# AND GVS.STATUS = 'ACTIVE' AND ASH.SQL_ID IS NOT NULL GROUP BY ASH.INST_ID, ASH.SESSION_ID, ASH.SESSION_SERIAL#, ASH.SESSION_TYPE, ASH.USER_ID, ASH.SQL_ID, ASH.SQL_CHILD_NUMBER, ASH.SQL_OPNAME, ASH.SQL_EXEC_ID, NVL(ASH.EVENT, GVS.EVENT), ASH.SESSION_STATE, ASH.BLOCKING_SESSION, ASH.BLOCKING_SESSION_SERIAL#, ASH.BLOCKING_INST_ID, ASH.CLIENT_ID, ASH.MACHINE, GVS.LAST_CALL_ET, GVS.TADDR, GVS.SADDR, GVS.LOGON_TIME, GVS.USERNAME, GVS.OSUSER, GVS.PADDR, (GVS.MODULE || '--' || GVS.ACTION || '--' || GVS.PROGRAM || '--' || GVS.PROCESS || '--' || GVS.CLIENT_IDENTIFIER || '--' || GVS.CLIENT_INFO || '--' || GVS.SERVICE_NAME), GVS.SQL_EXEC_START HAVING COUNT(*) > 6) WB ON (WB.SID = WA.SID AND WB.INST_ID = WA.INST_ID AND WB.SQL_ID = WA.SQL_ID AND WB.SQL_EXEC_ID = WA.SQL_EXEC_ID) ) ------------------------------------------ 笛卡尔积 SELECT DISTINCT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '笛卡尔积【' || COUNT(*) OVER(PARTITION BY T.INST_ID, T.SID, T.SERIAL#, T.SQL_ID) || '】个' MONITOR_TYPES FROM TMPS T WHERE T.PLAN_OPERATION = 'MERGE JOIN' AND T.PLAN_OPTIONS = 'CARTESIAN' AND T.USERNAME NOT IN ('SYS') UNION ALL ------------------------------------------ SQL执行时间过大 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '执行时间过大' MONITOR_TYPES FROM TMPS T WHERE T.ELAPSED_TIME_S > 10 -- 5 * 60 * 60 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ 分区表全分区扫描 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '分区表全分区扫描' MONITOR_TYPES FROM TMPS T WHERE T.PLAN_OPERATION LIKE 'PARTITION%' AND T.PLAN_OPTIONS = 'ALL' -- AND T.ELAPSED_TIME_S >= 0.5 * 60 * 60 UNION ALL ------------------------------------------ 执行计划中COST花费超大 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '执行计划中COST花费超大【' || T.PLAN_COST || '】' MONITOR_TYPES FROM TMPS T WHERE T.PLAN_COST >= 3107523095 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ 执行计划中预估行数超大 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '执行计划中预估行数超大【' || T.PLAN_CARDINALITY || '】' MONITOR_TYPES FROM TMPS T WHERE T.PLAN_CARDINALITY > 30748908521460 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ SQL请求并行数过多 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, 'SQL请求并行数过多【' || PX_MAXDOP || '】' MONITOR_TYPES FROM TMPS T WHERE T.PX_MAXDOP>=8 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ 系统预估的剩余执行时间过长 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '系统预估的剩余执行时间过长【' || ROUND(D.TIME_REMAINING) || '】' MONITOR_TYPES FROM TMPS T, GV$SESSION_LONGOPS D WHERE T.SQL_EXEC_ID = D.SQL_EXEC_ID AND T.SID = D.SID AND T.SERIAL# = D.SERIAL# AND D.TIME_REMAINING > 10 AND T.INST_ID = D.INST_ID AND D.TIME_REMAINING >0 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ 等待事件异常 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, '等待事件异常【' || T.EVENT || '】' MONITOR_TYPES FROM TMPS T WHERE T.EVENT NOT IN ('db file sequential read', 'db file scattered read','db file parallel write','db file parallel read') AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ------------------------------------------ TMP表空间占用过大 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, 'SQL占用TMP表空间过大【' || C.BYTES || '】Bytes' MONITOR_TYPES FROM TMPS T, (SELECT A.INST_ID, A.SESSION_ADDR, SUM(A.BLOCKS) * 8 * 1024 BYTES FROM GV$TEMPSEG_USAGE A GROUP BY A.INST_ID, A.SESSION_ADDR) C WHERE C.SESSION_ADDR = T.SADDR AND C.INST_ID = T.INST_ID AND C.BYTES > 10 --50 * 1024 * 1024 * 1024 AND (nvl(PLAN_DEPTH,1)=1) UNION ALL ----------------------------------------- SQL占用UNDO过大,INACTIVE的会话也可能占用UNDO,但是这里只记录正在运行的SQL语句 SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, 'SQL占用UNDO过大【' || USED_SIZE_BYTES || '】Bytes' MONITOR_TYPES FROM TMPS T, (SELECT ST.ADDR, ST.INST_ID, (ST.USED_UBLK * 8 * 1024) USED_SIZE_BYTES FROM GV$TRANSACTION ST, V$ROLLNAME R, GV$ROLLSTAT G WHERE ST.XIDUSN = R.USN AND R.USN = G.USN AND G.INST_ID = ST.INST_ID) V1 WHERE V1.ADDR = T.TADDR AND T.INST_ID = V1.INST_ID AND USED_SIZE_BYTES > 1024 -- 50 * 1024 * 1024 * 1024 UNION ALL ----------------------------------------- 耗费性能SQL SELECT T.INST_ID, T.SID, T.SERIAL#, T.SPID, T.OSUSER, T.USERNAME, T.EVENT, T.SESSION_STATE, T.SQL_TEXT, T.EXECUTIONS, T.ELAPSED_TIME_S, T.CPU_TIME, T.USER_IO_WAIT_TIME, T.BUFFER_GETS, T.PLAN_OPERATION, T.STARTS, T.PLAN_PARTITION_START, T.PLAN_PARTITION_STOP, T.PHYSICAL_READ_BYTES, T.PHYSICAL_WRITE_BYTES, T.BLOCKING_INSTANCE, T.BLOCKING_SESSION, T.BLOCKING_SESSION_SERIAL#, T.LAST_CALL_ET, T.SQL_ID, T.SQL_EXEC_START, T.SQL_PLAN, T.LOGON_TIME, T.ASH_COUNTS, T.SESSION_INFO, 'ASH捕获的次数【' || T.ASH_COUNTS || '】【'||SESSION_STATE||'】' MONITOR_TYPES FROM TMPS T WHERE T.ASH_COUNTS>=4 AND (nvl(PLAN_DEPTH,1)=1) ORDER BY SQL_EXEC_START DESC ; |
V$ACTIVE_SESSION_HISTORY
视图:VW_ASH_SESSION_LHR
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | CREATE OR REPLACE VIEW VW_ASH_SESSION_LHR AS SELECT B.INST_ID, B.SESSION_ID, B.SESSION_SERIAL#, A.PARSING_SCHEMA_NAME USERNAME, A.SQL_TEXT SQL_TEXT, A.SQL_FULLTEXT SQL_FULLTEXT, B.SQL_PLAN_OPERATION PLAN_OPERATION, SQL_PLAN_OPTIONS, B.SQL_ID, B.SQL_EXEC_START, (A.ELAPSED_TIME / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS) / 1000000) ELAPSED_TIME, SYSDATE INDATE, ROUND(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) MONITOR_VALUE, (B.MODULE || '--' || B.ACTION || '--' || B.PROGRAM || '--' || B.MACHINE || '--' || B.CLIENT_ID || '--' || B.SESSION_TYPE) SESSION_INFO, EXECUTIONS, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SESSION_TYPE, USER_ID, SQL_CHILD_NUMBER, SQL_OPNAME, SQL_EXEC_ID, B.SESSION_STATE, B.BLOCKING_SESSION, B.BLOCKING_SESSION_SERIAL#, B.BLOCKING_INST_ID, B.EVENT, B.COUNTS FROM GV$SQL A, (SELECT INST_ID, SESSION_ID, SESSION_SERIAL#, SESSION_TYPE, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_OPNAME, SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS, SQL_EXEC_ID, SQL_EXEC_START, EVENT, WAIT_CLASS, SESSION_STATE, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, BLOCKING_INST_ID, PROGRAM, MODULE, ACTION, CLIENT_ID, MACHINE, COUNT(*) COUNTS FROM GV$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME >= SYSDATE - 30 / 1440 AND SQL_ID IS NOT NULL GROUP BY INST_ID, SESSION_ID, SESSION_SERIAL#, SESSION_TYPE, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_OPNAME, SQL_PLAN_OPERATION, SQL_PLAN_OPTIONS, SQL_EXEC_ID, SQL_EXEC_START, EVENT, WAIT_CLASS, SESSION_STATE, BLOCKING_SESSION, BLOCKING_SESSION_SERIAL#, BLOCKING_INST_ID, PROGRAM, MODULE, ACTION, CLIENT_ID, MACHINE HAVING COUNT(*) > 6) B WHERE A.SQL_ID = B.SQL_ID AND A.INST_ID = B.INST_ID AND A.CHILD_NUMBER = B.SQL_CHILD_NUMBER AND A.LAST_ACTIVE_TIME > SYSDATE - 30 / 1440 AND A.SQL_TEXT NOT LIKE '/* SQL Analyze(%'; |
Oracle查询长时间运行的SQL语句的剩余时间:https://www.xmmup.com/oraclechaxunzhangshijianyunxingdesqlyujudeshengyushijian.html
另外,结合ASH,AWR,ADDM进行分析。
MySQL
1 2 3 4 | show processlist: show full processlist; SELECT * FROM information_schema.`PROCESSLIST`; |
另外,集合如下进行分析:
1、开启慢查询日志,分析慢查询日志,参考:MySQL之慢日志(慢查询日志,Slow Query Log)
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | -- 慢查询会话 SELECT pgsa.pid, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.wait_event, pgsa.wait_event_type, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, pgsa.backend_type, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa WHERE pgsa.state not in ( 'idle' ,'idle in transaction (aborted)' ) ORDER BY query_stay DESC,xact_stay DESC LIMIT 100; -- 锁 -- granted=t是阻塞别人的,f是被阻塞的 SELECT database dbid, pd.datname, locktype, relation, relation::regclass relation_name, mode, pid, granted FROM pg_locks pl left join pg_database pd on pl.database=pd.oid where pl.database > 0; -- 锁查询 SELECT pg_locks.pid as pid, --进程ID transactionid as transaction_id, --事务ID pd.datname db_name, nspname as schemaname, --schema名 relname as object_name, --对象名 locktype as lock_type, --锁类型 mode lock_mode, --锁模式 CASE WHEN granted = 'f' THEN 'get_lock' WHEN granted = 't' THEN 'wait_lock' END lock_satus, --锁状态:持有锁|等待锁 least(query_start, xact_start) AS query_start, --query请求开始时间 substr(query, 1, 25) AS query_text --当前SQL语句 FROM pg_locks left join pg_database pd on (pg_locks.database=pd.oid) LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity WHERE NOT pg_locks.pid = pg_backend_pid() AND pg_locks.pid = pg_stat_activity.pid ORDER BY query_start; -- 杀会话 select 'select pg_terminate_backend('||a.pid||');' kill1, 'select pg_cancel_backend('||a.pid||');' kill2 from pg_stat_activity a where pid<>pg_backend_pid() and state in ( 'idle'); create view vw_lock_lhr as with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted ), t_run as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted ), t_overlap as ( select r.* from t_wait w join t_run r on ( r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid <> w.pid ) ), t_unionall as ( select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg( 'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)|| 'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)|| 'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)|| 'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)|| 'SQL (Current SQL in Transaction): '||chr(10)|| case when query is null then 'NULL' else query::text end, chr(10)||'--------'||chr(10) order by ( case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end ) desc, (case when granted then 0 else 1 end) ) as lock_conflict from t_unionall group by locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ; create view vw_lock2_lhr as with recursive tmp_lock as ( select distinct --w.mode w_mode,w.page w_page, --w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start, --now()-w.query_start w_locktime,w.query w_query w.pid as id,--w_pid, r.pid as parentid--r_pid, --r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db, --r.relation::regclass, --r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start, --r.query_start r_query_start, --now()-r.query_start r_locktime,r.query r_query, from ( select a.mode,a.locktype,a.database, a.relation,a.page,a.tuple,a.classid, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid, a.transactionid, b.query as query, b.xact_start,b.query_start,b.usename,b.datname from pg_locks a, pg_stat_activity b where a.pid=b.pid and not a.granted ) w, ( select a.mode,a.locktype,a.database, a.relation,a.page,a.tuple,a.classid, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid, a.transactionid, b.query as query, b.xact_start,b.query_start,b.usename,b.datname from pg_locks a, pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity where a.pid=b.pid and a.granted ) r where 1=1 and r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.transactionid is not distinct from w.transactionid and r.pid <> w.pid ),tmp0 as ( select * from tmp_lock tl union all --查找root,同一时刻可能有多个root select t1.parentid,0::int4 from tmp_lock t1 where 1=1 and t1.parentid not in (select id from tmp_lock) ),tmp3 (pathid,depth,id,parentid) as ( --对过滤出的机构向下递归,构成tree SELECT array[id]::text[] as pathid,1 as depth,id,parentid FROM tmp0 where 1=1 and parentid=0 union SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid FROM tmp0 t1, tmp3 t0 where 1=1 and t1.parentid=t0.id ) select distinct '/'||array_to_string(a0.pathid,'/') as pathid, a0.depth, a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text),' ') as tree_id, --'select pg_cancel_backend('||a0.id|| ');' as cancel_pid, --'select pg_terminate_backend('||a0.id|| ');' as term_pid, case when a0.depth =1 then 'select pg_terminate_backend('|| a0.id || ');' else null end as term_pid, case when a0.depth =1 then 'select cancel_backend('|| a0.id || ');' else null end as cancel_pid ,a2.datname,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state --,a2.backend_start,a2.xact_start,a2.query_start from tmp3 a0 left outer join (select distinct '/'||id||'/' as prefix_id,id from tmp0 where 1=1 ) a1 on position( a1.prefix_id in '/'||array_to_string(a0.pathid,'/')||'/' ) >0 left outer join pg_stat_activity a2 -- select * from pg_stat_activity on a0.id = a2.pid order by '/'||array_to_string(a0.pathid,'/'),a0.depth; |
GreenPlum
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | -- 慢查询会话 SELECT pgsa.pid, pgsa.sess_id, pgsa.client_port, pgsa.datname AS datname, pgsa.usename AS usename, (SELECT nb.rrrsqname from gp_toolkit.gp_resq_role nb where nb.rrrolname=pgsa.usename) rsqname, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.waiting, pgsa.waiting_reason, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, state_change, pgsa.query_start AS query_start, TRUNC(EXTRACT( epoch FROM (NOW() - pgsa.xact_start) )) AS xact_stay, trunc(EXTRACT( epoch FROM (NOW() - pgsa.query_start) )) AS query_stay, REPLACE(pgsa.QUERY, chr(10), ' ') AS QUERY, yc_filesize_GB, yc_numfiles, 'select pg_terminate_backend('||pgsa.pid||');' kill1, 'select pg_cancel_backend('||pgsa.pid||');' kill2 FROM pg_stat_activity AS pgsa -- 溢出文件大小查询 LEFT JOIN (select pid,trunc(sum(size)/1024/1024/1024) as yc_filesize_GB,sum(numfiles) as yc_numfiles from gp_toolkit.gp_workfile_usage_per_query nb group by pid) as d ON pgsa.pid=d.pid WHERE pgsa.state not in ( 'idle','idle in transaction (aborted)' ) ORDER BY query_stay DESC,xact_stay DESC LIMIT 100; -- 会话占用内存 SELECT * FROM session_state.session_level_memory_consumption d WHERE d.datname IS NOT NULL ORDER BY d.vmem_mb DESC, d.sess_id, d.segid LIMIT 20; -- 锁 -- granted=t是阻塞别人的,f是被阻塞的 SELECT database, locktype, relation, relation::regclass, mode, pid FROM pg_locks where granted='f'; SELECT * FROM pg_locks; select * from gp_toolkit.gp_locks_on_relation ; SELECT pg_locks.pid as pid, -- 进程ID transactionid as transaction_id, --事务ID pd.datname db_name, nspname as schemaname, -- schema名 relname as object_name, -- 对象名 locktype as lock_type, -- 锁类型 mode lock_mode, -- 锁模式 CASE WHEN granted = 'f' THEN 'get_lock' WHEN granted = 't' THEN 'wait_lock' END lock_satus, -- 锁状态:持有锁|等待锁 CASE WHEN waiting = 'false' THEN 'already get lock,sql executing' WHEN waiting = 'true' THEN 'waiting get lock,sql waiting execute' END waitting_satus, -- 当前会话状态:执行中|等待中 least(query_start, xact_start) AS query_start, -- query请求开始时间 substr(query, 1, 200) AS query_text -- 当前SQL语句 FROM pg_locks left join pg_database pd on (pg_locks.database=pd.oid) LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity WHERE NOT pg_locks.pid = pg_backend_pid() AND pg_locks.pid = pg_stat_activity.pid ORDER BY query_start; -- 杀会话 select 'select pg_terminate_backend('||a.pid||');' kill1, 'select pg_cancel_backend('||a.pid||');' kill2 from pg_stat_activity a where pid<>pg_backend_pid() and state in ( 'idle'); -- 锁详情 create view vw_lock_lhr as with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted ), t_run as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath, b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted ), t_overlap as ( select r.* from t_wait w join t_run r on ( r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.virtualxid is not distinct from w.virtualxid and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.pid <> w.pid ) ), t_unionall as ( select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg( 'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)|| 'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)|| 'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)|| 'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)|| 'SQL (Current SQL in Transaction): '||chr(10)|| case when query is null then 'NULL' else query::text end, chr(10)||'--------'||chr(10) order by ( case mode when 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end ) desc, (case when granted then 0 else 1 end) ) as lock_conflict from t_unionall group by locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ; |
SQL Server
主要是第1和第2个SQL,包括了后续的SQL了。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 | -- sp_who2; -- sys.dm_exec_requests是 master.dbo.sysprocesses的替代视图 -- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等) -- sp_who2; SELECT a.percent_complete, a.spid, (SELECT count(*) FROM master.dbo.sysprocesses sp where sp.spid=a.spid) thread_cnt, -- a.dop, blocking_session_id blocking_spid, a.database_id, db_name(a.database_id) dbname, (wait_time/1000) wait_time, start_time, sp.last_batch, wait_type, last_wait_type, wait_resource, open_transaction_count, a.status, a.command, a.cpu_time/1000 cpu_time, a.total_elapsed_time/1000 total_elapsed_time, a.writes, a.logical_reads, sp.physical_io, a.row_count, a.sql_handle, SUBSTRING (c.text,a.statement_start_offset/2, (CASE WHEN a.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), c.text)) * 2 ELSE a.statement_end_offset END - a.statement_start_offset)/2) internal_SQL_TEXT, c.text Parent_SQL_TEXT, -- a.plan_handle, b.query_plan, 'kill '+cast(a.spid as varchar) kill1 FROM (select nb.session_id spid,nb.* from sys.dm_exec_requests nb WITH(NOLOCK)) a LEFT JOIN master.dbo.sysprocesses sp WITH(NOLOCK) on sp.spid=a.spid and sp.ecid=0 LEFT JOIN (select n1.session_id ,n2.query_plan from sys.dm_exec_requests n1 WITH(NOLOCK) CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) b on a.spid=b.session_id LEFT JOIN ( SELECT n1.session_id ,n2.text FROM sys.dm_exec_requests n1 WITH(NOLOCK) CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) c on a.spid=c.session_id WHERE a.spid <>@@spid and (a.status !='sleeping' or (a.status='sleeping' and a.spid IN (select blocking_session_id from sys.dm_exec_requests nb where open_transaction_count > 0 )) or (a.status='sleeping' and a.open_transaction_count > 0 ) ) and a.status!='background' order by total_elapsed_time desc GO SELECT sp.status,sp.cmd,sp.lastwaittype,sp.waitresource,sp.cpu,sp.physical_io,sp.* FROM master.dbo.sysprocesses sp WITH(NOLOCK) where sp.spid=85; -- 或者 SELECT er.percent_complete, P.spid, P.cnt thread_cnt, blocking, P.dbid, db_name(P.dbid) dbname, (waittime/1000) waittime, lastwaittype, waitresource, open_tran, P.status, cpu, physical_io, memusage, login_time, last_batch, hostname, [program_name], hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, P.sql_handle, SUBSTRING (s.text,er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT, s.text Parent_SQL_TEXT, er.plan_handle, er.query_plan, 'kill '+cast(P.spid as varchar) kill1 FROM ( select nb.spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library, max(nb.blocked) blocking, (count(distinct nb.blocked)-1) blocking_cnt, max(nb.waittime) waittime, max(nb.waittype) waittype,(count(distinct nb.waittype)-1) waittype_cnt, max(nb.lastwaittype) lastwaittype, max(nb.waitresource) waitresource, sum(cast(nb.cpu as bigint)) cpu, sum(nb.physical_io) physical_io, max(nb.login_time) login_time, max(nb.last_batch) last_batch, sum(nb.open_tran) open_tran, max(nb.loginame) loginame, max(nb.memusage) memusage, max(nb.status) status, max(nb.sql_handle) sql_handle, count(*) cnt from master.dbo.sysprocesses nb where (nb.status !='sleeping' or (nb.status='sleeping' and nb.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )) or (nb.status='sleeping' and nb.open_tran > 0 ) ) and nb.status!='background' group by spid,dbid,nb.hostname,nb.program_name,nb.hostprocess,nb.cmd ,nb.nt_domain,nb.nt_username, nb.net_address, nb.net_library ) P LEFT JOIN (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan from sys.dm_exec_requests n1 CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er ON er.session_id = P.spid LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s on P.spid=s.spid WHERE P.spid <>@@spid order by waittime desc GO -- 分步查看所有进程 select nb.spid, count(*) thread_cnt, (case when(max ( nb.blocked )=nb.spid) then 0 else max ( nb.blocked ) end ) blocking, dbid,db_name(nb.dbid) dbname, max ( nb.waittime/1000 ) waittime, max ( nb.waittype ) waittype, ( count ( distinct nb.waittype ) - 1 ) waittype_cnt, max ( nb.lastwaittype ) lastwaittype, max ( nb.waitresource ) waitresource, sum ( cast ( nb.cpu as bigint ) ) cpu, sum ( nb.physical_io ) physical_io, max ( nb.login_time ) login_time, max ( nb.last_batch ) last_batch, sum ( nb.open_tran ) open_tran, max ( nb.loginame ) loginame, max ( nb.memusage ) memusage, max ( nb.status ) status, max ( nb.sql_handle ) sql_handle, nb.hostname, nb.program_name, nb.hostprocess, nb.cmd, nb.nt_domain, nb.nt_username, nb.net_address, nb.net_library from master.dbo.sysprocesses nb where ( nb.status != 'sleeping' or ( nb.status= 'sleeping' and nb.spid IN ( select blocked from master.dbo.sysprocesses nb where blocked > 0 ) ) or ( nb.status= 'sleeping' and nb.open_tran > 0 ) ) and nb.status!= 'background' group by spid,dbid, nb.hostname, nb.program_name, nb.hostprocess, nb.cmd , nb.nt_domain, nb.nt_username, nb.net_address, nb.net_library order by waittime desc; select er.percent_complete,SUBSTRING (s.text,er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT, s.text Parent_SQL_TEXT, er.plan_handle, er.query_plan, 'kill '+cast(s.spid as varchar) kill1 from (select n1.percent_complete,n1.session_id ,n1.statement_start_offset,n1.statement_end_offset,n1.plan_handle,n2.query_plan from sys.dm_exec_requests n1 CROSS APPLY sys.dm_exec_query_plan(n1.plan_handle) AS n2 ) er LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s on er.session_id=s.spid WHERE s.spid =385 GO -- 历史SQL执行计划查看 SELECT deqp.dbid, db_name(deqp.dbid) dbname, SUBSTRING (desql.text,deqs.statement_start_offset/2, (CASE WHEN deqs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), desql.text)) * 2 ELSE deqs.statement_end_offset END - deqs.statement_start_offset)/2) internal_SQL_TEXT, desql.text AS Parent_SQL_TEXT, deqp.query_plan, (deqs.total_logical_reads + deqs.total_logical_writes) Total_IO, ((deqs.total_logical_reads + deqs.total_logical_writes) / deqs.execution_count ) [Average_IO] , deqs.execution_count, deqs.* FROM sys.dm_exec_query_stats AS deqs CROSS apply sys.dm_exec_sql_text ( deqs.sql_handle ) desql CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp WHERE 1=1 and db_name(deqp.dbid)='lhrdb' and desql.text like '%lhrdb.dbo.BigTable%' -- and deqs.last_execution_time < GETDATE() -- 指定查询的时间范围 ORDER BY deqs.last_execution_time DESC; SELECT desql.dbid, db_name(desql.dbid) dbname, desql.text, qp.query_plan, cp.* FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS apply sys.dm_exec_sql_text ( cp.plan_handle ) desql WHERE 1=1 and db_name(desql.dbid)='lhrdb' and desql.text like '%lhrdb.dbo.BigTable%' ; |
历史SQL执行计划请参考:https://www.xmmup.com/mssqlchaxunlishisqlyujudezhixingjihua.html
其它SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 | -- 简化版(无执行计划) -- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL、还原进度、恢复进度等) SELECT er.percent_complete, P.spid, blocked blocking, P.dbid, db_name(P.dbid) dbname, (waittime/1000) waittime, lastwaittype, waitresource, open_tran, P.status, cpu, physical_io, memusage, login_time, last_batch, hostname, [program_name], hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, P.sql_handle, SUBSTRING (s.text,er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), s.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) internal_SQL_TEXT, s.text Parent_SQL_TEXT, 'kill '+cast(P.spid as varchar) kill1 FROM master.dbo.sysprocesses P LEFT JOIN sys.dm_exec_requests er ON er.session_id = P.spid LEFT JOIN ( SELECT n1.spid,n2.text FROM master.dbo.sysprocesses n1 CROSS apply sys.dm_exec_sql_text ( n1.sql_handle ) n2 ) s on P.spid=s.spid WHERE P.spid <>@@spid and (P.status !='sleeping' or (P.status='sleeping' and P.spid IN(select blocked from master.dbo.sysprocesses nb where blocked > 0 )) or (P.status='sleeping' and P.open_tran >0 ) ) and P.status!='background' order by waittime desc GO -- 当前正在执行的SQL SELECT session_id spid, db_name( dbid ) AS dbname, transaction_id, wait_type, last_wait_type, wait_resource, start_time, status, estimated_completion_time, cpu_time, logical_reads, command, TEXT, open_transaction_count, open_resultset_count, percent_complete, 'kill '+cast(session_id as varchar) kill1 FROM sys.dm_exec_requests r CROSS apply sys.dm_exec_sql_text ( r.sql_handle ) s WHERE session_id <>@@spid order by start_time GO SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC; -- 当前正在堵塞与被堵塞SQL SELECT spid, blocked blocking, P.dbid, db_name(P.dbid) dbname, waittime, lastwaittype, waitresource, open_tran, status, cpu, physical_io, memusage, login_time, last_batch, hostname, [program_name], hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, sql_handle, TEXT , 'kill '+cast(spid as varchar) kill1 FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s WHERE blocked > 0 OR spid IN ( SELECT sp.blocked FROM master.dbo.sysprocesses sp WHERE sp.blocked > 0 ) GO -- 是否有未提交事务 SELECT spid, blocked, waittime, waittype, waitresource, db_name ( P.dbid ) dbname, cpu, physical_io, memusage, open_tran, status, login_time, last_batch, hostname, program_name, hostprocess, loginame, cmd, TEXT , 'kill '+cast(spid as varchar) kill1 FROM master.dbo.sysprocesses P CROSS apply sys.dm_exec_sql_text ( P.sql_handle ) s WHERE open_tran <> 0 --and last_batch < dateadd(minute,-15,getdate()) GO |