This is most common in DBA's troubleshooting task to identify an OS intensive process in the BOX and then MAP OS process back to a database process. If the database process is a SQL process then display user of the SQL statement and also the SQL. 1. Find the top consuming queries with the ps command. $ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head 2. you can now determine what type of Oracle process. SELECT 'USERNAME :'||s.username ||chr(10) || 'SCHEMA :'||s.schemaname ||chr(10) || 'OSUSER :'||s.osuser ||chr(10) || 'PROGRAM :'||s.program ||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) || 'TERMINAL :'||s.terminal ||chr(10) || 'SQL ID :'||q.sql_id ||chr(10) || 'SQL TEXT :'||q.sql_text ||chr(10) 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(+); 3. If you want to investigate further about the SQL statement consuming OS resources then SQL> SELECT * from table(DBMS_XPLAN.DISPLAY_CURSOR(('&&sql_id'))); You can also use ORADEBUG utility to display top consuming SQL Statement if you know the OS ID. |
|
|