We help IT Professionals succeed at work.

Get session information

avi_ny
avi_ny asked
on
I am a newbee in Oracle.Can soemone tellme how can I find SQL which is creating highest disk read ,
generating most archive log, using too much temp tablespace and how long it is been running.
Comment
Watch Question

SELECT * FROM v$session;

Commented:

select t.start_time,s.sid,s.serial#,s.username,s.status,s.schemaname,
s.osuser,s.process,s.machine,s.terminal,s.program,s.module,to_char(s.logon_time,'DD/MON/YY HH24:MI:SS') login_time
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by start_time;

Open in new window

Commented:

SELECT b.username "Oracle User", a.spid "Unix PID", b.SID "Oracle Sid",
       b.serial# "Oracle Serial", b.osuser "OS User", b.machine "Computer",
       b.program "Program",
       TO_CHAR (logon_time, 'HH24:MI:SS,yyyy/mm/dd') "Login Time",
       b.action "Session Action", c.action "SQL Action", lockwait "LockWait",
       status "Status", optimizer_cost "Optimizer_cost",
       c.sql_text "SQL Text"
  FROM v$process a, v$session b, v$sql c
 WHERE
       --spid=&PID and
       a.addr = b.paddr
   AND b.sql_address = c.address
   AND b.sql_hash_value = c.hash_value;

Open in new window

Commented:

SELECT   parsing_user_id, executions, sorts, command_type, disk_reads,
         sql_text
    FROM v$sqlarea
   WHERE disk_reads > &&access_level
ORDER BY disk_reads;

Open in new window

Commented:

SELECT namespace, gets, gethitratio * 100 gethitratio, pins,
       pinhitratio * 100 pinhitratio, reloads, invalidations
  FROM v$librarycache;

Open in new window

Author

Commented:
Thanks for all information provided by you.
Can you please explain what actually sql 1 is doing.

Actually the purpose is if user complains that system is running slow then from what point will DBA will start .
I want to check which session is using most of I/O ,CPU, PGA,SGA without using statpack.
Is there some user who have lock and all.
Which view I should se to check how much PGA is used.
SHOW SGA
SELECT * FROM v$lock

Explore More ContentExplore courses, solutions, and other research materials related to this topic.