0 like 0 dislike
73 views

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.4k points)  
Some very useful queries for Oracle DBA
#database #oracledba

1.How to identify who loged in?
Answer:
select username, osuser,program,module,machine,
terminal,process,
to_char(logon_time, 'YYYY-MM-DD HH24:MI:SS') as logon_time,
status,
CASE status when 'ACTIVE' THEN NULL
            ELSE last_call_et
END as idle_time
from v$session
where type='USER'
order by status;

2.What all sessions are consuming resources?
Answer:
select s.sid,s.username,s.osuser,
to_char(sm.begin_time,'HH24:MI:ss') as interval_start,
to_char(sm.end_time, 'HH24:MI:ss') as interval_end,
s.machine,s.process,s.program,s.module,
sm.cpu,sm.pga_memory,sm.logical_reads,sm.physical_reads,
sm.hard_parses,sm.soft_parses,
s.logon_time
from v$session s
inner join v$sessmetric sm
on sm.session_id=s.sid
where s.type='USER'
order by sm.cpu desc;

3.What all statements consume Most Resources?
Answer:
select * from
(select sql_id,sql_text,executions,
elapsed_time,cpu_time,buffer_gets,disk_reads,
elapsed_time / executions as avg_elapse_time,
cpu_time / executions as avg_cpu_time,
buffer_gets / executions as avg_buffer_gets,
disk_reads / executions as avg_disk_reads
from v$sqlstats
where executions >0
order by elapsed_time / executions desc
)
where rownum <=25;

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
0 answers
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...