1,查询高CPU消耗的pid
$ top
2,查询该pid的ADDR
SQL> select addr from v$process where spid='24764';
ADDR
----------------
00000007D3112C88
3,查询SQL_ID与用户session信息。
SQL> select sql_id from v$session where paddr='00000007D3112C88';
SQL_ID
-------------
dg5t8nfkaz8nk
SQL> select sid,SERIAL#,USERNAME,STATUS,sql_id from v$session where paddr='00000007D3112C88';
SID SERIAL# USERNAME STATUS SQL_ID
---------- ---------- ------------------------------ -------- -------------
2067 9786 APPS INACTIVE
2340 2720 APPS ACTIVE dg5t8nfkaz8nk
SQL> select sql_text from v$sql where sql_id='dg5t8nfkaz8nk';
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
BEGIN :1:=FND_GLOBAL.USER_ID; :2:=FND_GLOBAL.RESP_ID; :3:=FND_GLOBAL.RESP_APPL_ID; :4:=FND_GLOBAL.SECURITY_GROUP_ID; :5:=FND_GL
OBAL.LOGIN_ID; :6:=FND_GLOBAL.NLS_LANGUAGE; :7:=FND_GLOBAL.NLS_DATE_FORMAT; :8:=FND_GLOBAL.NLS_DATE_LANGUAGE; :9:=FND_GLOBAL.NLS
_TERRITORY; :10:=FND_GLOBAL.NLS_NUMERIC_CHARACTERS; :11:=FND_GLOBAL.NLS_SORT;END;
小技巧:
有时SQL_TEXT不能完整输出SQL语句,可以使用下面的函数进行输出转换
--从当前的内存中显示完整SQL语句select dbms_lob.substr(a.SQL_FULLTEXT) from v$sql a where sql_id='dg5t8nfkaz8nk';
--从AWR的snapshot中显示完整SQL,执行计划,绑定变量。
select * from table(dbms_xplan.display_cursor('dg5t8nfkaz8nk',null,'ADVANCED'));