Check out if SQL Query is really hanged or not

最新推荐文章于 2025-03-25 15:02:11 发布

数据架构

最新推荐文章于 2025-03-25 15:02:11 发布

阅读量138

点赞数

分类专栏： Tuning DBA SQL优化文章标签：数据库 oracle dba

本文链接：https://blog.csdn.net/u011868279/article/details/128847286

版权

DBA 同时被 3 个专栏收录

183 篇文章

订阅专栏

SQL优化

17 篇文章

订阅专栏

Tuning

5 篇文章

订阅专栏

该文展示了如何使用SQL命令来查找Oracle数据库中SYS用户的活跃会话，监控SQL执行状态，以及生成SQL监控报告。主要涉及v$session、v$sql_monitor和v$sql_plan_monitor视图，以及DBMS_SQLTUNE包的report_sql_monitor功能。

摘要由CSDN通过智能技术生成

1. Find the active sessions

SQL> 
SQL> select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username';
Enter value for username: SYS
old   1: select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='&username'
new   1: select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS') AS LOGON, event from v$session where username='SYS'

       SID    SERIAL#
---------- ----------
USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS                   SQL_ID                                  LOGON
------------------------ --------------------------------------- ---------------------------------------------------------------------------------------
EVENT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         2      59121
SYS
ACTIVE                                                           01-FEB-2023 15:16:00
OFS idle

       102      24168
SYS
ACTIVE                   6g5gsu0mcqmc5                           02-FEB-2023 10:17:57
SQL*Net message from client


Elapsed: 00:00:00.00
SQL> SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%';
Enter value for username: SYS
old   1: SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='&username' and status not like '%DONE%'
new   1: SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE  username='SYS' and status not like '%DONE%'

no rows selected

Elapsed: 00:00:00.02
SQL>

2. Monitoring Progress of a SQL Execution Plan

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%' and sid='&sid' <---
ORDER BY 1,4;

-- OR ---

column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status
SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;

3. REPORT_SQL_MONITOR in HTML (OR) TEXT format

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '5mxdwvuf9j3vp', <--- SQLID
  type         => 'TEXT',         <--- HTML
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF