[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More
Experts Exchange Solution brought to you by
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.
--getting dbid and instance number
select distinct dbid, instance_number from stats$database_instance where db_name='$DB';
--getting the snapshot IDs for start/end (we did 9-10am, seconds always vary so that's why there's the %)
select s.snap_id snap_id, to_char(snap_time, 'YYYY-MM-DD HH24:MI:SS') snapdate
from stats\$snapshot s, stats$database_instance di
where s.dbid = $dbid
and di.dbid = $dbid
and s.instance_number = $instnum
and di.instance_number = $instnum
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and (to_char(s.snap_time, 'YYYY-MM-DD HH24:MI:SS') like '$today 09:00:%'
or to_char(s.snap_time, 'YYYY-MM-DD HH24:MI:SS') like '$today 10:00:%')
order by snap_id asc;
--getting the actual data from the snapshots and the text of the queries, limited to the top 50
--this is specific to buffered gets, but there's several others you can order by if you switch those out or include them all at once
select /*+ orderd use_nl (topn st) */ st.SQL_ID, delta_buffer_gets, delta_executions, nvl(topn.module, 'null'), dbms_lob.substr(st.sql_fulltext,3500,1)
from ( select *
from ( select *
order by delta_buffer_gets desc
where rownum <= 50
, v$sqlarea st
where st.old_hash_value(+) = topn.old_hash_value
order by topn.delta_buffer_gets desc, topn.old_hash_value
Open in new window
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.