How to find optimizer underscore parameter



Last updated on November 23rd, 2015 at 03:31 pm

How to find optimizer underscore parameter
SELECT x.ksppinm name, y.ksppstvl VALUE,
ksppstcmnt update_comment FROM x$ksppi x,
x$ksppcv y WHERE x.inst_id = userenv(‘Instance’) AND y.inst_id = userenv(‘Instance’) AND
x.indx = y.indx AND x.ksppinm LIKE ‘%optim%%’;

How to find background process information
select nm, max(description) descript
from
(select regexp_replace( name,'[0-9a-z]’,’#’) nm, description
from v$bgprocess)
group by nm
order by nm;

How to find a session with high archive logs
SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc

How to find the long running queries
set linesize 1000
select
OPNAME,
sid,SOFAR/TOTALWORK*100,
to_char(start_time,’dd-mon-yy hh:mi’) started,
elapsed_seconds/60,time_remaining/60
from
v$session_longops
where
sid =&sid


Leave a Reply