通过操作系统进程找到top sql信息

SELECT 'USERNAME :' || s.username || chr(10) || 'SCHEMA:' || s.schemaname ||       chr(10) || 'OSUSER:' || s.osuser || chr(10) || 'SPID:' || p.spid ||       chr(10) || 'SID:' || s.sid || chr(10) || 'SERIAL#:' || s.serial# ||       chr(10) || 'KILL STRING: ' || '''' || s.sid || ',' || s.serial# || '''' ||       chr(10) || 'MACHINE: ' || s.machine || chr(10) || 'TYPE:' || s.type ||       chr(10) || 'SQL_ID:' || q.sql_id || chr(10) || 'SQL_TEXT: ' ||       q.sql_text  FROM v$session s, v$process p, v$sql q WHERE s.paddr = p.addr   AND p.spid = '&PID_FROM_OS'   AND s.sql_id = q.sql_id(+)

'USERNAME:'||S.USERNAME||CHR(10)||'SCHEMA:'||S.SCHEMANAME||CHR(10)||'OSUSER:'||S--------------------------------------------------------------------------------USERNAME :RPTSCHEMA:RPTOSUSER:zedSPID:54657092SID:1854SERIAL#:35907KILL STRING: '1854,35907'MACHINE: aixTYPE:USERSQL_ID:4wv8ms79s6m37SQL_TEXT: SELECT '073000' AS TIMEKEY FROM DUAL'USERNAME:'||S.USERNAME||CHR(10)||'SCHEMA:'||S.SCHEMANAME||CHR(10)||'OSUSER:'||S

SQL> select * from table(dbms_xplan.display_cursor(('&sql_id')));

Enter value for sql_id: 4wv8ms79s6m37

old 1: select * from table(dbms_xplan.display_cursor(('&sql_id')))

new 1: select * from table(dbms_xplan.display_cursor(('4wv8ms79s6m37')))

PLAN_TABLE_OUTPUT

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

SQL_ID 4wv8ms79s6m37, child number 0

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

SELECT '073000' AS TIMEKEY FROM DUAL

Plan hash value: 1546270724

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

| Id | Operation | Name | Rows | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | 2 (100)| |

| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT

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

13 rows selected.

也可以通过另外一种方式

SQL> conn / as sysdba

Connected.

SQL> oradebug setospid 54657092

Oracle pid: 45, Unix process pid: 54657092, p_w_picpath: oracle@aix (TNS V1-V3)

SQL> oradebug current_sql;

SELECT '235959' AS TIMEKEY FROM DUAL