[20190125]简单快速查看那些sql语句正在执行.txt

--//跟别人学了一招,很简单,直接查询v$sqlarea条件users_executing > 0就可以了.

select * from v$sqlarea where users_executing > 0;

--//简单测试看看:

1.环境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.执行如下:

select count(*) from dba_objects,dba_source;

...

3.打开另外会话执行:

SYS@book> select sql_id,sql_text from v$sqlarea where users_executing > 0;

SQL_ID        SQL_TEXT

------------- ----------------------------------------------------------------

abgy71uhtj9v6 select sql_id,sql_text from v$sqlarea where users_executing > 0

g36a0g53bgmtd select count(*) from dba_objects,dba_source

SYS@book> select sql_id,sql_text,ELAPSED_TIME,users_executing from v$sqlarea where users_executing > 0;

SQL_ID        SQL_TEXT                                                     ELAPSED_TIME USERS_EXECUTING

------------- ------------------------------------------------------------ ------------ ---------------

6vmp6a1ju93mu select sql_id,sql_text,ELAPSED_TIME,users_executing from v$s        63113               1

              qlarea where users_executing > 0

g36a0g53bgmtd select count(*) from dba_objects,dba_source                     229713149               1

--//ELAPSED_TIME一直在增加.

--//打开2个会话同时执行如下:

SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;

  COUNT(*)

----------

 105413504

--//打开另外会话执行:

SYS@book> select sql_id,sql_text,ELAPSED_TIME,users_executing from v$sqlarea where users_executing > 0 

          and sql_text not like 'select sql_id,sql_text,ELAPSED_TIME,users_executing%';

SQL_ID        SQL_TEXT                                                     ELAPSED_TIME USERS_EXECUTING

------------- ------------------------------------------------------------ ------------ ---------------

gau6fcukuvcz7 select count(*) from emp,emp,emp,emp,emp,emp,emp                 28563690               2

SYS@book> select sql_id,sql_text,ELAPSED_TIME,users_executing from v$sqlarea where users_executing > 0 

          and sql_text not like 'select sql_id,sql_text,ELAPSED_TIME,users_executing%';

SQL_ID        SQL_TEXT                                                     ELAPSED_TIME USERS_EXECUTING

------------- ------------------------------------------------------------ ------------ ---------------

gau6fcukuvcz7 select count(*) from emp,emp,emp,emp,emp,emp,emp                 30157961               1

--//不失为一个快速查看的方法,不过如果数据库很慢的情况下,查询v$sqlarea是否很更慢.