Link to home
Start Free TrialLog in
Avatar of avi_ny
avi_ny

asked on

Get session information

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.
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

SELECT * FROM v$session;
Avatar of sonicefu

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


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


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


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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sonicefu
sonicefu
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of avi_ny
avi_ny

ASKER

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