Wednesday, April 11, 2012

Tracking Down Busy Oracle Sessions

This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.


ACTIVE users



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
, v$process p
 Where s.paddr = p.addr
 and s.status = 'ACTIVE'
 and s.osuser <> 'oracle'
 order by to_number(p.spid)

ALL Users (except oracle)



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and s.osuser <> 'oracle'
 order by to_number(p.spid)

From there you can use the OS PID to run a top command to see what kind of resources the session is consuming



top -p PID

Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID


Find Active Users



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and p.spid IN (22057, 28919, 21972)
 order by to_number(p.spid)

Related Posts:

  • Work Temps Took the ol Laser Infrared Thermometer to work last week and recorded temps of things around the desk. Far left is the Baseline temp. The Up/Down t… Read More
  • Heart Rate on Antioch RunData collection has commenced with the new Zephyr Hxm Bluetooth Heart Monitor. I have been collecting data now for a few weeks and have developed a da… Read More
  • Torque Stats with a CTEUsing a Common Table Expression in SQL to analyze some Performance stats from the OBD scanner.;WITH cteRawData AS (SELECT CAST(TripAvgMPG as float) Tr… Read More
  • Moved Blog to Blogger.comFinally got tired of the image resize drama at Tumblr and migrated to the Blogger site. I am already deep into Google with gmail, my phone, drive, etc… Read More
  • Basement TemperatureStartingt to look at some of this data the Arduino is producing. I am recording the temperature in the basement every minute. The chart below shows th… Read More

0 comments:

Post a Comment