Oracle Identify Fat SQL’s

Esteemed Readers,

It’s been long time since my last post. Got busy in cricket season and some more interesting stuff ;). So I was holding back on some ideas I could share with you wonderful people.

This post will focus on determining long-running sql’s in your Oracle schema. My team has gone through some of the worst times in Oracle response time. So finally we were able to get access to some cool Oracle dictionary tables that could help identify time-consuming sql’s.

Below is the query that would return details of long-running sql’s:

SELECT s.username,
  s.schemaname,
  p.spid os_process_identifier,
  s.status,
  s.osuser,
  s.machine,
  s.program,
  s.state,
  sa.sql_fulltext,
  (sa.cpu_time    /1000000) cpu_time_in_secs,
  (sa.elapsed_time/1000000) elapsed_time_in_secs
FROM v$session s,
  v$sqlarea sa,
  v$process p
WHERE s.sql_id = sa.sql_id
AND s.paddr    =p.addr
ORDER BY sa.cpu_time DESC ;

You might not have privileges to access V$ tables. Bug you DBA until he/she gives in :P.

In Oracle 10g, you could also use below query to find long running operations. But I have a feeling, it keeps the record in the table even after query has finished execution.

SELECT *
FROM v$session_longops a
ORDER BY a.elapsed_seconds DESC ;
I would rely more on 1st query to identify problematic (requires tuning) sql’s.
Please feel free to leave your excellent comments.
Your’s Truly
Advertisements

About Badal Chowdhary

I am a Software Engineer by profession. I have done SCJP and SCWCD certifications. Like working on cutting edge technologies and frameworks. Driven by challenges and fascinated by technology. I love playing and watching sports: Cricket, Ping Pong, Tennis, Badminton, Racket Ball and Gym.
This entry was posted in Oracle and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s