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.
generating most archive log, using too much temp tablespace and how long it is been running.
SELECT * FROM v$session;
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;
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;
SELECT parsing_user_id, executions, sorts, command_type, disk_reads,
sql_text
FROM v$sqlarea
WHERE disk_reads > &&access_level
ORDER BY disk_reads;
SELECT namespace, gets, gethitratio * 100 gethitratio, pins,
pinhitratio * 100 pinhitratio, reloads, invalidations
FROM v$librarycache;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
SELECT * FROM v$lock