How to query cursor in Oracle

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;

Recommended tutorial: "

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!