The Oracle database administrator knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan.
SELECT 'USERNAME : ' || s.username || CHR (10) || 'SCHEMA : ' || s.schemaname || CHR (10) || 'OSUSER : ' || s.osuser || CHR (10) || 'PROGRAM : ' || s.program || CHR (10) || 'MACHINE : ' || s.machine || CHR (10) || 'TERMINAL : ' || s.terminal || CHR (10) || 'SPID : ' || p.spid || CHR (10) || 'SID : ' || s.sid || CHR (10) || 'SERIAL# : ' || s.serial# || CHR (10) || 'TYPE : ' || s.TYPE || CHR (10) || 'SQL ID : ' || q.sql_id || CHR (10) || 'CHILD_NUMBER : ' || q.child_number || CHR (10) || 'SQL TEXT : ' || q.sql_text RESULT FROM v$session s, v$process p, v$sql q WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';
To find the cpu intensive queries:
http://www.oracle-scripts.net/cpu-issues/
To display the Oracle explain plan:
http://www.oracle-scripts.net/display-cursor/
Vincent Fenoll – Montreal