SQL> desc v$sqltext;

 Name    Null?    Type

 ADDRESS     RAW(8)

 HASH_VALUE     NUMBER

 SQL_ID      VARCHAR2(13)

 COMMAND_TYPE     NUMBER

 PIECE     NUMBER

 SQL_TEXT     VARCHAR2(64)

但v$sqltext中存储的比较简单，没有该语句的统计信息，比如执行次数等。其中piece表示SQL语句分片之后的顺序编号，比如三行的值为0，1，2, 按照顺序连接起来就是一个完成的SQL语句。sql_text 表示分片后的sql语句的一部分，注意它的长度只有64 bytes . 字段HASH_VALUE 和 address 一起唯一标志一条sql 。

 

 

下面的sql，利用v$session视图里的 PREV_SQL_ADDR， PREV_HASH_VALUE列，确定某个SID或者spid对应的完整SQL

（1）根据sid获得sql语句

select /*+ ORDERED */ sql_text

from v$sqltext a

where (a.hash_value,a.ADDRESS) in

( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),

decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)

from v$session b

where b.sid=&SID)

order by piece asc

 

SQL> select sid,username from v$session;

 

  SID     USERNAME

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

36

37 SYS  --要查询的当前SYS用户在执行什么语句

38

40

 

26 rows selected.

 

SQL> select /*+ ORDERED */ sql_text

from v$sqltext a

where (a.hash_value,a.ADDRESS) in

( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),

decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)

from v$session b

where b.sid=&SID)

order by piece asc;

Enter value for sid: 37

old   7: where b.sid=&SID)

new   7: where b.sid=37)

 

SQL_TEXT

---------

select /*+ ORDERED */ sql_text from v$sqltext a where (a.hash_va

lue,a.ADDRESS) in ( select decode(sql_hash_value,0,PREV_HASH_VAL

UE,sql_hash_value), decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_AD

DRESS) from v$session b where b.sid=37) order by piece asc

 

（2）根据操作系统pid获得sql语句，这个进程是oracle进程并且是LOCAL=NO

select /*+ ORDERED */ sql_text

from v$sqltext a

where (a.hash_value,a.ADDRESS) in

( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),

decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)

from v$session b

where b.paddr = (select addr

from v$process c

where c.spid = '&spid'))

order by piece asc

 

以操作系统的process ID为参数。（这个可以通过top命令找出LOCAL=NO的oracle消耗CPU较高的进程PID）

下面是实际生产环境的例子，业务反馈CPU使用率过高，登入到主机使用TOP查看，可以看到使用top命令查看到PID为3193,31433,31690这几个oracle进程使用CPU达到了百分之百了，这里可以使用上面的v$sqltext视图结合v$session和v$process来定位正在运行的占用cpu较高的SQL语句。

oracle@ZJHZ-HW-ZQRZ-WSOP-2:~> top
top - 15:50:18 up 1041 days, 23:28,  6 users,  load average: 11.93, 8.62, 14.23
Tasks: 509 total,  18 running, 490 sleeping,   0 stopped,   1 zombie
Cpu(s): 89.2%us,  1.2%sy,  0.2%ni,  9.1%id,  0.4%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:     23641M total,    22667M used,      973M free,     1534M buffers
Swap:    25583M total,       80M used,    25503M free,    17946M cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                  
31493 oracle    20   0 16.3g 245m 241m R  100  1.0   3:18.24 oracle                                    
31433 oracle    20   0 16.3g 246m 242m R  100  1.0   3:24.92 oracle                                    
31690 oracle    20   0 16.3g 250m 246m R  100  1.1   3:11.16 oracle                                    
31609 oracle    20   0 16.3g 245m 241m R   99  1.0   3:15.02 oracle                                    
 9645 oracle    20   0 16.3g 320m 316m R   99  1.4   3:33.72 oracle                                    
