• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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.
0
avi_ny
Asked:
avi_ny
  • 5
  • 2
1 Solution
 
Jinesh KamdarCommented:
SELECT * FROM v$session;
0
 
sonicefuCommented:

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

0
 
sonicefuCommented:

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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sonicefuCommented:

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

0
 
sonicefuCommented:

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

Open in new window

0
 
sonicefuCommented:
0
 
avi_nyAuthor 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.
0
 
Jinesh KamdarCommented:
SHOW SGA
SELECT * FROM v$lock
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now