Interesting Oracle monitoring projects
Blogs
Oracle CIS audit
Oracle performance,tunning etc
10 slowest SQL queries
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
Check current running SQL statements
set line 200 pages 200
select s.module,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username is not null
order by s.sid,t.piece;
Find the Current SQL with SQL ID, Username and hash value , elapsed time, sql Text columns
set line 200 pages 200
col username for a20
select s.sid, s.username,optimizer_mode,hash_value,
address,cpu_time,elapsed_time,sql_text
from v$sqlarea q, v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;
Find SID and Serial# for the current SQL running
select s.sid,s.serial#,s.username,s.sql_id,q.sql_text
from v$sqlarea q ,v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;
Top 10 CPU consuming Session in Oracle
col program form a30 heading "Program"
col CPUMins form 99990 heading "CPU in Mins"
select rownum as rank, a.*
from (
SELECT v.sid,sess.Serial# ,program, v.value / (100 * 60) CPUMins
FROM v$statname s , v$sesstat v, v$session sess
WHERE s.name = 'CPU used by this session'
and sess.sid = v.sid
and v.statistic#=s.statistic#
and v.value>0
ORDER BY v.value DESC) a
where rownum < 11;
Top CPU Consuming Session in last 10 min
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
sample_time >= sysdate - interval '10' minute
group by session_id, session_serial#
order by count(*) desc
);
SQL Text top consuming CPU in Oracle
col cpu_usage_sec form 99990 heading "CPU in Seconds"
select * from (
select
(se.SID),substr(q.sql_text,80),ss.module,ss.status,se.VALUE/100 cpu_usage_sec
from v$session ss,v$sesstat se,
v$statname sn, v$process p, v$sql q
where
se.STATISTIC# = sn.STATISTIC#
AND ss.sql_address = q.address
AND ss.sql_hash_value = q.hash_value
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
and ss.username !='SYS'
and ss.status='ACTIVE'
and ss.username is not null
and ss.paddr=p.addr and value > 0
order by se.VALUE desc);
Check information about datafiles
SQL> select * from dba_data_files;
SQL> select * from v$datafile;
Check information about tablespace
SQL> select * from dba_tablespaces
SQL> select * from v$tablespace;
check size of datafiles
select file_name,bytes/1024/1024/1024 from v$datafile;
Check the tablespace size
SELECT df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
tablespace_name
FROM dba_segments
GROUP BY tablespace_name
) tu
WHERE df.tablespace_name = tu.tablespace_name;