Query method: 1. Use "select count(*) from v$open_cursor"; 2. Use "SELECT A.USER_NAME,COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME".
The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.
1. Check the number of system cursors (maximum number of cursors)
select value from v$parameter where name = 'open_cursors';show parameter open_cursors;
2. Check the number of currently open cursors
select count(*) from v$open_cursor;
##3. Check the cursor usage
select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'GLOGOWNER' and o.sid = s.sid group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type order by num_curs desc;
4. Modify the maximum number of cursors in Oracle
according to Cursor occupancy: Analyze whether the program that accesses the database is releasing resources normally. If there is no problem with the program releasing resources, increase the number of cursors.alter system set open_cursors=2000 scope=both;
5. Total number of open cursors for each user
SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;
6. Find the number of cached cursors for each terminal of each user in the database
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME 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') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;
7. Find the number of open cursors for each terminal of each user in the database
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME 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') AA GROUP BY AA.USERNAME, AA.MACHINE ORDER BY AA.USERNAME, AA.MACHINE;
Oracle Tutorial》
The above is the detailed content of How to query cursor in Oracle. For more information, please follow other related articles on the PHP Chinese website!