14 julho, 2014

Oracle - open cursor oracle



--open cursor oracle

--mostra sql executando e numero de cursores
select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;


--Check current open cursor in database:
select max(a.value) as highest_open_cur, p.value as max_open_cur
from gv$sesstat a, gv$statname b, gv$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value;

-- seleciona linha completa de parametros do open_cursor
select * from gv$parameter where name='open_cursors';


--Opened cursor by session:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid = a.sid
and b.name = 'opened cursors current'
and s.username is not null;


--para alterar o valor do open_cursors

--ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH


Fonte: vou ficar devendo, copiei o script tem tempo e perdi a fonte.

Postar um comentário