数据库当前会话查询汇总(当前性能问题快速分析)
Tags: DBA脚本MSSQLMySQLOraclePostgreSQLSQL Server会话信息会话查询当前会话快速分析性能性能分析性能排查数据库杀会话汇总
Oracle
结合v$session 、V$ACTIVE_SESSION_HISTORY 、 v$sql_monitor等视图分析。
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 | -- 慢查询会话 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.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.wait_event,pgsa.wait_event_type,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; -- 杀会话 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'); -- 锁查询 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; |
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 | -- 慢查询会话 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; -- 锁 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, 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'); |
SQL server
主要是第1个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 | -- 当前正在执行的SQL(包括等待的SQL、当前正在堵塞与被堵塞SQL) SELECT spid, blocked blocking, P.dbid, db_name(P.dbid) dbname, (waittime/1000) 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 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 ))) 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 |
参考 SQL Server 排查异常SQL:https://www.xmmup.com/sql-server-paichayichangsql.html
1、最大内存配置
2、并行数最大量配置