open cursors

on November 11, 2008 at 4:17 pm
Posted In: Oracle DB and SQL performance analysis (Cat)

set pages 200
set lines 150
col program format A30

–select * from gv$sysstat where name = ‘opened cursors current’;

select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
count(*) CNT, b.inst_id
from gv$open_cursor a, gv$session b
where a.saddr=b.saddr
group by b.inst_id,substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30)
order by 3 asc
;

accept SIDIN  number prompt ” Enter SID to acquire open cursors for: “

/*
select sql_text, inst_id, user_name from gv$open_cursor where SID=
(
select sid from (
select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
count(*) CNT
from gv$open_cursor a, gv$session b
where a.saddr=b.saddr
group by substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30)
order by 3 asc)
where rownum = 1
)
 ;
*/

 

select sql_text, inst_id, user_name from gv$open_cursor where SID=&SIDIN
 ;

/*
QL> desc gv$open_cursor
 Name                                                                                Null?    Type
 ———————————————————————————– ——– ——————————————————–
 INST_ID                                                                                      NUMBER
 SADDR                                                                                        RAW(8)
 SID                                                                                          NUMBER
 USER_NAME                                                                                    VARCHAR2(30)
 ADDRESS                                                                                      RAW(8)
 HASH_VALUE                                                                                   NUMBER
 SQL_ID                                                                                       VARCHAR2(13)
 SQL_TEXT                                                                                     VARCHAR2(60)

–select sql_text, inst_id, user_name from gv$open_cursor;
*/

exit