Home>Article>Database> How to query cursor in Oracle

How to query cursor in Oracle

青灯夜游
青灯夜游 Original
2022-01-25 17:53:09 9952browse

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".

How to query cursor in Oracle

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;

How to query cursor in Oracle


2. Check the number of currently open cursors

select count(*) from v$open_cursor;

How to query cursor in Oracle


##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;

How to query cursor in Oracle


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;

How to query cursor in Oracle


5. Total number of open cursors for each user

SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;

How to query cursor in Oracle


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;

How to query cursor in Oracle


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;

How to query cursor in Oracle

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn