Top Queries based on ADDM recommendations in oracle.

select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and  a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc)



User Last Login
*****************

select username, count(*) "SESSIONS", trunc(last_call_et/3600) "IDLE_HOURS", module
from v$session
group by username, trunc(last_call_et/3600), module
order by 4, 3, 1;
select osuser, program, count(program) "Active Count"
from v$session
where program not like '%$program%'
and (status = 'ACTIVE'
or last_call_et < 900)
group by osuser, program





Max Cursors
***********
select max(a.value) as highest_open_cur, p.value as max_open_cur
from
v$sesstat a, v$statname b, v$parameter p
where
a.statistic# = b.statistic#
and
b.name = 'opened cursors current'
and
p.name= 'open_cursors'
group
by p.value;