Di seguito una collezione di query che possono essere utilizzate per il monitoraggio degli open cursor aperti su una istanza oracle, in questo caso non è stato preso in considerazione il RAC, quindi queste funzionano per singola istanza su cui ci si collega.
Visto che non ho molto tempo, io vi scrivo la query poi se siete bravi lo capite da soli quello che fa...
le scrivo in modo che possiate inserirle in un Report di SQLDeveloper. La prima sarà la query master e le altre devono essere inserite come report figli. Nel momento in cui sul master selezionate un SID sul Report figlio avrete il corrispondente risultato.
Se poi chi conosce vuole avere il file xml da importare in SQLDeveloper basta che mi contatti.
SQL 1- Total cursors open, by session
select a.value, s.username OWNER , s.sid P_SID, s.serial# SERIAL,
case when a.statistic#= 3 then 'Possibile Problematica Select' else 'OK' end statistic,
case when b.name = 'opened cursors current' then 'OPENED CURSOR CURRENT - CONTROLLARE SE HO KO IN STATISTIC' else b.name end name
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';
SQL 2- Valore attuale di cursori aperti e massimo settato.
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;
SQL 3- Total cursors open by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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'
group by s.username, s.machine
order by 1 desc;
SQL 4- Session Cache Cursor
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 = 'session cursor cache count';
SQL 5- Dettaglio Query
select sql.first_load_time,sql.last_load_time,
c.user_name, c.sid, sql.sql_text, sql.sql_fulltext
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id
and c.sid=:P_SID
order by 2 desc,1;
SQL 6- Dettaglio Session Cached Cursor
select cach.value cache_hits, prs.value all_parses,
prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= :P_SID and prs.sid= cach.sid;
SQL 7- Dettaglio session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic# and s.sid=a.sid and a.sid=:P_SID
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count';
SQL 8- Dettaglio possibili leaking
SELECT SQL_ID, max(SQL_TEXT), count(*)
FROM v$open_cursor
WHERE sid = :SID
GROUP BY SQL_ID
HAVING COUNT(*) > 1
ORDER BY 3 DESC;
SQL 9- Dettaglio SQL_TEXT Cursori Aperti
SELECT a.user_name,
a.sid,
a.sql_text
FROM v$open_cursor a
where a.sid=:P_SID
ORDER BY 1,2;
SQL 10- Dettaglio SQL x SID
SELECT oc.user_name, oc.sid,st.sql_text
FROM v$sqltext st,
v$open_cursor oc
WHERE st.address = oc.address
AND st.hash_value = oc.hash_value
AND oc.sid = :P_SID
ORDER BY st.address, st.piece;
SQL 11- Dettaglio Session Management
SELECT
s.sid sid
, s.serial# serial_id
, lpad(s.status,9) session_status
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, lpad(p.spid,7) os_pid
, s.program session_program
, lpad(s.machine,14) session_machine
, sstat.value open_cursors
, ROUND((sstat.value/u.value)*100) open_pct
FROM
v$process p
, v$session s
, v$sesstat sstat
, v$statname statname
, (select name, value
from v$parameter) u
WHERE
p.addr (+) = s.paddr
AND s.sid = sstat.sid
AND s.sid=:P_SID
AND statname.statistic# = sstat.statistic#
AND statname.name = 'opened cursors current'
AND u.name = 'open_cursors'
ORDER BY open_cursors DESC;
