[20190125]簡單快速檢視那些sql語句正在執行.txt

[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是否很更慢.