11860 oracle    20   0 16.3g 374m 370m R   98  1.6   3:44.13 oracle                                    
31553 oracle    20   0 16.3g 243m 239m R   98  1.0   3:22.38 oracle                                    
31222 oracle    20   0 16.3g 247m 243m R   97  1.0   3:25.97 oracle                                    
  456 oracle    20   0 16.3g 321m 316m R   97  1.4   3:36.43 oracle                                    
31127 oracle    20   0 16.3g 247m 243m R   95  1.0   3:29.34 oracle                                    
 5205 oracle    20   0 2958m  79m  18m R   75  0.3   0:02.25 java                                      
 5173 oracle    20   0 98684  21m 7896 S    6  0.1   0:00.18 perl                                      
11357 root      20   0  672m 9556 6232 S    3  0.0  27:11.31 InforGuardMa                              
12016 root      30  10  470m 8256 3212 S    3  0.0   4909:32 hpi_program                               
26379 root      20   0  3876  468  372 S    1  0.0   4711:34 guard-userspace                           
 5162 oracle    20   0  9072 1532  872 R    1  0.0   0:00.03 top                                       
11425 root      20   0  223m  60m 6440 S    1  0.3   1299:53 java                                      
19249 oracle    -2   0 16.3g  15m  14m S    1  0.1   1473:10 oracle                                    
30529 oracle    20   0  9072 1544  876 S    1  0.0   0:01.42 top                                       

oracle@ZJHZ-HW-ZQRZ-WSOP-2:~> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 28 15:50:34 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> select /*+ ORDERED */ sql_text 
from v$sqltext a
where (a.hash_value,a.ADDRESS) in
( select decode(sql_hash_value,0,PREV_HASH_VALUE,sql_hash_value),
decode(sql_hash_value,0,PREV_SQL_ADDR,SQL_ADDRESS)
from v$session b
where b.paddr = (select addr 
from v$process c
where c.spid = '&spid'))
order by piece asc  2    3    4    5    6    7    8    9   10  
 11  ;
Enter value for spid: 31493   ---输入pid找出对应oracle进程执行sql语句
old   9: where c.spid = '&spid'))
new   9: where c.spid = '31493'))

SQL_TEXT    ---这里定位出消耗CPU较高的SQL语句了，就是这条SQL语句导致CPU使用率达到百分之百，后面就要对这条SQL语句进行分析优化了。
----------------------------------------------------------------
select *  from (select row_.*, rownum rownum_  from (
        SELECT        a.*, b.GROUPCUSTOMERNAME,b.GROUP
CUSTOMERID,m.MEMBERGROUPID           FROM AAA_WSOPGROUPSUBSREG
ISTERLOG a           JOIN AAA_WSOPGROUPCUSTOMER b    ON a.GRO
UPCUSTOMERKEY=b.GROUPCUSTOMERKEY    LEFT JOIN AAA_WSOPMEMBERGROU
P m    ON a.MEMBERGROUPKEY=m.MEMBERGROUPKEY    WHERE EXISTS (
       SELECT :"SYS_B_0" FROM (          SELECT c.GROUP
CUSTOMERKEY          FROM AAA_WSOPCUSTOMERORGREL c
      WHERE EXISTS (               SELECT :"SYS_B_1" FROM (
              SELECT ORGID, PARENTID
   FROM T_BME_ORGANIZATION             START WITH ORGI

SQL_TEXT
----------------------------------------------------------------
D = :1                 CONNECT BY PRIOR ORGID = PARENTID
          ) d            WHERE c.ORGID = d.ORGID
         )          ) e      WHERE a.REGISTERTYPE != :"SYS_
B_2" AND a.STATUS != :"SYS_B_3"     AND a.GROUPCUSTOMERKEY   = e
.GROUPCUSTOMERKEY

               )        order by a.GROUPCUSTOMER
KEY,a.USERNAME          )row_ where rownum <= :"SYS_B_4" ) whe
re rownum_ >= :"SYS_B_5"

20 rows selected.

 

上面例子还结合v$process这张视图，请参考我v$process这篇博客：Oracle v$PROCESS

另外v$sqltext诊断事件案例请参考我的博客： Oracle v$SQLTEXT案例