Re: Why is UGA size so large?

> If so, how can I distinguish between open cursors that are just
cached
> (PL/SQL Cache or Session Cached Cursors) and Cursors that are still
> open in the application?

As you suspect, cursors should be opening and closed constantly.

What I would do was watch the open cursor tables to see if the # of cursors was going below a certain threshold. For example, if the # of cursors goes up and down, but never falls below 12, then I have a good idea that those 12 cursors are not being closed properly.

For example, I would use the following two SQL statements in 8i:

· SELECT user_name, sid, count(*) FROM v$open_cursor GROUP BY user_name, sid;
· SELECT * FROM V_$OPEN_CURSOR WHERE USER_NAME LIKE 'SCOTT%' ORDER BY SID; You could then look at the actual SQL for that cursor and see if it is changing. I don't remember of the top of my head, but I believe it is the V$SQL_TEXT table. If the SQL text is not changing over a long period of time (depending on the speed of your system), then you can figure that it is not being closed properly. On our system, if a SQL lasted for more than a few seconds then I knew something was wrong. Received on Fri Apr 01 2005 - 05:36:24 CST