Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Meaning of v$sqlarea.users_opening

Meaning of v$sqlarea.users_opening

From: Mark Anderson <fnmpa_at_uaf.edu>
Date: Fri, 30 Nov 2007 22:01:10 -0900 (AKST)
Message-ID: <2230.137.229.7.101.1196492470.squirrel@fnmpa.email.uaf.edu>


I wish to use the "Shared Pool Minimium Size Calculator" script in Metalink Note 105813.1 to find out how the shared pool of an Enterprise 9.2.0.8.0 database is divided up between objects like packages and views, SQL, and a third component that I don't understand very well, per-user per-cursor memory. The Calculator computes per-user per-cursor memory as

select sum(250*users_opening) from v$sqlarea;

I run the Calculator script every ten minutes through the week. The values of the first two components start low when the instance is restarted after its weekly cold backup and shortly level off. The per-user per-cursor memory value climbs continuously through the week.

I thought I understood that the query above returns a value of 250 bytes for each concurrent user for each cursor that the user has open. But the continuously rising value returned by the query suggests that the database is experiencing some combination of a continuously rising number of database connections and/or a continuously rising number of open cursors per connection. That should not be the case in this database. Is my understanding of v$sqlarea.users_opening incorrect? Doesn't it represent the number of open cursors for a given shared SQL?

N.B. The underlying intent here, in case that helps, is to deliberately undersize the shared pool of a test database to "just large enough" so that load tests will quickly place it under stress, but we will not be hampered by continuous ORA-4031s.

Thank you,

Mark Anderson
University of Alaska

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Dec 01 2007 - 01:01:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US